In this article we will examine how to design a Calculated Member using Cube Designer in SQL Server Analysis Services 2012.
What is a Calculated Member?
A Calculated Member is the result of customized measures or dimension members, by combining cube data, arithmetic operators, numbers, and functions. For example, you can create a calculated member named 'Gross Profit' wich includes total sales amounts, minus the total product costs. Gross Profit can then be displayed to end users in a separate row or column.
What we'll cover in this session:
- Create Calculated Member
- Parent Hierarchy
- Parent Member
- Additional Properties
- Reviewing the Calculated Member
This article assumes you have the following:
- Installed SQL Server 2012 or 2008 R2, with Analysis Services.
- Attached AdventureWorksDW2012 Data File.
- Experience with SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS). Note: For this session we'll be using SSDT.
- Experience with deploying and attaching to an exisitng Analysis Services database using SSDT or Business Intelligence Development Studio (BIDS).
- Some experience developing with Multidimensional Expressions (MDX) language.
Note: A download of the demo is available at the end of this session.
Lets get started!
Step 1 - Define Calculation (Calculated Member)
1) Download and open the attached Analysis Services solution provided at the end of this turorial.
2) Under the Cubes folder, double click on the Analysis Services Tutorial to open the Cube Designer.
3) Click on the Calculations tab.
4) Right click from within the Action Organizer pane and select New Calculated Member.
This will render the Calculated Member designer.
5) Enter '[Product Gross Profit Margin]' for the Name value.
6) Select 'Measures' for the Parent Hierarchy value.
Note: The Parent Hierarchy are descriptive categories of a dimension by which the numeric data (that is, measures) in a cube can be separated for analysis.
Note: Because we are not using Measures for the Parent Hierarchy the Parent Member is unavailable. A Parent Member allows you to place the newly created calculated member under a parent member. The heading for the calculated member is added at the level directly below the parent member you select.
7) Copy and paste the following MDX into the Expression textbox.
([Measures].[Internet Sales-Sales Amount] - ([Measures].[Internet Sales-Total Product Cost] + [Measures].[Internet Sales-Tax Amount] + [Measures].[Internet Sales-Freight]))/[Measures].[Internet Sales-Sales Amount]
8) Select "Percent" for the Format String value.
9) Visible = True
10) Leave Non-Empty behavior blank.
11) Associated Measure Group = Internet Sales
12) You can leave Display Folder blank.
13) Click the Save icon from the Menu bar. You should see something similar.
15) From the menu bar, select Build >> Process. The Process Cube window will render. Click Run... to process the cube. After successful completion, click Close. Click Close again.
Step 2 - Review Calculated Member in Browser
1) Select the Browser tab.
2) From within the Measure Group pane, expand the Measures >> Internet Sales folder. You should see the newly created Calculated Member '[Product Gross Profit Margin]'.
3) Drag the following measures and calculated member over to over to the Report pane:
- [Measures].[Internet Sales-Sales Amount]
- [Measures].[Internet Sales-Tax Amount]
- [Measures].[Internet Sales-Total Product Cost]
- [Measures].[Internet Sales-Freight]
- [Measures].[Product Gross Profit Margin]
4) Expand the Product dimension and drag over the Category attribute. You should now see something similar in your Report pane.
You have completed creating a Caclulated Member using the Cube Designer in SQL Server Analysis Services 2012.
Download: Analysis Services Tutorial.zip (143.49 kb)