OWB 11gR2 – OLAP and Simba

Oracle Warehouse Builder was the first ETL product to provide a single integrated and complete environment for managing enterprise data warehouse solutions that also incorporate multi-dimensional schemas. The OWB 11gR2 release provides Oracle OLAP 11g deployment for multi-dimensional models (in addition to support for prior releases of OLAP). This means users can easily utilize Simba's MDX Provider for Oracle OLAP (see here for details and cost) which allows you to use the powerful and popular ad hoc query and analysis capabilities of Microsoft Excel PivotTables® and PivotCharts® with your Oracle OLAP business intelligence data.

The extensions to the dimensional modeling capabilities have been built on established relational concepts, with the option to seamlessly move from a relational deployment model to a multi-dimensional model at the click of a button. This now means that ETL designers can logically model a complete data warehouse solution using one single tool and control the physical implementation of a logical model at deployment time. As a result data warehouse projects that need to provide a multi-dimensional model as part of the overall solution can be designed and implemented faster and more efficiently.

Wizards for dimensions and cubes let you quickly build dimensional models and realize either relationally or as an Oracle database OLAP implementation, both 10g and 11g formats are supported based on a configuration option. The wizard provides a good first cut definition and the objects can be further refined in the editor.

olap_wizard_dim_1

Both wizards let you choose the implementation, to deploy to OLAP in the database select MOLAP: multidimensional storage.

olap_wizard_dim_3

You will then be asked what levels and attributes are to be defined, by default the wizard creates a level bases hierarchy, parent child hierarchies can be defined in the editor. Once the dimension or cube has been designed there are special mapping operators that make it easy to load data into the objects, below we load a constant value for the total level and the other levels from a source table.

olap_mapping_dim_1 

Again when the cube is defined using the wizard we can edit the cube and define a number of analytic calculations by using the 'generate calculated measures' option on the measures panel. This lets you very easily add a lot of rich analytic measures to your cube.

olap_cube_6

For example one of the measures is the percentage difference from a year ago which we can see in detail below.

image

You can also add your own custom calculations to leverage the capabilities of the Oracle OLAP option, either by selecting existing template types such as moving averages to defining true custom expressions.

image

The 11g OLAP option now supports percentage based summarization (the amount of data to precompute and store), this is available from the option 'cost based aggregation' in the cube's configuration. Ensure all measure-dimensions level based aggregation is switched off (on the cube-dimension panel) - previously level based aggregation was the only option.

image

The 11g generated code now uses the new unified API as you see below, to generate the code, OWB needs a valid connection to a real schema, this was not needed before 11gR2 and is a new requirement since the OLAP API which OWB uses is not an offline one.

image

Once all of the objects are deployed and the maps executed then we get to the fun stuff! How can we analyze the data? One option which is powerful and at many users' fingertips is using Microsoft Excel PivotTables® and PivotCharts®, which can be used with your Oracle OLAP business intelligence data by utilizing Simba's MDX Provider for Oracle OLAP (see Simba site for details of cost).

I'll leave the exotic reporting illustrations to the experts (see Bud's demonstration here), but with Simba's MDX Provider for Oracle OLAP its very simple to easily access the analytics stored in the database (all built and loaded via the OWB 11gR2 release) and get the regular features of Excel at your fingertips such as using the conditional formatting features for example.

image

That's a very quick run through of the OWB 11gR2 with respect to Oracle 11g OLAP integration and the reporting using Simba's MDX Provider for Oracle OLAP. Not a deep-dive in any way but a quick overview to illustrate the design capabilities and integrations possible.

Comments:

hi, In that .mdl file when importing we are getting error. i.e can not import because the base language in the mdl is missing . error code is MDL 1601.

Posted by anil on June 20, 2010 at 04:27 AM PDT #

Hi Anil Which URL are you using? Cheers David

Posted by David Allan on June 21, 2010 at 01:29 AM PDT #

Hi,
Is it possible to send me the source of this project of molap in owb that you have done.

Posted by guest on November 09, 2011 at 01:01 AM PST #

I don't have the MDL to hand for that specific example. There are OWB OBEs that have the collateral for similar tasks or other blog posts such as the one below that illustrates cube MVs. (https://blogs.oracle.com/warehousebuilder/entry/owb_11gr2_cube_organized_materialized_views)

Cheers
David

Posted by David on November 09, 2011 at 10:16 AM PST #

hi,
but this link is not open and it showa error 404 not found.

Posted by guest on November 10, 2011 at 07:24 AM PST #

For some reason the ( ) were added into URL....
https://blogs.oracle.com/warehousebuilder/entry/owb_11gr2_cube_organized_materialized_views

Cheers
David

Posted by guest on November 10, 2011 at 07:43 AM PST #

How can I do a connection from Jdeveloper 11g?

Posted by Diego on May 31, 2013 at 03:00 PM PDT #

Not sure what you are trying to do, can you explain?

Cheers
David

Posted by David on May 31, 2013 at 03:03 PM PDT #

@David
Hi, thanks for the response, I am trying to connect to a warehouse cube from Jdev and using the graph components and pivot tables be able to diplay the data, I am building a decision making system.

Posted by Diego on May 31, 2013 at 03:12 PM PDT #

Hi Diego

Not sure. OWB used BI Beans controls many years back, what controls exactly are you trying?

Cheers
David

Posted by David on May 31, 2013 at 03:46 PM PDT #

I've read about BI Beans, but they are no longer supported right?
I use EJB 3.0 what I do is I map my tables to entities and create datacontrols and drag & drop the methods to the UI, I'd like to know how to do it If I have a cube/warehouse

Posted by Diego on May 31, 2013 at 04:32 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today