Performance Troubleshooting: Tabular Model in Power BI

For the last 2 years, I have been using Power BI as one of the core tools to provide different data insights for the top management. 

Together with my great team, I was constantly improving our key reporting tools. As in a classical way we were facing problems with the performance. The rising complexity reflects on the user's response and on the stability of report updates.

The Tabular model allows boosting both the performance and the speed of development of massive reports with complicated data models and too many DAX.  

Let us consider the use case and different ways of improving report performance including usage of the Tabular model.

About DataSet: 

• Classical OLAP schema - Snowflake 
• Size of fact table - over 40 mln rows
• Over 40 dimensional tables
• Data Connectivity Mode - Import
• Type of Connector - SQL Server Database

The report was hosted in Power BI Report Server. 

Ways of investigation and steps to improve the performance:

1. Migrate report from on-premise PBI RS to the Cloud Solution PBI Service. The benefits of PBI Service you can find here
2. Use built-in Performance Analyzer 
3. Apply Incremental refresh on a fact table
4. Rewrite all connectors on stored procedures instead of direct connection to the tables

All these steps allow us to improve a lot of features regarding our service:

1. Quality and diversity of interaction with the report: customized dashboards for each customer based on the same report, personal bookmarks by their personal needs, etc.
2. Accessibility to the report becomes more friendly - no need to connect via VPN
3. Data are more up-to-date - PBI Service allows to refresh 8 times per day  (of course it should be synchronized with the ETL process in the database)                                             

Still, the performance of the report and the speed of the user's response were not improved essentially. 

At the same time, the increasing number of customers and the variations of their requests permanently require fast and effective decisions. 

At the start, pbix file weighed approximately 250 MB. Later, the size jumped to 700 MB due to the massive extension of data and an increasing number of different hot topics that should be highlighted all in one place. To apply new changes of production might take over 30 mins. 

The main bottleneck - the constantly increasing number of rows in the dataset requires serious transformations.

In this case, we decided to take a look at the in-memory data engine that intends usage of Tabular model database. It requires another method of connecting the data - Live Connection. 

Power BI allows connection to data by 3 methods and at that time we used import data type of connectivity:

The advantages of usage Tabular data model:

• In-memory data engine that performs faster 
• The size of the Power BI file decreases essentially (in my case from 700 Mb to 4 Mb). Data is no more stored in the Power BI model. Power BI has only a visualization layer
• All processes regarding the changes in the data model are done on the server-side via Visual Studio. It allows to deploy changes in GIT and revert  them in case of any problem
• Tabular contains 'partitions' to speed up the process of refreshing data on server-side
• Refresh data changes that are set up on the server-side reflects at once on Power BI Service where the report is distributed for users. No need to set up scheduled refresh on Power Service

Here is presented a sample architecture:

In our case ETL process runs several times per day, after ETL process is successfully finished, it's time to process the data model in SSAS. Once it is processed, users automatically can see refreshed data in Power BI Report Service.

How to organize this architecture:

1. Set up ETL process with necessary jobs and notifications. For each both fact and dimensional tables there are separate 3 stored procedures - Extract, Transform and Load
2. Upload PBI Desktop data model into SSAS Tabular. Here is the hint on how to connect the data model from PBI into SSAS. Then, import it into SSAS. You need to make it one time at the start. Afterward, you will modify and improve the data model according to user's requests via Visual Studio
3. Once you have the data model in SSAS, you need to change the connection in the report from import to live (or from direct to live) 
4. Set up On Premise Gateway for Analysis Services connection

Once these steps are done, download Visual Studio Community. This will be the main tool for developing new changes regarding data modeling, DAX, and granting access. In the case of Live Connection type, the Power BI Desktop file has no longer Power Query functionality. 

Pay attention: DAX is still might be created in the Power BI Desktop file. DAX also might be created in Visual Studio. The difference is that all DAX that you have created in Power BI Desktop remain on the report level. All DAX that you have created in Visual Studio in the project, you might use it easily for other new reports while connecting to this data model without recreating them once again. 

How to organize work in Visual Studio:

1. Create a new project. To import existed one from the server, please select 'import', otherwise, if you want to create a new one, select 'Analysis Services Tabular Project'
   
   
   
2. If you have a prod and dev server, select one of them. Later in the settings of the project, you can set up both of them (Project ➝ Database Properties)
3. Set up Impersonate Account. Learn more
4. Customize database deployments for multiple environments 
   
   
   
5. Make necessary changes in the project: create new DAX and row filters, set up roles, add transformations in Power Query (Select Table ➝ Table Properties ➝ Design)
6. *Might be optional: create a partition to boost the processing phase
7. Build the solution 
8. Deploy Solution on the selected Prod/Dev Environments. Pay attention to the 'Processing Option' setting in the environment. If you don't have to process every time data while deploying, I would recommend selecting 'Do not Process'. Thus, the process will run much faster. Learn more
9. Commit the changes of 'model.bim' and related files of Tabular model in GIT

The positive result of transferring report from Import to Live connection (Tabular data model in SSAS):

1. Boost the performance of the report 2x
2. Use multiple environments to improve development workflow: instead of local testing in PBI Desktop, we created dev and prod environments 
3. Find out a new way of optimization the refreshing of data sources - partitions
4. Find out a new way of filtering data for customers according to row filters, which also boosts the time of the user's response

Recommended for reading: Tabular Modeling in Microsoft SQL Server Analysis Services by Marco Russo and Alberto Ferrari