A common practice and architecture of data warehouse processes includes storing the transaction level data into staging tables. Perform the data staging process to transform, merge, standardize,
A relational data warehouse consists of two
The values stored in the fact table are called measures and are usually aggregated values from multiple detail rows in the operational data source, as shown in Figure 12-4.
Figure 12-4: Fact and dimension tables related through foreign keys
The grain of the fact_sales table is defined by the
Dimension tables represent the sets of label lists that can be used to reference the values contained in the fact table. Each row of the dimension table is called a member. Each member represents a unique label for the dimension entities. Each member can have many additional attributes, which are not required to be unique.
For example, a product dimension can include the list of unique products, in which each product is a member of the dimension. Additional attributes can include
Common dimensions include Customer, Product, Geography, and Time. One simple method to refer to the dimension tables is by using the Show Me By tables. This language helps clarify the relationship of a fact and a dimension by allowing the
The Show Me By tables can also be enabled by dimension hierarchies. Hierarchy levels define values relative to dimension attributes (for example, by size or by color) or via parent-child relationship hierarchies (worldwide total, regional subtotals,
One of the more popular data warehouse (and data mart) database models is the star schema. The star schema was introduced as a way to organize business data for fast query access. Its design is significantly different from the database models used by OLTP applications. Look at some of the characteristics of the star schema and their effect on SSIS package design. Figure 12-5 depicts a star schema.
Figure 12-5: Star schema consisting of a fact and multiple dimensions
It's called a star because of its conceptual shape. A star schema relational database model contains one fact table and at least one dimension table. 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 best design the SSIS package control flow and data flow components.
The fact table contains the transaction detail values from source OLTP systems. However, it is common to roll up detail rows to the lowest level. 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 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 is a database model design characteristic, but it is the database design that affects how you design your 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.
Figure 12-6: Simple snowflake schema
In this database, the related product category, subcategory, and product information are stored in three separate tables. Although this is a typical schema, the same information could be stored in a single table as with the year, quarter, month, and other hierarchy-level values stored in the time dimension table. There are various
Microsoftu00ae SQL Server(TM) 2005 Analysis Services Step by Step (Step by Step (Microsoft))
Microsoftu00ae SQL Serveru00ae 2008 Analysis Services Step by Step (Step by Step (Microsoft))
Microsoftu00ae SQL Serveru00ae 2008 Reporting Services Step by Step (Step by Step (Microsoft))
Professional SQL Server 2005 Integration Services (Programmer to Programmer)