Forum Discussion
About the data model used for analysis between business processes
Don't link the fact tables, that would destroy the star schema. Instead create a new dimension table using the unique ID common to all the tables, which I'll call LeadID.
There are a couple of approaches you can take to create the dimension table, depending on how your process works. If you know that every row in Deal Closure and Opportunity Creation must exist in Lead Acquisition, because that is how your system works, then you can just take values from the Lead Acquisition table like
Dim Lead = DISTINCT( 'Lead Acquisition'[LeadID] )If there are more columns you want to be able to slice by using the new dimension table, then you can use SUMMARIZE instead of DISTINCT.
Link the new dimension table to each of the 3 fact tables.
If necessary, you could create calculated columns in the dimension table to indicate whether a lead became an opportunity or a deal, e.g.
Is Lead = NOT ISEMPTY ( RELATEDTABLE ( 'Lead Acquisition' ) )
Thank you so much, Johnt75!
This was very insightful.
I misunderstood and thought it was about the fact tables, but I see now that the key is to work with the dimension tables. I feel like there might be various models and approaches to creating this kind of dimension table.
If you happen to know any keywords I could use to research this topic further and gain a more systematic understanding, could you please share them with me?
Thanks for the reply from johnt75 , please allow me to provide another insight:
Hi, yoshi_matsu
Thanks for reaching out to the Microsoft fabric community forum.
I'm glad to hear that you're interested in learning about dimension tables and fact tables. You can try searching for keywords like "Star Schema," "Fact Table vs. Dimension Table." To avoid confusion, it's recommended to add the prefix "Power BI" to your search, as other products also have similar concepts.
Secondly, here are some related documents that might be helpful:
This article introduces star schema design and its application in Power BI semantic models:
Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
This tutorial shows how to build a complete Power BI report starting from a dimensional model:
Tutorial: From dimensional model to stunning report in Power BI Desktop - Power BI | Microsoft Learn
This article explains the concept of dimension tables and provides examples of creating dimension tables:
Lastly, you can also look for learning resources in blogs and forums:
Here is a blog link:
Fabric community blogs - Microsoft Fabric Community
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.