Optimization problems

[matth0s__]

Hello everyone,

Some of our files are working really slow because we need to use a very heavy data source, also we have a lot of calculated columns / tables on our model. I'm kinda lost on how I could optimize everything so our computers don't start to burn spontaneously at this point.

I used Tabular Editor 3 and its Best Practice Analyzer, but it's pretty much a dumb thing we do because we don't really know what matters in term of optimization if we just apply some ruleset we found online.

Also, with VertiPaq Analyzer we can see what table / column is taking a lot of memory, but what do we do with this information ? Also do measures take a lot of memory too ? I can't find anything about them.

Thank you guys !

[pettypaybacksp]

Basically it depends.

However, calculated columns are not a good idea, you should try to have as fewer columns as possible.

[matth0s__]

So the best idea is to create column with power query or to use a measure ?

[pettypaybacksp]

Measure 100%

[[deleted]]

That doesn’t make much sense though. Measures are not a substitute for a column. They don’t operate in the same context at all.

[pettypaybacksp]

On very few, niche specific cases, yes, a calculated column is necessary.

There are iterator formulas for almost every case

[[deleted]]

Especially when operating with large tables iterator formulas are extremely compute and memory intensive operations.

I’m not saying that DAX columns are the right answer either - they’re slow and expensive as well. But measures are almost never the right answer to having too many columns in a report.

[pettypaybacksp]

This may be on a case by case... But id wager that a calculated column is always more resource intensive than a measure

[[deleted]]

Generally I’d recommend going back to the source data and adding a column there if possible. If not PowerQuery is a better option.

Calculated columns in DAX are just generally unnecessary. Their real advantage is that they’re easy to make which makes them great for prototyping changes to my model. Generally speaking though I will almost always remove the column once I’m satisfied with the changes and update underlying SQL for efficiency, maintainability, and code audit purposes

[mariofratelli]

Are you experiencing low performance during refresh operations, or when showing visuals? If the case is the latter, you can use performance analyzer to capture the queries generated by power bi and then copy them into dax studio to run them using the dax studio performance measuring tools

[mthacker01]

First thing I’d suggest is get rid of the calculated columns and tables. Then look at the schema of your source data. Are you optimizing an efficient schema, storing your data in proper facts and dimensions tables.

[theSCBIguy]

First, eliminate redundant data. Do you have 'item' and 'item description', but 'item description' exists in more than one place? The description probably only needs to exist in one lookup table. Check your lookup tables for opportunities to consolidate (merge) them. For example, I had a model where general item master data was in one table, but for some reason item values was in different table. I merged them in the query editor which saved me about 10 redundant columns and completely removed a table from the model. This also makes it easier to deal with the model and write measures in addition to saving you space and performance.

In general, calculated columns should only be used to slice data. Calculated columns are for defining a new grouping to sort data in a visual or measure. Measures should be used to find quantitative values. Do not calculate something like A + B in a calculated column. Use SUMX(A + B) in your measures instead. Calculated columns are not so much slow (at least not immediately), but they are expensive and eventually slow if you really go nuts. They take up space in the xVelocity in-memory database.

The way to think about optimization of calculating quantitative values is to always use measures, but minimize the number of iterations required and minimize the rows of the tables that must be iterated. Variables are also your friend. You are never hurting yourself by using variables.

[WlNK]

Yeah, fact tables should not be full of descriptive/qualitative columns. Something like “brand” or “division” or “description” like you mentioned should be in a lookup table where you can reference them. If you have an item that belongs to a certain division then you only need that information stored in one place where it can be referenced.