Chapter 5. Handling Transactional-Level Data
In this chapter, we will analyze how to add detailed information about each transaction in a fact table, such as invoice document and line number. We'll compare the use of MOLAP (Multidimensional Online Analytical Processing) and ROLAP (Relational Online Analytical Processing) dimensions for this purpose, and we will use the drillthrough feature to expose this data to the end user. We will also explain the reason this approach is better than exposing a large dimension directly to the end user.
In the second part of this chapter, we will add a dimension to the sales cube that describes the reasons for a sale. Since each sale can have multiple reasons associated with it, we will make use of the many-to-many dimensions relationship feature of Analysis Services, discussing its properties and possible performance issues. We will also take a brief look at possible modeling patterns available using many-to-many dimension relationships.
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...
In conceptual terms, the drillthrough feature of Analysis Services allows you to retrieve data at the fact table granularity for any multidimensional tuple. From a practical point of view, when users look at a pivot table result, they can request the list of fact table rows that are aggregated into any specific cell in the result set. For example, the following screenshot shows the sales of a particular month divided by product categories and regions:
With Excel, when you double-click on a cell (for instance, the sales of Accessories in Europe, whose value is 1,140.65) a DRILLTHROUGH query is sent to Analysis Services and the result is shown in a new worksheet, returning the list of fact table rows that correspond to that cell, as shown in the following screenshot:
Note
The drillthrough operation differs from a generic drilldown because it is intended to retrieve a set of rows from the relational source data that has been aggregated into a single cell, while drilldown typically...
Many-to-many dimension relationships
In the dimensional model, the fact table has a many-to-one relationship with each dimension. However, sometimes this kind of modeling cannot represent the real world: for example, a product might belong to several categories. One way of solving this problem might be to choose a "primary" category for each product, to allow the use of a classical star schema. But, doing this, we lose possibly important information.
Analysis Services 2005 introduced the ability to handle many-to-many relationships between dimensions. This feature brings to the OLAP world the approach of modeling many-to-many relationships using bridge tables or factless fact tables that we saw in Chapter 2, Building Basic Dimensions and Cubes.
Implementing a many-to-many dimension relationship
Our example scenario for implementing a many-to-many relationship is based on Sales Reason. In Adventure Works, each Internet sale has a list of reasons for the transaction. This list is the result of...
In this chapter, we have seen how to use the drillthrough feature, discussing in detail several options available to model and optimize drillthrough actions. We have seen the differences between using MOLAP and ROLAP dimensions to enable drillthrough on transaction details. We have also discussed how to include a many-to-many dimension relationship in our model, highlighting that this single feature enables much more complex modeling techniques, giving some examples of the possible scenarios and providing links to external resources about modeling and optimization for this feature. In the next chapter, we will show how to add complex calculations to the model.
