Dimensional Data Modeling

1
Dimensional Data Modeling

• Lecture 3 Dimensional Modeling Considerations

2
Monster Dimensions

• Case where the dimension table is very large
  (millions of rows) changes frequently and
  contains a large number of attributes
• Example Customer
• Assume there is a small set of variables that
  change often

• Customer_Dim
• Name
• Address
• etc
• Income
• Education
• Marital Status
• Etc.

Sales_Fact
3
Monster Dimensions

• Strategy split the variables that change
  frequently into own table.
• Need to band continuous variables like salary
  into ranges (20000-40000, 40000-60000, etc).
• Each variable on the new table needs to have
  small number of variables.
• Need to create one row in the new table for each
  combination of variables. Assume 2 variables
  with 5 values each. How many rows would be in
  the table?

4
Monster Dimensions

• Answer 25 (55)
• The base Customer Dimension table contains the
  key to the new table. Keys of both the new key
  and the are placed as foreign keys on fact table

• Customer_Dim
• Cust_key
• Name
• Address
• Demo_key

Sales_Fact Cust_key Demo_Key

• Demo_Dim
• Demo_key
• Income_Range
• Education
• Marital Status

What happens when customer income changes?
5
Degenerate Dimensions

• Consider an order. What is the grain of
  information we would want in a fact table?
• What information is left at the order level,
  other than the information that we place in the
  fact table (date, customer, product, quantity,
  etc.?)

6
Degenerate Dimensions

• The answer probably only the order number.
• A degenerate dimension is one that has no
  attributes other that the key value
• Strategy make the order number an attribute of
  the fact table. It will look like a dimension
  key, but will not join to anything it is just
  an attribute.
• This allows us to perform analysis at the order
  level (GROUP BY)
• What are some other degenerate dimensions?

7
Different types of facts

• There are multiple types additive,
  semi-additive and non-additive.
• Additive can add the values across all
  dimensions (e.g., sales revenue).
• Semi-additive Certain types of facts are not
  perfectly additive but represent a snapshot at
  a point in time (account balances, inventory
  balances). These cannot be treated the same as
  perfectly additive facts
• Non-additive Some facts can be textual
  (non-additive). Basically, can only count these.

Example of a non-additive fact?
8
Families of facts

• When designing a data warehouse, need to think of
  the process to be supported.
• Its important to realize that this translates
  into a set of related facts a value chain.
  Examples
• Inquiry ? Order ? Shipment? Invoice ? Return?
  Credit

9
Transaction and Snapshot Facts

• When the operations of an organization are
  examined, its important to realize that most
  organizations want to look at their information
  on a transactional and a snapshot basis.
• Transaction basis look at individual
  transactions (inventory movement, sales, ATM
  transactions, etc.). Allows analysis of patterns
  of behavior (time of day analysis, market basket
  analysis, etc.)

10
Transaction and Snapshot Facts

• Typically created in addition to a transaction
  fact.
• Typically, create snapshots at the end of
  specific reporting periods (month-end, etc.)
• Rolling snapshot continuously update then
  publish advantage spreads the work. Example
  monthly sales. Bank month end account balance
  and monthly transaction counts, etc.

11
Snapshot Example
ATM Activity Snapshot (Foreign_Keys) . . . Tran
saction count Account balance Revenue_earned Avera
ge_daily_balance . .
A snapshot for ATM usage, by account, by month
12
Factless Facts

• Type of fact where there is no real measure
  (additive or otherwise)
• Typically factless facts related to events
• Attendance in class, for example
• Typically, add a dummy variable with value of 1
  for purpose of counting