RowNumber In DAX Studio’s Storage Engine

RowNumber In DAX Studio’s Storage Engine

No comments

This tutorial will discuss about RowNumber in DAX Studio. This can be a source of confusion because it shows up at some storage engine queries but it doesn’t exist in your data model. You’ll learn how these obstacles are generated, how they affect the whole calculation, and how to deal with them.

RowNumber shows up if you’re returning an entire table in your query. It can potentially slow down your DAX. So if you see this column, you have to filter down your query before you start iterating and executing, or start using the data cache.

The RowNumber Column

If you recall from the previous VertiPaq Analyzer modules, there was an example where you can see a RowNumber column in the VertiPaq Analyzer Metrics.

rownumber

You can see that the cardinality is zero, which means there are no unique values. This happens when you bring your whole table in your query or when there is no unique key identifying your table.

One of the proper data modeling techniques is that you should have a unique or primary key in every table. Always make sure to build and format your queries properly to avoid performance hits. If you accidentally query your entire fact table, the storage engine will show a RowNumber like this in the xmSQL statement.

rownumber

This will slow down your DAX because it will bring up all the other columns inside the fact table. You need to filter down the table in order to improve the performance of your DAX codes. There are a lot of ways to mitigate this problem using DAX or your data model.

If you see the RowNumber when you’re running your DAX or trying to produce the desired results, look back at your data model and DAX codes. Then, check if there are complications and apply appropriate solutions to maximize the performance of your measures.

It’s important to remember that your data model, DAX, and power query work together in an ecosystem.

If you have a good grasp of the whole ecosystem, you’ll know how to fix issues regarding slow performances, long execution durations, and inaccurate results.

Conclusion

The storage engine shows useful information that can help optimize your DAX codes. The RowNumber column is one of the information that you should pay attention to because it has the potential to slow down the DAX’s performance.

To learn more about the RowNumber column, try to practice what you’ve learned in DAX Studio. This will help you understand the storage engine more, and improve your report development skills. You may also check the previous blog posts with similar topics.

Nick

***** Related Links *****
VertiPaq Storage Engine On Multi-Threaded Environment
VertiPaq Compression And Storage In Power BI
What Is DAX Studio In Power BI Desktop

***** Related Course Modules *****
Ultimate Beginners Guide To DAX
Mastering DAX Calculations
DAX Formula Deep Dives

***** Related Support Forum Posts *****
Context Transition – Behind The Scenes
DAX – Switch Optimisation
Data Model Optimisation
For more vertipaq support queries to review see here….

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.