1 / 11

MIS 451 Building Business Intelligence Systems

MIS 451 Building Business Intelligence Systems. Logical Design (2) – Design Single-fact Dimensional Model. Attributes in DW tables. Attributes in DW tables. Dimension Table One Warehouse Key --- Primary Key Dimension Attributes Fact table

lillian-osborne
Download Presentation

MIS 451 Building Business Intelligence Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MIS 451Building Business Intelligence Systems Logical Design (2) – Design Single-fact Dimensional Model

  2. Attributes in DW tables

  3. Attributes in DW tables • Dimension Table • One Warehouse Key --- Primary Key • Dimension Attributes • Fact table • Warehouse keys --- A collection of warehouse keys from all its associated dimension tables • All warehouse keys in fact table are foreign keys referring to its associated dimension tables • All/part of warehouse keys in fact table form the primary key of fact table • Fact Attributes

  4. Design Steps Step 1: Decide fact and fact measurements: • What do customers want to analyze? --- Fact • Sales • Find measurements of fact in the source database – Fact Measurements • Measurements of sales --- price, quantity, price*quantity

  5. Design Steps Step 2: Decide grain of fact: • Grain is the level of detail at which fact measurements will be stored in data warehouse. • Potential grain of sales --- order level, order line level • Data warehouse usually stores facts at their lowest level of detail. • Grain of sales --- order line level

  6. Design Steps Step 3: Decide fact table: • Fact  Fact Table • Sales  Sales Table • Fact Measurements  Fact Table Attributes • Price  Price Attribute • Quantity  Quantity Attribute • Price*Quantity  Sales Amount Attribute

  7. Design Steps Step 4: Determine Primary Key of Fact Table Find the natural keys that can uniquely identify records in fact table from the source database – primary key of fact table • Potential primary key 1: OID, OLID • Potential primary key 2: PID, CID, ORDER_DATE Transform natural keys into warehouse keys • PID PRODUCT_KEY • CID CUSTOMER_KEY • ORDER_DATE TIME_KEY

  8. Design Steps Step 4: Determine Primary Key of Fact Table

  9. Design Steps Step 5: Decide key dimension tables for a fact table: • Key dimension tables provide primary key for a fact table. For example,

  10. Design Steps Step 6: Decide analysis dimension tables for a fact table: • Analysis dimension tables provide foreign keys for fact tables.

More Related