Data Warehouse Database

The next components of a data warehouse are its database and the management system. As data is integrated into the data warehouse it builds up in layers. Each layer forming a snapshot of the organisation at a particular moment in time.

The subject table of the star schema becomes the fact table. Each dimension becomes a dimension table. Each of the dimension tables will have a 1:n relationship with the fact table.

• sales (member code, wine code, order time code, area code, quantity, itemcost)
• member (member code, membername, memberaddress)
• wine (wine code, name, vintage, description, bottleprice, caseprice)
• area (area code, areadescription)
• time (time code, date, periodnumber, quarternumber, year)

A few things about the tables:

• No need to record relationships and descriptions. These are implicit in the star schema.
• Primary key of the fact table is a composite of all the primary keys from the dimension tables.
• Time dimension table.
• Business may have a different financial year.

Fact table need not be normalized. Makes access quicker!? The facts in the fact table are not part of the fact table primary key. This is to enable the data for these non-primary key facts to be processed by an aggregate function.

Comments, suggestions, ideas to
Stuart Banner