Forum Discussion
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.
Anonymous - 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)
3 Replies
- Greg_Deckler
Community Champion
Anonymous - 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.
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?
- Greg_Deckler
Anonymous - 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)