Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 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.

 


Learn more about SQL Server tools