Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Introduction to Drillthrough

In the last two articles in our series, "Building Calculated Members," we completed a review of many aspects of creating and using calculated members. In the first article of this dual-phased lesson, we practiced the creation of dynamic calculated members, both on measure and non-measure dimensions. We compared and contrasted the two general types to help make selection and planning for real-world uses straightforward in most respects. We discussed ways of handling the creation of calculated members with MDX, and practiced adding multiple members in a single MDX query.

We practiced controlling the order in which calculated members are solved, as well as discussing why precedence of calculation might be critical for generating expected results. In the second article, we moved into the realm of aggregation functions, practicing with cumulative totals and other uses. Finally, we drew upon our exposure to member functions in earlier articles to calculate values based upon the retrieval of member properties with our calculated members, completing practice examples that demonstrated the potential value of this capability.

In this article, we will explore executing drillthrough statements on multidimensional cubes. First, we will discuss scenarios in which 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 we will set up a sample drillthrough in the Cube Editor 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 part of our hands-on exercises.

In this lesson, we will do the following:

  • 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

  • + Share This
  • 🔖 Save To Your Account