TOP 3 Tips for Faster Power BI Reports

The best practices for Fact/ Dim relations within Tabular models are based on the Kimball DW design. This design has been integrated into the VertiPaq compute engine in a manner that maximizes performance over large datasets and many dimensions and is usable by business users as well as advanced technical resources.

While the flexibility of the VertiPaq engine enables usage of almost any data model, there are specific techniques within Kimball that can ensure the highest performance and ease of implementation within this compute engine.

TIP #1 INTEGER BASED COLUMN STORE

The VertiPaq engine is built around and highly optimized for integers. This has led to the following best practices within the largest and highest performing models:

• Fact Tables only contain Integer Values
• All joins are Integer Based (recent updates may have tuned to enable GUID based joins to perform as effectively- I have not seen this in action)
• Date joins as Integers in the format of yyyymmdd have been highly optimized and tuned for very complex and sophisticated time-series analytics (more on this later)
• Minimize the size of string values (even in dimensions) as much as possible- 255 is good, 127 is better, 31-63 is better still
• Note- there are new features and new techniques that would enable people to ignore these string limitations. These are very new and proper guidance is not widely known and usage is not obvious to people. We will be able to easily deal with this in the future.

TIP #2 DIMENSIONAL RELATIONS

The way dimensions are broken out in-memory and the calculations are stored in Cache based upon the joins and best performance can be derived from optimizing this cache. The cache uses concepts of HOT and COLD for where and how the data is stored, with HOT cache providing the fastest responses and COLD taking a little longer. To maximize what can remain in HOT cache, we follow the following best practices:

• Integer based Joins
• Minimize Cardinality of Dimensions
• Highest performance is obtained by keeping Cardinality of a DIM sub 127k (above this performance will start to degrade)
• Move Type 2 attributes to Fact Tables and separate into a different dimension

TIP #3 DATE DIMENSION

The Date Dimension within the VertiPaq engine has been specifically and highly tuned for time-based analytics to the point where there is no other engine that offers the same level of dynamic time-based analysis to business units and data scientists alike. If you are unfamiliar with what the Tabular model can do with Time based analytics, I highly recommend checking out the many articles Marco Russo has published on time intelligence (https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/)

• Date Dimension based off of Datekey integer join of yyyymmdd
• Dynamic calculations and breakouts by Date Dim and features and functions within the dimension
• A standard DateDim makes enabling these features much easier
• This DateDim can also be built to minimize data refreshes, partition cubes and reduce our overall cloud spend while improving performance.