Rittman Mead - Delivered Intelligence

Even though the new release of Oracle Warehouse Builder has support for the creation of Analytic Workspaces, up until recently I’ve still been using Analytic Workspace Manager to create them, as it’s the tool I’m familiar with and it gets the job done with the minimum of fuss. At one of my recent BI seminars though, one of the delegates mentioned in the feedback that he’d had liked to have seen how AWs are created using the new release of Warehouse Builder, and as I’ve had nothing else to do all week in my bleak hotel room, I started working though some examples to see how it worked. In the end I got it working, but there are a few differences between dimension creation with OWB and AWM, and between creating relational (ROLAP) and multidimensional (MOLAP) dimensions within in OWB itself, such that I thought it worth jotting down some notes if anyone else tries to do this.For these notes, I’m working on the assumption that you know how to create relational dimensions and cubes using OWB10gR2, and you know how to create multidimensional dimensions and cubes using AWM10gR2.

To start off the process then, I go and create my first MOLAP dimension, based off of the product dimension that comes with the Global Sample Schema. Once I select MOLAP as the storage type, the first thing I noticed is that there’s no “Surrogate Key” dimension type in the list of dimension attributes:

Contrast this with relationally-stored dimensions where you have both Business, and Surrogate identifier types. What I did then was to delete the ID attribute, and then rename the DSC one as ID and the DESCRIPTION one as DSC, like this…

… the reason being, that the original DSC and DESCRIPTION attributes are also designated as being the short and long description attributes for this dimension. After adding three additional attributes that the product dimension requires - ITEM_BUYER, MARKETING_MANAGER and PACKAGE_ID, when scroll across now, you’ll see short and long description set correctly now for the ID and DSC attributes.

Apart from the lack of a surrogate key attribute and the renaming of the other two standard attributes, the creation of this dimension was the same as if it was created relationally. Next, I map my source data to the dimension, noting again that there’s no surrogate key attribute that we normally end up leaving blank, for warehouse builder to populate for us.

Now to my mind what’s interesting here, is that although Surrogate was no longer available as an identifier type when I created the dimension attributes, if you take a look in the Data Object Editor at the storage properties for the dimension, it says that a surrogate key is being used - what I think is happening is that the surrogate key is being generated and used to create the dimension member ID behind the scenes, hence we don’t get to see it but OWB can still guarantee that all dimension IDs are unique across all levels, something it can’t do just by using my natural (business) key provided in the mapping.

Once I’ve created this dimension, I then create the other Customer and Channel user dimensions, again not using a surrogate key attribute and in these cases, ending up with just two attributes per level, rather than the three (WH_ID, ID and DSC) that I normally have.

the next step is to create the time dimension, which is special as it has to be marked, in the Standard Form OLAP metadata as a “time” dimension, plus we need the END_DATE and TIMESPAN atttibutes so that the OLAP API can perform time-series analysis.

Now as far as I can tell, the only way you can create a dimension of type “Time” using OWB10gR2 is to use the Time Dimension Wizard - if you use the Data Object Editor, I can’t find any way of designating the dimension as being a time dimension. So, once I select the wizard from the menu, like this…

… I then pick the hierarchy, select the levels and have the wizard create the dimension (and a mapping, but more on this later). Now the problem as I saw it was the wizard went ahead and created a whole bunch of additional attributes - MONTH_OF_YEAR, START_DATE and so on that I don’t want, and moreover if I leave these in the dimension, but don’t map data to them, the mapping fails and I can’t load the dimension.

The way around this that I found was to go into the level properties for the time dimension, then deselect the attributes that I don’t want. At the same time, I unselect the ID attribute that it adds in by default - this presumably was for the (now non-existent) surrogate key attribute, and instead rename the CODE attribute, which is the business key, to ID so that it fits in with my normal naming scheme.

As in this case I’m not allowed by OWB to designate the business key as also the short description, I therefore keep the NAME attribute and leave it as the short description, and set the DSC attribute to be the long description.

At the end of this step, I end up with a familiar-looking time dimension, with a business key (ID), short description (NAME) and long description (DSC) per level, and none of the additional attributes that OWB is trying to force on me actually implemented for any of the levels.

Now, when I map to the time dimension, I bring across the source values that I need, like this:

In this particular instance, the business key for the time dimension is actually an integer, but I could just as well make the business key a date-style value such as ‘APR-98′, or anything else I wanted.

Just as an aside, earlier on I mentioned that the Time Dimension Wizard also auto-creates a mapping for you to populate the Time dimension it creates. Like earlier versions of OWB, this mapping using a table function to provide values, but I found that this mapping wasn’t of much value, as:

• It wants to work with the original version of the Time dimension, with all the attributes I don’t really need, and

• More importantly, the business IDs it uses for each of the time dimension members aren’t going to match with the business IDs in my data.

Apologies if I’ve missed something here, but it doesn’t seem too useful a feature to me.

One final point to note with MOLAP storage is that by default, the cubes that OWB sets up for you have no sparsity handling set up - all the dimensions are marked as dense, which contrasts with AWM which makes time dense but leaves everything else sparse. The risk here is that, if you don’t know about this feature - sparsity handling is hidden away in the dimension tab of the Data Object Editor under an “Advanced” button, like this:

you’re going to end up creating potentially very large cubes, so be warned. I went in and marked all the dimensions as being sparse apart from Channel, and in addition enabled partitioning on the quarter level of the time dimension.

Looking back, I should really have selected Compression as well, to take advantage of the incremental load improvements in 10gR2 (only partitions with new data get re-aggregated).

You can also go in and select the levels in the dimensions are pre-computed. Unlike sparsity, OWB uses a sensible default for pre-computing and I leave this at the default settings:

Now the setup is complete, I deploy the dimensions and cube, then the mappings, then run the mappings, and check out the cube in the OWB cube viewer.

Not bad. Anyway, I thought these notes might be useful for anyone else building MOLAP cubes using OWB. One or two people mentioned to me that they thought it actually didn’t work, so it’s good to see it all working fine even if there are a few subtle differences between it and the ROLAP implementation.

Posted in Oracle Warehouse Builder |