Building custom measures for OLAP cubes

3

In one minute (and 10 seconds, but are you measuring that?) you can add a custom measure (also known as calculated members) to your OLAP cube. Watch this video to create a simple calculation multiplying units * price.

Remember that this mechanism is not just used for multiplications, you can choose to use MDX functions to generate complex logic.
One of my favorites is to use IIF techniques to test for conditions. An example is available in the SAS Global Forum paper "Measures, Members, and Sets, Oh My!" from my friends Bryan Stines and Ben Zenick. Another friend, Steve Overton, posted a blog post about using MDX to generatepercent of totals. For more information on MDX logic, the Microsoft programming technique for MultiDimensional Expressions is available at http://msdn.microsoft.com/en-us/library/ms145506.aspx.

Two very nice things about creating custom measures:

  • Anyone accessing this cube from Enterprise Guide, Information Maps, Web Reports, etc etc can use this custom measure - making it reusable!
  • These measures are calculated on the fly. Using this mechanism measures are not pre-calculated but are based on what the user has selected for view.

For those with no youtube access - here are the basic steps:

  1. Select the cube name on the left panel of OLAP Cube Studio
  2. Click the Calculated Member icon on the top menu bar
  3. Select Add to create a Calculated Member
  4. Follow the GUI to create simple or time based calculations
  5. For custom MDX expressions, choose custom calculations and then click Build Formula
Share

About Author

Angela Hall

Senior Technical Architect

Angela offers tips on using the SAS Business Intelligence solutions. She manages a team of SAS Fraud Framework implementers within the SAS Solutions On-Demand organization. Angela also has co-written two books, 'Building BI using SAS, Content Development Examples' & 'The 50 Keys to Learning SAS Stored Processes'.

Related Posts

3 Comments

  1. Donna Lanningham
    Donna Lanningham on

    The SAS OLAP Server User's Guide also contains information for calculated measures. See the topic "Specifying Calculated Members and Measures". It also has an example for how to create a calculated member in SAS OLAP Cube Studio. See the example "Adding Calculated Members to a Cube".

Leave A Reply

Back to Top