Database Software

Dear all

We have a client with an interesting problem. I was wondering if the group could offer any feedback on this design question, where we think it involves a concept called 'skip levels'. The client is using Oracle 9.0.2 with their data mart cube held as ROLAP. Our first thoughts are that what they want to do is better served by using a true OLAP tool such as Oracle Express, or Oracle 9i OLAP, which deals with skip level dimensions easier. Anyway, here's the problem :

"For our warehouse project, we need some dimensions (initially: account, company) that use a skip level hierarchy. This means that the lowest level dimensions are of the same level (G/L account or company) and they need to roll up in a structure of varying levels, before they come together again in a highest level.

Ideally, we would like to use a data structure that can be used transparently by the user and allows for rollups of amounts through the dimension. We would like to be able to populate the dimension through OWB and to query the data using both Crystal reports v9 and BI beans and possibly other tools that recognize the Oracle data warehouse concept.

The two solutions we are aware of are:

1. Insert Dummy levels:

This is not a nice solution but is will work and it is simple.

2. Insert a helper table that contains the higher level account with all the children (as per Ralph Kimball)

This seems a cumbersome solution but I am sure it would work also. The question is how the Oracle's BI beans and the CWM2 metadata would recognize this and how we can make this transparent to the user.

I am sure there are other solutions to the problem. It seems like the problem would be solved by the next release of Oracle (9.0.3?), which allows us to deal with skip level hierarchies through CWM2 (which I believe this is). The solution we choose should also allow us to easily upgrade to the new skip level hierarchy, which I would expect to be easier to use then the above."

The main thing to bear in mind here is that they want to implement a ROLAP solution, using Oracle 9i tables, dimensions and heirarchies. It also has to work with Oracle Warehouse Builder, take advantage of the CWM2 metadata (if possible) and work with the BI Beans.

Any thoughts on this? Does anyone know how Oracle are going to cater for skip-level dimensions with the next release? Is this through the improved support for the CWM2 metadata standard for ROLAP cubes, or is the customer best of going to the true OLAP 'Analytic Workspace'? If we ignore a MOLAP solution, what's the best way of dealing with this, in such a manner that it's transparent to query tools, and isn't too much of a 'hack'?

Many thanks in advance,

Mark Rittman

-------------------------------------------------------------------------------
Mark Rittman
Consulting Manager, Plus Consultancy
mrittman@plusconsultancy.co.uk


-------------------------------------------------------------------------------