Forum Discussion
Resolver IHow find the most computation expensive calculated columns
Resolver IYes, 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
Community Champion
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.
- eddd83
at time of writing, i can't install bravo because of the policies in my company. I will need to work with IT to see if i can get around this issue. I have tried doing the calculated columns in power query, but it takes a loooong time. Since the model is built on multiple dataflows and frequent refreshes, it's something i would need to figure out with my supervisor if this is feasible at all.
As i mentioned in a previous post, it is very poorly designed (ie, too much power query, too little SQL, too many calculated columns), but it's a critical report for many people. It's like I'm trying to repair the airplane while it's still flying.
- aj1973
Gotcha,
Vertipaq Analyser, I personally haven't used yet! But I think I saw a video on how to use it and to identify them columns.
Check out YouTube
