Star Schema Advice

[ghe7549]

I have a database with arond 20 million rows of data.  I am not the ones who created the database i am the one who is pulling into Power BI.

I have 3 fact tables (Assets, PMs and WOs) these 3 fact tables also share dimension tables plus the asset table and pm table join as well.  Can someone provide some insight on how best to put this together within Power BI.

 

[v-angzheng-msft]

Hi, @ghe7549 

Here are some references that might be useful:

Understand star schema and the importance for Power BI 

Data model / star schema 

Power BI Advanced Tutorial: Explaining the Star Schema Data Model with Example | DIM and Fact Table 

Power BI Data Modelling Tutorial 1 - Star Schema? 

Power BI Tips for Star Schema and Dimensional Data Modeling 

Power BI Basics of Modeling: Star Schema and How to Build it 

Implementing Star Schemas in Power BI Desktop 

Star Schema Advice 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ghe7549]

Any suggestions?

[ghe7549]

I was told that the datbase team is using a Star Cluster Schema for the Database.

Here are my 3 "fact" tables with assests being the main fact table.  I have also added grouped_pm table.  As you can see all these table have the same dimenision tables but WO and PMs roles up to asset table.  

Can I get some advice on how best to use these fact tables in Power BI?

[amitchandak]

@ghe7549 , screenshot is too small to check and suggest.

Remember the rules.

1. No two fact join with each other

2. No two dimensions join with each other

3. Facts and dimensions is 1 step way.

4. Merge dimension in the hierarchy

5. If you have master-detail transaction table. Merge and have one fact at the level of the detail. If needed two; one at header level and one at the detail level, but they will not join in power bi. so make they take columns from each other