Page 37 - DCAP606_BUSINESS_INTELLIGENCE
P. 37
Business Intelligence
Notes For example, a dimension table for stores in the StandardMart sample database includes the
following columns:
Table 3.1: Sample Dimension Table
Column Description
store_country Specifies the country or region in which the store is located. This is the country
level of the hierarchy.
store_state Specifies the state in which the store is located. This is the state level of the
hierarchy.
store_city Specifies the city or province in which the store is located. This is the city level of
the hierarchy.
store_id Specifies the individual store. This is the lowest level of the hierarchy. This field
contains the primary key of the store dimension table and is used to join the
dimension table to the fact table.
store_name Specifies the name of the store. The values in this column are used to identify the
store to users in a readable form.
Source: http://msdn.microsoft.com/en-us/library/aa905979(v=sql.80).aspx
Self Assessment
Fill in the blanks:
7. Dimension tables consist of attributes that describe ....................... in the fact table.
8. .......................... contain attributes that describe business entities.
3.4 Surrogate Keys and Alternative Table Structure
A surrogate key in a database is a unique identifier for either an entity in the modelled world or
an object in the database. The surrogate key is not derived from application data. Surrogate keys
are keys that are maintained within the data warehouse instead of keys taken from source data
systems.
Example: Say for the employee ‘Emp12 the Business unit changes from B1 to B2. Now, if
you use the natural primary key ‘Emp12 for your employees within your data warehouse then
everything would be allocated to Business unit ‘B22 even what actually belongs to ‘B1.’
If you use surrogate keys, you could create on the other day a new record for the Employee
‘Emp12 in your Employee Dimension with a new surrogate key.
Figure 3.3: Surrogate Key Example
Source: http://mahaveersingh.files.wordpress.com/2012/05/surrogate_key_blog_banner1.jpg
32 LOVELY PROFESSIONAL UNIVERSITY
