Creating OLAP DML Formulas Using AWM Templates
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</pre>
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</pre>
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 9510 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_VERSION1 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_MEAS2 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_MEAS4 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_BRANACCOUNTS 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_salesWAGES_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:
<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.
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.