Olap Tutorial 3 3j2a6i

Go

http://freedatawarehouse.com:80/tutorials/dmtutorial/Location Dimension.aspx

JUN NOV JAN

05

51 captures

2004 2005 2006

16 Apr 2004 - 12 May 2009

FreeDataWarehouse.com Data Warehouse Design Tutorials and Information

Tutorials Dim. Model TOC

Tutorials -> Dimensional Modeling Tutorial

Dimension Tables - Key elements of a Dimension Table Sponsored Links

Definition OLTP Schema Location Dimension Time Dimension Product Dimension Fact Table Star Schema Report Mapping Snowflake Schema Step by Step Design

Dimensional modeling allows only one table per dimension. But your OLTP data spans across multiple tables as described. So we need de-normalize the OLTP schema and export into your Dimension Tables.

US/Canada Residents Only. Free Info. Explore the undergraduate and graduate programs at the University of Phoenix. Click here for your no obligation information package.

For example, for the location dimension, you achieve this by ing the three OLTP tables and inserting the data into the single Location http://pagead2.googles table.

Your Location Table will look like this:

Location Dimension Table Schema Field Name Dim_Id Loc_Code Name State_Name Country_Name

Type INTEGER (4) VARCHAR (4) VARCHAR (50) VARCHAR (20) VARCHAR (20)

All Dimension tables contain a key column called the dimension key. In this example Dim_Id is our dimension Id. This is the unique key into our Location dimension table. The actual data in your Location Table may look like this Location Dimension Table Data Dim_Id Loc_Code Name 1001 IL01 Chicago Loop 1002 IL02 Arlington Hts 1003 NY01 Brooklyn 1004 TO01 Toronto 1005 MX01 Mexico City

State_Name Illinois Illinois New York Ontario Distrito Federal

Country_Name USA USA USA Canada Mexico

You may notice that some of the information is repeated in the above dimension table. The State Name and Country Name are repeated through out the table. You may feel that this is waste of data space and against the normalization principles. But in dimensional modeling this type of design makes the querying very optimized and reduces the query times. Also we will learn later that in a typical data warehouse, the dimension tables make up only 10 to 15 % of the storage as the fact table is by far the largest table and takes up the rest of the storage allocation. Next>> Time Dimension Freedatawarehouse.com © Copyright 2000-2004 All Rights reserved. This site is for data warehouse, dimensional modeling and OLAP educational purposes only. We do not guarantee the correctness of the content. The risk of using this content remains with the . Please read our Send any comments to: . W3C HTML 4.01 compliant.

👤

⍰❎ f 🐦

▾ About this capture