Creating OLAP DML Formulas Using AWM Templates

Mark Rittman's picture
articles: 

One of the most powerful features of the multidimensional engine behind analytic workspaces is the ability to create formulas. Formulas, or "calculated measures" as they're referred to in AWM10g, are measures that are derived from other measures. Using AWM, you can create simple formulas that reference other measures in a cube, allowing you for example to create a "margin" measure derived from sales and costs measures. If you're an old Express hand though, you'll know that this simple type of formulas is just the tip of the iceberg, and what you often used to end up doing was creating for example a three dimensional formula based on measures from four and five dimensional variables, rolling up unneeded dimensions and pulling in variables held in what would now be referred to as "cubes".

To take an example, say that you had a table of branches:

SQL> select * from branches;

 BRANCH_ID BRANCH_DESC      REGION_ID REGION_DESC     TOTAL_BRANCHES_ID TOTAL_BRANCHES_
---------- --------------- ---------- --------------- ----------------- ---------------
         1 Brighton                10 South-East                    100 All Branches
         2 Worthing                10 South-East                    100 All Branches
         3 Charing Cross           11 London                        100 All Branches
         4 Liverpool               12 North-West                    100 All Branches
         5 Manchester              12 North-West                    100 All Branches

and a table of accounts:

SQL> select * from accounts;

ACCOUNT_ID ACCOUNT_DESC    TOTAL_ACCOUNTS_ID TOTAL_ACCOUNTS_
---------- --------------- ----------------- ---------------
         1 Wages                         100 All Accounts
         2 Sales                         100 All Accounts

and then a table of account balances:

SQL> select * from balances;

ACCOUNT_ID  BRANCH_ID    BALANCE
---------- ---------- ----------
         1          1         50
         2          1         80
         1          2         40
         2          2         40
         1          3        100
         2          3        125
         1          4         80
         2          4         60
         1          5         90
         2          5         95

10 rows selected.

We then create an analytic workspace with account and branch dimensions, and a balances cube:

Analytic workspace

Once the analytic workspace has been loaded from our source tables, we can open up the OLAP Worksheet and take a look at what's been created.

