cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gabrielm97
Helper I
Helper I

What is better between Star Schema x Append?

The title is self explanatory but what is the difference? do both serve the same purpose? what are the advantages and disadvantages between them.

1 ACCEPTED SOLUTION

@Gabrielm97 - I would append your two tables ensuring that you have a Location ID in your fact table. I would then create a Location dimension as a separate table. So:

 

Location

LocationID,Name

1,Paris

2,New York

 

Fact Table

LocationID,Date,Rainfall

1,1/1/2020,2

1,1/2/2020,3.5

...

 

You would also want a Date dimension (calendar table)


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@Gabrielm97 - I do not find this self-explanatory at all. A Star Schema involves a fact table and multiple dimension tables. I don't understand how this compares to an Append? Are you saying a Star Schema versus a single fact table with no dimension tables?

 

The generally accepted best practice is a star schema as performance is better, etc. etc. etc. @marcorusso and others have tons of material on this.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

@Greg_Deckler 

I have 5 bases with dimensions, I can create a fact table and join the 5 dimensions. But as all dimensions are equal in the 5 bases, what changes is just the location, I can make an append

 

Example: I have 2 bases, one from each location, but they all have the same dimension.

 

Table 1 - Paris

Days and Rainfall

 

Table 2 - New York

Days and Rainfall

 

I can make a fact table with the names of New York and Paris, or I can make an append.

 

Everyone has the same purpose, but which would be better?

@Gabrielm97 - I would append your two tables ensuring that you have a Location ID in your fact table. I would then create a Location dimension as a separate table. So:

 

Location

LocationID,Name

1,Paris

2,New York

 

Fact Table

LocationID,Date,Rainfall

1,1/1/2020,2

1,1/2/2020,3.5

...

 

You would also want a Date dimension (calendar table)


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors