olap oracle |
Documentation
About
An alternative approach to Materialized view and Query Rewrite is to use Oracle OLAP, which has been an option for Oracle Database Enterprise Edition since Oracle 9i. Oracle OLAP allows you to store your data in a special format inside the database using cubes and dimensions that enables fast analysis and query reporting.
The multi-dimensional query
Traditionally, queries against a data warehouse were created to answer questions like :
- ‘How much profit did
- each regional division make?’
But organizations were quickly realizing that the wealth of data in their warehouse enabled much more interesting and sophisticated reports and analysis to be performed.
Now a typical query might be :
- ‘For calendar quarters this year,
- show the percentage change in sales
- for Electronic products sold to customers
- who live in the USA
- compared to the same quarters last year’.
This type of query is often called a multi-dimensional query and business analysts create these queries on-line and ‘slice and dice’ and refine their view of the data to uncover and highlight underlying trends and hence online analytical processing, or OLAP, was born.
The multi-dimensional query expresses a business question in terms of the multiple dimensions that describe the data. In our example above, the data is Sales data and the dimensions are Time, Products and Customers (in this case, the geographical location where the customers live in the USA).
This type of query can be difficult to express relationally using SQL, but would be simple to describe multi-dimensionally using the Oracle OLAP option.
Analytical processing still has to store the data it accesses in a specialized format within the Oracle database and this is an integral part of the Oracle OLAP implementation: data is composed of cubes containing the measures (the data) and whose ‘edges’ are the dimensions that define their levels and hierarchies.
The diagram below shows one cube that is defined by three dimensions, Time, Product and Customer, and the cells within the cube are the data values, (which are also known as measures). In the Figure, the dimensions have been simplified and only depict the lowest levels, for example the months on the Time dimension, but dimensions also incorporate hierarchies that define the grouping of the data. For example, that Months roll up to Quarters that roll up to Years.
Within Oracle Database 11g, the OLAP Option provides specialized storage via the analytic workspace and processing for multidimensional data, using the Multidimensional Calculation Engine.
Analytic Workspace (AW)
The Analytic Workspace (AW) is used to store the multidimensional data types, e.g. the dimensions, measures and cubes. An Oracle database schema can contain one or more analytic workspaces in addition to owning the normal relational objects such as the tables, indexes and materialized views.
Multi-dimensional Calculation Engine
The Multi-dimensional Calculation Engine provides the calculation functionality that enables the user to create sophisticated analytical queries that execute efficiently. For example, queries that can show trends in the data by comparing results to previous time periods or to other groupings of the data such as product categories or geographic regions. The engine executes the analytical queries but also enables forecast and model trends to be constructed, and to run other “what if” types of examinations that are also commonly performed analytical operations. In addition, there is the SQL interface that enables tools to use regular SQL to query the analytic workspace. The SQL is transformed by the database to operate against the analytic workspace objects and the results returned as rows and columns by the SQL interface back to the SQL query.
OLAP API
Finally, there is the OLAP API. This is a programming interface that enables tools and applications to access the analytic workspace and calculation engine directly. Oracle products, such as Oracle Business Intelligence Discoverer 10g and the Excel Spreadsheet Add-In, use the OLAP API to access analytic workspaces.
Materialized View and Cube in the Oracle Database
The Figure below illustrates how analytic workspaces sit alongside materialized views and the relational data in Oracle Database 11g and the different tools used for their administration.
Materialized views are used for pre-computed results for both :
- relational
- and multi-dimensional data.
The cost based optimizer can now rewrite SQL queries to the analytic workspace and materialized views can be defined over the cubes.
OLAP Metadata Integration
All metadata for cubes and dimensions is stored in the Oracle database and revealed in the data dictionary views, so that you can query the entire business model in SQL. Use of the data dictionary to store the metadata officially codifies the dimensional model in the database, provides significant improvements for metadata queries, and supports other new features such as SQL object security for cubes and dimensions.
Overview of the Dimensional Data Model
Dimensional objects are an integral part of OLAP. Because OLAP is on-line, it must provide answers quickly; analysts pose iterative queries during interactive sessions, not in batch jobs that run overnight. And because OLAP is also analytic, the queries are complex. The dimensional objects and the OLAP engine are designed to solve complex queries in real time.
The dimensional objects include cubes, measures, dimensions, attributes, levels, and hierarchies. The simplicity of the model is inherent because it defines objects that represent real-world business entities. Analysts know which business measures they are interested in examining, which dimensions and attributes make the data meaningful, and how the dimensions of their business are organized into levels and hierarchies.
The dimensional data model is highly structured. Structure implies rules that govern the relationships among the data and control how the data can be queried. Cubes are the physical implementation of the dimensional model, and thus are highly optimized for dimensional queries. The OLAP engine leverages this innate dimensionality in performing highly efficient cross-cube joins for inter-row calculations, outer joins for time series analysis, and indexing. Dimensions are pre-joined to the measures. The technology that underlies cubes is based on an indexed multidimensional array model, which provides direct cell access.
The OLAP engine manipulates dimensional objects in the same way that the SQL engine manipulates relational objects. However, because the OLAP engine is optimized to calculate analytic functions, and dimensional objects are optimized for analysis, analytic and row functions can be calculated much faster in OLAP than in SQL.
The dimensional model enables Oracle OLAP to support high-end business intelligence tools and applications such as OracleBI Discoverer Plus OLAP, OracleBI Spreadsheet Add-In, OracleBI Suite Enterprise Edition, BusinessObjects Enterprise, and Cognos ReportNet.
Basic Queries for Monitoring the OLAP Option
Come from Monitoring Analytic Workspaces
Is the OLAP Option Installed in the Database?
The OLAP option is provided with Oracle Database Enterprise Edition. To verify that the OLAP components have been installed, issue this SQL command:
SELECT comp_name, version, status FROM DBA_REGISTRY
WHERE comp_name LIKE '%OLAP%';
How Big is the Analytic Workspace?
To find out the size in bytes of the tablespace extents for a particular analytic workspace, use the following SQL statements, replacing GLOBAL with the name of your analytic workspace.
SELECT extnum, SUM(dbms_lob.getlength(awlob)) bytes FROM global.aw$global
GROUP BY extnum;
EXTNUM BYTES
---------- ----------
0 191776956
To see the size of the LOB table containing an analytic workspace, use a SQL command like the following, replacing GLOBAL.AWGLOBAL with the qualified name of your analytic workspace.
SELECT ROUND(SUM(dbms_lob.getlength(awlob))/1024,0) kb
FROM global.aw$global;
KB
----------
187282