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

One approach to solve this issue is to add a regular dimension to the cube which
has the same granularity as the fact table (as we saw in Chapter 2 , Building Basic
Dimensions and Cubes ); this is referred to as a Fact dimension . Using columns such
as invoice number, invoice line number, and notes on the fact table, we can link
each fact sale with a dimension member, calling the dimension itself something as
Document . At this point, the end users will have a dimension that can be used with
other one. If they filter by a particular month in their client tool and put the Invoice
Number attribute of the Document dimension on rows, the resulting query will
display the list of invoices of that particular month. However, this is not a good idea.
The most important reason to avoid making a fact dimension navigable to end users
is performance. Adding a large dimension to the cube (and a dimension with a
granularity near to the fact table, such as Document, will be always large) increases
the multidimensional space that can be queried. The Analysis Services engine might
spend a lot of time filtering the visible members of the Document dimension, and
this could be critical when the query involves calculated members or other MDX
calculations. The performance of this type of scenario has been improved in Analysis
Services 2008 and then 2012, if compared to 2005. However, even if the query
was lightning fast, there is still an issue for the client tool: before making a query,
many client tools get all cube and dimension metadata to populate their navigation
controls. A Document dimension might have millions of members on a single
attribute, with no possible hierarchies to group them. Getting this metadata will slow
down the client tools even before the user is able to query the cube. Some client tools
are able to handle large dimensions quite well, but the original problem is always the
same: why should we display a dimension when we really don't need to navigate it?
If we consider the original requirement carefully, there is a similarity between
the need to get information at the transaction level and the need to get descriptive
properties of an attribute member of a dimension. When we see a member on the
Customer hierarchy, we might want to see the customer's phone number, their
picture, or their address on a map, but this information will never be used as the
filter in a query. For this reason, we should set the AttributeHierarchyEnabled
property of all these descriptive attributes to false; this means, they are only visible
as properties of a Customer member. The need to see the transaction details is
somewhat similar, with the difference being that the information is related to each
single row of the fact table. Analysis Services offers the drillthrough feature, which
should satisfy this kind of need. However, this functionality is not perfect in its
actual implementation. There are limitations in its use and you'll often encounter
performance issues using it. In the next section, we will describe how to use the
drillthrough feature in the most effective way.