Positioning OracleBI Discoverer for OLAP
A number of clients I've worked with recently have been looking to implement OracleBI Discoverer 10.1.2 for OLAP as their general ad-hoc query tool. Whether running against relational OLAP data or data in analytic workspace, Discoverer for OLAP comes with a slick new interface, an OLAP-aware query builder and works against a logical dimensional model that mirrors the way that users picture their business.
Given all the publicity around Discoverer for OLAP, most people aren't actually aware that the relational version of Discoverer Plus, previously known as Oracle Discoverer Plus 9iAS, as now been updated to version 10.1.2, and is now known as OracleBI Discoverer Plus 10.1.2. This new version has much the same look and feel as Discoverer for OLAP, but runs against an End User Layer and queries regular tables and columns.
Both versions are installed alongside each other and are reached using the same URL - http://yourserver:port/discoverer/plus, with the user selecting which version to run from a drop-down menu.
So why have Oracle done this, and why would you still want to use the relational Discoverer Plus version rather than Discoverer for OLAP?
The benefits of using Discoverer for OLAP, and the OLAP Option, are fairly well understood; what's not so apparent for most Discoverer users is what Discoverer Plus offers that Discoverer for OLAP cannot. I've been thinking about this a lot recently and I think it comes down to two factors, and this is disregarding for the moment the cost issue of implementing the OLAP Option.
- The logical dimensional model, though powerful, does not lend itself to including dimension attributes in a report, and
- Discoverer for OLAP, as it always generates a crosstab report, is not a valid solution when you need to produce tabular reports.
Because of this, I believe that the vast majority of Discoverer installations will require both Discoverer for OLAP and Discoverer Plus to be implemented. Let me explain further:
To understand the role of the different versions of Discoverer, you need to think about what OLAP is, what sort of reports it generates, and how these map to your organisation's requirements. Discoverer for OLAP works against a logical dimensional model, which organises your data into measures (the things you add up, like sales, headcount, income and insurance claims), dimensions (such as customers, products, time, sales territories). Within Oracle OLAP, an analytic workspace consists of a number of dimensions, and a number of cubes, which are containers for measures that share the same dimensionality. A typical cube could be drawn as this:
where you have a measure, "Sales", dimensioned by four dimensions, "Customer", "Time", "Products" and "Channel". Each of these dimensions will have attributes, such as "Customer Name", "Customer Type", "Customer Profit Segment", "Product Description", "Product Colour" and so on, and will usually have levels and hierarchies so that you can drill into your data and produce summaries at meaningful levels. Note here that attributes exists as parts of dimensions, and there purpose with regard to Discoverer for OLAP is to allow you to refine the subset of data that you are looking to analyze.
When you think of your data dimensionally and you have added useful attributes to these dimensions, you can formulate dimensional queries using terms familiar to business people. For example:
What was the percent change in
revenue
for a grouping of my top 20% of products
during a year ago rolling three month time period
as compared to the current period this year
for each region of the world?
or
what are my top ten customers
without worrying about how the data is actually stored in the database, instead using the logical dimensional model-aware query wizard that comes with Discoverer for OLAP.
This flexibility and ability to create subsets of data through reference to dimensional attributes is a key feature for Discoverer for OLAP, and means that you can take a set of figures, analyze and model it through refining your dimension member selection, without having to worry about outer joins, analytic SQL or query performance. For more details on what the benefits are of a true OLAP data source, check out Bud Endress's OpenWorld paper "Oracle OLAP 10g: Enhance Content and Improve Performance".
The thing is though, that although with Discoverer for OLAP you can build pretty powerful data retrieval queries using selections of attributes, it's not so easy to have these attribute values displayed in your workbook. This is because attributes exists as a means to make dimension member selections, not as something that appears in your report - those things are called measures. Take an example, where I want to produce a crosstab that displays the value of product sales, broken down by product and channel (I'm using the Global Sample Schema that you can download from OTN). So far, no problem - but what if I want to include the names of the product marketing managers in the report, subdividing the products by these product managers?
Using Discoverer Plus, this isn't a problem. Although Marketing Manager is a dimension attribute (a column in the CUSTOMER_DIM table) I can drop it onto the crosstab and see it in the report:
Using Discoverer for OLAP though, I'm stuck, as although Marketing Manager is a dimension attribute, I can't display it in the crosstab - I can only use it to make dimension member selections:
If you're an old Express user and you're reading this, you're probably thinking "what about one dimensional text variables - they're our way to include labels in crosstabs". What this involves is using AWM10g to create an additional cube in your analytic workspace, with one dimension (in our case, Product) and a single measure defined as a text variable. Then, whenever you display products on the screen, you can include your "attribute measure" in the report as well. Traditionally, this is done as a way of adding text annotations to cubes, with the annotation being held in a variable dimensioned the same way as the measure.
Note that the measure is nonadditive, which means that it will only exist at the bottom level of the product dimension.
Next, we add the measure to the crosstab:
and then the measure appears - but it gets added alongside the Sales measure, and then is only visible when you drill down to the bottom level. So - it's not really where we were expecting it to appear, and it's not being used as a way of subdividing our products into those managed by particular product managers.
Now I'm sure there are probably ways of getting around this - maybe making product managers into a hierarchy of their own, or there may be some other way of achieving this. But what this says to me is that, when you use the logical dimensional model and you load up your dimension with attributes, it's not all that straightforward to have the attributes appear on the report, or use them to further group or subdivide your data. It's just the way the logical dimensional model works - it's got it's advantages (logical grouping of data into measures, dimensions, attributes and so on) but it's not as freeform as an SQL query tool that lets you group data by just about anything.
The other issue is around the production of tabular reports. As you know, Discoverer for OLAP is designed around crosstab reports - indeed it's the only option that you get when putting together a new workbook. But what if your organisation wanted to produce a report that lists out the products that you sell, who is the marketing manager, grouped into product categories and listed in order of sales volumes. Using Discoverer Plus, you could produce something like this:
A straightforward listing report, group sorted by category and with the category, product name, marketing manager (remember, an attribute) and sales total on the report.
With Discoverer for OLAP, although you can't choose to create a tabular report, you can achieve something like this by creating a worksheet using only one dimension (product) and hiding the others.
Then, by bringing in the Marketing Manager measure that we created earlier, we can put something together that is "sort of like" our relational tabular report:
However, it's probably not what we were really after. The product name and category are part of the dimension crosstab element, not the report, the report is all spread out rather than available on one page, and there's lots of cells that have no values in. It's probably not what the users were expecting and probably won't be acceptable for production use.
Now none of this is meant to detract from what Discoverer for OLAP, and the OLAP Option, offers. It's just that not all enterprise reporting fits neatly into the category of OLAP analysis of cubes of financial data, and formal OLAP structures aren't always the most appropriate for ad-hoc, freeform reporting. What this says to me is that, in reality, most Discoverer implementations are going to consist of a bit of relational Discoverer analysis, and a bit of OLAP Discoverer analysis; Discoverer for OLAP will be best suited to analysis of cubes of financial or performance data, where you want to drill into and investigate how the figures are made up using complex multidimensional selections, whilst Discoverer Plus is for when your reports need a lot of textual data, you need to produce simple listings of data, or your users want the freedom to have just about anything that's in the underlying database appear in the report.
Any comments from anyone, perhaps who's worked on a Discoverer 10.1.2 implementation or perhaps who has used Express to meet some of these non-OLAP reporting needs?