You can embed SQL statements in OLAP DML programs using the OLAP DMLSQL command. SQL sql_statement
When formatting a SQL statement that is an argument to the OLAP DML, be sure to use single quotes (') wherever you need quotes. In the OLAP DML, a double quote (") indicates the beginning of a comment.
OLAP DML Support for SQL
You can use almost any SQL statement that is supported by Oracle in the OLAP DML SQL command. You can use SELECT to copy data from relational tables into analytic workspace objects. You can use the INSERT command to copy data from analytic workspace objects into relational tables.
The following Oracle SQL extensions are also supported:
COMMIT and ROLLBACK are ignored as arguments to the SQL command. To commit your changes, issue the OLAP DML UPDATE and COMMIT commands. You cannot roll back using the OLAP DML.
Most SQL commands are submitted directly to the SQL command processor; however, a small number are first processed in the OLAP DML, and their syntax may be slightly different from standard SQL. Table describes the OLAP DML commands that support embedded SQL.
OLAP DML Commands for Embedded SQL
Process: Copying Data From Relational Tables Into Analytic Workspace Objects
Using the OLAP DML, you can populate a standard form analytic workspace from relational tables by taking the following steps:
Fetching Dimensions Members From Tables
There are several strategies for fetching dimension members. The best practice is to fetch just the dimension members first, and fetch their attributes as a separate step. For Time members, the best practice is to fetch one level at a time, making a separate pass through the source table for each level. This practice enables you to fetch the Time members in the correct order so that they do not need to be sorted afterward.
However, the simplest method, and the one shown here, populates dimension members at all levels, and all of the objects that support hierarchies, at the same time. Before using this method, be sure that SEGWIDTH is set correctly, as discussed in "Setting the Segment Size". Example is a template that you can use for fetching dimensions in one pass. The program does the following:
Template for Fetching Dimension Members
Sorting Dimension Members
When you fetch dimension members at all levels in a single pass through the source table, they are mixed together in the target workspace dimension. For most dimensions, the order does not affect processing, although you can sort the members by level if you wish.
However, it is very important for Time dimension members to be ordered chronologically within levels so that the Time dimension supports time series analysis. Functions such as LEAD, LAG, and MOVINGAVERAGE use the relative position of Time members in their calculations. For example, LAG returns the dimension member that is a specified number of values before the current member. If the time periods are not in chronological order, the returned value is meaningless. Your analytic workspace will perform better if you load the dimensions in the correct order instead of sorting them afterward. Example contains an OLAP DML program template for sorting the Time dimension. It does the following:
Template for Sorting the Time Dimension
Fetching Measures From Tables
To fetch data from relational tables, you must write a program that defines a SQL cursor with the selection of data that you want to fetch, then retrieves the data into the analytic workspace objects that you previously created. Example is a template that you can use for fetching all of the measures for a particular cube. It does the following:
Template for Fetching 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|
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.