What to do with tables which has facts and Dimensions in it?
Hello everybody hope someone can help me:
Right now im creating a datamodel but in this datamodel it seems like that most of the tables have a mix of Dimension fields and fact fields. lets say a table has 20 string fields and 7 fact fields.
Is it recommended to split this table into only Dimensions and only facts tables?
Merging those 4 tables into a single one, yes - but not per join else per concatenate (union in sql) because joins have always a risk to change the number of records. Of course this could be handled but the efforts could become quite high depending on the relationships and the data-quality.
It's recommended to design the data-model in the direction of a star-scheme with a single fact-table and n dimension-tables. The essential measure-fields should be placed within the fact-table. To have dimension-fields also within the fact-table is usually not a problem and must be not mandatory be switched into any dimension-table.
lets say that I have 4 of theses with the same granularity and have the same key fields. is it then the best practice to join the facts into one fact table and keep the Dimension fields in the tables? this should be then the Star schema which you are talking about right?
Merging those 4 tables into a single one, yes - but not per join else per concatenate (union in sql) because joins have always a risk to change the number of records. Of course this could be handled but the efforts could become quite high depending on the relationships and the data-quality.
