RelationshipsWhen you are creating relationships in PowerPivot you should see a number of tables and solid lines connecting them. I still get this question a lot: “When is good to combine tables vs loading them in the Power BI and creating a relationship between them?” This article is going to answer this question. R/.Despite my many articles about the importance of star-schema and design principals of fact and dimension tables.
The sample below is an example of that type of relationship tables with relationships in Power BIWe hope you enjoyed this lesson. In Power BI models, you can load tables into the model, and create relationships between tables. The Date Field to the Date. This could be purposefully done or else you have accidentally joined two tables together based on different fields e.g.
If you are building a reporting system, and the loading the report takes five minutes, then what is the benefit of this report over doing it in the operational system? because it would have taken that much in the operational system anyway!When you build a reporting system, you should target creating reports that load fast. PerformanceOne of the distinguishing features of a reporting system is the performance of it. Relationship or Combined?So the question for this article: Should you combine the Product, ProductCategory, and ProductSubcategory tables into one Product dimension? Or should you leave them as is, and create relationships between tables in the Power BI? Let’s talk about the pros and cons of each method. What is the Direction of the Relationship?You can also combine some of those tables together and have a simpler model with fewer relationships, like the example below combined tables instead of relationshipsTo combine tables with each other, you can use the Merge transformation in Power Query. What is the Cardinality of the Relationship? Cool lesson, huh Share it with.To understand how the relationships work in Power BI, I recommend you to look at my articles here:
Creating A Relationship In Power Pivot Code Is Like
Complexity of the relationshipRelationships come with a cost of complexity. If a data visualizer have 500 tables in a model all inter-related, he/she would have a hard time to understand the model.Having a few tables and relationships is always helpful to make the model simple to understand. Simplicity vs a MessAnother big deterministic of a good reporting model is the ability to make it easy-to-understand for data visualizers. And it would perform faster when you have many large tables. The DAX queries behind the scene would be longer.The DAX query generated by this visual is different when tables are combined, or when they are separated with relationships.This is the DAX query when separated product tables with relationships: // DAX Query'DimProductCategory','DimProductSubcategory',"CountRowsDimProduct", CALCULATE(COUNTROWS('DimProduct'))NOT(ISBLANK('DimProductCategory')),NOT(ISBLANK('DimProductSubcategory'))NOT(ISBLANK('DimProduct'))"'DimProductCategory'", 'DimProductCategory',"'DimProductSubcategory'", 'DimProductSubcategory',"'DimProduct'", 'DimProduct'If tables are combined, the code is like below: // DAX Query'DimProduct',The second code is much simpler.
You can then take the next step of implementing it using Power BI. All of these are much better to be done using a pen and paper. You need to list fields in your dimension and fact tables. You need to draw the dimensional model, the layout of the tables. Yes, no tools or services needed.
This is wrong! You need to transform that format. How are you going to build a database diagram of a reporting system, when you don’t know what values should be shown in the report?A good model also requires an understanding of the existing data, and how that data can be transformed into the star-schema model.Don’t build your model on this rule that this is the format that I get data, so can’t really do much about it. It should first start with the requirement.
It is up to you to decide in which world you want to live in. Your model, your worldYour Power BI data model is your world. You should do it as soon as possible, or your whole building might collapse at some point.
Reza is also co-founder and co-organizer of Difinity conference in New Zealand.His articles on different aspects of technologies, especially on MS BI, can be found on his blog. Reza is an active blogger and co-founder of RADACAD. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. He has a BSc in Computer engineering he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies.
And He is a Microsoft Certified Trainer.Reza’s passion is to help you find the best data solution, he is Data enthusiast.This entry was posted in Uncategorized and tagged Modeling, Power BI, Power BI from Rookie to Rock Star. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. He is the leader of the New Zealand Business Intelligence users group.
0 kommentar(er)
