6.2 Data Warehouse Data Structures

 < Day Day Up > 



Relational databases typically use a cost-based optimizer. If a query contains multiple table joins or links, the optimizer will examine each combination of table joins in turn to determine the best execution plan. For example, a query, such as show me all of the sales of beer brand X in all Kwik-E-Mart stores for the past two weeks excluding London, would probably touch a products table, a stores table, a sales table, and a regions table in a conventional database schema.

This can lead to many combinations being examined-for example, a 4-table join has a total of four factorial (24) possible join combinations a 10-table join has an amazing 3,628,800 possible join combinations! The query optimizer in SQL Server 2000 has been tuned to try to overcome this multijoin problem, but the issue still exists and is subject to the limitations of the data structure.

Within a data warehouse environment, where a wide variety of queries can be executed, the designer needs to try to preempt the user by reducing these table joins. This is achieved by using a star or snowflake schema, amalgamating commonly used data into a single table.

Data merging to produce a star schema is a useful design tool when the following conditions are met:

  • Tables share a common key.

  • Data from the tables are used together on a frequent basis.

  • Data insertion, if appropriate, is the same across the tables.

In fact, there is a slight difference between the star and snowflake structures since the snowflake structure has the surrounding dimension tables in a more normalized form.



 < Day Day Up > 



Microsoft  .NET. Jumpstart for Systems Administrators and Developers
Microsoft .NET: Jumpstart for Systems Administrators and Developers (Communications (Digital Press))
ISBN: 1555582850
EAN: 2147483647
Year: 2003
Pages: 136
Authors: Nigel Stanley

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net