Hands-On Microsoft SQL Server 2008 Integration Services part 58. Deploy and manage high-performance data transformation solutions across your enterprise using the step-by-step techniques in this fully revised guide. Hands-On Microsoft SQL Server 2008 Integration Services, Second Edition explains the tools and methods necessary to extract conclusive business intelligence from disparate corporate data. Learn how to build and secure packages, load and cleanse data, establish workflow, and optimize performance. Real-world examples, detailed illustrations, and hands-on exercises are included throughout this practical resource. . | 548 Hands-On Microsoft SQL Server 2008 Integration Services In this model the dimensions are denormalized and a business view of a dimension is represented as a single table in the model. Also the dimensions have simple primary keys and the fact table has a set of foreign keys pointing to dimensions the combination of these keys forms a compound primary key for the fact table. This structure provides some key benefits to this model by making it simple for users to understand. Writing select queries for this model is easier due to simple joins between dimensions and the fact tables. Also the query performance is generally better due to the reduced number of joins compared to other models. Finally a star schema can easily be extended by simply adding new dimensions as long as the fact entity holds the related data. Snowflake Model Sometimes it is difficult to denormalize a dimension or in other words it makes more sense to keep a dimension in a normalized form especially when multiple levels of relationships exist in dimension data or the child member has multiple parents in a dimension. In this model the dimension suitable for snowflaking is split into its hierarchies and results into multiple tables linked to each other via relationships generally one-to-many. The many-to-many relationship is also handled using a bridge table between the dimensions sometimes called a FactLessFact table. For example the AdventureWorksDW2008 products dimension DimProduct is a snowflake dimension that is linked to DimProduct SubCategory which is further linked to the DimProductCategory table refer to Figure 12-2 . This structure makes much sense to database developers or data modelers and helps users to write useful queries especially when an OLAP tool such as SSAS supports such a structure and optimizes running of snowflaked queries. However business users might find it a bit difficult to work with and would prefer a star schema so you have to find a balance in choosing when to go .
