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

This entry is part 4 of 8 in the series Exam 70-458

 

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

Series Navigation<< Exam 70-458 – Objective 3 – Implement high availabilityExam 70-458 – Objective 5 – Extract and transform data >>

Related Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.