Written by Aaron Campeau
January 23, 2017
Category: Business Intelligence, Power BI, Uncategorized
5 Tips for Optimizing SSAS Tabular for Power BI
SSAS Tabular and Power BI are two cornerstones of Microsoft’s new generation of BI tools, and play quite well together out of the box. For these tips, I’ll assume you have a properly connected gateway up and running linked to a tabular model in Power BI, and that the model has been properly designed so none of this will look unfamiliar to anyone who has previously worked with a pivot table.
Still, there are many things that can be done by developers to ensure their model is optimized for Power BI in order to give their stakeholders the best possible experience – here are a few that can have a major impact.
1. Use Perspectives Where Appropriate
The extent to which perspectives can be useful in creating a better user experience will naturally vary depending on the design of your tabular model. For simple models containing a limited number of tables, columns, and measures – such as those designed to support a single report, for example- the use of perspectives may be unnecessary.
However, in cases where your model is designed to support multiple areas of a business — or even an entire organization — the additional complexity makes the use of perspectives almost mandatory to create a decent user experience.
More robust models will generally contain tables that are unrelated to one another, columns or measures that are only valid or useful in a specific business context, and potentially multiple date dimensions.
If your users will be doing any kind of self-service reporting, logically-designed perspectives will make the process significantly simpler – and may help to prevent incorrectly-drawn conclusions and costly processing operations.
While well-designed perspectives are a must-have for giving your end users the best possible experience, it is important to note that it should not be considered a replacement for a strong security model. Additionally, in situations where heavy usage and large datasets create the risk of compromised server performance, row-level security can be used not only to restrict access to data but to limit query size as well.
2. Avoid Calculated Columns
Even the most meticulously planned data model is going to be forced to change with the needs of the business, and depending on the complexity of your data platform it may be very tempting to add calculated columns here or there as development goes along.
Resist that urge!
While calculated columns are sometimes unavoidable – especially if your data source is something other than a SQL database – it’s almost always worth the extra effort whenever possible.
In addition to cutting down on model processing times, DAX-calculated columns have a more expensive memory cost than columns calculated at the source-data layer. That means more efficient queries, which is vitally important for visualizations using multiple fields or complex measures.
While these cost savings are obviously going to be more important in more robust models, avoiding unnecessary calculated columns even in smaller models can help prevent nasty surprises as usage increases or complexity scales up.
3. Choose the Proper Approach to Dates
Date dimensions in SSAS Tabular models can often be a tricky thing as even smaller datasets will often have multiple associated dates. While creating a dedicated date table for each distinct date in the model may seem appealing, it can create plenty of headaches from the end-user perspective.
“Keep it simple” is obviously the guiding principle, but that’s entirely an entirely subjective standard. If you’re only dealing with a few dates that are pretty clearly defined, then the dedicated date table approach may indeed be the correct one.
Alternatively, if you have multiple dates that are only useful in the context of specific measures, a better approach may be to use a single date dimension while utilizing the USERELATIONSHIP DAX function. In larger, enterprise-level models, some combination of the two is likely the way to go.
No matter which approach you use, it’s incredibly important that it’s clear to your end users. If you’re using multiple date tables, give them names that make it clear how each table should be used. Also, consider adding an identifier to each column in the table. Because Power BI does not display the name of tables for fields that are added to visualizations or the filter pane, it may not be immediately clear exactly which date is being used where.
Finally, be sure to clearly explain how to use each date table in whatever associated documentation is made available to your stakeholders. Date handling is one of the few areas in which even an impeccably designed SSAS Tabular model can be confusing to even savvy users, so education is key.
4. Mix Up Your KPI Approach
As of early 2016, Power BI has a built-in KPI visualization. It’s a clean and clear visual that looks especially great in dashboards, and its inclusion was the cause of a great deal of celebration in the Power BI developer community.
But in some cases, the KPI functionality within SSAS Tabular allows for some added flexibility that the native Power BI visual doesn’t afford.
KPIs defined at the Tabular level can be added to cards, meaning multiple KPIs can be displayed in a single visualization. Indicators can even be added to tables, so long as they don’t contain so much data that performance would be compromised.
And in some cases, it’s simply an aesthetic choice; there is a wider variety of indicator types available when defined at the model level, some of which may tell a more effective story than the native Power BI visual.
One important note about KPIs, no matter which approach you take. Be sure that your visualizations are accessible! Any visuals that utilize red and green should also utilize distinct shapes (think ü and û instead of the default stoplight shapes). Where this isn’t an option, consider using a different color scale.
5. Build an Automated Data Dictionary
Every field within an SSAS Tabular model can have a description added to it, which is often used as a place to write a natural-language explanation of the business logic used within a measure. This is incredibly helpful for the next developer that comes along, but it can also be helpful to your stakeholders as well.
By querying your own model, you can create a table that contains the descriptions for all of your measures, which can then be used in Power BI reports.
The indispensable PowerPivot Pro blog has a step-by-step guide, and this simple step can go a long way toward simplifying the end-user experience. Descriptions can be added to a dedicated page within reports or to cards or tables adjacent to visualizations, removing a great deal of the ambiguity inherent in choosing workable field names.
Just be sure that the SQL server agent job that processes your tabular model processes your data dictionary table as the final step; that way you can be certain that your most up-to-date descriptions are included every time.
What are some ways you are optimizing SSAS Tabular for Power BI in your world?