-> listnames
  39 DIMENSIONs                      52 VARIABLEs
   --------------------------------   --------------------------------
   ACCOUNTS                           ACCOUNTS_ACCOUNT_H_HIERDEF
   ACCOUNTS_HIERLIST                  ACCOUNTS_ACCOUNT_LEVELDEF
   ACCOUNTS_LEVELLIST                 ACCOUNTS_COLUMN_COUNT
   AGGREGATE_DIMENSION_PROP           ACCOUNTS_COLUMN_MAP
   AGGREGATE_GENERIC_PROP             ACCOUNTS_CREATEDBY
   ALLOCATE_DIMENSION_PROP            ACCOUNTS_HIER_IS_VALUE
   ALLOCATE_GENERIC_PROP              ACCOUNTS_IS_SESSION
   ALL_ATTRIBUTES                     ACCOUNTS_LONG_DESCRIPTION
   ALL_ATTRTYPES                      ACCOUNTS_SHORT_DESCRIPTION
   ALL_CALC_MEMBERS                   ACCOUNTS_TOTAL_ACCOUNTS_LEVELDEF
   ALL_CUBES                          AGGREGATE_DIMENSION_CATALOG
   ALL_DESCTYPES                      AGGREGATE_GENERIC_CATALOG
   ALL_DIMENSIONS                     ALLOCATE_DIMENSION_CATALOG
   ALL_HIERARCHIES                    ALLOCATE_GENERIC_CATALOG
   ALL_LANGUAGES                      ALL_DESCRIPTIONS
   ALL_LEVELS                         ALL_TOOLS_PROP
   ALL_MEASUREFOLDERS                 ATTR_DATA_MAP
   ALL_MEASURES                       ATTR_VISIBLE
   ALL_MODELS                         AW_NAMES
   ALL_OBJECTS                        BALANCES_BALANCE_COUNTVAR
   ALL_SOLVEDFNS                      BALANCES_BALANCE_STORED
   ALL_SOLVEGROUPS                    BRANCHES_BRANCHES_H_HIERDEF
   ALL_SOLVES                         BRANCHES_BRANCH_LEVELDEF
   BALANCES                           BRANCHES_COLUMN_COUNT
   BRANCHES                           BRANCHES_COLUMN_MAP
   BRANCHES_HIERLIST                  BRANCHES_CREATEDBY
   BRANCHES_LEVELLIST                 BRANCHES_HIER_IS_VALUE
   CALC_MEMBER_PROP                   BRANCHES_IS_SESSION
   COLUMN_DIM                         BRANCHES_LONG_DESCRIPTION
   CUBE_PROP                          BRANCHES_REGION_LEVELDEF
   DIM_OBJ_LIST                       BRANCHES_SHORT_DESCRIPTION
   FORECAST_PROP                      BRANCHES_TOTAL_BRANCHES_LEVELDEF
   GEN_OBJ_ROLES                      CALC_MEMBER_CATALOG
   GID_DIMENSION                      CUBE_CATALOG
   IS_LOADED_DIMENSION                DIMKEY_IS_UNIQUE
   MAPGROUP_DIM                       DIM_AW_OBJS
   MEASURE_PROP                       DIM_KEY_MAP
   TIME_GLEVEL_DIMENSION              FORECAST_CATALOG
   TIME_OFFSET_DIMENSION              GEN_AW_OBJS
                                      MEASURE_CATALOG
                                      MEAS_DATA_MAP
                                      MEAS_KEY_MAP
                                      MEAS_OPERATOR_MAP
                                      OBJECT_LOADED
                                      OBJ_CREATEDBY
                                      OBJ_ORIGINATOR
                                      PARENT_KEY_MAP
                                      PARENT_LVL_MAP
                                      SOLVEDFN_TYPE
                                      SOLVE_MEMBER_SELECTION
                                      VISIBLE
                                      ___XML_USER_AW_VERSION

   1 PROGRAM                          1 FORMULA
   --------------------------------   --------------------------------
   ONATTACH                           BALANCES_BALANCE

   37 RELATIONs                       5 COMPOSITEs
   --------------------------------   --------------------------------
   ACCOUNTS_FAMILYREL                 ATTR_MAP_COMPOSITE
   ACCOUNTS_FAMILYRELVAL              BALANCES_COMPOSITE
   ACCOUNTS_GID                       HIERLVL_MAP_COMPOSITE
   ACCOUNTS_LEVELREL                  LVL_MAP_COMPOSITE
   ACCOUNTS_LOADED                    MEAS_MAP_COMPOSITE
   ACCOUNTS_PARENTREL
   BRANCHES_FAMILYREL
   BRANCHES_FAMILYRELVAL
   BRANCHES_GID
   BRANCHES_LEVELREL
   BRANCHES_LOADED
   BRANCHES_PARENTREL
   CALC_MEMBER_BASE_DIMENSION
   CUBE_AGGREGATION
   CUBE_DFLT_PARTITION_HIERARCHY
   CUBE_DFLT_PARTITION_LEVEL
   CUBE_MEASURES
   DEFAULT_HIER
   DIM_ATTRIBUTES
   DIM_HIERARCHIES
   DIM_LEVELS
   DYNAMIC_MEAS_AGGREGATION
   FOLDER_PARENTREL
   HIER_SORT_ATTR
   MAPGROUP_CUBEREL
   MAPGROUP_DIMREL
   MAPGROUP_HIERREL
   MAPGROUP_LVLREL
   MEAS_DOMAIN
   MEAS_PARTITION_HIERARCHY
   MEAS_PARTITION_LEVEL
   MODEL_BASE_DIMENSION
   RELATIONAL_ATTRIBUTE_DATA
   RELATIONAL_MEASURE_DATA
   SOLVE_BASE_MEAS
   SOLVE_SOLVEDFN
   SOLVE_SOURCE_MEAS

   2 MODELs                           22 VALUESETs
   --------------------------------   --------------------------------
   BALANCES_ACCOUNTS_AWXMLMODEL       ACCOUNTS_AGGRDIM_VSET
   BALANCES_BRANCHES_AWXMLMODEL       ACCOUNTS_AGGRHIER_VSET
                                      ACCOUNTS_HIER_LEVELS
                                      ACCOUNTS_INHIER
                                      ACCOUNTS_LOAD_STATUS_VSET
                                      BRANCHES_AGGRDIM_VSET
                                      BRANCHES_AGGRHIER_VSET
                                      BRANCHES_HIER_LEVELS
                                      BRANCHES_INHIER
                                      BRANCHES_LOAD_STATUS_VSET
                                      CALC_MEMBERS_IN_MODEL
                                      CALC_MEMBER_OTHER_DIMENSIONS
                                      CUBE_COMPOSITE_BASES
                                      CUBE_DIMENSIONS
                                      DEPENDENT_MEASURES
                                      MEAS_COMPOSITE_BASES
                                      MEAS_IN_FOLDER
                                      MODEL_OTHER_DIMENSIONS
                                      SOLVEDFN_CALCULATION_ORDER
                                      SOLVEDFN_SOLVE_ORDER
                                      SOLVE_ORDER
                                      SOLVE_TARGET_MEAS

   4 AGGMAPs                          11 SURROGATEs
   --------------------------------   --------------------------------
   OBJ1962518006                      ACCOUNTS_ACCOUNT_SURR
   OBJ1962518006_PRT_PRTAGGMAP        ACCOUNTS_HIERLIST_SURR
   OBJ1962518006_PRT_RUNAGGMAP        ACCOUNTS_LEVELLIST_SURR
   OBJ1962518006_PRT_TOPAGGMAP        ACCOUNTS_TOTAL_ACCOUNTS_SURR
                                      BRANCHES_BRANCH_SURR
                                      BRANCHES_HIERLIST_SURR
                                      BRANCHES_LEVELLIST_SURR
                                      BRANCHES_REGION_SURR
                                      BRANCHES_TOTAL_BRANCHES_SURR
                                      __XML_GENERATED_1
                                      __XML_GENERATED_2

