Forum Discussion
SHARING MY DATA MODELING RELATIONSHIP
Hi all,
I have been working on a big project, it does required me tons of excel spreadsheets and few datasets, then I was "forced" to use other table to clean, sort in sequence, display more infromation, slicers that control all the tables.
Now it does looks like the pic I`ll share below but my main goal for this topic is - I would like to hear from you if this kind of practice is right or if it slowdown the process and mess with power bi.
HI Anonymous ,
I would never suggest bringing in more than say 5 tables in Power BI because this is adding complexity to your data model. As I don't have much information on the excel files data and structure they have, Few things what I can suggest is:
- If there are few files with exact same structure then use Folder approach to bring them in Power BI so they are seen as a single dataset in Power BI rather than multiple. See this blog.
- Try to go with a more database approach rather then excel approach here. And do all the modifications on the Source side, rather than in Power BI.
- Also try to work out if you can join few of the excel files using a common key in Power BI rather then bringing them as different datasets.
Also what is the source of these excel files? Is it on local machine or in cloud?
Thanks,
Pragati
4 Replies
- Pragati11
Super User
HI Anonymous ,
I would never suggest bringing in more than say 5 tables in Power BI because this is adding complexity to your data model. As I don't have much information on the excel files data and structure they have, Few things what I can suggest is:
- If there are few files with exact same structure then use Folder approach to bring them in Power BI so they are seen as a single dataset in Power BI rather than multiple. See this blog.
- Try to go with a more database approach rather then excel approach here. And do all the modifications on the Source side, rather than in Power BI.
- Also try to work out if you can join few of the excel files using a common key in Power BI rather then bringing them as different datasets.
Also what is the source of these excel files? Is it on local machine or in cloud?
Thanks,
Pragati
Hi Pragati11
Didnt expect such complete answer, it deffinetely gave me some perspective about.
I feels like I got too deep into the project then I forgot to think about performance on the calculations.
The excel files are all in our local server, 2 of the datasets are online in Azure Services.
I`ll pick the idea about merge few of them, they already have their rolling data, lets say, a manual version of the automated one you mentioned but I did rember me the fact where I`m able to merge from a main colum and try to make it in less tables.
CNENFRNLCommunity Champion
I saw some many to many relationships and bilateral filters. As seasoned PBI user, I alway avoid such time bombs, which would produce unexpected results at certain unexpected moment in a fairly complex data model.
You are right, I did avoid max as I could, the only ones that still as many to many is the ones that contains WEEK relations instead DAYS relations, unfrotunately I tried to clean it today and I messed up some stuffs so, really good tip of you, wish I have heard about it earlier, before make so many wrong relations and results.
So yeah, thank you for the tip, took me long to recognize many to many is the best way to bring weird numbers in unexpected moments as you said!