Using Relational Data

Overview: How you can use the OLAP DML to analyze relational data

Oracle OLAP Services allows you to either move data from the relational database into an analytic workspace, or load data (for example, from a flat file) directly into an analytic workspace. In most cases, the relational database will be used as the primary data store, because it offers advantages related to manageability, scalability, and open access. Analytic workspaces provide support for some analytic features such as forecasting, models, and custom analytic functions defined using the OLAP DML.

To use analytic workspaces, they must be populated with data. The primary method of populating analytic workspaces is to load data from tables in the relational databases. You might also want to update tables with data generated using the OLAP DML. For example, you might load historical sales data from tables onto an analytic workspace, forecast sales data for future time periods, and commit the results of the forecast to tables in a data warehouse.

The SQL command in the OLAP DML is used to interact with the relational database using SQL statements. Using the SQL command, you can select data from tables and load it into analytic workspaces, commit data in an analytic workspace to tables, and perform most other operations supported by SQL.

Differences between the relational database and the OLAP DML models

Relational tables

A relational database stores information in tables organized by rows and columns. Each table contains a column or a combination of columns whose values uniquely identify each row. These unique values are called primary keys and help ensure the integrity of the data. When the values in a column match the values of another table's primary key, that column is called a foreign key. Relationships between tables are established through primary and foreign keys. You can select columns of data from different tables and view them together as long as the tables are related in this way.

OLAP DML variables contain multidimensional data

In contrast, when you use an analytic workspace, you define variables to store data. These variables are multidimensional. Each cell in a variable represents a unique combination of dimension values. Dimensions act as keys to variables. The OLAP DML operates on multidimensional variables and dimensions in analytic workspaces.