Drilling from OLAP to Relational using Discoverer 10.1.2

In this article Mark explais how one can drill from a "Discoverer for OLAP worksheet" to a "Discoverer Plus Relational worksheet" using Discoverer 10.1.2.

"I have a question about drilling from an OracleBI Discoverer for OLAP 10.1.2 worksheet to a Discoverer Plus Relational worksheet. When you pass values from an OLAP worksheet you pass either the dimension name or the dimension value to the associated parameter in the relational worksheet. Obviously, in OLAP this dimension is treated as an object, and we have no idea which level the user may have picked before he drills out. On the other hand, in the relational world, each level of the dimension would be split out as a separate parameter. Could you run through a simple example where you drill from an OLAP worksheet to a relational worksheet and show how this is done?"

One of the key features of OracleBI Discoverer 10.1.2 is the integration between the OLAP and the relational data. If you've taken a look at any of the promotional material available around Discoverer 10.1.2's launch, you'll probably have seen that it's now possible to drill directly from an OLAP workbook, running against data in a multidimensional OLAP cube, to a relational workbook, running against a regular end user layer.

Once you come to try this out though, you come across an interesting problem. Your OLAP worksheet can set up a drill to a relational Discoverer Plus or Viewer worksheet, and can pass across the dimension values that apply to the cell that the user is drilling from. However, in the OLAP world, a dimension consists of all the dimension values for all levels, with no distinction between values from one level in the dimension hierarchy or another. When you come to pass these dimension values out to the relational worksheet, assuming you've got a customer dimension with customer, region, warehouse and total customer levels, your value might be from any of those levels - a single customer, a customer region, warehouse or even the "total customer" indicator. The problem comes when trying to apply this dimension value to the relational worksheet, where your customer dimension is actually represented by four separate items, and your OLAP parameter will apply to only one of those items. How to you set parameters up in the relational workbook when there's this mis-match between the way that the OLAP worksheet passes across your customer dimension selection, and the way that the relational workbook works?

To show how this works, I'm using the GLOBAL Sample Schema that you can download from OTN, which gives me an analytic workspace template definition and source data that can be loaded into Analytic Workspace Manager 10.1 and then used to build the GLOBAL schema. I've also created an End User Layer, GLOBAL_EUL, that has a business area defined over the GLOBAL source tables. I will create an OLAP workbook using the GLOBAL analytic workspace, and a relational workbook over the GLOBAL_EUL end user layer.

The relational workbook I'm going to drill to is a tabular listing of product sales to customers, with group sorts applied to each of the product and customer items. This is the layout of the workbook before any parameters or conditions are applied, and the report contains details of every sale in the UNITS_HISTORY_FACT table.

In this worksheet I'm displaying the Region, Country (warehouse) and customer (ship_to) from the CUSTOMER_DIM table, and product class (class), product family (region) and product (item) from the PRODUCT_DIM table. Now, as the dimension value that comes from the OLAP worksheet could be for any of these columns (or indeed for the total customer or total product items in the business area but not included on the worksheet) I next need to create a parameter that refers to each one.

Starting with ship_to first, note that the parameter is based on the customer_dim.ship_to_dsc item, and that the "Create condition with operator" tickbox is deselected. This is very important. Do the same for the other items so that you have a total of 8 parameters, one for each of the product and customer items that relate to the levels in the OLAP product and customer dimensions. Note also that I'm basing the parameters on the descriptions, not the values - more on this later.

Once you've done this, you should end up with 8 parameters for the worksheet.

The next step is to create a condition that works off of these parameters. What you're doing here is saying "show me the rows where (either the ship_to description = ship_to parameter, or the region description = the region parameter or the warehouse description = warehouse parameter, or the total_customer description = total_customer parameter) and (the item description = item parameter or the family description = family parameter or the class description = class parameter or the total product). Your screen should look like this:

To test this out, you can run the worksheet and bring up the parameter entry screen. Pick a customer level - in my case Europe, from the region level - and then paste that into the other customer parameter fields. Do the same with products - Sentinel Financial, from the item level - and paste that into all of the customer parameter fields.

This is what our OLAP worksheet is going to do - it's going to pass the product dimension label to each of the product parameters, and the customer dimension label to all four of the customer parameters, and the condition I've just set up will then compare it to each of the product and customer item descriptions to find the right match. If we run the query, the worksheet will come back with the right subset of values:

Note that all customers in Europe have been selected, but only the Sentinel Financial products. Next, it's over to the OLAP worksheet. This worksheet will display sales, dimensioned by customer and product. I created the link as a link through to Plus relational, and specified the workbook and worksheet that I'd just set up.

The key bit now is to create eight parameters, one for each of the relational worksheet parameters we'd just set up, tied either to the customer or product dimension labels.

And that's all there is to it. Now, to try out the drill, bring up the OLAP worksheet, then right-click on a particular cell and select the "Drill to Link" option, selecting the link we've just created.

Then, after the link is selected, Discoverer Plus relational will then start up and list out the details for the dimension combination that we've previously selected.

And that's all there is to it. In fairness though, there's a few caveats to be aware of:

• First of all, this approach, where we feed the dimension label into the worksheet parameters, will only work when each dimension's labels are unique across all levels. To take an example, if we had a customer called "American Industries" which rolled up to a customer group also called "American Industries", the dimension label will match the values for both the customer and customer group parameters. The way around this is to use the dimension value, not the dimension label, and have this match up with the product ID, family ID and so on. The only issue with this (and the reason I didn't do this) is that you need to ensure that your relational IDs are the same as your analytic workspace dimension member IDs, which they often aren't as AWM by default appends a surrogate key to your dimension ID, to ensure that all dimension values across the dimension levels are unique. This is the case with the GLOBAL Sample Schema. If you're going to go down this path, you need to ensure that you just use the natural key for the AW dimension member ID, and make sure they are all unique across the entire dimension.

• The second issue I had was the time it takes the relational version of Discoverer to start up when drilled to from the OLAP version. With my setup (Dell laptop, 2GB RAM running Windows XP, BI10g installed along with 10.1.0.4) it takes a good 30-60 seconds for Discoverer Plus relational to start up, which isn't unusual but probably isn't the "seamless drill-to" that users might be expecting. On a proper server with lots of memory and CPU (from looking at Windows Task Manager, the CPU was maxxed out whilst Discoverer Plus started up) it might not be an issue, but test this out on your hardware before making it a key part of your project.

• The last issue is around passwords and security. If you haven't enabled Discoverer for SSO your user will be presented with the Discoverer Plus login page and prompted to enter their password - again not exactly "seamless". Also, think about the situation where you create your OLAP workbook, apply security to it so that the user can only see a subset of the cube, and you then want to drill out to the relational workbook whilst preserving this personal view of the data. Given that the Create Link wizard either specifies a set username (in my case, the name of the global EUL) or a set connection, I can't help thinking that having this link work with individual usernames and passwords is going to be a bit problematic. Again, try this out and test it, using the real scenario you're going to need to use, before assuming that it'll all work fine when rolled out to the real user community.

Other than that, the link between OLAP cubes and relational worksheets works, and it's possible to create one link that works for all dimension levels selected. There's a few issues over performance, and things to look out for with regard to security, but here at least is a technical solution that delivers what was promised in the Discoverer "Drake" publicity.