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 Superhero 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 Management and Analytics. She has worked on SQL Server 6.5 and up including currently SQL 2017 RC1. She enjoys monitoring, performance tuning, and high availability and disaster recovery technologies. Tracy is currently a co-organizar the for special interest group for Advanced DBA Topics for the TriPASS user group.

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

Tracy has volunteered through the NC Guardian ad Litem program since 2003 advocating for abused and neglected foster children in court.This is her passion outside of SQL Server and favorite job.More information about this program in North Carolina can be found at http://volunteerforgal.org or the national organization CASA at http://www.casaforchildren.org.
Tracy Boggiano
Follow me