Creating OLAP DML Formulas Using AWM Templates
May 29th, 2005 by Mark Rittman
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:
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.
-> listnames39 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 ->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.
The next step then is to use a text editor to create an AWM template file. In my case, the template looked like this:
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.
Now, if you use the View Data option to look at the calculated measure within AWM, you should see the values as expected.
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.
