Showing posts with label OLAP. Show all posts
Showing posts with label OLAP. Show all posts

Oracle On-line Analytic Processing (OLAP) (I)



OLAP Cubes can play three (3) roles in an Oracle Installation:


(a) Full-featured, fully integrated multidimensional server
  • Applications can query all data using a dimensional query language (Oracle OLAP API or MDX)
  • OLAP engine runs within the kernel of Oracle Database.
  • Dimensional objects (cubes, measures, dimensions, levels, hierarchies, attributes) are stored in Oracle Database in their native multidimensional format.
  • Cubes and other dimensional objects are first class data objects represented in the data dictionary.
  • Data security is administered in the standard way, by granting and revoking privileges to users and roles.
  • Applications can query dimensional objects using SQL.

(b) A Summary management solution
  • The Oracle cube is used to manage summary data and is accessed transparently by SQL-based BI applications as a Cube-organized Materialized View using Query rewrite feature.
  • Cube-Organized MVs were introduced on Oracle 11g, and play the same role as table-based MVs
  • Cube-organized MVs allow any application to transparently access summary data managed by the cube.
  • Cube-organized MVs provide substantial query performance enhancement with much lower maintenance overhead than table-based MV alternatives
  • With Cube-organized MVs, applications query the detail tables and the database automatically rewrites the query to access summary data in the materialized view.

To expose the cube as a Materialized View, you can use the Analytic Workspace Manager:

Both detail and summary data in the cube are exposed in the Materialized View.
When the detail tables are queried, the optimizer rewrite the query to use the cube-organized materialized view
SQL> select t.calendar_year_name,
       p.department_name, 
       cu.region_name,
       sum(s.quantity) as quantity, 
       sum(s.sales) as sales
from times t, 
     customers cu, 
     products p,
     sales_fact s
where cu.customer_key = s.customer
  and p.item_key = s.product
  and s.day_key  = t.day_key
group by t.calendar_year_name, p.department_name, cu.region_name;


Explain plan: 
OPERATION
-----------------------------------

SELECT STATEMENT
  HASH
    CUBE SCAN CB$SALES_CUBE


(c) A supplier of rich analytic content
  • OLAP cubes are exposed as Cube views.
  • Cube views include detail and aggregate data as individual columns (measures)
  • Cube views can list complex analytic content, including hiearchical aggregations, statistical forecasts, additive and non-additive aggregations, calculated measures, etc


Cubes and Dimensions seen from a relational perspective

Since Oracle provides a complete SQL interface to the OLAP cube:
  • Cubes and Dimensionas can be thought of as relational objects.
  • Cubes as relational objects that offer improved performance and advanced analytic content, and
  • Dimensions as relational objects that include columns with information useful to creating hiearchical queries.
  • The cube is simply another data type in the database.




Some benefits OLAP Cubes bring to Business Intelligence applications


(a) Improved query performance for Ad-hoc query patterns
  • As query patterns become less predictable, creation and maintenance of materialized views for specific queries becomes impractical.
  • Consider a data model with four dimensions (time, customer, product, channel), each with six levels of summarization (i.e in the time dimension: day, week, month, quarter, half year and year).
  • In this case, there are (4**6 -1) = 4095 possible combinations representing sumary level data that users might query.

(b) Improved query performance for summary data
(c) Fast incremental update
(d)Rich analytic content
(e)Metadata that describes the logical business model and the relational representations of the cube



(Q) What two categories can OLAP Metadata be grouped into?

(a) Metadata about the Cube's structure, data, and how it is calculated
(b) Metadata about how the cube is represented for query using SQL cube views
  • Description of the cube's structure include information on: dimensions, hierarchies, levels, attributes and measures.
  • For measures, metadata is available describing how the cube is calculated and the calculation expression of a measure.
SQL> select table_name from dict where table_name like '%CUBE%';
TABLE_NAME
------------------------------
...
DBA_CUBES
DBA_CUBE_ATTRIBUTES
DBA_CUBE_ATTR_VISIBILITY
DBA_CUBE_BUILD_PROCESSES
DBA_CUBE_CALCULATED_MEMBERS
DBA_CUBE_DIMENSIONALITY
DBA_CUBE_DIMENSIONS
DBA_CUBE_DIM_LEVELS
DBA_CUBE_DIM_MODELS
DBA_CUBE_DIM_VIEWS
DBA_CUBE_DIM_VIEW_COLUMNS

TABLE_NAME
------------------------------
DBA_CUBE_HIERARCHIES
DBA_CUBE_HIER_LEVELS
DBA_CUBE_HIER_VIEWS
DBA_CUBE_HIER_VIEW_COLUMNS
DBA_CUBE_MEASURES
DBA_CUBE_VIEWS
DBA_CUBE_VIEW_COLUMNS
...

=> Checking the existing dimensions in SALESTRACK analytic workspace 
SQL> select * from dba_cube_dimensions;

OWNER    DIMENSION_NA DIMENSION_TYPE   AW_NAME      DEFAULT_HIERARCHY_NAME       DESCRIPTION
---------- ------------ ----------------- ------------ ------------------------------ --------------------
OLAPTRAIN  PRODUCT2 STANDARD   SALESTRACK   STANDARD         product2
OLAPTRAIN  CHANNEL STANDARD   SALESTRACK   SALES_CHANNEL        Channel
OLAPTRAIN  GEOGRAPHY STANDARD   SALESTRACK   REGIONAL         Geography
OLAPTRAIN  PRODUCT STANDARD   SALESTRACK   STANDARD         Product
OLAPTRAIN  TIME  TIME    SALESTRACK   CALENDAR         Time

You can use SQL Developer to:
  • check existing hierachies, levels and order within hierarchies in the 'PRODUCT2' dimension.


  • List existing cubes, measures within the cube, whether a measure is derived or stored and the equation that generates that measure:



Cube Views
  • OLAP Cubes are exposed through views on a star schema.
  • As the structure of dimensions and cubes are updated,views are automatically maintained
  • If you add new measures, the cube view will be updated
  • Views are always in sync with the underlying dimensions and cubes
  • A single cube view exposes (as columns) all the aggregations and all the calculations for a cube
SQL> select * from dba_cube_views;

OWNER    CUBE_NAME    VIEW_NAME
---------- --------------- -------------------
OLAPTRAIN  FORECAST    FORECAST_VIEW
OLAPTRAIN  SALES_CUBE    SALES_CUBE_VIEW
OLAPTRAIN  NEW_CUBE    NEW_CUBE_VIEW

SQL> select owner, cube_name, view_name, column_name from dba_cube_view_columns;

OWNER    CUBE_NAME    VIEW_NAME     COLUMN_NAME
---------- --------------- ------------------------------ ------------------------------
OLAPTRAIN  FORECAST    FORECAST_VIEW    BEST_FIT
OLAPTRAIN  FORECAST    FORECAST_VIEW    LINEAR_REGRESSION
OLAPTRAIN  SALES_CUBE    SALES_CUBE_VIEW    SALES
OLAPTRAIN  SALES_CUBE    SALES_CUBE_VIEW    QUANTITY
OLAPTRAIN  SALES_CUBE    SALES_CUBE_VIEW    SALES_YTD
OLAPTRAIN  SALES_CUBE    SALES_CUBE_VIEW    SALES_YTD_PY
...

=> The Cube's SQL interface is also available in SQL Developer: