Support/MetricsDashboardPRD/implementation

	THIS PAGE IS A WORKING DRAFT
	The page may be difficult to navigate, and some information on its subject might be incomplete and/or evolving rapidly. If you have any questions or ideas, please add them as a new topic on the discussion page.

Simple tutorial

This is included first because, as a developer, my technical skills of understanding and explaining are not always intuitive, so it's better to walk through this example before you read the technical aspects below. Once that is done, you should read the rest of this document and re-do the tutorial, by correlating the steps with the information below.

Create a simple metric

• Go to /tiki-admin_metrics.php (you will need admin rights for this)
• Click on "Edit/Create Metric" at the top links, you will be taken down the page to /tiki-admin_metrics.php#editcreate
• Enter the following information:

Name: # new KB posts Range: Weekly Datatype: Integer Query:

SELECT SUM(measure) AS count, DATE(date_field) AS date_field

FROM f_events NATURAL JOIN d_date 
  NATURAL JOIN d_eventtype

WHERE $date_range$ AND sourceType = 'knowledge base'
   AND actionType = 'create' $range_groupby$ 
   ORDER BY date_field DESC;

• Click on the "Create new metric" button below this form
• Once the form is submitted, notice the new metric shows up in the list at the top under the heading "Metrics"

Create a tab

• Click on "Edit/Create Tab" at the top links, you will be taken down the page to /tiki-admin_metrics.php#editcreatetab
• Enter the following information:

Name: Overview Order: 1 Content:

{metric name="# new KB posts" value="count" sparkline=true toggle=true  table=true trend=true}

• Click on the "Create new tab" button below this form
• Once the form is submitted, notice the new tab shows up in the list under the heading "Tabs"

Assign the metric to the tab

Now that you have a metric and a tab, it's time to assign the former to the latter.

• Click on "Assign Metric" at the top links, you will be taken down the page to /tiki-admin_metrics.php#assign
• Select the metric name "# new KB posts" and the tab "Overview"
• Click on the "Assign" button below this form
• Once the form is submitted, notice the new assigned metric shows up in the list under the heading "Assigned Metrics"

CONGRATULATIONS! You have just created a simple metrics tab!

Dashboard UI

Overview

Go to:

 /tiki-metrics.php

• No sidebar for more room
• Uses JQuery, JQuery UI and JQuery Sparklines
• Tabs fetch content through AJAX from /metrics-tab.php
• Parameters passed to /metrics-tab.php:
  • tab_id (integer)
  • date range in format yyyy-mm-dd, date_from and date_to
  • range_type - weekly, monthly, custom

Administration

Preferences

Go to:

 /tiki-admin?page=metrics

• Show past results + count => how many results to track back on. This number affects the date range for a query.
• Metrics Database => defaults to sumodw, the data warehouse where we keep all of the metrics tables

Editing Content

Go to:

 /tiki-admin_metrics.php

Creating metrics

Metrics have the following fields:

• Name - gives the name by which metrics can be retrieved in tab content
• Range - (more clearly, range type) how often this metric is to be collected, how it should be treated (not currently used in implementation, but available)
• Data type - what type of data this metric returns
• Query - the SQL query that will be run. MUST contain these tokens:
  • $date_range$ - must be placed in the WHERE clause, will be automatically replaced with something like:

 (date_field >= date_from AND date_field <= date_to)

• • $range_groupby$ - must be placed in the GROUP BY clause. If there isn't one, the $range_groupby$ token will contain the "GROUP BY" string. This looks like:

 GROUP BY WEEK(date_field,1) -- for weekly display
 GROUP BY MONTH(date_field) -- for monthly display
 GROUP BY date_field -- for custom display

• • • Note: the $range_groupby$ token does not reflect the metric range type. Instead, it reflects the selected date range in the dashboard display

Creating tabs

Tabs have the following fields:

• Name - gives the name of the tab, displayed on the dashboard
• Order - gives the order of the tab in the display (tabs are sorted ascendingly by this)
• Tab content - smarty content that will be parsed in metrics-tab.php. This, too, may contain some functions
  • The metrics are created using a smarty {metric} function, with the following syntax:

/**
 * Smarty function for getting a metric. Syntax:
 * {metric 
 * [sparkline|trend|table|toggle][=true]
 * [name="name from metrics_metric"
 * id="id from metrics_metric"] (one of name and id required)
 * numrows = # of data pts to go back (max # of row from SQL query)
 * value = "name of field in SQL query to display"
 * date_field = "date column in SQL to get dates from"
 * html_before (optional) = "HTMl before table"
 * html_after (optional) = "HTML after table"
 * }
 * @param string name (required if id not specified) metric name to get
 * @param integer id (required if name not specified) metric id to get
 * @param string title (optional) title to display, defaults to name
 * @param integer numrows (optional) number of data points to show in the sparkline
 * 		also number of rows to show in the table, defaults to all if not specified
 * @param string value name of field in SQL query to display
 * @param string date_field (optional) name of field in SQL query to get dates from
 * 		defaults to "date_field"
 * @param string html_before (optional) HTMl before table
 * @param string html_after (optional) HTML after table
 *
 * @param boolean sparkline defaults to false, specify "sparkline" 
 * 		or "sparkline=true" to enable
 * @param boolean trend defaults to true, specify "sparkline" 
 * 		or "sparkline=false" to disable
 * @param boolean table defaults to true, specify "table" 
 * 		or "table=false" to disable
 * @param boolean toggle defaults to false, specify "toggle" 
 * 		or "toggle=true" to enable
 * @return HTML formatted for metric
 */

• • Include option=true if you want option to show up, where option is one of: sparkline, trend, toggle, table

Assigning metrics to tabs

This process is fairly intuitive. When you assign a metric to a tab, it just means that the query for that metric will be run on that specific tab. Of course, since the tab content is customized, you can just assign all metrics everywhere. But the purpose of tabs grabbed through AJAX and assigning metrics to tabs is to ease the query load and decrease page loading time. So it's best practice to assign to each tab only the metrics that are actually going to be displayed.

How it all works

• tiki-metrics.php calculates the date range, and range type. It passes these parameters to metrics-tab.php for each tab
• metrics-tab.php grabs the tab content, runs it through smarty with the given metrics functions, and returns HTML output that then populates the tab
• metrics-tab also populates the arrays $m and $m_id, both associative arrays which contain the metrics data in the following format:

 $metric['result']

The query result, an array of rows with data returned by the metric query. This is an array of results of the SQL query. E.g. $metric['result'][0]['count'] will contain the actual count displayed result for the function:

{metric name="# new KB posts" value="column name" }

And $metric['result'][1]['column name'] will contain the first past result, etc.

 $metric['range']

The actual range in English (Weekly, Monthly, etc)

 $metric['range_id']

The range id (the value displayed in parentheses on the admin page)

 $metric['datatype']

The data type (Integer, Float, etc)

 $metric['datatype_id']

The data id (similar to range id, displayed in parentheses)

• $m['insert metric name here'] contains the above data (allows addressing metrics by name)
• $m_id[metric_id(integer)] contains the above data as well (allows addressing metrics by id)

These values can be fetched by using:

 {metric expr="@m['# new KB posts']['range_id']"}

The above gets the range_id of a metric named "# new KB posts" (case sensitive!)

Feedback

[paulc] Please add your feedback here.