What we've got here is the two dimensions we've created (ACCOUNTS and BRANCHES), the measure BALANCES_BALANCE_STORED, and a whole load of additional objects that make up the standard form metadata. The measure, which we called BALANCES in the AWM Model view, is named within the AW using the format CUBENAME_MEASURENAME_STORED.

We can then take a look at the ACCOUNTS dimension that has been set up, listing out the member ID (taken from our ACCOUNT_ID source column) and the long description (taken from the ACCOUNT_DESC source column).

->rpr down accounts w 30 accounts_short_description

               --ACCOUNTS_SHORT_DESCRIPTION--
               --------ALL_LANGUAGES---------
ACCOUNTS           ENGLISH_UNITED KINGDOM
-------------- ------------------------------
TOTAL_ACCOUNTS All Accounts
_100
ACCOUNT_1      Wages
ACCOUNT_2      Sales

Do the same for the BRANCHES dimension,

->rpr down branches w 30 branches_short_description

               --BRANCHES_SHORT_DESCRIPTION--
               --------ALL_LANGUAGES---------
BRANCHES           ENGLISH_UNITED KINGDOM
-------------- ------------------------------
TOTAL_BRANCHES All Branches
_100
REGION_10      South-East
REGION_11      London
REGION_12      North-West
BRANCH_1       Brighton
BRANCH_2       Worthing
BRANCH_3       Charing Cross
BRANCH_4       Liverpool
BRANCH_5       Manchester

and then list out the contents of the measure.

->rpr balances_balance_stored

               -------------------------------------BALANCES_BALANCE_STORED--------------------------------------
               ---------------------------------------------BRANCHES---------------------------------------------
               TOTAL_BRAN
ACCOUNTS        CHES_100  REGION_10  REGION_11  REGION_12   BRANCH_1   BRANCH_2   BRANCH_3   BRANCH_4   BRANCH_5
-------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
TOTAL_ACCOUNTS     760.00     210.00     225.00     325.00     130.00      80.00         NA         NA         NA
_100
ACCOUNT_1          360.00      90.00     100.00     170.00      50.00      40.00     100.00      80.00      90.00
ACCOUNT_2          400.00     120.00     125.00     155.00      80.00      40.00     125.00      60.00      95.00

Now, say that we wanted to create a new measure, that contained the percentage of sales that wages represented. This measure would have one dimension, BRANCHES, and would be calculated by taking the BALANCES measure for each branch and dividing wages by sales then multiplying by 100. We could do this at the relational end, creating a new table for this measure, calculating the percentage and then loading it into a RATIOS cube. Old Express hands though would create a formula instead, dimensioned by BRANCHES, that derived the value from the BALANCES measure.

->define wages_pct_of_sales formula decimal <BRANCHES>


->eq (BALANCES_BALANCE_STORED(ACCOUNTS 'ACCOUNT_1') / BALANCES_BALANCE_STORED(ACCOUNTS 'ACCOUNT_2'))*100


