Rittman Mead - Delivered Intelligence

I’ve been playing around with one of the OWB "Paris" betas, and one of the things
that most struck me about this version of OWB compared to previous ones is the
degree to which it utilizes the

OLAP Option. Current and  previous versions of OWB primarily created
relational data warehouses, with the addition of CREATE DIMENSION statements to
help with aggregate navigation. If you wanted to

enable your OWB projects for the OLAP Option you had to use the "OLAP
Bridge" which either created CWM1 metadata for a relational OLAP implementation
or an analytic workspace together with CWM2 metadata for a multidimensional
implementation. Particularly in the case of relational OLAP, you had to do lots
of (non-intuitive) things to make your facts and dimensions work with tools such
as Discoverer for OLAP or BI Beans, such as adding particular suffixes to column
names to create the long and short descriptions needed, and there was no support
whatsoever for the type of materialized view required when using the OLAP
Option. In short, if you use current versions of OWB together with the OLAP
Option, you need to be somewhat "in the know" and carry out lots of post-OWB
steps to make your cubes work properly.

I was pleasantly surprised therefore to note the degree of support that OWB
"Paris" has for creating OLAP Option-ready cubes and dimensions. Some of these
changes that have been put in place to provide this support will however be
initially disorientating for existing OWB developers, and therefore I thought it
worth going through some of what’s coming up and highlight what the point of
this all is.

Just like existing OWB projects, a "Paris" design repository will consist of
a number of projects that contain one or more source and warehouse target
modules. A warehouse target module contains one or more dimensions, together
with one or more cubes containing measures of the same dimensionality.
Dimensions themselves are created using a Dimension Wizard, as before, and one
of the first question the developer is asked is whether the dimension is to be
implemented relationally (ROLAP) or multidimensionally (MOLAP).

I
mentioned this the other week, and the point to note here is that the
decision you make isn’t final; you can change the storage type later on and the
definition of the dimension would still stand. What you are defining at this
point is the logical dimensional model and this applies to both relational and
multidimensional OLAP. The difference here however is that you can create a
multidimensional implementation without going through the intermediate step of
building a relational version, an improvement that’s down to the
new AWXML Java API
that was first introduced with the 10.1.0.3 OLAP "A" patchset.

With existing versions of OWB, the next step would be to specify all the
levels that the dimension uses, across all hierarchies. With OWB "Paris" though,
your next step is to specify all the attributes that will be used by your
dimension, across all levels. One change from existing OWB versions is that you
define a set of attributes, and you then choose to implement each of these at
each of your dimension levels. For example, each level might implement the ID
attribute, plus the NAME and DESCRIPTION attribute - these are all pretty much
mandatory. Then, your might define two more measures, CUSTOMER SEGMENT and
CUSTOMER PROFIT BAND that will then go on to be implemented just by the lowest
CUSTOMER level, and another REGION_HOUSEPRICE_GROWTH_BAND that goes on to be
implemented by just the REGION level.

This idea of having attributes defined in terms of the whole dimension, then
implemented (or not) at each dimension level as required, is something that
comes from the Oracle Express / Oracle OLAP world. When you implement your
dimension relationally, each level-attribute combination turns into a separate
column in the table, and if you implement into an analytic workspace, well
that’s just how attributes are natively stored.

You might also have noticed that the ID and NAME attributes are
specified as Surrogate and Business attributes. What’s happening here is that
each dimension level will have to implement both a surrogate, synthetic ID and a
business ID, meaning for example that your REGION level would not only have the
REGION code from your source system (together optionally with the REGION
DESCRIPTION), it would also have a synthetic region ID generated at load time by
OWB. The reason for this is again down to how dimensions are stored in Express
and analytic workspaces - in a relational star schema dimension, each level is
stored in a separate column, and the IDs for each level member only has to be
unique within the level, whereas analytic workspaces store all dimension level
members within a single dimension object and the level IDs need to be unique
across the whole dimension, hence the need for a synthetic ID at all levels in
the dimension. However, as you’ll see later, OWB "Paris" handles all these
synthetic keys automatically and all you have to bring across is the business
key.

Also, note the significance of the NAME and DESCRIPTION field.
The NAME field is actually the one that you put the business key into (such as
REG10) whilst the DESCRIPTION field is where the descriptive text goes (such as
NORTH-EAST). The NAME field becomes the SHORT_DESCRIPTION whilst the DESCRIPTION
field becomes the LONG_DESCRIPTION, and it’s these that you’ll see in tools like
Discoverer for OLAP when you drill into your dimension hierarchy.

The next page of the wizard lets you define the levels that your
dimension implements. Note the TOTAL_CUSTOMERS level.

Current versions of OWB don’t make you specifically define
TOTAL levels for your dimensions, as you can obtain total figures for a
particular dimension by just omitting the "where" clause in your SELECT
statement. However, OLAP tools such as Discoverer for OLAP and BI Beans (and
before those, tools such as OFA or Express Analyzer) expect a TOTAL level for
each dimension, otherwise you’d not have anywhere in your measure to store data
aggregated up to the TOTAL level;

also, you need to define a specific TOTAL level in the dimension
for it to appear as a selectable hierarchy node in your query builder, as such:

