Wednesday, January 23, 2013

[OBIEE 11g] Using Oracle OLAP

Using Oracle OLAP 11g with OBIEE

The cube's dimensions are associated with facts (also called "measures"). In relational terms, the facts have a many-to-one relationship with the dimensions. For example, Acme Computer Supplies may have a database for sales. Dimensions are usually Customers, Products, and Time Element (month, quarter, etc.).

The Dimensions are stored on individual tables and so are the facts—i.e. the sales figure. So the fact table, in relational terminology, is a child table of the dimension tables.

Whereas MVs store pre-computed results to avoid joins and aggregations, cubes store raw data and compute most summaries on the fly.

Now imagine, if you would, the best of both worlds: the rewrite feature of MVs combined with the performance benefits of OLAP cubes. In Oracle Database 11g, you have it. Perhaps the most important new functionality in this area is the ability to represent OLAP cubes as MVs, via a new feature called Cube Organized Materialized Views.

One new function, CUBE_TABLE, accomplishes searching through the cube in plain SQL. Since the MV is actually an OLAP cube, the Query Rewrite feature will rewrite the queries to use the cube, without you even knowing it. That should allow you to take advantage of the performance of cubes from any tool that supports 

SQL: Oracle Business Intelligence Enterprise Edition, Cognos, Business Objects, Oracle Apex, SQL*Plus, or custom Java programs; the list is endless. In fact you don't even have to know any special syntax.

Benefits of using Oracle OLAP 11g with OBIEE

Improving the BI Solutions:

Because OLAP cubes are exposed using traditional star design, BI solutions can easily query cubes and gain access to their rich analytic content. OBIEE metadata can automatically be generated over cubes. End users benefit from the excellent query performance and enhanced content in business intelligence applications.

Centralized Management:

Metadata (eg. dimensions, levels, hierarchies) and calculation rules are key assets of a BI solution and the organization that owns it. Oracle OLAP allows organizations to manage these assets within the Oracle Database and share them among any number of BI and reporting tools. This management strategy allows the Oracle Database to be a single version of the truth.

1 comment: