More Dimensional Modeling Techniques

Assignment 5: More Dimensional Modeling Techniques

Choose 2 dimensional modeling techniques from the following list:

1. Role-Playing Dimensions

2. Junk Dimensions

For each dimensional modeling technique chosen:

a) Choose a business scenario and business process to model

b) Create a simple dimensional model that clearly illustrates the concept chosen including a fact table with at least 2 facts, a date dimension, and at least one additional dimension

c) Populate the dimension model with at least 2 rows of related data for each table

d) Describe how your model illustrates the concept chosen in at least 1 paragraph

1. a. Role-Playing Dimension:

The business scenario for this dimension is going to consist of a large retailer clothing company, something similar to Nordstrom or Macy's. This scenario will be looked at by its performance over time. Since this is a transaction-grained fact table the primary date column is the order date or the transaction date. This fact table will contain multiple references to a single dimension related to the transaction or order date.

The business process involves a single dimension that simultaneously appears several times in the same fact table. The underlying dimension exists as a single physical table but different roles can be represented as views of the underlying table. By providing descriptive naming we can facilitate understanding the joins. For a retailer company I will be using an online order date dimension. This will be broken down into a small dimensional model that uses the order date dimension and a customer ship-to dimension.

Order Transaction Fact

OrderDateKey

ActualCustomerShipDateKey

CustomerShipToKey

1. b. Role Playing Model:

Fact Table 

Role-Playing Model:

Order Date Dimension View

OrderDateView

OrderDateKey

ActualOrderDate

OrderYear

Customer Ship To Dimension View

CustomerShipDateView

ActualCustomerShipDateKey

ActualCustomerShipDate

OrderYear

OrderMonth