Forum Discussion
Resolver IHow find the most computation expensive calculated columns
So in the data model that I inherited, there are a number of calculated columns in the main table. By chance, I noticed if I delete this particular "end customer" column, it would cut the time to open the .pbix in half (~six minutes to three minutes).
According to the metrics in DAX studio, there wasn't anything particularly notable about this "end customer" column. For example, in terms of size and cardinality, it's much smaller than other columns in the same table. However, the logic in this "end customer" was poorly written (it checks if the related(other_table_column) is blank and if not, returns that value) and hence my theory that eliminating this calculation would dramatically speed up the model.
My main question: aside from manually deleting columns 1 by 1 and then manually timing the time to open the file, is there a faster way to find these expensive computation columns? As i mentioned, nothing seems to jump out at me looking at the dax studio metrics.
27 Replies
- aj1973
Community Champion
Hi eddd83
In general and for performance reasons, adding Calculated columns to the semantic models is not a good practice. Add a calculated column only when it is really necessary to use it for a slicer for example and if you can add it in power Query.
Replace them by measures using DAX
eddd83Yes, I understand that using measures is preferable, but this data model is a mess (ie, it is 82 columns wide. > 50% of the columns are calculated columns). Ideally, my original question would be answered and then I know which columns i can proritize my effort into possibly converting into a measure.
- aj1973
Also Brovo from SqlBi can help you identify columns that are not referenced in your model and need to be removed. I don't think all those 40+ calculated columns are being used.
- eddd83
As I mentioned, it takes 6 minutes just to open the model. Similar amount of time or longer is needed to even add a measure and then another chunk of time to commit a measure (in total 10-15 minutes). So yeah, I want to know which columns are using the most computation power, because it would take forever to change everything.
- Brunner_BI
Impactful Individual
The easiest would be to remove everything that is currently not used. As aj1973 pointed out you can use Bravo. The downside is this does not take into account what is going on in the report (it only looks at the data model itself).
You should use Measure Killer to automatically remove all measures and calculated columns that are not used anywhere, this should already cut down the time it takes to open your report.
It only takes a few minutes. Compared to hours you will spend to find which calculated column is consuming most processing power.
Additionally, you can also check for the calculated columns that are used somewhere if this is really important or not (and kick them out as well).
As a third step you can move some calculated columns to Power Query maybe? It will be much better for performance if you can already build the columns there.
- lbendlin
Super User
In DAX Studio check the query plan for that DAX - especially the Records column. High numbers there indicate cartesians.
- eddd83
I'm not familar with using query plan. Is there a particular video or webpage that explains how I go about using query plan in relation to analyzing calculated columns?
- lbendlin
First you can use Performance Analyzer to see the impact on the UX
And then you can use DAX Studio to check the query plan for your column (or measure)
Check the length of the query plan but also the max number of records.
- eddd83
So i did what you suggested. Here's the problematic calculated column (end_customer).
However, here's another column (transaction_id_order). Removing this from the data model makes almost no difference in speed. Whereas removing end_customer makes a big difference. Again, i see nothing different between the 2 query plans aside from the problematic end_customer having fewer records.
Here's what the end_customer logic looks like. Basically it's checking the related(XXX) for a number of tables and returns the first one that isn't blank.
- lbendlin
I am surprised that the first query performs worse than the second. What do the engine timings look like for both?
