Creating A Calculated Measure Cube - IOGALFF

(Firstly, this is information relates only to OLAP 10gR2. There are a number of changes within OLAP11gR1 and the following scenario has not been tested with 11g)

When you venture into most supermarkets today, somewhere on one of the many isles there will be a BOGOF – buy one get one free. Well how about an OLAP offer: “IOGALFF” – install one get at least fourteen free. I admit it is not quite as catchy as the original, but the benefits are huge and it has the result of making life so much easier.

One of the more interesting challenges of using Analytic Workspace Manager (and also OWB) relates to managing all the calculated measures within a cube. Every measure should really be accompanied by a standard set of calculated measures such as:

• Current Period
• Last Year
• Last Year %
• Prior Period
• Prior Period %
• Year to Date
• Year to Date Last Year
• Year to Date Last Year %
• Quarter to Date
• Quarter to Date Last Year
• Quarter to Date Last Year %
• 3-Month Moving Average
• 6-Month Moving Average
• 12-Month Moving Average
• Dimension “A” Share of All Members
• Dimension “A” Share of Parent

Its is the addition of these types of measures that adds real value to the your BI application. As I have stated many times before: business users are not interested in looking at data from base measures, typically they are more interested in trends based on the prior period or prior year, share of revenue, moving averages and so on. This adds a lot of work for the cube designer as they need to add at least 14 calculated measures for each base measure as well as two additional calculated share measures for each dimension associated with each cube.

Which adds up to a lot of measures and manually creating all these measure is likely to take a considerable amount of time. If we take the command schema sample that is shipped with BI10g, this contains 5 base measures (costs, quantity, revenue, margin, price) with 4 dimensions (channel, geography, product, time), which translates to

5 * 14 calculated measures
+
5 * 3 (dimensions - channel, geography, product) * 2 calculated share measures

= 100 calculated measures.

As I said, it is very easy to generate a lot of calculated measures.


So is there a more intelligent way of managing calculated measures? What is the quickest way to add all these additional calculations to a cube? One way is to use the Excel Accelerator to add the calculated measures to your AW. It is relatively easy to use and allows you to use normal Excel tools such as cut & paste to quickly create all the calculations you could ever need. You can download the Excel Accelerator from the OLAP OTN Home Page, or by clicking here. Look for these links:

Creating OLAP Calculations using Excel
Creating OLAP Calculations using Exce Readme

During a recent POC, we used a more interesting approach that takes the use of customer calculations to a new level. In many ways this goes back to the various techniques we used when creating Express databases for use with Sales Analyzer to make life easier for both administrators and users. The approach is quite simple – use and additional dimension to define the type of calculation you want to execute and link this to a DML program to return the required results. The list of calculated measures listed above are converted into dimension members, which provides another way to slice, dice and pivot the data. As you can see here, the dimension member that identifies the type of calculation is in the row edge with the measure in the column edge. In this way, only one calculation is required which is used across all the available measures.



What this means is: by installing one simple (well, everything is relative) calculated measure you can get upto fourteen additional calculations for free. Hence the acronym “IOGALFF” – install one get at least fourteen free.

How is all this managed? There are five basic steps to creating this type of reporting solution:

1. Create a new dimension to control the types of calculations returned
2. Add some additional attributes to the time dimension to help manage some of the time series arguments for specific calculations
3. Create an OLAP DML program to return the data
4. Create a new cube that includes the new calculation type dimension and add a calculated measure that calls the OLAP DML program to return the required data
   
5. Create a SQL View over the cube

Step 1 – Creating the new Time View dimension

I have called the dimension containing the list of calculated members, Time View (mainly because the majority of the calculations are time based but may be a better name would be "measure view" ?). The Time View dimension has one level and one hierarchy with three attributes:

• Long Label
• Short Label
• Default sort order

The source data looks like this:



With the source table definition as follows:

CREATE TABLE TIME_VIEW
(TIME_VIEW_ID VARCHAR2(2 BYTE),
TIME_VIEW_DESC VARCHAR2(35 BYTE),
SORT_ORDER NUMBER(*,0));

And the view definition as follows:

CREATE OR REPLACE FORCE VIEW VW_TIME_VIEW AS
SELECT
TIME_VIEW_ID
, TIME_VIEW_DESC
, SORT_ORDER
FROM TIME_VIEW
ORDER BY SORT_ORDER;

To populate the base table the following commands are used:

