Reducing Number of Dimension table

I have 20- 21 dimension tables, is it recommended to reduce the dimension tables by joining them using sql and then upload? I am struggling with data relationships with few being bi-directional.

Hi @raniramachandra,

It’s hard to understand what you are dealing with, based on the 2 lines above. Personally I have never encountered a model with 20+ DIM tables and this raises a lot of questions, here are a few:

Are you trying to build one single centralized model?
Can your model be split into several smaller models to address business questions?
Can some DIM tables be combined/ denormalized?
Can you stay within a star schema layout?
What is the reason for the bi-directional relationships?
.

Here are some helpful references when facing modeling challenges:

Hello @raniramachandra

Did the response from @Melissa help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark her answer as the SOLUTION.

Thank you

Hi @Melissa ,
Thanks for your reply and posting links!!
The report wasn’t created by me, it’s passed on to me.
The tables are exported as it is from the database, all the transformations are achieved through power query and there are visuals which are filtered by columns from different tables, hence ended up with bi-directional relationships.
I am assuming there is lot of work to be done in terms of joining the dimension tables to reach star schema model. Among the best practices I learnt that we export with needed columns which I am planning to implement too.
The other hurdle I am facing is that the tables contains millions of rows and with the current report, its becoming to manage the daily refresh, with file size now is 444mb in power bi service but with growing data, its unpredictable. the workspace is on PPU, as report is shared to end users on third party application. Power bi throws an error that incremental refresh isn’t possible as there is query folding. Please give some pointers on how to avoid this? I suspect is it because of some power query transformations is causing query folding.
Kind Regards
Rani

There’s actually a relative new M function that can stop folding, see this article.

And best of luck with this project, you seem to have a real challenge on your hands…

1 Like

Hello @raniramachandra

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION.

Thank you

Hello @raniramachandra

Just following up if the response above helps you solve your inquiry.
If it did, please mark his answer as the SOLUTION.

We’ve noticed that no response was received from you on the post above. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @raniramachandra

Due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,.

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!

Thanks @Melissa, very helpful and supportive

1 Like