Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint®

Creating Calculations

Now that you know the basics of MDX, this lesson will show you the most important applica-

tion for it: creating custom calculations. Calculations are ways of deploying MDX code snippets

to the server so that new measures show up when you’re browsing the cube. When you create a

calculation, only the formula is stored in SSAS and no new data is stored in the cube. This makes

processing of these measures very fast; querying using them, however, is slower. In some cases,

the only way to implement logic for your browsers easily is through calculations.

To start this lesson and make sure you have the same configuration of your cube

as we do, please download Lesson16Complete.zip, which is available as part of

the download for this lesson from the Wrox website (

www.wrox.com). After down-

loading, make sure you can deploy the file to your development SSAS instance.

The download for this lesson also includes Lesson18Complete, which is the

solution that includes the completed calculation code.

CALCULATION BASICS

To create a basic calculation, open the Cube Editor and go to the Calculations tab. Right-click

the Script Organizer pane in the top left and select New Calculated Member. Type the calcula-

tion in brackets, like [Total GPM]. Brackets are required for any calculations that have spaces

in them, so it’s a good habit to add them to all calculations. In the Expression box, ...