9/11 Class Flashcards
design dimensional model
- user requirements - ask users about their most frequent queries
- existing data resources
precomputed attributes
we can have this in a datawarehouse but not in a database
warehouse key
unique identifier for the data warehouse data
every table must have one
design single fact dimensional model
- decide fact measurements
- decide grain of fact - level of detail
- Decide fact table
- Determine primary key for fact table
- decide key dimension tables for a fact table
- Decide analysis dimension tables for a fact table
factless fact table
is good for tracking an event
read chapters 3, 5, 12, 13
do it or at least the bold terms
aggregate fact table
additional table created to measure something
you don’t want too many aggregate tables
conformed dimension tables
a dimension table shared by fact tables
support drill across queries on star schema that help us find connections in data like amount of promotion versus sales
allows for more options to analyze data
multi fact dimensional model
dimension tables
one warehouse key - primary key
dimension attributes
Step 1 decide facts and fact measurements
Fact 1: Sales
Quantity
Price
Shipping fee
Sales Amount = price * quantity
Fact 2:Promotion Cost
CostStep 2: decide grain of facts
find the record for the facts in your fact tables and find the lowest level of
sales - order line
promotion cost - product_promotion record
Step 3: Decide fact tables
sales_fact quantity price shipping_fee sales_AMT promotion_cost_fact cost
Step 4: Determine Primary Keys of Fact Tables
find the natural keys that can uniquely identify records in fact tables and transform into warehouse keys sales_fact #product_key #customer_key #orderdate_key *quantity *price *shipping_fee *sales_AMT promotion_cost_fact # Product_key #Promotion_key *cost
key dimension table
connect the two fact tables with this key table Product #product_key *PID *PName *PCName Promotion #promotion_key *PMID *PM_Name *PM_Date Customer #Customer_key Order Date #OrderDate_key you cannot remove these tables
Step 5: Decide key dimension tables for a fact table
above step
Step 6: Decide analysis dimension tables for a fact table
see notes on canvas
Step 7: Connect Separate dimension models using conformed dimension tables
see notes on canvas
Multi-Valued dimension table tools
determine cardinality
crows foot
means a one to many
referer
tells what site you came from
group bridge
solves many to many tables
D7 Special dimensions
large number of indicators or flags left in the source data
Solution 1:
leave them unchanged in the fact table. This is bad because it makes the table very large and hurts performance
Solution 2:
making tables for every indicator. This is bad it causes a performance issue
Solution 3:
get rid of data = bad :)
Solution 4:
junk dimension: throwing all of the related indicators in one table this is the solution
mini dimension
splitting large tables into smaller tables
D8 Too few or too many dimensions
most dimension models end up with 5 to 15 dimension tables. this is how many should be in your product questions to ask: analysis dimensions role playing dimensions junk dimension mini dimension
local design tools
F1calculation
additive semi additive non additive fact attributes
factless fact table
aggregate
local design tools
D's 1 Slowly changing dimension 2 time dimension 3 snowflake 4 roleplaying dimension 5 conformed dimension 6 multi valued dimension 7 special dimension(junk and mini) 8 too few and too many dimensions
