Build logs tracking - advanced usage builds

[AssafHanina]

Build logs tracking - advanced usage builds

Background

Elasticube are widely used in data modeling. However, there are instances where tracking Elasticube from various perspectives becomes essential, such as monitoring total builds, total tables, total rows, error messages, and more.

Sisense provides the Usage Analytics which collects information about the Builds. 

The Usage Analytics has 2 related dashboards which track Builds:

• Build Usage Overview: Provides insights for data admins into the building usage at a high-level and identifies usage and/or performance anomalies - Daily average build, success rate, how many cubes are built daily, and performance.
• Build Usage Failures: Provides insights into build failures to help you detect the cause of the failures quickly, such as specific cubes not working, overload to the system, or errors in the system

Usage Analytics Build Schema

Dashboard MetricsThe 
The builds overview dashboard provides an overview of the Total Builds, Build Errors, and Builds logs.

Build Logs

• Track total builds, Count of Unique Data model builds, and average duration
• Data points Break by Data model, Build Status, Start Build Date
• Tracking each build per Data model by status, total tables, columns, duration, Total Rows

Screenshots Disclaimer: The data model name was removed from the screenshots only.

Build Errors Overview

• Filter on build status - 'Failed'  
• Overview of build errors by category and error message 
• Summary Error by for each build-id
  
  

Build Logs Tracking

• Overview of the build logs, in focus on Base tables, Custom tables, and Custom Columns
• Summary table for Base tables and Custom tables
• Summary table for Custom Columns
  
  

Implementation Requirements

1. Enable Usage Analytics 
2. Set up the Retention days 
3. Navigate to the Data tab and Build Usage Analytics data model 
4. Make sure the Builds table is available (as in the screenshot). 
   In case the builds table is not available, refer to the related directory in the Usage directory in the file management

Implementation
The Main tables from Usage Analytics is the FinishBuild and SummarizeBuild which transform into Custom tables to build the Sub model. 

1. Create 2 Custom tables:
   - Dim_finish_builds: Mandatory table.
   - Fact_builds_logs: Mandatory table.
2. Create a relation between Dim_finish_builds AND Fact_builds_logs ON Build_id
3. Run Manual Build
4. Create a Custom table: Dim_dup_check - Hidden table, Set for validation of duplicate columns
   

   select 
       'Dim_finish_builds' as table_name,
       count(d.[build_id]) as count_all,
       count(distinct d.[build_id]) as count_distinct,
       sum(case when d.[build_id] is null then 1 else 0 end) as total_nulls
   from [Dim_finish_builds] d​

5. Preview Dim_dup_check and make sure the data is unique. Meaning:
   - Count_all equal to Count_unique
   - total_nulls equal to 0
   
   
6. Download the BuildLogsOverview file and Extract the Dash file.
7. Upload BuildLogsOverview dash file. 

The Final Model

Best Regards,

Assaf

[DmytroB]

Really helpful and easy imporvment of basic UA functionality!  Thank you, Assaf!