- About the Series
- Introduction to Drillthrough
- Overview of Drillthrough In Analysis Services
- Enabling Drillthrough for an OLAP Cube
- Reprocessing the Cube to Include Drillthrough
- Drilling through from Analysis Services
- Drilling through from an MDX Query
- The Drillthrough-enabled MDX Syntax in Action
- Running the DTS Package and Viewing Query Results
- Next in Our Series
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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 although 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 the cube would (which stores data at the summary level at all times).
Thus, the strength of the OLAP data source is its capability 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, suppose that a single store stands out from its peers in an analysis of store income because it consistently has a higher profit margin. We want to determine what the store is doing differently, so 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 because 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 preaggregation and so forth 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 straightaway. Although 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, in order 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.