In this tutorial, you’ll learn a Power Query optimization technique to reduce RAM usage. Specifically, you’ll learn how to reduce the numbers stored after a decimal place. This is helpful if you’re dealing with columns stored in a decimal number format.
In this example, you can see that the Net Price is consuming 11MB of RAM whereas the Total Cost, Unit Cost, and Unit Price are consuming 8MB each.
When you go to Power Query and click the filter option of the Net Price column, you can see that the column is storing values of up to three decimal places.
This can cause a performance issue. If multiple digits are stored as the decimal number, the dictionary will contain more unique values. This will cause VertiPaq to create a huge storage structure for the dictionary.
It’s therefore recommended to optimize these columns.
Power Query Optimization For Columns
Ideally, you want to store values up to two decimal places. Right-click on the column you want to optimize. Click Transform > Round > Round.
Then, set the Decimal Places to 2 and click Okay.
A quick way to transform all the columns in one go is by highlighting them and then following the same steps.
This applies the decimal number format to all the columns using a single step. Once done, save your work.
Check RAM Usage
To check how much RAM was reduced, open DAX Studio. Go to the Advanced tab and click View Metrics.
By comparing the original with the optimized columns, you can see reductions in the Column Size and Dictionary Size.
For this example, it would appear that the difference in kilobytes doesn’t amount to a good reduction in RAM space. However, if you’re dealing with more distinct values such as numbers with seven decimal places, the savings in RAM would be great.
You can also notice that for the Unit Price column, the cardinality doesn’t change but there’s a significant reduction in the column size.
When you reduce the granularity or make changes to the data model in a column, you’re introducing a new sort order in Analysis Services. This can either increase or decrease the data model size.
Even if the number of distinct values didn’t change, Analysis Services might have found a better sort order which reduced the size of the column.
This optimization technique is especially helpful if you’re storing a Date/Time column. This could be storing values up to the millisecond.
When you reduce the cardinality of each value to a second, this reduces the unique values in the Dictionary.
***** Related Links *****
Simple Power BI Transformations For More Optimized Data
Optimize Power BI Formulas Using Advanced DAX
Storage Engine – Its Role In Optimizing DAX Queries In Power BI
Conclusion
If you’re working with a tabular model, focus on reducing the cardinality of a column.
The cardinality is the deciding factor on the amount of RAM the data model will consume. It also tells you how much time it’ll take to scan a particular column when you execute your code.
Optimizing your DAX queries is crucial in maintaining a good Power BI report. Not only does it ensure that your work performs well, but it also reduces the strain on your machine.
Enterprise DNA Experts
