Forum Discussion
Model Performance Enhancements?
It’s very hard to say without seeing it. Every relationship has a cost, and the cost is higher the higher the cardinality of the related columns. So you can improve things by removing high cardinality relationships. This could be done by consolidating fact tables.
Options I think worthy of consideration include
Removing columns in fact tables that are not used
Unpivoting columns in fact tables
Consolidating fact tables together
Eg
if you can have 1 fact table with columns
type, amount
target, 5
actual, 4
other fact, 3
etc, 8
then you can write
Total target = calculate(sum(table[amount]),table[type=“target”)
Total actual = calculate(sum(table[amount]),table[type=“Actual”)
Divide([total actual],[total target])
Thanks for the reply. I have 25 tables in the model - 4 main fact tables that add up to about 400k rows, several dimension tables, and several complimentary tables with targets and such. The fact table with the most rows (200k) has about 24 columns.
The majority of the joins between these tables are done via int keys.
I do have a bunch of calculated measures that reference multiple tables in the model - could this be the problem?
For example:
% of target = DIVIDE(sum(Facts[Amount]),sum(Targets[Amount]))*100
Or maybe it's just the number of tables and I should somehow consolidate them?
Thanks again.
It’s very hard to say without seeing it. Every relationship has a cost, and the cost is higher the higher the cardinality of the related columns. So you can improve things by removing high cardinality relationships. This could be done by consolidating fact tables.
Options I think worthy of consideration include
Removing columns in fact tables that are not used
Unpivoting columns in fact tables
Consolidating fact tables together
Eg
if you can have 1 fact table with columns
type, amount
target, 5
actual, 4
other fact, 3
etc, 8
then you can write
Total target = calculate(sum(table[amount]),table[type=“target”)
Total actual = calculate(sum(table[amount]),table[type=“Actual”)
Divide([total actual],[total target])
My previous reponse was to your earlier post. Regarding visuals, yes - definitely this can slow it down. But it is not so much the number of visuals as the number of data points displayed. Eg if you have 200k rows and 24 columns and you are rendering all of those numbers, the rendering will take time. If you Instead summarise the numbers and render 10 data points, it will be fast. It is easy to test. Just create a new page, add a single card with a single measure. Add a slicer and see how long it takes to slice