Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint®

14



LESSON 2 Dimensional moDeling

This way, all your historical reporting is accurate, and your current reporting is accurate as well.

Otherwise, historical reporting could mistakenly tie sales to the wrong manager.

There are three main types of slowly changing dimensions:

Type I:



Updates changing data only, no historical tracking

Type II:



Tracks historical records, expires the row, and puts a new one into the table

Type III:



Same as Type II, but only tracks a certain number of revisions

It is common to have columns from each type in the same table; for instance, if you need to track

history on last names for employees, but not on their addresses, you may have a Type II LastName

column and a Type I Address column. This is perfectly acceptable and common.

This design has also been proven to improve performance significantly since the main goal of a data

warehouse or BI system is to extract data as quickly as possible. The more denormalized type of this

model lends itself to the quick retrieval of data from the tables to serve to populate a cube, run a

report, or load data into Excel. You’ll do all of these things in later lessons!

Here are some general design tips for working with your dimension tables:

Try to avoid unnecessary normalizing.



In a star schema, this practice is called snowflaking

and while sometimes it is warranted, it usually isn’t, unless your dimension table is ...