Tuesday, October 13, 2020

Reduce Query Runtime by 95% with Tabular Performance Analysis on Power BI


Business Case:

Our client, a large software company, has an extended partner network across multiple geographies. To track partner performance, our client uses SQL Server Analysis Services (SSAS) tabular models. Tabular models are databases that contain measures (numeric values associated with a company’s goals). On average, 33% of our client’s projects rely on tabular measures, and over 1,000 team members use tabular models daily. Approximately 91,000 queries are fired each day. High volumes of queries increase load on the server, causing poor tabular performance.

Users with limited experience were firing cross-joins (an ineffective way of combining data from multiple columns) on the tabular models, which increased query runtime to up to 2 hours. To help our client reduce tabular query runtime and increase productivity, we needed to design a solution that could identify performance gaps in real time.

Key Challenges:

  Monitor tabular load time and queries 
  Identify performance gaps in real time  
  Eliminate query cross-joining  

Our Solution:

We designed a tool that monitors the performance of tabular measures and queries.  

Figure 1: Solution Architecture

Business analysts can use our tool to identify performance gaps and to remove unnecessary measures that increase query runtime.  

Submitted requests are converted into Azure DevOps work items (actions) using Logic Apps. All actions are stored in a single DevOps workspace that can be accessed from anywhere in the world. We were able to further customize user experience using custom rules. Users are notified when actions are created or assigned to them; actions left unresolved for a certain period are automatically escalated to sales managers.  

An on-premises stored procedure provides the measure and its related dimensions. Simultaneously, a SSAS structured query language (SQL) profiler gathers the following data:  
1.  Query description 
2.  Server and application details from the location the query is triggered 
3.  Who triggered the query 
4.  Time to execute the query 

The data is pushed through Azure Data Factory and Azure Data Lake Storage to Azure Databricks, where it is consolidated and formatted. Finally, the data is extracted from Databricks and displayed to the end user in Power BI. The dashboard visualizes measure execution details, and measure details, such as data analysis expressions (DAX) and measure group name. The dashboard provides several tabs to organize information:  

Measure Details: Provides a detailed list of measures that are used and unused in the given timeframe. With this view, users can identify and delete unused measures. 

Figure 2: Measure Details Tab of Measure Execution Dashboard

Adhoc Query Details:  
  Queries: Identifies frequently used queries, enabling developers to optimize or retire measures. 
  Tabular: Identifies frequently used tabular models, enabling developers to optimize and retire unused tabular models. 

Figure 3: Adhoc Query Details Tab of Measure Execution Dashboard

With Dimensions/Without Dimensions: Automatically analyzes all measures and measure combinations to highlight which measures have a high execution time. 

Figure 4: With Dimensions Tab of Measure Execution Dashboard

Business Outcomes:

Our solution enabled our client to identify performance gaps and optimize query executions. We reduced query runtime up to 95%, eliminated time-consuming query cross-joining, and enabled our client to retire unused measures 

Highlights:

    Designed a tool that monitors performance of tabular measures and queries.
    Visualized performance gaps, enabling our client to retire unused measures and optimize executions.
    Reduced query runtime up to 95%
    Eliminated time-consuming query cross-joining