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

We would then set AttributeHierarchyVisible to False on this new attribute
hierarchy and use it as the bottom level of your user hierarchy. This would guarantee
that we only saw one instance of a Customer appear under each City and since it
would be a natural user hierarchy, we would have the best possible performance.
The price we would pay for this would be that your dimension processing time
would increase because of this new, potentially large attribute; we would have to
decide whether this was a price worth paying.
Another benefit of this solution is that the Customer SCD attribute hierarchy is never
made available for direct querying to the end user. It's still necessary to have it there
from a modeling point of view, but the fact it is hidden can be useful: since its key is
based on the surrogate key of the dimension table, the unique names of the members
on it therefore will incorporate surrogate key values. Although not necessarily the
best practice, it is sometimes the case that a data warehouse needs to be completely
emptied and reloaded, and if that happens, it's highly likely that a completely new
set of surrogate key values will be used in a dimension. If this were to happen it
would break existing reports, or worse, cause them to return unexpected data, and
for this reason it's a good idea to avoid exposing surrogate key values through MDX
unique names where possible.
Handling member status
Type II SCDs typically also have three columns in their dimension tables to track
the status of a row: a column that tells us whether this is the most recent, or current
version of the row, perhaps containing either the values Current or Previous ; and
start and end dates that show you the time span for which each row was the current
row. It can be useful to include this information in your Analysis Services dimension,
although it's by no means always necessary or even a good idea.
Building an attribute from the Current version column is straightforward: we
get an attribute hierarchy with an All Member and two members underneath it for
the True and False values. Doing this will, for instance, allow us to write queries
and calculations that filter our Customer SCD attribute to retrieve only the current
versions of each Customer so we can answer questions, such as "How many of my
customers are married?" We may not want to make this attribute visible to our users
though because it will also slice measure values so they only show values for the
current version of each Customer , which probably isn't very helpful.