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

Test your knowledge of demographics with this quiz, covering gender, age, marital status, and more!