"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.
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|
The Multidimensional Data Model
The Sample Schema
Developing Java Applications For Olap
Defining A Logical Multidimensional Model
Creating An Analytic Workspace
Sql Access To Analytic Workspaces
Exploring A Standard Form Analytic Workspace
Adding Measures To A Standard Form Analytic Workspace
Predicting Future Performance
Acquiring Data From Other Sources
Administering Oracle Olap
Materialized Views For The Olap Api
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.