->update


->commit

which when queried would give the correct results:

 ->rpr wages_pct_of_sales

               WAGES_PCT_
BRANCHES        OF_SALES
-------------- ----------
TOTAL_BRANCHES      90.00
_100
REGION_10           75.00
REGION_11           80.00
REGION_12          109.68
BRANCH_1            62.50
BRANCH_2           100.00
BRANCH_3            80.00
BRANCH_4           133.33
BRANCH_5            94.74

The problem with this approach though, is when you're working with Oracle OLAP and analytic workspaces creating the fornula isn't enough - to display it as a calculated measure in AWM and Discoverer for OLAP, you've got to create all the associated standard form metadata. Now whilst this is undoubtedly possible, it's by no means a simple affair (I've yet to get this working) and the metadata itself changes from release to release. Therefore, what you've got to do is take your formula definition and process it through AWM. Thanks for Anthony Waite and Bud Endress for explaining how this takes place.

The first step is to create a new cube, in my case called RATIOS, that will hold my new calculated measure. This cube will have one dimension, BRANCHES, as ACCOUNTS is being rolled up into the ratio. Note that the cube has no measures.

Create Ratios Cube

The next step then is to use a text editor to create an AWM template file. In my case, the template looked like this:

<Create Id="Action315475">
    <ActiveObject>
          <DerivedMeasure  Name="WAGES_PCT_OF_SALES" LongName="Wages % of Sales" 
                   ShortName="Wages/Sales" PluralName="Wages % of Sales" 
                   Id="RATIOS.WAGES_PCT_OF_SALES.MEASURE" DataType="decimal" isInternal="false" 
                   UseGlobalIndex="false" ForceCalc="false" ForceOrder="false" 
                   SparseType="STANDARD" AutoSolve="DEFAULT" 
                   ExpressionText="(BALANCES_BALANCE_STORED(ACCOUNTS 'ACCOUNT_1') 
                       / BALANCES_BALANCE_STORED(ACCOUNTS 'ACCOUNT_2'))*100"/>
    </ActiveObject>
</Create>

The bits in bold are the bits that I had to change to suit my formula. Two points to note on this:

The ID is made up of CUBE_NAME.FORMULA_NAME.MEASURE, where CUBE_NAME is the name of the one dimension cube I just set up, FORMULA_NAME is the name of my formula, and "MEASURE" is just a literal - i.e. just type in "MEASURE".

The ExpressionText is the text of the formula definition, minus the "eq" at the start.

Save the template, and then right-click on the calculated measures node in the RATIOS cube, and create a calculated measure from the template file. Once the template is loaded, the new calculated measure should be visible within AWM.

New calculated measure

Now, if you use the View Data option to look at the calculated measure within AWM, you should see the values as expected.

View calculated measure

Note as well how the aggregation has been carried out for you, properly aggregating the percentages rather than just SUMming them up. As it's a formula, there's no need to separately process this cube - as long as the base data has been loaded and aggregated, the formula then picks up the values and displays them without further processing.

Finally, if I then go into the OLAP Worksheet again, I can view my formula and then display the values.

->listnames formulas
   2 FORMULAs
   -------------------------
   BALANCES_BALANCE
   RATIOS_WAGES_PCT_OF_SALES

->rpr ratios_wages_pct_of_sales

               RATIOS_WAG
               ES_PCT_OF_
BRANCHES         SALES
-------------- ----------
TOTAL_BRANCHES      90.00
_100
REGION_10           75.00
REGION_11           80.00
REGION_12          109.68
BRANCH_1            62.50
BRANCH_2           100.00
BRANCH_3            80.00
BRANCH_4           133.33
BRANCH_5            94.74

The points to take away from this are that firstly, you're not restricted to just the calculations that AWM gives you (as long as you know a smattering of OLAP DML) and that secondly, if you want to manually create these calculations, don't try and create them using the OLAP Worksheet and OLAP DML, use AWM templates instead as the standard form metadata is automatically created for you, and the definition of the calculation is then preserved in the AW template and can be saved along with the rest of the AW definition.

Comments

A very complex way of creating a formula measure. I think Oracle Financial Analyzer provides a much simpler way of creating such a formula measure. Dont know whether developers would fancy such a complex means of creating calculated measure unless Oracle comes out with a more developer-friendly front-end driven application.