The enabler for the BI Beans creates fact views with columns specifically for custom measures defined by the DBMS_AW_UTILITIES package. There are 100 columns for numeric data named CUST_MEAS_NUM1 to CUST_MEAS_NUM100, and 100 columns for text data named CUST_MEAS_TEXT1 to CUST_MEAS_TEXT100.
This is the basic syntax for creating a custom measure:
The BI Beans enabler creates CWM2 metadata for the views of analytic workspaces, and DBMS_AW_UTILITIES creates CWM2 metadata for the custom measures added to these views. This metadata is stored in tables that identify the mapping between the custom measures and the generic column names of the view:
Case Study: Adding Sales to Global Using DBMS_AW_UTILITIES
"Identifying Required Business Facts" identifies the data requirements of the Global Corporation. Only three facts are stored in the star schema; the others must be calculated in the analytic workspace. Because GLOBAL is a standard form analytic workspace that has been enabled for the BI Beans, the DBMS_AW_UTILITIES package is available for the DBA to define these measures.
Acquiring Information About the Analytic Workspace
Before you can define custom measures, you must know the names of measures that are already defined in the analytic workspace. You can query the ALL_ OLAP2_ AW_ CUBE_ MEASURES view in the Active Catalog for the names of measures defined in the GLOBAL analytic workspace. Example shows how to obtain the names of the measures.
Querying the Active Catalog for Measure Names
The ALL_AW_CUBE_ENABLED_VIEWS view identifies the cubes that are enabled for the BI Beans, the names of the views created by the enabler to access those cubes, and the dimensions and dimension hierarchies for each view. Example shows that the Price cube is dimensioned by PRODUCT and TIME, and can be queried through a view named GLOB_GLOBA_PRICE_CU4VIEW. The Units cube is dimensioned by CHANNEL, CUSTOMER, PRODUCT, and TIME. The CUSTOMER dimension has two hierarchies: MARKET_ SEGMENT is shown in GLOB_ GLOBA_UNITS_CU9VIEW and SHIPMENTS is shown in GLOB_GLOBA_UNITS_CU10VIEW.
SELECT Statement for Querying the Active Catalog
Using DBMS_AW_UTILITIES to Define Sales as a Custom Measure
After getting the information you need to define a custom measure, you can define your custom measures using DBMS_AW_UTILITIES. This example defines SALES, which calculates the product of two other measures, UNITS and UNIT_PRICE, for each combination of dimension members.
UNITS is a measure in the Units cube, and UNIT_PRICE is a measure in the Price cube. The Units cube has four dimensions: TIME, PRODUCT, CUSTOMER, and CHANNEL. The Price cube has only two dimensions, TIME and PRODUCT. The product of these two measures will have four dimensions, so SALES must be added to a view of the Units cube.
Example adds the SALES measure to both views for the Units cube. Notice that only the first call specifies the equation for the SALES formula. The second call just identifies the existing SALES formula.
Defining SALES Using DBMS_AW_UTILITIES
Viewing the Workspace Formula
Use this command to see the formula created in the analytic workspace:
You can also view the property sheet for SALES in Analytic Workspace Manager.
Querying the Sales Custom Measure
OLAPSYS.CWM2$_AW_PERM_CUST_MEAS_MAP identifies the mapping between the SALES custom measure and a column in the views.
Queries for the SALES measure must select values from the CUST_MEAS_NUM1 columns of the two tables.
Creating Custom Measures Using OLAP_EXPRESSION
The DBMS_AW package contains several procedures for specifying run-time calculations.
You can use these procedures to specify inter-row calculations using SELECT statements on a view of analytic workspace data. The calculations are performed by the OLAP engine. The only requirement for using these functions is that the SELECT statement for the view must contain a call to the OLAP_TABLE function with a ROW2CELL clause. The enabler for the BI Beans generates views of this type, and you can also generate custom views with ROW2CELL columns as described in "Using OLAP_TABLE for Direct Access to Workspace Data".
The syntax of the four functions is identical. The difference between them is only in data type. This is the basic syntax for OLAP_EXPRESSION:OLAP_EXPRESSION(r2c, expression)
For example: OLAP_EXPRESSION('R2C', 'units * unit_price')
OLAP Related Interview Questions
|Informatica Interview Questions||Data Warehouse ETL Toolkit Interview Questions|
|PL/SQL Interview Questions||Data Warehousing Interview Questions|
|Testing Tools Interview Questions||SQL Database Interview Questions|
|MySQL Interview Questions||ERP Tools Interview Questions|
|Oracle 11g Interview Questions||Hyperion Financial Management Interview Questions|
|Hyperion Essbase 5 Interview Questions||Database Design Interview Questions|
|Data modeling Interview Questions||Oracle Hyperion Planning Interview Questions|
|Biztalk Esb Toolkit Interview Questions|
OLAP Related Practice Tests
|Informatica Practice Tests||PL/SQL Practice Tests|
|Data Warehousing Practice Tests||Testing Tools Practice Tests|
|SQL Database Practice Tests||MySQL Practice Tests|
|ERP Tools Practice Tests||Oracle 11g Practice Tests|
|Hyperion Financial Management Practice Tests||Hyperion Essbase 5 Practice Tests|
|Database Design Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.