Designing More Complex Dimensions - Expert Cube Development with SSAS Multidimensional Models - page 71

For the start and end date columns, at this point the only recommendation to make
is that rather than storing them as DateTime types, it's better to use the same integer
surrogate key format we're using for your Time dimension surrogate keys. This will
give us the option of creating role-playing dimensions based on our Time dimension
which can then be joined to your SCD on these columns with a Referenced
relationship, which again will allow us to filter our Customer SCD attribute to only
return those members that were current within a specific date range. It will also,
if required, provide the platform for building even more sophisticated models for
analyzing the changes made to a dimension over time, such as those described in
the Cross Time and Transition Matrix sections of the white paper The Many-to-Many
Revolution , available at http://tinyurl.com/m2mrev .
The Type property of an attribute hierarchy can be set to
SCDStatus , SCDStartDate , and SCDEndDate for these
three columns. These properties have no effect on the behavior
of Analysis Services and are purely descriptive.
Type III SCDs
A Type III SCD uses two columns to capture the current and either the previous
or original states of something—it allows us to store history, but only a limited
amount; probably as a result this approach is very rarely used in the real world.
In Analysis Services, this translates to two different sets of attributes; for example,
in our Customer dimension example, we might have four attributes for Current
City , Current Country , Previous City , and Previous Country , with the attribute
relationships modeled as follows: