Skip to content

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 (
    • Remember cannot be used in SSAS, will need to create intermediate table
  • Appropriately index a fact table using columnstore indexes (
    • 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 (
  • Additive measures– Can be summarized across all dimensions
  • Semi-additive measure – Can be summarized across some dimensions (
  • 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 >>

Leave a Comment