Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ghe7549
Helper III
Helper III

Star Schema Advice

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.

ec_schema_20210318.jpg

4 REPLIES 4
ghe7549
Helper III
Helper III

Any suggestions?

ghe7549
Helper III
Helper III

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?

ghe7549_0-1621538457085.pngghe7549_1-1621538462433.pngghe7549_2-1621538468106.png

 

ghe7549_3-1621538477411.png

 

amitchandak
Super User
Super User

@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

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors