Data Warehouse Characteristics


One of the more popular data warehouse (and data mart) database models is the star schema and its use as a way to organize business data for fast query access. Its design is significantly different than the database models used by Online Transaction Processing (OLTP) applications. Look at some of the characteristics of the star schema and its effect on SSIS package design.

It's called a star because of its conceptual shape, illustrated in Figure 13-1. A star schema relational database model contains a fact table (as its center) and at least one dimension table (as a point). SSIS is used to load data into both fact tables and dimension tables. When using SSIS to maintain star schemas, you need to review the relationships and dependencies between fact and dimension tables to design the SSIS package control flow and data flow components most efficiently.

image from book
Figure 13-1: Star schema model

The fact table contains the transaction detail values from source OLTP systems. Typical examples include a Sales Order fact that might include the dollar value and the unit count value for a given customer and product on a given date. The customer ID, product ID, and sales date and time ID are used to join the fact table to the dimension tables. The customer ID, product ID, and time ID are surrogate keys. The dimension surrogate keys are the unique primary keys for the dimension rows (or members). The fact table keys are foreign keys that identify the corresponding dimension members for the fact.

Every member of a dimension table is unique to that dimension and identified by the unique surrogate key. Surrogate keys that maintain the relationships between dimension and fact tables are a database model design characteristic, and it is the database design that affects how developers design SSIS packages to load and maintain these tables within the data warehouse. One of the ways used to help optimize the processes required by the SSIS packages for data warehouses is with the use of staging tables, the subject of the next section.




Microsoft SQL Server 2005 Integration Services Step by Step
MicrosoftВ® SQL Server(TM) 2005 Integration Services Step by Step
ISBN: 0735624054
EAN: 2147483647
Year: 2007
Pages: 152

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