Handling Transactional-Level Data - Expert Cube Development with SSAS Multidimensional Models - page 123

Drillthrough modeling
We started this chapter with a precise requirement. We wanted to add detailed
information about each transaction, such as invoice document and line number, to
our cube. The drillthrough feature we introduced is what we will use to display this
data. However, there are several ways to model this data in the cube, so that it can be
queried using the drillthrough feature.
We have seen that drillthrough can show both measures and related dimension
attributes. Thus, we have two possible ways to model our data—in a dimension or
in measures. The most common approach is to define a separate dimension with
transaction details. This dimension would have a fact dimension relationship with
the related measure group. The other option is to store this data in the measures
defined in a separate measure group, with their AggregateFunction property set to
None (although this is only possible in the Enterprise edition). This option is limited
to numeric attributes, but document and line numbers might be numeric values that
could leverage this option, which we will call drillthrough on alternate fact table.
Drillthrough using a transaction detail dimension
The Sales fact table has two fields, SalesOrderNumber and SalesOrderLineNumber ,
which we want to display in drillthrough queries. We need to create a dimension
that will have these two attributes. To keep our model using surrogate keys in
the key attribute of a dimension, we will use the surrogate key of the Sales fact
table ( ID_FactSales ) as the key of this dimension. All the degenerate dimension
attributes will be attributes of this dimension. In other words, we are creating a
dimension that will have the same granularity as the fact table.
If our degenerate attributes have a cardinality that results in the
dimension's granularity being much higher than the fact table, then
creating a separate dimension table in the relational model and
a regular Analysis Services dimension would be a better idea.