Fundamentals Data Warehouse subject

Explain star schema keys

STAR Schema Keys Figure 10-13 illustretes how the keys are formed for the dimension and fact tables. 139 Fact Table Product Dimension Store Dimension STORE KEY Store Dese District D District Dese Region ID Regice Desc Level STORE KEY PRODUCT KEY TIME KEY Dallas Time Dimension *Do not ese production system keys Fact Talde Compound primary key.one segment for each dimension Dimension Table Generated primary key Figure 10-13 The STARcheme keys Primary keys Each row in a dimension table is identified by a unique value of an attribute des gnated as the primary key oi the dimension in a product dimension table, the primary kay identifies each product uniquely. In the customer dimension table, the customer number identifies each customer uniquely. Similarly, in the sales representative dimension table, the social security number the sales representative identifies each sales representative Surrogate Keys There are two general principles to be applied when choosing primary keys for dimension tables. The first principle is derived from the protslem caused when the product began to be stored in a sfferent warehouse. In ather words, the product key in the operational system has built-in meanings. Some positions in the operational system product key indicate the warehouse and some other positions in the key indicate the product category. These are built-in meanings in the key. The first principle to follow is avald built-in meanings in the primary key of the dimension tables. The surrogate keys are simply system-generated sequence numbers. They do not have any built-in meanings. The STORE KEY is the surrogate primary key for the store dimension table. The operatonal system primary key for the score reference table may be kept as just another non-key attribute in the store dimension table. Foreign Keys Each dimension table is in a one to many relationship with the central fact table. So the primary key of each dimension table must be a foreign key in the fact table. If there are four dimension tables of product date, customer and sales representative, then the primary key of each of these four tables must be present in the orders fact table as foreign keys. Let us reexamine the primary keys for the fact tables. There are three options