Oracle’s 11g Launch Impresses – Intelligent Enterprise

The Intelligence Enterprise’s weblog has a new article about Oracle 11g.  One thing worth highlighted is the feature he feel impressed about on the advancement of OLAP.

“Materialized views are a technique for speeding multidimensional queries, such as those exploring sales across regions, products and customers. However, as the number of materialized views mounts along with query volumes and complexity, managing those views becomes difficult. In 11g, Oracle is using an OLAP cube to store up to millions of materialized views so they can be managed more efficiently.”

One of the problems of the old materialized view is that  you have to know the level you are going to reported by and create the materialized view accordingly.  The benefit of using an OLAP engine is that the aggregation can be available at many different levels andyou do not need to created multiple specific materialized views for storing the summaries.   However, the OLAP cubes was stored in a different technology and populated in different languages.    You need to write codes to map the data from your source system or warehouse to the cubes.  The process needs to be run periodically in order to have the refreshed data from OLAP.  Also, querying the data from OLAP requires a different programming interface, unless you create the OLAP-to-Relational mapping view.

The new feature of 11g seems combining the best from both world and solve all the problems!

Aggregation for Data Warehouse, Part 5

This is the final posting about the aggregation for data warehouse. In the prior posts, I described the following approaches of providing aggregation for data warehouse.

1. ETL and aggregated tables

2. Hand code summarization processes and aggregated tables

3. Materialized Views

I think that for a large scale data warehouse project, a two tiered approach may be favorable. When I say a two tiered approach, I mean that you can have a data warehouse, and …

Aggregation for Data Warehouse, Part I

A data warehouse typically contains historical data that are viewed in a summary form and then let the users to drill down to the more detail transactions. The fact data can easily reach the size of gigabytes to terabytes. Summarizing the huge volume of the fact data online may not be practical.

One of the techniques employed by data warehouse designers to improve performance is the creation of summaries, or aggregated tables, which contains pre-calculated data.

For example, you may see the costs against your project with a inception to date amount, which include the historical cost charged against the project up to the current date. Without the pre-calculated data, a query to fetch the ITD amount may take minutes to fetch and summarize the fact data. Using an aggregated table, the amount is periodically refreshed with the amounts from the recent transactions in the latest summarization process. Fetching the data online from the aggregated table probably takes only seconds.

There are multiple ways to create such aggregations:

Oracle Business Intelligence EE 10.1.3.2 (MAUI) Tutorial

Oracle.com provides a very good tutorial for Oracle Business Intelligence Enterprise Edition 10g:

When to use Oracle OLAP Option

You may find that this Oracle OLAP Option white paper is useful.

http://www.oracle.com/technology/products/bi/olap/1450_olap10g_enhance_content_performance.pdf

A tip of using value-based hierarchy in AW

A value-based hierarchy is currently supported only in AW, the Oracle OLAP option.

I heard that OBIEE may support it in a later release after it supports the data source from AW.

A value-based hierarchy can be used to model the management reporting hierarchy, in which you do not have the specific “level” concept in the hierarchy.

If you have sales are rolled up to their manager, and sales managers are also selling products, you should craete dummy node as a child record of every sales manager so the sales data can be associated with the sales manager.