The Global Corporation requires numerous custom measures in addition to the three stored measures fetched from a star schema into the GLOBAL analytic workspace. The OLAP_TABLE function offers a method of creating these derived measures, although other methods are also available to GLOBAL.
UNITS is one of the stored measures, and the units for the prior period is a required derived measures. Although they are not required, other derived measures such as the difference from the prior period or the percent change may also be desirable. Derived measures can be defined permanently in the analytic workspace or specified in the syntax of the OLAP_TABLE function. This example adds these two measures:
This example creates a new OLAP Catalog cube for these measures.
Defining Formulas in the Analytic Workspace
If it does not already exist, add UNITS_PP, which returns the value of the prior time period, to the GLOBAL analytic workspace with these commands:
This syntax for defining a formula gives it the same data type and dimensionality as the source object. The new formula has this definition:
Alternatively, you can define UNITS_PP using the property sheets in Analytic Workspace Manager.
Example defines UNITS_PCTCHG_PP in the OLAP_TABLE function, using the OLAP DML LAGPCT function. UNITS_PCTCHG_PP calculates the percent change from the prior period.
Neither UNITS_PP nor UNITS_PCTCHG_PP are defined as standard form measures. To comply with standard form, they need several OLAP DML properties, and they must be registered as measures in the standard form catalogs. However, OLAP_TABLE and the OLAP Catalog do not require standard form; only the tools that simplify their use require standard form.
Querying an Analytic Workspace Using OLAP_TABLE
Example shows a script that fetches data directly into a SQL application using a SELECT statement with the OLAP_TABLE function. This selection is separate from any application enablement process.
To query the Units measures in the GLOBAL analytic workspace, take these steps:
There is neither standard form metadata nor application metadata for UNITS_PP. An explanation of the example follows the code.
UNITS_QUERY Script for Querying with OLAP_TABLE
Results of Running the UNITS_QUERY Script
In Example, the arguments to OLAP_TABLE provide the most basic information: the measures you want to see, their dimensions, and the descriptive names for time periods that make this data meaningful. In addition, the OLAP_TABLE function needs the names of the parent relations, which define the hierarchical structure of the dimensions. Since these dimensions were created by the Create Analytic Workspace wizard in Analytic Workspace Manager, the parent relations are named dimension_PARENTREL.
The CUSTOMER dimension has two hierarchies, and a LIMIT command selects the second hierarchy, MARKET_ SEGMENTS; SHIPMENTS is the first hierarchy in the CUSTOMER_ HIERLIST hierarchy dimension, and so it is the default. The other dimensions have only one hierarchy, so there is no need to limit their hierlist dimensions..
The limit map identifies two measures (UNITS and UNITS_PP), both of which are formulas in the analytic workspace. UNITS calculates aggregates from a stored measure, and UNITS_PP returns the value of the prior period, as defined in "Defining Formulas in the Analytic Workspace". Data types are specified only for the selected columns: TIME_NAME, UNITS, and UNITS_PP.
SELECT Statement
In Example, the SELECT statement identifies the columns and rows of interest, just as it does for physical tables in the database. In this particular selection, the WHERE clause limits all dimensions except TIME to a single value, then labels the result set only with the long descriptions for TIME.
Using OLAP_TABLE to Create a Measure View for the BI Beans
Example shows how you can make the data in an analytic workspace available to the BI Beans using OLAP_TABLE. The process involves these steps:
This example creates a measure view of UNITS, UNITS_PP, and UNITS_PCTCHG_PP for the CUSTOMER MARKET_ROLLUP hierarchy. A second view is required for the SHIPMENTS_ROLLUP hierarchy. The example does not show the dimension views either, although the OLAP Catalog and the BI Beans require views of each dimension.
UNITS_PCTCHG_PP is a custom measure defined in the limit map using the AW_EXPR keyword. It uses the OLAP DML LAGPCT function to calculate the percent difference from the prior period.
Creating and Executing the SQL Script
To create the views for the OLAP API, take these steps:
Creating Views for the OLAP API
About the Sample Script
Example defines a view that conforms to the requirements of the OLAP API fora fact table:
These objects were created by the Create Analytic Workspace wizard. Notice that the GID variables are the only ones that are mapped to columns in the view.
Defining OLAP Catalog Metadata for Workspace Views
To define OLAP Catalog metadata for views of an analytic workspace, you must use the CWM2 write APIs. You can then view CWM2 metadata in the OLAP Catalog view of Analytic Workspace Manager, or by querying the OLAP Catalog views directly in SQL. You can neither define nor view CWM2 metadata using Oracle Enterprise Manager.
The new measures (UNITS_PP and UNITS_PCTCHG_PP) could be added to the existing Units cube. However, Example shows how you can create a new cube for them using predefined dimensions. The example also creates a new measure folder.
To create the OLAP Catalog metadata for the new measures, follow these steps:
These settings enable you to see any error messages and view the full report from the validation programs that are run by the script. It is important to validate the metadata before committing it to your database.
Once these measures are defined in the OLAP Catalog, they are available to BI Beans applications the same as the standard form measures. Figure shows the result set of a query issued through a BI Beans application.
New Measures Queried Using a BI Beans Sample Application
Script for Creating OLAP Catalog Metadata for GLOBAL Measures
|
|
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.