About the data model used for analysis between business processes

[yoshi_matsu]

I’d like to ask a question in the Power BI community.

So far, we’ve been aggregating monthly business data using a fact table structured with SCD2, built with SQL, and establishing relationships with other dimension tables like a calendar table in a star schema format.

Moving forward, I’d like to analyze processes between business stages, and I’m wondering what kind of data model or schema would be appropriate for such cases.

Details

In the case of the sales process, where the stages include “Lead Acquisition → Opportunity Creation → Deal Closure,” I’d like to analyze which leads and opportunities ultimately result in closed deals.

In our previous aggregations, we structured the data by linking each table (“Lead Table,” “Opportunity Table,” “Deal Table”) with a “Calendar Table.” However, in this case, I’m considering a structure where fact tables are linked to each other, such as linking the “Lead Table” to the “Opportunity Table” and the “Opportunity Table” to the “Deal Table.”

Alternatively, I’m wondering if it might be better to pre-join the Opportunity and Deal tables with the Lead table before analysis.

I’d appreciate any advice on how to construct fact tables and set up relationships for this scenario.

Note: I’d like to keep the fact tables as generic as possible.

[yoshi_matsu]

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?

[johnt75]

As well as the keywords @v-linyulu-msft  mentions you could also search for "kimball data modelling". You could also consider taking the data modelling course run by SQLBI, it is excellent.

[v-linyulu-msft]

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:

Basics of Modeling in Power BI: What is a Dimension Table and Why Say No to a Single Big Table - RAD...

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.

[johnt75 · Accepted solution]

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' ) )