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

ALT Text: A diagram displaying a network of nodes connected by lines. The central node, labeled "SummarizeBuild," is in yellow and surrounded by several other nodes, including "FinishBuild" also in yellow, and others in lighter colors such as "DimCubes" and "ConnectorsInBuilds." The nodes represent various functions or components related to a building process, indicated by their respective labels.

Dashboard Metrics
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.

ALT Text: A dashboard displaying the "Builds Overview." It includes four panels: total builds (521), total build demands (13), average build time (0.9 minutes), and a graph showing total builds by status with a percentage (95%). The graph has bars representing daily, weekly, and quarterly data. The interface is sleek and features a navigation menu on the left.

Build Errors Overview

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

ALT Text: An overview of build errors displayed in a dashboard format. The image includes categories of errors represented by bar graphs on the left, indicating totals for each category, and messages for the most frequent errors on the right, shown as a list with corresponding counts. The bottom section summarizes detailed information about individual build errors, formatted as a table with various data points. The interface appears to facilitate tracking and resolving build issues.

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

ALT Text: A dashboard titled "Build Logs Tracking - Base/Custom Tables & Custom Columns." It features a pie chart displaying build process distribution, with 50% in gray and 45% in red. Below the chart, there is a detailed table summarizing build logs by custom tables, showing various column headers and corresponding data entries. The design includes visualizations and analytics to provide insights into build processes.

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
   
   
   ALT Text: A screenshot of a SQL query titled "Dim_day_check," displaying a SELECT statement that retrieves the table name, count of build IDs, count of distinct build IDs, and total nulls from the "tbl_finish_build" table. Below the query, a results table shows the statistics for "count_all," "count_distinct," and "total_nulls," with values listed for each. A note indicates that "Count All and Count Distinct" must be equal values and that total nulls should be equal to 0.
   
   
6. Download the BuildLogsOverview file and Extract the Dash file.
7. Upload BuildLogsOverview dash file. 

The Final Model

ALT text: A diagram featuring three circular nodes connected by lines. The top node is labeled "Dim_finish_builds," the right node is labeled "Fact_builds_logs," and the bottom node is labeled "Dim_dup_check." The nodes are colored in light blue against a light gray background.

Best Regards,

Assaf

[DmytroB]

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