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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
codyraptor
Resolver I
Resolver I

Data Modeling large Fact table with multi granularity vs Multi Facts

Hey all,

 

Quick question.  I have a fact table that has 3 levels of granularity that will need to be aggregated.  For example...Sales Counts/Price, Job Cost, and Item Cost.  I'll need to sum each level.  The data is pulled together by left joins...so Sales, Price, and Job Cost all repeat rows with item cost.  The table is ~30Mil rows.  Would you recommend breaking these down into 3 individual fact tables at their own granularity...or keep it in 1 fact table and use dax to calculate appropriately.  Breaking them down would make 1 table ~13Mil, 2 table ~25Mil, and 3 table around 27Mil...so I'd assume the model would be much larger this way.  Mainly concerned about the quickness and ease of dax calculations and using best practice.   Thanks!!

4 REPLIES 4
selimovd
Super User
Super User

Hey @codyraptor ,

 

can you give an example how the data would look like?

Do they have columns in common? If not, then there is nothing wrong with using multiple fact tables. But it really depends.

 

Best regards

Denis

@selimovd  So here's an example of the data 'if' I were to use a single fact table.

 

Customer ID

Price

Job IDJob CostItem IDItem Cost

1

200

45001250
120045002250
120036001300
120036002300

 

The result would be Customer 1 has a Price Sum $200...Job 4 cost is $500 and Job 3 cost is $600...both broken down by item cost.  Imagine this rough sketch...30mil rows.  There are more dimensions obviously...but this is the idea.   The alternative would be create a Customer Table w/Price...Job table w/Job Cost..and Item table with Item Cost...and join all 3 with dimension keys.

@selimovd  Checking in to see if you had a chance to look over this.  The able above is just a simple example...but all 3 levels share dimensions such as State, Product, etc...    I currently have the model has a star schema with 3 fact tables.  However....The requirements continue to grow and the model is slowing.  Just making sure I'm using best practice.

Hello @codyraptor,

 

I am in a similar situation as yours. Were you able to find a solution for this?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.