When Dundas BI users 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
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
3. Dundas BI application database
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 application database would be Dundas BI Instance1.
The usage data can be accessed from the database view, dbo.vUsageTracking, which has the following columns:
|ViewId||The ID of the dashboard/report/scorecard being viewed.|
|SessionId||The ID of the user's logon 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).|
3.2. FileSystemEntry table
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:
|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.|
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:
- Create a new data connector using the SQL Server provider to connect to the Dundas BI Application database.
- Create a new data cube using the Blank option.
- Go to the Explore window, expand your data connector, and then expand its Views folder.
- 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.
- 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.
- Go to the Explore window and drag the [dbo].[FileSystemEntry] table to the canvas. It will appear as a 2nd SQL Select transform.
- Connect the 2nd SQL Select transform to the join transform, and then double click the Join transform to configure it.
- In the Join configuration dialog, drag the ViewId element on the left and drop onto the Id element on the right.
- In the left table, select these columns:
- In the right table, select these columns:
The next few steps show you how to replace the ProjectId column with the project name instead:
- 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.
- 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.
- 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.
- 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.
Here is the Data Preview for the 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:
- Create a new metric set.
- Expand the data cube in the Explore window and drag the DashboardName dimension to the canvas.
- 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.
- Go to the toolbar and re-visualize the metric set as a bar chart.