Developing a Calculated Measure



By:

Overview

Measures created directly from the fields of a fact table are called base measures. But often we require measures based on custom requirements, so we apply some logic and/or formula to these base measures and create calculated measures. We will add two measures from two measure groups and create a calculated measure.

Explanation

Open the cube designer, and click on the Calculations tab. Click on “New Calculated Measure” from the toolbar, and key in the values as shown in the below screenshot.



We have named this new calculated measure “TotalSales”. The "Parent hierarchy" specifies which parent hierarchy the measure will be part and in this case it will be “Measures”. It’s a built-in hierarchy and all measures normally fall under this.

In the Expression, we can specify any MDX expression. Here we are adding Internet Sales Amount from FactInternetSales and Reseller Sales Amount from FactResellerSales measure groups. You do not need to type the values you can just drag and drop values from the panes on the left-hand side of the window.

In the additional properties you can set additional options for this measure. Save your solution, in the next section we will create named sets and then deploy these at the same time.


Last Update: 5/3/2011






Comments For This Article




Thursday, December 20, 2018 - 5:17:49 AM - Ishvari Back To Top (78532)

 I have used all the above process but having below warnings. 

Now Cube is created but I can't able to browse it. Does any steps that I missed?

 

Hierarchy [Cube Dim Sales Territory].[Hierarchy] : Attribute relationships do not exist between one or more levels of this hierarchy.  This may result in decreased query performance.

Dimension [Cube Dim Sales Territory] : Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.

Dimension [Cube Dim Product] : Create hierarchies in non-parent child dimensions.

Hierarchy [Cube Dim Date].[Hierarchy] : Attribute relationships do not exist between one or more levels of this hierarchy.  This may result in decreased query performance.

Dimension [Cube Dim Date] : Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.

Cube [Cube-AdventureWorks] : The 'Fact Internet Sales' and 'Fact Reseller Sales' measure groups have the same dimensionality and granularity. Consider unifying them to improve performance.

 



download








get free sql tips
agree to terms