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:
- Select the cube name on the left panel of OLAP Cube Studio
- Click the Calculated Member icon on the top menu bar
- Select Add to create a Calculated Member
- Follow the GUI to create simple or time based calculations
- For custom MDX expressions, choose custom calculations and then click Build Formula