Tracy Boggiano's SQL Server  Blog Rotating Header Image

Exam 70-458 – Objective 4 – Design and implement a data warehouse

Design and implement dimensions

Design and implement fact tables

  • Design a data warehouse that supports many to many relationship (http://msdn.microsoft.com/en-us/library/ms170463.aspx)
    • Remember cannot be used in SSAS, will need to create intermediate table
  • Appropriately index a fact table using columnstore indexes (http://msdn.microsoft.com/en-us/library/gg492088.aspx)
    • Used for read-mostly tables, updates that append data, partitioning permitted and index drop/rebuild, queries that reads lots of data
    • Do not use if updated frequently, partition switching and rebuild indexes don’t fit your situation, or for small queries
    • Only one per table
  • Partitioning (http://msdn.microsoft.com/en-us/library/ms188730(v=sql.120).aspx)
  • Additive measures– Can be summarized across all dimensions
  • Semi-additive measure – Can be summarized across some dimensions (http://technet.microsoft.com/en-us/library/ms175356.aspx)
  • Non-additive measures – Normally a calculated measure
  • Implement fact tables
    • Grouping facts – make sure facts have the same dimension relationships in the fact table, no nulls to the dimension tables
    • Fact tables should have all keys to the relating dimension tables
    • Primary key, composite of dimension relationships
  • Determine the loading method for the fact tables
  • Implement data lineage of a fact table (see notes on dimension tables)
  • Design summary aggregation tables
    • Record the data need to give you aggregations, ex. record count and sum, if you need an average

 Preparation resources

Click here to go back to main page for exam 70-458.

Leave a Reply

%d bloggers like this: