Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services , Part II

Monday Dec 15th 2003 by William Pearson
Share:

Create calculated cells via an MDX query. In this article, Bill Pearson extends his introduction of calculated cells in Analysis Services to the realm of direct MDX, where we practice hands-on calculated cell creation at the query level.

About the Series ...

This is the eighteenth article of the series, Introduction to MSSQL Server 2000 Analysis Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series is an introduction to the practical creation and manipulation of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services ("MSAS"), with each installment progressively adding features and techniques designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.

Introduction

In this article, we will extend the exploration of calculated cells that we began in our last session, Using Calculated Cells in Analysis Services, Part I. In Part I we learned that calculated cells offer functionality previously reserved for calculated members, custom members, and custom rollup formulas (all of which we have explored in previous articles) to a specific range of cells-or even to a single cell. We will revisit the construction of a calculated cell, touching upon another means of creating one: through an MDX query.

In this lesson, we will do the following:

  • Overview the creation of calculated cells from the perspective of an MDX query
  • Discuss two approaches to the creation of a calculated cell via an MDX query
  • Discuss the ramifications of the two approaches, within the context of scope assigned by each
  • Practice creation of a calculated cell using the WITH clause in an MDX query
  • Parallel the methods within the MDX query to mirror the actions we performed in Using Calculated Cells in Analysis Services, Part I within the Analysis Services Manager and associated subcomponents, including:
    • creation of a calculated cell;
    • formatting of calculated cell contents;
    • manipulation of other cell properties, such as FORE COLOR and BACK COLOR, to enable exception highlighting.

Calculated Cells in an MDX Query

As we learned in Part I, the value within a calculated cell is computed at run time through an MDX expression, which is specified when the calculated cell is defined. The expression can be conditionally applied to a cell or range of cells, based upon an MDX logical expression, which would also be specified at the point of definition of the calculated cell.

We explored the means of creating a calculated cell from the Analysis Manager, and practiced various aspects of the creation of a calculated cell in an illustrative example, in our last lesson. Our interaction with calculated cells at that juncture was through the graphic interface of the Analysis Manager, from which we input some of the supporting MDX syntax, via the appropriate Member Properties fields. In this lesson, we will perform all the steps for calculated cell creation directly in MDX.

Overview

As in the process of creating a calculated member within an MDX query (see my various DatabaseJournal articles, index page, for a listing), the WITH clause comes into play: We can also use the WITH clause to define calculated cells and their properties. Alternatively, calculated cells can be created in an MDX query using the CREATE CELL CALCULATION statement, but we will focus on the WITH clause approach. This would be a typical approach for creating a calculated cell with session scope, in contrast to the global scope one would expect to provide with a calculated cell created by using the Calculated Cells Wizard or by using the CREATE CELL CALCULATION statement. We discussed session and global scope in Part I.

Creating a Calculated Cell in MDX

We will begin our creation of a calculated cell by firing up the MDX Sample Application once again, as it provides a platform that is available to most MSAS users from which to build MDX queries.

1.  Go to the Start button on the PC, and then navigate to Microsoft SQL Server --> Analysis Services, then to the MDX Sample Application.

We are initially greeted by the Connect dialog, shown in Illustration 1 below.


Illustration 1: The Connect Dialog for the MDX Sample Application

The illustration above depicts the name of my server, MOTHER1, and properly indicates that we will be connecting via the MSOLAP provider (the default).

2.  Click OK.

(We might also choose to cancel the dialog box and connect later by clicking Connect on the File menu.)

The MDX Sample Application window appears.

3.  Clear the top area (the Query pane) of any remnants of queries that might appear.

4.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

5.  Select the Warehouse cube in the Cube drop-down list box.

The MDX Sample Application window should resemble that shown in Illustration 2, complete with the information from the Warehouse cube displaying in the Metadata tree (left section of the Metadata pane).


Illustration 2: The MDX Sample Application Window

We will create an MDX query that defines and creates a calculated cell, and that also returns cell properties as a part of the query result. Let's start with a simple core query, upon which we will base our work in the steps of the practice example.

6.  Type the following core query into the Query pane of the sample application:


-- Step 1, Calculated Cells Tutorial
SELECT  {[Measures].[Warehouse Cost]} ON COLUMNS, 
    [Warehouse].[Warehouse Name].Members ON ROWS 
FROM Warehouse

7.  Execute the query using the Run Query button.

The results dataset appears as shown in Illustration 3.


Illustration 3: Results Set, Core Query

8.  Save the query as ANSYS18-1, leaving it open for the next step.

Now we will add a WITH clause, containing the definition of a calculated cell, which, for this exercise, we will call "Warehouse Cost Scope."

9.  Remove the comment line (-- Step 1, Calculated Cells Tutorial) from the top line of the query.

10.  Add the following syntax into the Query pane, before the SELECT clause:

-- Step 2, Calculated Cells Tutorial
WITH 
   CELL CALCULATION [Warehouse Cost Scope] 
   FOR '([Warehouse].[Warehouse Name].MEMBERS)'
   AS 'CalculationPassValue (Warehouse.CurrentMember, 0)',
      CONDITION = 'CalculationPassValue ((Warehouse.CurrentMember, 
          [Warehouse Cost]), 0) > 11000'

The full query should now appear in the Query pane as shown in Illustration 4.


Illustration 4: Creating the Calculated Cell via the WITH Clause

The WITH clause contains several of the required elements that were "filled in" by the Calculated Cells Wizard, as we saw in Part I. Let's look at each in turn to reinforce our understanding.

The first part of the WITH clause function

WITH 
   CELL CALCULATION [Warehouse Cost Scope]

functions just like it did for the creation of a calculated member, as we saw in Part I. It defines the calculated cell (CELL CALCULATION is the syntax), giving it a name (Warehouse Cost Scope). The next line of the WITH clause,

FOR '([Warehouse].[Warehouse Name].MEMBERS)', 

equates to the first step of the Calculated Cells Wizard, and is the point of definition of the first of the three required elements for a calculated cell, the subcube (or cube section / region). We are defining our subcube as the Warehouse Names members of the Warehouse dimension. (Note that we kept it simple for the exercise: we can have multiple dimensions specified here, together with additional, more sophisticated syntax for other purposes).

The next line of the clause,

AS 'CalculationPassValue 
   (Warehouse.CurrentMember, 0)',

represents the calculation formula, the second of the three main requirements. As we learned in Part I, the calculation formula is responsible for the ultimate value of the cell once it is evaluated by Analysis Services - and provided that any given cell concerned 1) lies within the subcube definition and 2) passes the conditional test (if any) imposed by the third main part of the calculated cell definition, the condition statement itself. We are not seeking to change the values themselves, merely to highlight them; therefore, we are basically making the cells targeted "equal to themselves." (See the comments in Part I for the corresponding step in the Calculated Cells Wizard, to review the reasoning behind the use of the CalculationPassValue function.)

The last line of the clause,


CONDITION = 'CalculationPassValue ((Warehouse.CurrentMember, 
      [Warehouse Cost]), 0) > 11000',

fills the third (optional) requirement in defining the calculated cell, and supplies the calculated cell condition that is imposed upon the subcube cell selection to determine if the value in the calculation formula is assigned to the respective cells. (The comments in the earlier section surrounding the use of the CalculationPassValue function apply here, as well.)

11.  Execute the query using the Run Query button.

The results dataset appears as depicted in Illustration 5.


Illustration 5: Results Set, MDX Query Step 2

As we can see, it is difficult to tell if any benefit is obtained through our newly created calculated cell: No tangible evidence of its presence appears. We see no highlighting at this stage of creation, because we have yet to define the setpoints for the cell properties we wish to put in place to make the highlights appear. We will accomplish this in the following steps.

12.  Save the query as ANSYS18-2; leave it open, once again, for the next step.

13.  Change the comment line to read as follows:

-- Step 3, Calculated Cells Tutorial

14.  Immediately following the CONDITION = 'CalculationPassValue ((Warehouse.CurrentMember, [Warehouse Cost]), 0) > 11000', (ensure that there is a comma after > 11000') line of the WITH clause, insert:


FORE_COLOR = '16711935', BACK_COLOR = '0', 
     FORMAT_STRING = '$#,##0.00;($#,##0.00', FONT_FLAGS = '3'

Note: Many of the keywords use underscores - in this case all with spaces between two-worded keywords.



This puts into place the cell property setpoints, namely the ForeColor of magenta and the BackColor of black, which we used in Part I, together with the same format string that we used. For details surrounding these setpoints, please see the discussion surrounding properties in Part I.



All that remains is to "enable" the property setpoints by adding a final line to the query. We will employ the CELL PROPERTIES keyword, along with one or more intrinsic cell properties: in our case, these are FORE_COLOR and BACK_COLOR.



15.  Immediately following the FROM Warehouse line of the SELECT clause, insert:




CELL PROPERTIES VALUE, FORMATTED_VALUE, 
     FORE_COLOR, BACK_COLOR, FONT_FLAGS


The combination of the CELL PROPERTIES keyword and the properties that follow it, as shown above, is not required. We just need to keep in mind that, by default, if the CELL PROPERTIES keyword is not used, the cell properties returned are VALUE, FORMATTED_VALUE, and CELL_ORDINAL, and that, if the CELL PROPERTIES keyword is used, only those cell properties explicitly stated with the keyword line are returned. So it is basically a case of "if you specify, specify completely."



The full query should now appear in the Query pane as shown in Illustration 6:




Illustration 6: Adding the Cell Properties Information to Finish

16.  Execute the query via the Run Query button.

The results dataset appears as shown in Illustration 7.


Illustration 7: Results Dataset, MDX Query Step 3

We see the effects of the calculated cell that we have created in action, with the appearance of the exception highlighting we defined it in the MDX query, for individual Warehouses with Warehouse Costs over $ 11,000.

17.      Save the query as ANSYS18-2, closing the sample application when finished.

In addition to providing exception highlighting, as we have seen in the two exercises above, calculated cells could provide other functionality. Examples include:

  • Customization of query results
  • Restriction of calculated cells-based formulas to precise data subsets (through subcube definition)
  • Efficient and simple subset manipulation
  • Flexibility and portability options
  • Provision of a cube structure layer outside (and independent of) cube physical structure
  • Capacity for multi-pass evaluation (such as in a "simultaneous equation" scenario where costs are allocated in more than one step)

An added benefit of using the powerful calculated cells functionality relies upon the fact that they can be configured on the server via Analysis Manager, much as we accomplished in Part I. This can mean the deployment of enhancements to distributed applications and user interfaces with immediate benefit, in many cases without the immediate necessity of reprogramming the interfaces / applications to take advantage of the new features that accrue.

Summary ...

In this lesson, we returned to the exploration of calculated cells which we began in Part I. We focused on the construction of a calculated cell via an MDX query. We overviewed the creation of calculated cells from the perspective of an MDX query, discussing approaches to their creation, as well as associated ramifications of each approach, within the context of scopes assigned.

We then practiced creation of a calculated cell using the WITH clause in an MDX query, paralleling our steps within MDX to mirror the actions we performed in Part I, within the Analysis Services Manager and associated subcomponents. Our steps included the creation of a calculated cell, formatting of calculated cell contents, and the manipulation of other cell properties, such as FORE COLOR and BACK COLOR, to enable exception highlighting.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved