Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 


Drilling From OLAP to Relational Workbooks Using Discoverer 10.1.2 - Part II
July 28, 2005
Mark Rittman

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.


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational