9/11 Class

Question 1

design dimensional model

Answer 1

1. user requirements - ask users about their most frequent queries
2. existing data resources

Question 2

precomputed attributes

Answer 2

we can have this in a datawarehouse but not in a database

Question 3

warehouse key

Answer 3

unique identifier for the data warehouse data

every table must have one

Question 4

design single fact dimensional model

Answer 4

1. decide fact measurements
2. decide grain of fact - level of detail
3. Decide fact table
4. Determine primary key for fact table
5. decide key dimension tables for a fact table
6. Decide analysis dimension tables for a fact table

Question 5

factless fact table

Answer 5

is good for tracking an event

Question 6

read chapters 3, 5, 12, 13

Answer 6

do it or at least the bold terms

Question 7

aggregate fact table

Answer 7

additional table created to measure something

you don’t want too many aggregate tables

Question 8

conformed dimension tables

Answer 8

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

Question 9

multi fact dimensional model

Answer 9

dimension tables
one warehouse key - primary key
dimension attributes

Question 10

Step 1 decide facts and fact measurements

Answer 10

Fact 1: Sales
              Quantity 
              Price
              Shipping fee   
              Sales Amount = price * quantity
Fact 2:Promotion Cost
                Cost

Question 11

Step 2: decide grain of facts

Answer 11

find the record for the facts in your fact tables and find the lowest level of
sales - order line
promotion cost - product_promotion record

Question 12

Step 3: Decide fact tables

Answer 12

sales_fact
  quantity
  price
  shipping_fee
  sales_AMT
promotion_cost_fact
   cost

Question 13

Step 4: Determine Primary Keys of Fact Tables

Answer 13

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

Question 14

key dimension table

Answer 14

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

Question 15

Step 5: Decide key dimension tables for a fact table

Answer 15

above step

Question 16

Step 6: Decide analysis dimension tables for a fact table

Answer 16

see notes on canvas

Question 17

Step 7: Connect Separate dimension models using conformed dimension tables

Answer 17

see notes on canvas

Question 18

Multi-Valued dimension table tools

Answer 18

determine cardinality

Question 19

crows foot

Answer 19

means a one to many

Question 20

referer

Answer 20

tells what site you came from

Question 21

group bridge

Answer 21

solves many to many tables

Question 22

D7 Special dimensions

Answer 22

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

Question 23

mini dimension

Answer 23

splitting large tables into smaller tables

Question 24

D8 Too few or too many dimensions

Answer 24

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

Question 25

local design tools

Answer 25

F1calculation
additive semi additive non additive fact attributes
factless fact table
aggregate

Question 26

local design tools

Answer 26

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