INSERT INTO TIME_VIEW VALUES ('1', 'Current Period', '1')
INSERT INTO TIME_VIEW VALUES ('2', 'Last Year', '2')
INSERT INTO TIME_VIEW VALUES ('3', 'Last Year %', '3')
INSERT INTO TIME_VIEW VALUES ('4', 'Prior Period', '4')
INSERT INTO TIME_VIEW VALUES ('5', 'Prior Period %', '5')
INSERT INTO TIME_VIEW VALUES ('6', 'Year To Date', '6')
INSERT INTO TIME_VIEW VALUES ('7', 'Year To Date Last Year', '7')
INSERT INTO TIME_VIEW VALUES ('8', 'Year To Date Last Year %', '8')
INSERT INTO TIME_VIEW VALUES ('9', 'Quarter To Date', '9')
INSERT INTO TIME_VIEW VALUES ('10', 'Quarter To Date Last Year', '10')
INSERT INTO TIME_VIEW VALUES ('11', 'Quarter To Date Last Year %', '11')
INSERT INTO TIME_VIEW VALUES ('12', '3-Month Moving Average', '12')
INSERT INTO TIME_VIEW VALUES ('13', '6-Month Moving Average', '13')
INSERT INTO TIME_VIEW VALUES ('14', '12-Month Moving Average', '14')

After this, the next set of values depends on the dimensions within your data model. For my model, I have three additional dimensions that I want to analyze: Channel, Product and Customer. For each of these dimensions I want to see the % share of each member in relation to the value for all members (i.e. the top level) and the shared based on the parent value. To enable these calculations I add the following lines to the TIME_VIEW table:

INSERT INTO TIME_VIEW VALUES ('15', ' Product Share of All Products', '15')
INSERT INTO TIME_VIEW VALUES ('16', ' Product Share of Parent', '16')
INSERT INTO TIME_VIEW VALUES ('17', ' Channel Share of All Channels', '17')
INSERT INTO TIME_VIEW VALUES ('18', ' Channel Share of Parent', '18')
INSERT INTO TIME_VIEW VALUES ('19', ' Customer Share of All Customers', '19')
INSERT INTO TIME_VIEW VALUES ('20', ' Customer Share of Parent', '20')

