Power BI Metrics: Hierarchies performance tips

Power BI Hierarchies performance tips and tricks

Today we are excited to talk about some tips and tricks for improving load performance of hierarchies in Power BI. Hierarchies in scorecards is a hugely powerful tool, so let’s talk about how to optimize content load times for your users and make cascading scorecards as useful as possible. First, some background.

What is ‘hierarchies’?

Power BI Metrics enables ‘cascading scorecards’ that roll up along hierarchies in your data. You can set up a hierarchy for a scorecard and map the Power BI datasets referenced by your metrics to the hierarchy levels and owner fields, automatically creating a new scorecard view for each slice of your data. That’s potentially thousands of automated scorecard views with just a few clicks.

Power BI will cascade connected metric values to each level of the hierarchy. Users can easily drill into the hierarchy to see progress, statuses and do check-ins at different levels. In the images below, you can see the different levels of a project hierarchy in the slicer, and as you navigate to each level or sublevel of the hierarchy, your metric values, statuses, owners, and progress will change along with it.

Tips and Tricks:

Now, with great hierarchies comes great data quantities. For context, each time you select an option from the slicer dropdown, we query the connected datasets to bring in the current value and all the historical data so that we can build a nice sparkline and history for each slice of data you want to look at. That’s potentially a lot of querying, but we have our top 5 tips and tricks to help improve your performance when using hierarchies.

1. Enable query caching

Enabling query caching on connected datasets in premium workspaces could significantly speed up content loading by caching previously used query results. The setting is applied at the dataset level so any reports using this dataset can benefit from the cache. This feature is not applicable to LiveConnect datasets leveraging Azure Analysis Services or SQL Server Analysis Services. Note: the connected datasets must be in premium workspaces to enable query caching, not just the scorecard.

2. Relative date filters

Building relative date filters into the connected visuals could improve load performance by limiting the number of datapoints, and therefore queries, that need to be performed for each selection of the hierarchy to load. Rendering history for 1 data point every day for 2 years, for 100 metrics is much more data than rendering 1 data point every day for the past 2 months. If your metric history doesn’t need to be preserved from the inception of the datapoint, relative date filters may be a nice way to improve performance.

3. Connect to drilled down/expanded visual data

Similar to relative date filters, one way to bring in less data and potentially improve content load times is to build a date hierarchy on the visual you intend to connect to, and drill to only the date granularity you need. Then, you can connect to the visual with the date granularity you need. For example, instead of connecting to the entire time series which contains 1 data point per day, you could drill to a monthly aggregation, and bring in far less data.

4. Adjust periodicity (time period) for metrics to no cycle or a lesser time period

Adjust the time period on your metric(s) by opening the details pane in edit mode and navigating to the time period section. This control will change the time period that shows in the sparkline. We will still take the last 7 data points, but with a setting like “no cycle” the time period for the data points may span less time, which could improve performance.

 

5. Connect to a singular data point rather than a time series

Connecting to a singular data point instead of bringing in the history as you connect could improve performance as well, again, by limiting the data that needs to be rendered each time a user loads the scorecard. If the entire history of the data point in not needed, you can connect to a singular data point and build history from there.

Source: Microsoft