Once you’ve defined your levels, you then get to define which
levels implement which attributes:

In my case the CUSTOMER level will implement all of the
attributes except the REGION_HOUSEPRICE_GROWTH_BAND one, which only applies at
the REGION level, whilst the REGION level will implement the mandatory ID, NAME
and DESCRIPTION ones, the REGION_HOUSEPRICE_GROWTH_ONE one but not the others
which only apply at the CUSTOMER level.

Once you’ve defined your levels and Paris has implemented these
as a default hierarchy, you might then want to move on to the mapping editor to
bring some data into the dimension.   Note that I’ve added a few more
levels into the dimension to illustrate the example:

Remember, what we’re loading here is a

logical model of the dimension, which does not necessarily have to be
implemented as a relational OLAP star schema table. It could be a relational
value-based "a.k.a. parent-child" dimension, or it could be a dimension within
an analytic workspace, which are also value-based. Notice within each level of
the dimension there are items that relate to the level above, so that when you
come to populate the dimension, you populate it level-based and also, if you
need to, value-based as well. By putting this in place Oracle have made it
possible to create mappings that can support data loads into both relational
OLAP dimensions and multidimensional dimensions if needed, but from what I’ve
seen so far if you don’t need to support this, you can just populate the regular
level-based items and leave the value-based ones empty.

Also, note that you don’t now need to explicitly set up
sequences to provide values for the surrogate keys in each dimension level. OWB
Paris sets up the sequences for you and plugs their values into the dimension
mapping "behind the scenes", taking away the need for you to create these
constructs within the mapping.

Another new OLAP Option feature that is pretty special for
relational OLAP users is the support for creating

DBMS_ODM materialized views. Normal materialized views don’t work too well
with tools such as Discoverer for OLAP, as the SQL generated by the OLAP API
uses the GROUPING SETS feature to bring back complete sets of aggregated data, a
feature your regular MVs won’t usually have used. If you want to summarize data
for use with Discoverer for OLAP you have to use DBMS_ODM and up until now
there’s been no GUI tool for doing this.

When you bring up a dimension definition using Paris and you’ve
previously chosen for it to be implemented relationally, you can specify what
dimension / level combinations within the cube are pre-aggregated. Looking at
the SALES cube that uses our CUSTOMER dimension, we can choose to pre-aggregate
at some dimension levels, and have Oracle perform the remaining aggregations on
the fly, very much like you get with analytic workspaces.

Paris will then go off an generate calls to DBMS_ODM to
implement the grouping sets-using materialized view, and register the
aggregations with the OLAP Catalog.

BEGIN
DBMS_ODM.CREATEDIMMV_GS('null','CUSTOMERS','CUSTOMERS_MV.sql','{{PlatformSchema.RTP_TEMP_DIR}}');
DBMS_ODM.CREATEDIMMV_GS('null','PRODUCTS','PRODUCTS_MV.sql','{{PlatformSchema.RTP_TEMP_DIR}}');
DBMS_ODM.CREATEDIMMV_GS('null','CHANNELS','CHANNELS_MV.sql','{{PlatformSchema.RTP_TEMP_DIR}}');
DBMS_ODM.CREATEDIMMV_GS('null','PROMOTIONS','PROMOTIONS_MV.sql','{{PlatformSchema.RTP_TEMP_DIR}}');
DBMS_ODM.CREATEDIMMV_GS('null','TIMES','TIMES_MV.sql','{{PlatformSchema.RTP_TEMP_DIR}}');
DBMS_ODM.CREATEDIMLEVTUPLE('null','SALES');
update SYS.OLAPTABLEVELS set selected = 0 where dimension_name = 'CUSTOMERS' and level_name not in ('PROVINCE','SUBREGION','TOTAL');
update SYS.OLAPTABLEVELS set selected = 0 where dimension_name = 'PRODUCTS' and level_name not in ('TOTAL','SUBCATEGORY');
update SYS.OLAPTABLEVELS set selected = 0 where dimension_name = 'CHANNELS' and level_name not in ('CLASS','CHANNEL');
update SYS.OLAPTABLEVELS set selected = 0 where dimension_name = 'PROMOTIONS' and level_name not in ('CATEGORY');
update SYS.OLAPTABLEVELS set selected = 0 where dimension_name = 'TIMES' and level_name not in ('YEAR','MONTH');
DBMS_ODM.CREATECUBELEVELTUPLE('null','SALES');
DBMS_ODM.CREATEFACTMV_GS('null','SALES','SALES_MV.sql','{{PlatformSchema.RTP_TEMP_DIR}}',true);
CWM2_OLAP_CUBE.set_mv_summary_code('null','SALES','GROUPINGSET');
 END;
/

I thought this was all quite interesting as this is the first
time we’ve seen proper support for the OLAP Option within Warehouse Builder.
Apart from the direct support of multidimensional analytic workspaces (as
covered in more detail in these
two
articles
published earlier) this particular support for the relational implementation of
Oracle OLAP is something pretty special, and something you just can’t really do
with the current generation of OLAP developer tools.

Posted in Oracle Warehouse Builder |