|Oracle9i OLAP User's Guide
Release 2 (9.2)
Part Number A95295-01
Developing OLAP Applications, 2 of 5
SQL-based applications can access multidimensional data, which is stored in analytic workspaces. Two mechanisms in the database's object technology make this possible:
CREATE TYPEstatement, you create an abstract template that corresponds to a real-world object.
In OLAP, these "real-world objects" are measures, dimensions, hierarchies, attributes, and so forth. By defining object types for the objects in an analytic workspace, you can describe the format of multidimensional data to SQL as rows and columns.
FROMclause of a query. A table function can take a collection of rows as input.
You can use table functions to fetch data from objects in an analytic workspace. The table functions require arguments that are passed to the OLAP engine, which selects, manipulates, and returns the data. By incorporating table functions into your application, you have the most power and flexibility in selecting and manipulating data in the analytic workspace.
If you overlay the table functions with relational views, then you can make the table functions (and thus the source of the data) transparent to SQL-based applications. Your applications can use standard SQL to run against these views of multidimensional data, the same way that they access other relational tables and views in the database.
PL/SQL User's Guide and Reference for detailed information about object types and table functions.
Figure 3-1 shows how a SQL application can access multidimensional data (using table functions and views) as well as relational data.
There are several ways that SQL can access the multidimensional data of an analytic workspace. An abstract data type and the table functions underlie all of them. The method that you choose depends on how you want to use the data.
CWM2_OLAP_AW_ACCESSPL/SQL package to define a star schema of dimension views and fact views, which represent the measures, dimensions, hierarchies, and attributes in the analytic workspace. You can then query these views using standard SQL
SELECTstatements. (This package is part of CWM2 because it is also used to make workspace objects accessible to the OLAP catalog metadata.)
If you are using the
CNV_CWM.TO.ECM OLAP DML program to create an analytic workspace, you can also use it to generate relational views of your workspace data. The
CNV_CWM.TO.ECM program employs the
CWM2_OLAP_AW_ACCESS package for this stage.
OLAP_TABLEfunction in SQL
SELECTstatements. This method is more complex than using the
CWM2_OLAP_AW_ACCESSpackage (which is a wrapper to this technology), but it provides more flexibility and power in an application than using predefined views.
Using the procedures and functions in the
DBMS_AW package, SQL programmers can issue OLAP DML commands directly against analytic workspace data. They can move data from relational tables into an analytic workspace, perform advanced analysis of the data (for example, forecasting), and copy data from the analytic workspace back into relational tables.
While the data is in the analytic workspace, SQL programmers can also issue
SELECT statements against the data in the analytic workspace using the