(Obviously, these additional share calculations are not really related to time, so using the name Time View is sort of confusing and in hindsight the term Measure View would have been a better name for the dimension). Once the table has been defined, it can be mapped in AWM to create the dimension mapping (in this case I am using a view over the source



The last part of this step is to then load the members into the dimension using the dimension data load wizard or via the SQL command line using a load script. The result should look like this when you view the members using the Dimension Data Viewer:



As can be seen here, there are fourteen base calculations that can be applied to any model, plus the additional share calculations that are based on the dimensions within the source cube. Hence the title “IOGALFF” – install one get fourteen free. In reality it is more like: install one and as many calculations as you like or need, but that translates to “IOGASMAYNORL” which does not really role off the tongue.

Step 2 – Updating the Time Dimension with new Attributes.

To make the program that delivers all these calculated measures as simple as possible a number of attributes are added to the time dimension:

• Lag Prior Year
• At the Year level this equates to 1
• At the Quarter level this equates to 4
• At the Month level this equates to 12
• At the Day level it is the number of days in the year (365 or 366)
• Parent Quarter
• This is the quarter for each time period and used as the reset trigger for the cumulative totals
• Parent Year
• This is the year for each time period and used as the reset trigger for the cumulative totals

The information for the two parent attributes is taken from existing columns within the source data since this information already exists and the Lag Prior Year is a simple hard-coded value apart from the day level where the timespan value for the year is used to cope with leap years. Therefore, all the information for these three attributes should be readily available within your source data, especially if you are using the OWB Time Wizard to create your time dimension. Obviously using a view over the source table for the mapping within AWM makes adding this information relatively trivial.

Step 3 – Create the OLAP DML Calculation Program.

The new OLAP DML program, called TIME_VIEW_PRG (again feel free to change the name of program if you want), takes two arguments:

• The cube.measure_name for the base measure, in this case SALES_M1, which is the Revenue measure in the SALES cube.
• The identifier for the calculated measure within the REPORT_CUBE cube, which in this case is M1_PRG (this is explained in more detail in the next section)
  

The program code is as follows, which for those of you familiar with OLAP DML will find relatively easy to understand:

DEFINE TIME_VIEW_PRG PROGRAM DECIMAL
argument T_MEASURE text " Full measure name including cube name
argument T_MEASURE_ID text " Measure name

variable T_FORM text " Name of the formula
variable T_TIME_VIEW text " Value of TIME_VIEW dimension
variable D_RETURN decimal " Return value

trap on ALLDONE noprint

T_TIME_VIEW = TIME_VIEW
T_FORM = joinchars('REPORT_CUBE_', T_MEASURE_ID)

switch T_TIME_VIEW
do
case 'VL1_1':
D_RETURN = &T_MEASURE
break
case 'VL1_2': "LY
D_RETURN = lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL)
break
case 'VL1_3': "LY%
D_RETURN = lagpct(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL)
break
case 'VL1_4': "PP
D_RETURN = lag(&T_FORM(TIME_VIEW 'VL1_1'), 1, TIMES, LEVELREL TIMES_LEVELREL)
break
case 'VL1_5': "PP%
D_RETURN = lagpct(&T_FORM(TIME_VIEW 'VL1_1'), 1, TIMES, LEVELREL TIMES_LEVELREL)
break
case 'VL1_6': "YTD
if TIMES_LEVELREL EQ 'YEAR'
then D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')
else D_RETURN = cumsum(&T_FORM(TIME_VIEW 'VL1_1'), TIMES, TIMES_PARENT_YEAR)
break
case 'VL1_7': "YTD LY
if TIMES_LEVELREL EQ 'YEAR'
then D_RETURN = lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL)
else D_RETURN = cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_YEAR)
break
case 'VL1_8': "YTD LY%
D_RETURN = (&T_FORM(TIME_VIEW 'VL1_6')-cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_YEAR))/cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_YEAR)
break
case 'VL1_9': "QTD
if TIMES_LEVELREL EQ 'YEAR'
then D_RETURN = NA
else if TIMES_LEVELREL EQ 'QUARTER'
then D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')
else if TIMES_LEVELREL EQ 'MONTH'
then D_RETURN = cumsum(&T_FORM(TIME_VIEW 'VL1_1'), TIMES, TIMES_PARENT_QUARTER)
else if TIMES_LEVELREL EQ 'DAY'
then D_RETURN = cumsum(&T_FORM(TIME_VIEW 'VL1_1'), TIMES, TIMES_PARENT_QUARTER)
else D_RETURN = NA
break
case 'VL1_10': "QTD LY
if TIMES_LEVELREL EQ 'YEAR'
then D_RETURN = NA
else if TIMES_LEVELREL EQ 'QUARTER'
then D_RETURN = cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER)
else if TIMES_LEVELREL EQ 'MONTH'
then D_RETURN = cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER)
else if TIMES_LEVELREL EQ 'DAY'
then D_RETURN = cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER)
break
case 'VL1_11': "QTD LY%
if TIMES_LEVELREL EQ 'YEAR'
then D_RETURN = NA
else if TIMES_LEVELREL EQ 'QUARTER'
then D_RETURN = (&T_FORM(TIME_VIEW 'VL1_9')-cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER))/cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER)
else if TIMES_LEVELREL EQ 'MONTH'
then D_RETURN = (&T_FORM(TIME_VIEW 'VL1_9')-cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER))/cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER)
else if TIMES_LEVELREL EQ 'DAY'
then D_RETURN = (&T_FORM(TIME_VIEW 'VL1_9')-cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER))/cumsum(lag(&T_FORM(TIME_VIEW 'VL1_1'), TIMES_LAG_PRIOR_YEAR, TIMES, LEVELREL TIMES_LEVELREL), TIMES, TIMES_PARENT_QUARTER)
break
case 'VL1_12': "3MMA
if TIMES_LEVELREL eq 'MONTH'
then D_RETURN = MOVINGAVERAGE(&T_FORM(TIME_VIEW 'VL1_1'), -2, 0, 1, TIMES, LEVELREL TIMES_LEVELREL)
else D_RETURN = NA
break
case 'VL1_13': "6MMA
if TIMES_LEVELREL eq 'MONTH'
then D_RETURN = MOVINGAVERAGE(&T_FORM(TIME_VIEW 'VL1_1'), -5, 0, 1, TIMES, LEVELREL TIMES_LEVELREL)
else D_RETURN = NA
break
case 'VL1_14': "12MMA
if TIMES_LEVELREL eq 'MONTH'
then D_RETURN = MOVINGAVERAGE(&T_FORM(TIME_VIEW 'VL1_1'), -11, 0, 1, TIMES, LEVELREL TIMES_LEVELREL)
else D_RETURN = NA
break
case 'VL1_15': "Product Share of All Products
D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')/&T_FORM(TIME_VIEW 'VL1_1', PRODUCTS limit(PRODUCTS to TOPANCESTORS using PRODUCTS_PARENTREL)
break
case 'VL1_16': "Product Share of Parent
D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')/&T_FORM(TIME_VIEW 'VL1_1', PRODUCTS PRODUCTS_PARENTREL)
break
case 'VL1_17': "Customers Share of All Customers
D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')/&T_FORM(TIME_VIEW 'VL1_1', CUSTOMERS limit(CUSTOMERS to TOPANCESTORS using CUSTOMERS_PARENTREL)
break
case 'VL1_18': "Customers Share of Parent
D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')/&T_FORM(TIME_VIEW 'VL1_1', CUSTOMERS CUSTOMERS_PARENTREL)
break
case 'VL1_19': "Channels Share of All Channels
D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')/&T_FORM(TIME_VIEW 'VL1_1', CHANNELS limit(CHANNELS to TOPANCESTORS using CHANNELS_PARENTREL)
break
case 'VL1_20': "Channels Share of Parent
D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')/&T_FORM(TIME_VIEW 'VL1_1', CHANNELS CHANNELS_PARENTREL)
break
doend


