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

If we need to implement drillthrough on a single ID which can be stored in a 32-bit
integer, putting it in an invisible measure in the measure group and including it in
the Drillthrough Columns list will always be the fastest and cheapest way to do
it. If, on the other hand, there are several fields, all numeric, that we want to see in
Drillthrough Columns and that are not already measures in the cube, we might
opt for a separate measure group. We could use a separate fact table too, but if one
hasn't been created it is better to create another view that will be used as the basis for
the secondary measure group that will only contain measures used for drillthrough
operations. This secondary measure group will not need to have any aggregations
designed for it.
It is possible to create two measure groups from the same fact table
in the cube editor, but it isn't obvious how to do it. First of all you
have to add a new distinct count measure to an existing measure
group. Every time you do this, a new measure group is created and
immediately afterwards we can change the AggregateFunction
of the new measure from DistinctCount to None . Adding other
measures requires some manual modification of the XML file
containing the cube definition. Having a separate fact table, even if
only through a view, is much simpler to handle.
Processing a MOLAP measure group does not require sending a SELECT DISTINCT
query to the relational source in the way that a dimension does—a simple SELECT
query is used instead. Therefore, if we are going to use MOLAP storage, the alternate
fact table technique processes faster than the dimension approach, and can also
leverage partitioning, if needed. Also, this new measure group can be processed
incrementally or on a per-partition basis similar to any other measure group.
If we want to use the alternate fact table approach, we might also consider shortening
processing time and lowering used disk space by changing the StorageMode property
of the measure group to ROLAP . However, this is not a good idea for reasons similar
to those that discourage the use of ROLAP dimensions for drillthrough purposes.
While processing time for a ROLAP measure group is practically nothing, at query
time Analysis Services generates a SQL query that is similar to the one generated for
drillthrough on a ROLAP dimension. This query will have several JOIN conditions
and it often produces a full scan of the fact table. Therefore, with large cubes, choosing
ROLAP storage for measure groups can be dangerous; choosing MOLAP for measure
groups created expressly for drillthrough operations is usually a better idea.