Support/MetricsDashboardPRD/implementation

Design/Functionality

• Use jQuery for tabs & etc: simple, efficient, portable

How it works

• Create queries
• Use special syntax to output query result in a report (= tab)
• Dashboard page fetches list of reports (= tabs) associated with it

Create new queries

• Set query source and text/code
• Set how often each query needs to run
• Set date ranges to offer
• consider SQL query versus a query builder or some kind of query helper
  • query helper would show database structure and comments on tables and columns to help with building the SQL
  • could optionally include Omniture API
• Preview option. E.g sample result returned
  • this counts towards bulding output. E.g click "Preview" and the output options will be customized to show a sample for previewed data
• How far back in time to keep reports (requires separate cleanup script)

Generate report

• Easy markup for generating report text
• Use data from multiple queries
• Comparison with past results + absolute value
• Single "row": columns + type per column
  • Types: string, integer, percentage, floating point
  • Additional operations to perform on the data
• List of "rows": same settings as above + limit # of rows
  • Combine or compute results of different rows (e.g. for ratio of votes "article easy to understand" calculated from: number of yes/no votes)
• Format specific to type of output
• Types of output
  • Pie chart / bar chart
  • Histogram
  • Sparklines
• need to decide which software to use - openflashchart? timeline/timeplot?
• Consider using ideas form my qmo_bugzilla module at [[1]]
  • Mapping feature is easy to use (+), but has learning curve (-) and is not as flexible as some reports might require (-)

Create page

• Dashboard page
  • Format layout for a page of multiple reports -- e.g. define block structure
  • Tabs, drop downs
  • 1 tab = 1 report
• (future) Have generic .tpl file for individual report
• (future) Create .tpl files for different types of dashboard pages

Implementation

Architecture

• Layer to pull data from to-be-created metrics DB to match the requirements at [[2]].
  • Extra layer to cache data with an optional preference for caching level
• Layer for cron file(s) of various scripts to pull necessary data
• The above two layers organized into folders source of report
  • TikiWiki specific
  • Omniture specific
• Benefit of the above approach is to abstract getting data implementation from retrieving data implementation (eliminate dependency on the source of the data)

PHP structure

• separate TikiWiki and Omniture folders

Storage structure

• XML vs SQL DB?
• Probably best to use separate DB for metrics. Same DB as site would affect performance.
• DB table structure:
  • source table - may be unnecessary
    • contains information about how to connect to different sources. e.g. db connection for getting TikiWiki and Omniture info
  • query table
    • purpose: stores the different queries to be run. proposed structure:

 id | query | source (foreign key: id from source table) | last run | last changed

• • result table
    • purpose: stores different results. proposed structure:

 id
 query_id (foreign key: id from query table)
 datetime run
 results (serialized array)

• • report table
    • purpose: stores generated user reports. proposed structure:

 id
 uid (created by)
 date created
 last changed
 date ranges (can be limited by query report)
 other report options
 keep history

• • queryreport table
    • purpose: stores queries associated with reports. proposed structure:

 id
 query_id
 report_id
 frequency (date ranges)
 output formatting (i, %, f, list, custom array)