Usage Tracking

Contents[Hide]

1. Overview

When Dundas BI users go to view a dashboard, report, or scorecard, some statistics are recorded in the Dundas BI Application database. If you're a Dundas BI Developer, you can create a data cube to process these statistics into a more user-friendly format and ultimately display this usage information with a chart or table visualization.

The recorded statistics keep track of the dashboard (or report/scorecard) being accessed, the user who is accessing the dashboard, and the time and duration of access. Based on this core data, it is possible to derive additional usage information such as;

  • Number of times a dashboard is viewed
  • Average time spent viewing a dashboard
  • Time of day usage patterns for a dashboard

Tip
Administrators can install and use the Usage Tracking Dashboard sample which is included with the Deployment application.

2. Enabling tracking

Tracking of view usage is enabled by default in a Dundas BI instance. To view or change the current setting, go to the Admin Config screen, click the Show Advanced Settings checkbox, and then scroll down to find these two configuration settings:

  • View Usage Tracking Data Maximum Age - Tracking data is stored by default up to 30 days
  • View Usage Tracking Enabled - This is set to True by default

 

View usage configuration settings
View usage configuration settings

3. Dundas BI Application database

3.1. vUsageTracking

When tracking is enabled, usage statistics are recorded in the Dundas BI Application database.

For example, if your Dundas BI instance is named Instance1, the corresponding SQL Server database would be Dundas BI Instance1.

The usage data can be accessed from the database view, dbo.vUsageTracking, which has the following columns:

ColumnDescription
 ViewId The ID of the dashboard/report/scorecard being viewed.
 SessionId The ID of the user's login session.
 ClientId This ID identifies the user's web browser.
 VisitId A unique ID identifying this tracking event.
 AccountId The ID of the user (account) viewing this dashboard/report/scorecard.
 AccountName The username of the user (account) viewing this dashboard/report/scorecard.
 EntryTime The UTC date and time the viewing began.
 LastSeenTime The UTC date and time the viewing ended.
 TotalSeconds How long the dashboard/report/scorecard has been viewed so far (in seconds).

The database view containing the tracking data (SQL Server Management Studio)
The database view containing the tracking data (SQL Server Management Studio)

Note
If a user returns to view a dashboard more than 5 minutes later than their last seen time but still within the same session, a new tracking event (and VisitId) is created.

3.2. FileSystemEntry

The vUsageTracking database view gives you the ID of a dashboard/report/scorecard that is being accessed. In order to extract more user-friendly information, such as the name of a dashboard, you need to also use the FileSystemEntry table in the Dundas BI Application database.

Using the FileSystemEntry table, you can look up the names of files in the Explore window based on their IDs. Files such as dashboards, projects, data cubes, and metric sets can all be found in this table. You can determine the ID of any file in the Explore window or Admin File Explorer by right-clicking on the file and opening its Properties dialog.

Below are some of the columns from the FileSystemEntry table:

ColumnDescription
Id The ID of a file such as a dashboard, report, data cube, or metric set.
Name The name of the file (e.g., dashboard name, report name, data connector name).
ProjectId The ID of the project containing this file. You can look up the ID in this same table in order to get the name of the project.

Important
This table and others in the Application database are critical to the operation of Dundas BI. Do not modify these tables.

4. Examples

4.1. Build a data cube to show usage information

The following example shows you how to create a data cube that combines the vUsageTracking data with the FileSystemEntry table in order to show more user-friendly usage information.

Here are the detailed steps:

  1. Create a new data connector using the SQL Server provider to connect to the Dundas BI Application database.
  2. Create a new data cube using the Blank option.
  3. Go to the Explore window, expand your data connector, and then expand its Views folder.
  4. Drag the [dbo].[vUsageTracking] view to the data cube designer canvas. It will appear as a SQL Select transform that is connected to a Process Result transform.
  5. Select the connection link between the two transforms, go to the toolbar, click Insert Common, and then select Join. This will insert a Join transform between the two transforms.
  6. Go to the Explore window and drag the [dbo].[FileSystemEntry] table to the canvas. It will appear as a 2nd SQL Select transform.
  7. Connect the 2nd SQL Select transform to the join transform, and then double click the Join transform to configure it.
  8. In the Join configuration dialog, drag the ViewId element on the left and drop onto the Id element on the right.
  9. In the left table, select these columns:
    • AccountName
    • EntryTime
    • LastSeenTime
    • TotalSeconds
  10. In the right table, select these columns:
    • Name
    • ProjectId

Data cube for joining vUsageTracking with FileSystemEntry
Data cube for joining vUsageTracking with FileSystemEntry

Join transform configuration
Join transform configuration

The next few steps show you how to replace the ProjectId column with the project name instead:

  1. Select the last connection link, go to the toolbar, click Insert Common, and then select Lookup. This will insert a Lookup transform just before the Process Result.
  2. Go to the Explore window and drag another [dbo].[FileSystemEntry] table to the canvas. It will appear as a SQL Select transform. Connect this transform to the Lookup transform.
  3. Double-click the Lookup transform to open its configuration dialog. Drag the ProjectId element from the left table and drop it onto the Id element in the right table. De-select the ProjectId element in the left table. Set the Lookup Element dropdown to Name. Click the (X) to close the dialog.
  4. Open the Lookup configuration dialog again, scroll down, and then click Rename output elements. You'll see two Name columns in the output. Rename the first one to DashboardName, and the second one to ProjectName.

Lookup transform configuration
Lookup transform configuration

Here is the Data Preview for the final data cube:

Data Preview for final data cube
Data Preview for final data cube

You can now easily display this information on a dashboard using a table visualization.

4.2. Show how many times each dashboard has been viewed

Using the data cube created previously, it is easy to set up a chart to display the number of views per dashboard:

  1. Create a new metric set.
  2. Expand the data cube in the Explore window and drag the DashboardName dimension to the canvas.
  3. In the Metric Set panel, click the click to add link in the MEASURES field. From the list of available elements, click <Count> under CALCULATED ELEMENTS. This will add a count measure to the metric set.
  4. Go to the toolbar and re-visualize the metric set as a bar chart.

Click to add a measure to the metric set
Click to add a measure to the metric set

Add a count measure to the metric set
Add a count measure to the metric set

Re-visualize metric set as a bar chart
Re-visualize metric set as a bar chart

5. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri