How many aggregation tables are required?

[Anonymous]

Hi there,

I have created a composite model in Power BI (1 fact and 15 dimensions). This will be used as a signle dataset for 20 or 30 reports for my client.

Question is, what could be the best strategy to plan and create aggregation tables like how many aggregation tables need to create or one or two generic aggregation tables can be created to use across all report? Or there will be 1 aggregation tables per report?

Any help will be highly appreciated.

Thank you

[v-deddai1-msft]

Hi @Anonymous ,

It all depends what is needed in your report. You can refer to https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[amitchandak]

@Anonymous , It depends on data. you should create aggregation when it significantly reduces the data. so that you can get performance.

do it for the dimensions having low volume. Do not add aggregation when you do not get data reduction or query performance

refer if needed

https://radacad.com/aggregation-to-speed-up-the-performance-of-a-power-bi-report-even-if-all-imported

[Anonymous]

Hi @amitchandak 

No, this link does not provide what I'm looking for.

[v-deddai1-msft]

Hi @Anonymous ,

Since we don't have your data, and we don't know what will be used in your report, so it is difficult for us to provide you detailed suggestion. All we can provide is general suggestion like https://www.youtube.com/watch?v=EhGF372t0sU

Best Regards,

Dedmon Dai

[Anonymous]

Hi @v-deddai1-msft 

Appreciate your reply.

Thank you

[Anonymous]

Thank you for your reply @amitchandak 

I have more than 100 million records in fact table and in few dimentions too.

I need to understand if I need to create one big aggregated table which will service (may be) 90% of the queries generated by different reports. Or I need more than one aggregation tables to service reports?

Probably one aggregation table per report does not make sense.

Is there an article, how to plan or design aggregation tables in a composit model?

I don't want to add an aggregation table every few days in my centralized Power BI Model.

Thank you