Creating a Custom Usage Analytics dashboard

[Astroraf]

I want to utilize the Usage Analytics Dashboard that will give me these key insights/KPIs:

Metric	Description
Views per day	Total number of views per day per dashboard
Views per user	Number of views in the past 365 days, broken down by individual users.
Total views	Number of views in the past 365 days.
Total in organization	Count all dashboards or reports in the entire organization that had at least one view in the past 365 days.
Rank: Total views	For total views of all dashboards or reports in the organization over the past 365 days, where does this dashboard or report rank.

Is this achievable with the current Usage Analytics Elasticube model? 

@DRay 

[DRay]

Hi @Astroraf,

Not that I'm able to find. If you like, I can connect you with a technical resource to help you build this dashboard.

[MikeGre]

@Astroraf you can adjust the DimUsers table in the Usage Analytics Model, by adding a new column to group your users by domain or any other split you desire using CASE WHEN conditions.

The rest are calculatable given that views = actionids 🙂 

Hope it helps!

[AssafHanina]

Hey @Astroraf ,

The information is available in the Usage Analytics data model, and the recommended approach is to create a new dashboard. 

Setting Up Retention Days for 365 Days in Admin Tab --> Monitoring & Support --> Usage Analytics.
Retention Days Notes 

• Increase the amount of Files in the File management. 
• Longer Build Time
• Larger Elasticube 

Usage Analytics Main tables (dashboard Related):

1. Usage Table - Collect the actual Usage per Day, User, Dashboard 
2. FactActions - A custom table built based on the Usage table. Main Fact Table used for the Aggregations
3. DimDashboards
4. DimUsers

Dashboard Main Metrics

• Add Dashboard filter: Category = 'Analytics' (to filter only Analytics Usage) 
• Main Metric is the ActionID which can group by DimUsers.UserName, FactActions.TimeStamp
• Additional Metric is Count Unique FactActions.DashboardID
  (the Usage store all the dashboards which Used which shall cover the 1 dashboard in used)
• Rank: Total views - Use the Dashboard Rank function

Please find additional information from the Usage Analytics Documentation

Best Regards

[Astroraf · Accepted solution]

This is what I came up with regarding the KPIs that I was trying to achieve. I created a subquery to capture the number of views per dashboard based on the action 'dashboard. loads'. I ran the subquery from the DimDashboards dimensional table that has already been created and added a value called "Dashboardloads".

SELECT distinct o.DashboardID AS [Dashboard ID],
                o.dashboardPath AS [Dashboard Path],
                CASE WHEN StrParts(o.dashboardPath,'/',1) like '' THEN 'null'
                ELSE StrParts(o.dashboardPath,'/',1) END  AS [Dashboard Folder],
                o.dashboardTitle AS [Dashboard Name],
                (SELECT Count(*) FROM FactActions WHERE FactActions.[Dashboard ID] = o.DashboardID AND FactActions.[Action] = 'dashboard.load') AS Dashboardloads
                FROM [usage] o
                WHERE o.[category] = 'Analytics'

The subquery:

(SELECT Count(*) 
FROM FactActions 
WHERE FactActions.[Dashboard ID] = o.DashboardID 
AND FactActions.[Action] = 'dashboard.load') AS Dashboardloads

Queries from FactActions where we have the DashboardID equals that DashboardId from Usage and the Action equates to 'dashboard.load'. From there I was able to use the RANK function from Sisense to see which Dashboards rank the highest. 

Then I created another dimensional table called "FactUserDashboardLoadCount" where the query:

SELECT fa.[Dashboard ID],
    dd.[Dashboard Name],
    fa.[User ID],
    dm.[User Name],
    (SELECT COUNT(FactActions.[Dashboard ID]) FROM FactActions WHERE FactActions.[Dashboard ID] = fa.[Dashboard ID] AND FactActions.[User ID] = fa.[User ID] AND FactActions.[Action] = 'dashboard.load' ) AS LoadCount
FROM FactActions fa
LEFT JOIN DimUsers dm on fa.[User ID] = dm.[User ID]
LEFT JOIN DimDashboards dd on fa.[Dashboard ID] = dd.[Dashboard ID]
GROUP BY fa.[Dashboard ID], fa.[User ID], dm.[User Name], dd.[Dashboard Name]

Which counts all the dashboards, that have a Dashboard ID, User ID, and 'dashboard.load' to determine how many times a user has viewed a specific dashboard. 

I believe this answers my questions and any feedback is welcomed. 

[AssafHanina]

Hey @Astroraf ,

The Custom Tables should provide the questions mentioned.

Few Points which can be added:

1. Filter on Different Usage Date time 
2. Dashboard Load Time 

Best Regards