ALLDONE:
return D_RETURN
END

In simple terms, the program checks to see which member of the dimension TIME_VIEW is being requested and then executes the correct OLAP function to return the required data. At the moment this has only been tested on a Julian calendar hierarchy but I am doing some more testing over the next few weeks for other types of hierarchies. Some of the measures are dependant on the level within the Time dimension, so you may need to change references to specific level names etc if you want to reuse this program code. For example:

if TIMES_LEVELREL EQ 'YEAR'

My time dimension has levels; Year, Quarter, Month, and Day. In some cases it is necessary to change the processing depending the level, for example:

if TIMES_LEVELREL EQ 'YEAR'
then D_RETURN = NA
else if TIMES_LEVELREL EQ 'QUARTER'
then D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')
else if TIMES_LEVELREL EQ 'MONTH'
then D_RETURN = cumsum(&T_FORM(TIME_VIEW 'VL1_1'), TIMES, TIMES_PARENT_QUARTER)
else if TIMES_LEVELREL EQ 'DAY'
then D_RETURN = cumsum(&T_FORM(TIME_VIEW 'VL1_1'), TIMES, TIMES_PARENT_QUARTER)
else D_RETURN = NA


In this code extract you can see the reference to one of the additional time attributes we added in the previous step, PARENT_QUARTER that is used by the program. All the references within the program are based on Standard Form naming conventions. Therefore, the full standard name for this attribute is TIMES_PARENT_QUARTER since the dimension is called TIMES and the attribute is called PARENT_QUARTER.

For simplicity I created the program in the AW containing the cubes, which is not exactly ideal because if you delete the AW to rebuild it you will lose the program code. Alternatively, you can just create a new AW and add the program to the new AW then modify the ONTTACH program in your data AW to automatically attach the program AW.

Calculations for dimension members VL_15 to VL_20 are provided as examples and would need to be changed to match the dimensions within your own AWs. For each dimension you will need to create two new dimension members within the TIME_VIEW dimension to return the following share calculations:

• Share of based on the top level total, i.e. All Members
• Share based on parent.

As the code uses Standard form notation the only thing you should need to change is code highlighted in bold and replace the phrase “Channels” with your own dimension name:

case 'VL1_19': "Channels Share of All Channels
D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')/&T_FORM(TIME_VIEW 'VL1_1', CHANNELS limit(CHANNELS to TOPANCESTORS using CHANNELS_PARENTREL)
break
case 'VL1_20': "Channels Share of Parent
D_RETURN = &T_FORM(TIME_VIEW 'VL1_1')/&T_FORM(TIME_VIEW 'VL1_1', CHANNELS CHANNELS_PARENTREL)
break


Step 4 – Creating the New Cube.

In my demo schema I have a cube called SALES and it contains two measures: Revenue (called M1) and Quantity (called M2). The dimensionality for this cube is Time, Channel Product, and Customer.

To provide all the time series and comparative calculations I create a new cube that has the same dimensionality as the SALES cube, with one additional dimension. In this cube the Time View dimension is also used. This reporting cube contains no stored measures, but it does contain custom calculated measures for each measure in the SALES cube, however, each calculated measure will return 14 additional time calculations and 6 share calculations to support the base measure controlled by the dimension TIME_VIEW.



The implementation for the cube is largely irrelevant since no stored measures will be present within the cube, only calculated measures. Therefore, you can if you want either accept the default settings or de-select all the various options.



