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 >>
Tracy Boggiano
Follow me

Tracy Boggiano

Database Administrator at ChannelAdvisor
Tracy has spent over 20 years in IT and has been using SQL Server since 1999 and is currently certified as a MCSE Data Platform. She has worked on SQL Server 6.5 and up including currently SQL 2017 CTP 2.0. She enjoys monitoring, performance tuning, and disaster recovery technologies.

She also tinkered with databases in middle school to keep her baseball card collection organized.

Her passion outside of SQL Server is volunteering with foster children as their advocate in court through http://www.casaforchildren.org.
Tracy Boggiano
Follow me