One big fat dimension vs Multiple Small dimensions
Written by: Ankit G Some days ago I came across a DW query posted on a technical discussion: “Based on prior experience, can someone please comment on which is better: small dimensions versus a large dimension. I am asking in terms of performance and reporting?” Now this is a common dilemma which DW designers face while designing Star Schemas. My thoughts: If we want to split a dimension (along width) we can do that by snowflaking it (normalizing the dimensions) or by creating mini-dimensions. Snowflake schema will reduce data redundancy and duplication, but it will increase the number of joins required to fetch all the dimensional information. The decision needs to be made based on two major factors: Performance and Usability Dimension tables that contain customer or product information might easily have 50 to 100 attributes and many millions of rows. However, dimension tables with too many attributes can lead to excessively wide rows and poor performance . For this reason, you might