The OLAP_TABLE function provides the basic technology for extracting data from an analytic workspace. All of the views of analytic workspaces that are generated by the enablers use the OLAP_TABLE function. By using OLAP_TABLE directly, you have full control over data access. You can develop your own views to support applications for which there are no enablers, and you can extract workspace data directly into your application. This capability can provide your application with tremendous flexibility, since user queries can be formulated into calls to OLAP_TABLE at runtime.
While the OLAP tools that use the OLAP_TABLE function require a standard form analytic workspace, the OLAP_TABLE function itself does not use standard form metadata.
Designing Views of an Analytic Workspace
The number of views that you create, and the number and characteristics of the columns in these views, depends largely on the requirements of the applications that these views are designed to support.
Because analytic workspaces contain aggregate data, the views must include the aggregates. There are several formats for presenting aggregate data:
These are the basic steps you must follow to generate views of data stored in an analytic workspace.
You use the OLAP_TABLE function in a SQL SELECT statement to query the multidimensional data stored in an analytic workspace. OLAP_TABLE can be used wherever you would use the name of a table or view. You can use SELECT statements to create views, or to fetch data directly from an analytic workspace into an application.
OLAP_TABLE returns a table of objects that can be joined to relational tables and views, or to other tables of objects populated by OLAP_TABLE. It can also return stored workspace data, or it can perform calculations on stored data and return the results of the calculations.
Example is a template that you can use as the starting point for the SQL scripts that you will develop for extracting data from your analytic workspace. You can then execute the script with the @ command in SQL*Plus.
Template for Using OLAP_TABLE
Using the SELECT MODEL Clause
When used in a SELECT statement that queries OLAP_TABLE, the MODEL clause is an optimization that results in significantly faster response time. It can be used only when creating a table type with embedded total dimensions, such as the views used by the BI Beans and the OLAP API.
Note that while the MODEL clause is used in relational queries for inter-row calculations, you should not use it for this purpose with OLAP_TABLE. For OLAP_TABLE, the MODEL clause is used only to optimize the query. When used in a SELECT statement that queries an analytic workspace, MODEL has the following arguments.
The names of the embedded total dimension columns, as defined in the limit map. For BI Beans applications, include the GID columns in this list.
Any other columns in the DIMENSION BY list disables this optimization. A properly constructed SELECT statement still executes, but more slowly.
The measures, attributes, R2Cs, and any other columns excluded from the DIMENSION BY list.
RULES UPDATE SEQUENTIAL ORDER
The RULES clause is required, but it should not include complex or inter-row calculations since they will slow the query. Any calculations specified in the RULES clause are performed by SQL. If you want to perform inter-row calculations, you can create a custom measure in the analytic workspace using any of the alternative methods discussed in this chapter, including the limit map of OLAP_TABLE.
UPDATE indicates that you are not adding any custom members in the DIMENSION BY clause. Be sure to include this keyword, because otherwise the SQL WHERE clauses for measures are discarded, which can significantly degrade performance. SEQUENTIAL ORDER prevents Oracle from evaluating the rules to ascertain their dependencies.
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.