Forum Discussion

Astroraf's avatar
Astroraf
Data Pipeline
10-21-2024
Solved

Creating a Custom Usage Analytics dashboard

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 

Dashboards & Reporting
Data Models
  • Astroraf's avatar
    Astroraf
    10-28-2024

    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. 

5 Replies

  • MikeGre's avatar
    MikeGre
    ETL
    10-23-2024

    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's avatar
    AssafHanina
    Sisense Employee
    10-28-2024

    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's avatar
    Astroraf
    Data Pipeline
    10-28-2024

    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's avatar
      AssafHanina
      Sisense Employee
      11-02-2024

      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

  • DRay's avatar
    DRay
    Admin
    10-22-2024

    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.