In this lesson, Drilling Through to Details: From Two Perspectives, we will explore executing drillthrough statements on multidimensional cubes. First, we will discuss scenarios where drillthrough from summary cube data to the underlying details might be valuable to information consumers. Next, we will examine strengths and weaknesses of the capability in MSSQL 2000 Analysis Services. We will discuss the steps that need to be taken to implement drillthrough, then set up a sample drillthrough in the Cube Editor, so as to focus on concepts in an introductory fashion.
We will then practice the creation and use of an MDX query that uses the DRILLTHROUGH statement to retrieve the source data for a cube cell. We will explore the syntax for the DRILLTHROUGH statement, and discuss options and parameters that surround this functionality, as a part of our hands-on exercises.
In this lesson, we will:
- Enable a cube for Drillthrough in the Cube Editor;
- Perform a Drillthrough within Analysis Services;
- Discuss filtering and other aspects of Drillthrough in general;
- Discuss Drillthrough using an MDX Query;
- Perform a Drillthrough using MDX.
Overview of Drillthrough in Analysis Services
One of the attractions of using an OLAP cube is the speed with which it allows us to browse and report upon summary data for the organization. The summary information that we are retrieving typically aggregates detailed transactions at a lower level. The aggregated totals stored in the cube can represent thousands (sometimes far more) of rows of information (transactions) within the data source from which it originates.
The source containing the detail data is often built to be optimized for Online Transaction Processing (OLTP), and, while it can most often deliver summary information to us directly, the process of aggregation is far more optimal in the database (our cube) that is designed for Online Analytical Processing (OLAP). The OLTP source would effectively have to aggregate every line of detail whenever we needed a total of any sort, and would take longer to provide us with that total, than would the cube, which stores data at the summary level at all times.
Thus, the strength of the OLAP data source is its ability to deliver aggregated data rapidly. The disadvantage of this scenario becomes evident when we need to view underlying detail. One of the many business advantages that we seek, through the implementation of business intelligence, is the capability to analyze data, to track activities and balances through to the causative factors, and, therefore, to determine ways to increase or decrease the activity, as we deem desirable.
For example, say a single store stands out from its peers in an analysis of stores income, because it has a higher profit margin consistently. We want to determine what the store is doing differently, so that we can attempt to apply the same principles to our other stores, to achieve the same desirable effects, or to at least better their current results. We might also see an increase in overall HR costs, and decide to investigate the underlying causes. After isolating and examining the detail that supports the balances we have determined to be unusually high, we might find that increases in turnover are driving higher overall HR costs, as turnover means increased training, recruitment expenses, and so forth. In cases like these and many others, effective analysis relies upon drillthrough, or the ability to look at the transactions underneath the aggregate numbers, with which we typically begin the analysis process.
We must be mindful of the fact that, in the case of most data warehouses, the data we see in the drillthrough presentation is the detail in the fact table of the data warehouse, and not necessarily the data as it appears in the original OLTP data source. As a result, some pre-aggregation, etc., might have occurred during the ETL process that brought the data to the fact table from the OLTP source. This is certainly a drawback, in the eyes of many, who would prefer to "go further," and be able to drill all the way to the original OLTP data source straightway. While this is certainly possible with programming, through Data Transformation Services, or through the use of a combination of approaches, the "native" drillthrough supports only drilling to the table from which the data enters Analysis Services. In this case, the fact table is that source.
We will examine the drillthrough process within the Cube Editor, inside Analysis Services, to gain an understanding of how we can enable drillthrough for our cubes. We will then examine the drillthrough process, and discuss a few of its characteristics and limitations, while we are inside Analysis Services, to gain a comfort level for the concepts before diving into the MDX approaches to drilling through.
Next, we will look at drillthrough from an MDX query, and discuss how we might apply filters to the views we receive within drillthrough, as well as other attributes of the process that bear consideration.