Data architecture for many tables - slow load

[Anonymous]

Hey all,

I have created PowerBI report, let's call it Scorecard, that integrates data/reports from multiple sources.

Each measure has following must-have 4 columns:

• measure volume, indicatior
• customer
• time/date
• country

It is important to add that in each table/report there may be different customer naming and also other information on lower granulality.

Because I want user to have ability to filter in the dashboards by customer/time/country, I have made those 3 reference tables that I have connected (make relation) to each measure table, it looks like below:

As you can see, there are a lot of many-to-one, single relations. Because of that it takes more than it should for report to load after user filters one of this ref. values (customer,time,country).

What would be yours recommandation when dealing with such a big database? Is it proper approach? Should it work well with well optimized measures?

Thanks for your inputs,

Rafal

[v-danhe-msft]

Hi @Anonymous,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered to close this topic?

Regards,

Daniel He

[Anonymous · Accepted solution]

@Anonymous It's going to be super hard to say without knowing some sense of scale (# of rows/columms) and something about the measures themselves.

*Generally* lots of tables are not a problem... especially if you have a simple star schema.  But there are always exceptions and trade offs.

You might grab DAX Studio and call some measures there w/ timing&traces turned on to see if that gives a hint.

[v-danhe-msft]

Hi @Anonymous,

Based on my research, you could refer to the new feature(New modeling view) in Power BI Desktop released in November 13, 2018.

Reference:https://docs.microsoft.com/en-us/power-bi/desktop-modeling-view

https://www.youtube.com/watch?v=1xsXXoyTxfk&feature=youtu.be&t=1642

It could help you manage multiple tables and relationships.

Regards,

Daniel He