Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 


Creating OLAP DML Formulas Using AWM Templates - Part I
July 18, 2005
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.

-> 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

 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational