Rittman Mead - Delivered Intelligence

"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.

Posted in Oracle Discoverer |