What is better between Star Schema x Append?

[Anonymous]

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

[Greg_Deckler]

@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.

[Anonymous]

@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?

[Greg_Deckler · Accepted solution]

@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)