Details about transactional data

The goal of a multidimensional cube is to analyze aggregated data across several dimensions. However, when there is some interesting data, the user might be interested in drilling down to a lower level of detail. For example, when it comes to sales analysis, it could be interesting to look at the individual invoices that caused a particular high volume of sales in a single month. This is a very common request for end users to make; in fact, the question is not if the users will need this, but when.

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...