How OWB “Paris” Enables The OLAP Option

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.