Demographics Survey Quiz

DIM_DEMOGRAPHICS

• Pre-loaded with every combination of responses
• Columns: DemoKey, Gender, Age, AgeGroup, MaritalStatus, ReturnStatus, Child15Status
• Contains 8,100 rows (calculated by multiplying the number of potential values for each attribute)

Junk Dimensions

• Calculated by taking the total number of potential values for each attribute
• Should be restricted to a total potential size of up to 20,000 rows
• May want to remove unrealistic combinations, but this adds complexity to the load process

Data Model

• The Query: selects sum of QuantitySold and sum of NetPrice from SalesFact, filtered by DivisionName, CategoryName, and CharDate
• Critical Success Factors: a central goal, e.g. to grow sales across all market segments and product lines
• Business Questions: specific questions tied to data, e.g. how many men's statement shirts were sold in the Philippines during NCAA semifinals 2016 and what was the total net sales?

Dimensional Modeling Process

• Consists of four main steps: select the business process to model, declare the grain of the business process, choose the dimensions that apply to each fact table row, and identify the facts
• Dimensional modeling is both science and art

Cardinal Merch Case Study

• Step 1: select the business process to model, e.g. POS retail sales
• Step 2: declare the grain of the business process, specifying the level of detail associated with fact table measurements
• Choose the most granular or atomic information captured by the business process

Date Dimension

• Example attributes: Date, CharDate, etc.
• Grain conveys the level of detail associated with fact table measurements

Product Dimension

• Example attributes: SKU Number, UPC, Product Description, Brand Description, etc.
• Product dimension will contain about 150K rows when accounting for different merchandising schemes across stores and historical products
• Product hierarchy: SKU=>Brand=>Category=>Department

Store Dimension

• Example attributes: Store Number, Store Name, Store Street Address, etc.
• Represents primary geographic dimension
• Store hierarchies include: Store=>State, Store=>District=>Region, etc.

Additivity

• If CMI wants to look at gross margin, it's calculated by gross profit/sales dollar amount
• Should choose to store gross profit or gross margin as a fact

Simple Warehouse Data Model

• Consists of DateDim, ProductDim, SalesFact, and StoreDim tables
• Each table has primary keys and foreign keys to establish relationships

Time of Day

• If we add Time to the Date dimension, the size of the Date_Dim for 10 years of data to the minute would be significant
• Alternatively, we can add it as a new dimension, changing the grain to Store, Date, SKU, Transaction ID, Time

Promotion Dimension

• Example attributes: Promotion Code, Promotion Name, Price Reduction Type, etc.
• We can create a new dimension to support tracking promotions, with a promotion type 'No Promotion' to account for non-promotion sales
• Revised schema includes the Promotion dimension, making the grain Store, Date, SKU, Transaction ID, Time, Promotion