To add the calculated measures to the cube requires the use of a custom measure XML template. To create a calculated measure to support the measure “Revenue” from the SALES cube you will need to create a custom calculation either using an XML template (email me I can send you a blank template) or use the Excel Calculation Utility that can be downloaded from the OLAP Home Page on OTN to install the two custom calculations.

Once you have added these calculated measures to the REPORT_CUBE, the tree in AWM should look like this:



By selecting each of the calculated measures, the details of each calculation will be displayed within the right-hand panel of AWM. For the first calculated measure linked the Revenue measure in the Sales cube the panel looks like this:



For the second calculated measure linked the Quantity measure in the Sales cube the panel looks like this:



The key part is the line showing the “Expression” (in the XML Template this is the attribute ExpressionText), this defines the call to an OLAP DML program called TIME_VIEW_PRG passing the two required arguments of

ExpressionText="TIME_VIEW_PRG('SALES_M1', 'M1_PRG')”

• The cube.measure_name for the base measure, in this case SALES_M1, which is the Revenue measure in the SALES cube.
• The identifier for the calculated measure within the REPORT_CUBE cube.

At this point you may or may not have the correct formula/expression text. When you load calculations via the XML interface OLAP tries to be a bit too clever and it attempts to convert any physical name it can match with the equivalent standard form name. In most cases this is fine, but in this case we need to refer directly to the physical objects. Modifying the expressions is relatively easy if you follow these steps. Using AWM, open the OLAP Worksheet and then:

1. CNS REPORT_CUBE_M1_PRG
2. EQ TIME_VIEW_PRG('SALES_M1', 'M1_PRG')
3. UPDATE
4. COMMIT
5. CNS REPORT_CUBE_M2_PRG
6. EQ TIME_VIEW_PRG('SALES_M2', 'M2_PRG')
7. UPDATE
8. COMMIT

If want to do this via the PL/SQL interface then you can do something like this:

EXEC DBMS_AW.EXECUTE('AW ATTACH AW_NAME RW FIRST')
EXEC DBMS_AW.EXECUTE('CNS REPORT_CUBE_M1_PRG')
EXEC DBMS_AW.EXECUTE('EQ TIME_VIEW_PRG(''SALES_M1'', ''M1_PRG'')')
EXEC DBMS_AW.EXECUTE('UPDATE')
EXEC DBMS_AW.EXECUTE('COMMIT')
EXEC DBMS_AW.EXECUTE('CNS REPORT_CUBE_M2_PRG')
EXEC DBMS_AW.EXECUTE('EQ TIME_VIEW_PRG(''SALES_M2'', ''M2_PRG'')')
EXEC DBMS_AW.EXECUTE('UPDATE')
EXEC DBMS_AW.EXECUTE('COMMIT')
EXEC DBMS_AW.EXECUTE('AW DETACH AW_NAME')


Step 4a – Viewing the Data.

Once the calculated measures have been added to the new cube, REPORT_CUBE, the AWM Data Viewer can be launched to check the results:



In the Query Wizard both sets of measures (stored measures and the new calculated measures) are both available for selection. To see the new calculations that are now available we can select both of the Report View measures as shown here.

Once the measures have been selected, the dimension selector will allow us to pick the calculations we want to display from the list of twenty (there are fourteen base calculations that can be applied to any model, plus the additional share calculations that are based on the dimensions within the source cube, in this case six additional share calculations, making twenty calculated measures in total).



The Dimension Viewer shows all the available calculations for this demo schema and the final report is shown below.




Step 5 – Making the Data Available via SQL.
Is there any real point to this post except to show how clever Oracle OLAP can be? In opinion yes, since this technique can be extremely useful when you need to make an OLAP cube visible to SQL based tools. Analytic Workspace Manager 10g has an relational view generator that is available as a plugin and this makes generating the SQL views to support your OLAP cubes a very quick and easy process. But there we know users don’t just want base measures they want lots and lots of calculations as well. When the time comes to create SQL views as the limit on the number of columns within a view is 1000. This may have been increased in 10g/11g, but even so, navigating a cube with that many columns is not easy.

The OLAP View Generator plugin for AWM10gR2 can be downloaded from here, and the associated readme is here.

Using this model, the calculations simply resolve to another dimension, which translates to one additional column in the view as opposed to, at least, 14 additional columns per source measure and in this case 20 additional columns per source measure. Therefore, this approach makes exposing the cube much easier to manage as can be seen here:



As we can see here, all the calculations are contained within the dimension Time View, which simply gets exposed like any other dimension.

Eh voila, install one measure and you can automatically generate fourteen or more additional measures which are guaranteed to bring a smile to the face of any business user.