Some Discoverer OLAP - D4O - queries answered

This is in response to a recent comment left behind by Anand in response to my post - "OLAP 10g R2 web seminar"

The answers here have been provided by Keith Laker (you may already know him from the BI Beans OTN Forum)

1. Does D4OLAP supports the attribute reporting?
OLAP currently supports dimensional selections based on attribute members. If you have an attribute called "Product Color" you can select all products where color is red. If you want to include the values of Product Color in your report you need to either add Product color as a dimension or as a measure (which would allow you to include product color in the databody of the report)

2. How do we do the Dimension based measures calculations (E.g.. ratio of Sales Value by Discount Value (where discount and sales are transaction type dimension members across value measure)) ?
The calculation templates provided within Discoverer, Excel, BI Beans are designed to provide the most commonly used calculations in an easy to use wizard. However, the OLAP engine supports many more functions, such as forecasting. Customers can also create their own functions to generate specific types of calculations using the extensive library of OLAP DML commands and associated functions. The easiest way to create this specific type of calculation is to use Analytic Workspace Manager and create the calculation definition directly within the AW.

3. How do we use the Parameters in OLAP reports?
OLAP Parameters are not supported within Discoverer in the current release. However, if you do need to create a specific report that does require parameter support you can access an Analytic Workspace using SQL via the OLAP_TABLE function. This would allow you to create relational queries against AW cubes. However, you would lose the multi-dimensional query model, therefore, I would only recommend this approach for use with specific types to reports that do not require detailed analysis by end-users.

4. Need to review the YTD function to check if the calculation is proper.
Not sure what the question is? If the customer wants to see the SQL generated by OLAPI the answer is we do not expose our SQL statements. If you want to ensure the data is correct, and that the calculation is actually performing the correct aggregations, then you could use AWM and compare the results from an OLAP DML report to the values in your report. However, I can assure you the calculation syntax for YTD is correct - assuming you have the correct time based metadata available. This is covered in the OLAP database documentation.

My note: Users are more likely to get answers by posting such queries to the appropriate OTN Forum. So do feel free to leave feedback, but for detailed responses like this, your best bet should still be the OTN Forums:

• Discoverer
• BI Beans
• OLAP
• Warehouse Builder
• Data Mining
  

UIX Hack 1 - Removing the 'Connect Directly' section

What is UIX?
This is what Oracle says about UIX: "UIX is an extensible, J2EE-based framework for building web applications. It is based on the Model-View-Controller (MVC) design pattern, which provides the foundation for building scalable enterprise web applications. UIX is server-based and supports a variety of clients, including web browsers and mobile devices. While UIX is based on Java technology, Java is not required on the client."

There is a lot of very useful information on OTN as well as on Jonas Jacobi's blog at http://www.orablogs.com/jjacobi/ and I would suggest you spend time going over it if you are so inclined. In particular, these are good posts to read:

• Is ADF UIX a new technology?
• What is ADF UIX?
• Roadmap for the ADF UIX technology and JavaServer Faces
  

This post is not about UIX, in case you were wondering. However, this post --is-- about how to modify Discoverer UIX files to change certain aspects of your Discoverer web pages. Discoverer Viewer pages and portlets are rendered using UIX rather than XSL in 10.1.2. If you want to make changes to the look and feel and more to your Viewer pages or Discoverer Portlets, beyond what is provided by the App Server Control's Viewer customization capabilities, you have to change these UIX files. I keep getting asked a lot on the kinds of changes that can be made, and how to make certain changes, like remove the 'Connect Directly' link, etc...
Most of these changes are fairly straightforward to make if you spend a little time looking and going over these UIX files, and I am going to try and cover a few of the more popular requests in this blog over the coming weeks.

Before I proceed any further, I have to make this standard disclaimer:

• I am NOT suggesting that you go around and muck with these UIX files.
• Any changes you make to UIX files are unsupported.
• Oracle Support will not support your installation if you run into problems as a result of making changes to these UIX files.
• You will have to reproduce any errors on an instance without these UIX changes for Oracle Support to help you.
• Any upgrades or patches you apply may overwrite custom changes you make to your UIX files.

Ok, on with what this post is really about.
Assuming that you have associated your Discoverer middle-tier with an infrastrucure, and therefore have public/private/SSO connection functionality, when you connec to Discoverer Plus or Viewer, you are presented with two ways to connect to Discoverer.
The first one is by using a pre-defined public or private connection.
The second way is by entering the connection information directly.
Ok, there is a third way also, of using URL parameters, but let's forget that for a minute.

But what happens if you do not want to present users with the option to connect directly at all? To do this you have to make a minor change in a UIX file.

The file to change is under your Discoverer middle-tier's installation folder. If you have installed Discoverer under 'D' drive under a folder named 'ias', the actual path would be D:\ias\j2ee\OC4J_BI_Forms\applications\discoverer\discoverer

The file you need to edit is ViewerConnections.uix

Note: Make a backup of this file before you begin. Make a backup of this file before you begin. In case you weren't paying attention; Make a backup of this file before you begin!

Open this file in a text editor like Notepad, TextPad, JEdit, etc... and locate the following lines where this piece of text appears: "connectDirectly"

You can use the line numbers in the screenshot above as a guide to where in the file you would find the text.
You have to delete a lot of lines now, as shown below.
Start with the line that begins with <rowLayout width="90%"> two lines above the 'connectDirectly' text that you were searching for. Now scroll all the way down in the file (almost 300 lines down) till you find the closing tag for the <rowLayout width="90%"> tag above. You will know you have found the correct closing tag because it will be followed by the closing tags on the succeeding lines as shown in the screenshot below, and also because the indentation will look 'even'. and

Save the file (you DID make a backup of this file, right?!).

Restart your middle-tier's OC4J_BI_Forms component.

Close your browser, open a fresh browser window, and open the Discoverer connections page (http://yourservername.com:portnumber/discoverer/viewer or /plus)

If you do screw up (like I did the first time), you will see this nice pretty "500 Internal Server Error" screen.

However, if you did do this right, you should see your new Discoverer connections page, without the 'Connect Directly' option.

Since both Plus and Viewer use the same screen, the change will be visible on both the Plus and Viewer screens.

To undo your changes, simply delete your existing ViewerConnections.uix file and copy the backup file you made as ViewerConnections.uix.

Warning: Do not try this without adult supervision. The material described here is intended for mature audiences, reader discretion is advised. Seriously, these hacks are not supported; use them with caution and common sense. See my disclaimer above.

BI Tools, Forms & Reports Services, and more now available for Phase 2

More and more platforms are getting added to the list of downloads available for Phase 2 (10.1.2.0.2). Check the Oracle Application Server 10g Release 2 (10.1.2.0.2) page. Some notable additions to the grid:

• Full app server now available on Windows (x86), Solaris (SPARC), and Linux (x86)
• Oracle Business Intelligence now available on Windows (x86), Solaris (SPARC), and Linux (x86)
• Business Intelligence Tools - which includes Discoverer Desktop,
  
• Forms and Reports Services now available on Windows (x86), Solaris (SPARC), and Linux (x86)

Keep checking the downloads page for more additions to the grid (products and platforms).

Phase 2 available on Windows also

Checked OTN this morning, and saw that the Windows version of Phase 2 is also available now for download . Look under the Oracle Application Server 10g Software Downloads page. Click the Oracle Application Server 10g Release 2 (10.1.2.0.2) link. The download is 3 CDs large (1.8GB - CD1: 643,932,847 bytes, CD2: 639,731,241 bytes, and CD3: 638,443,773 bytes, if you must know.). As I wrote in an earlier post, this gives you the ability to install Discoverer in the same Oracle home as the rest of the application server. Also new are updated releases of Reports and Forms.

Change PDF options when emailing reports

This post is the result of a query I answered on the OTN DiscovererForum a day back. If you want to send a worksheet as a PDF attachment, how do you change the properties of the generated PD? E.g, if you want the margins to be larger, smaller, or the orientation to be portrait (or landscape), etc, how do you do that? On the face of it there doesn't seem to be any option or screen available to customize PDF export options. Well, the truth is that there is. The PDF generated is identical whether you are creating a printable version of your worksheet in Viewer (PDF), or whether you are exporting the worksheet as a PDF file. The printable page properties screen allows you to change and set a whole lot of properties such as the orientation, paper size, margins, shrinking factor, etc...

Below is a report where one field, 'Profit SUM' spills over to the next page. What I want to do is to shrink the size of the text (scale down) in the PDF so that all columns appear on a single page.
1. Click the 'Printable Page' link
2. Change the page and print properties and required. Check out a sample PDF page if required. Here I have changed the scaling factor to 60%, and also reduced the margins from their default values to a smaller value. I can take a look at a sample PDF based on these print settings as many times till I get the desired result. The sample itself is based only on a small subset of rows of the actual worksheet, so you don't have to worry (too much) about hogging middle-tier resources.
3. Go back to the worksheet.
4. Click the 'Send as email' link and select PDF as the export format.
5. In the send email page you can click the 'View attachment' link to see how the PDF looks.


(For some reason this is the third time I am (re)publishing this post - the first two times I left some really silly errors behind... must be the Friday evening syndrome: 'what am I doing bloggin at 8pm on a Friday?')

Phase 2 Nugget - Change paper size in Plus

Even though Discoverer in Phase 2 (10.1.2.0.2) doesn't have a whole lot of new features, there are a number of small but interesting (and useful) enhancements that would be worth going over. I hope to spend the next few weeks going over the ones that leap to mind.
One such minor tweak is the ability to change the paper size in Plus. If you go to the Page Setup screen, you will see that there is now a dropdown available for the paper size.
Change the paper size in Plus. You would need to save the worksheet to persist those changes.
Open the worksheet in Viewer; click the 'Printable Page' link and click the 'Page Setup' tab.
And it works the other way too (i.e. change paper size in Viewer, and it is picked up by Plus).
You do need to save your worksheet first. How do you know you need to save? A little asterisk next to the worksheet name tells you that the worksheet has been changed and that you need to save to persist those changes. If you are not the owner of the worksheet, you would only see a 'Save as' link.
Once saved, open the worksheet in Plus to verify that the changes are indeed displayed.

Note: Phase 2 of the App Server is 10.1.2.0.2, but the major version number of Discoverer is actually 10.1.2.1.0 (I covered this in an earlier post).

10.1.2.0.2 - Phase 2 is now available on OTN

The download page has also been improved: makes it easier to find the specific component(s) that you want to download.
Oracle Application Server 10g Release 2 (10.1.2.0.2)

If you want to install just Discoverer Plus and Viewer, and do not want to associate it with an infrastructure, you should use the Business Intellence option (shown below). This also has a smaller download footprint - 1GB.

If you want to use Discoverer portlets, public connections, SSO, etc... then the full installable is what you need:
The Windows version is not yet available, but should be out soon enough (don't have dates yet).

Phase 2 Doc is now available

Phase 2 documentation is now available on OTN. The software has not yet been externalized on OTN, but should become available by the end of the week (or sooner).

You can view or download (a trifle matter of a 295MB zip file, but one that gives you PDF as well as HTML versions of all the doc guides) the doc at http://www.oracle.com/technology/documentation/appserver1012.html

Click the 'View library' link and then click the 'Business Intelligence' tab. There you will see that two new sections have been added:

• Oracle Application Server Reports Services
• Oracle Application Server Personalization

One of the more commonly asked for features that now becomes available in phase 2 is the export/import of Discoverer portlets from one Portal instance to another. This is documented in the 'Discoverer Publishing Workbooks in Oracle Application Server Portal' doc in chapter 2, under the section titled 'How to export Discoverer portlets to a transport set'.

And you will see that the version number has changed (as I described, or attempted to describe, in an earlier post).
Where as the current version number was 10.1.2.0.0, this new release (Phase 2) is version number 10.1.2.1.


Happy doc reading!

Passing a password to Viewer

In the 10.1.2 release (aka the 'Drake' release), the use of passwords in URLs has been discontinued for security reasons. Specifically, you cannot send passwords using the HTML Form GET method. What would happen when you enter a Discoverer URL of the form http://[Discoverer Viewer host name]:[port number]/discoverer/viewer?us=[user name]&pw][password]….? The password is not recognized and the user is instead redirected to a Viewer login screen.

So what do you do? Well, the solution is fairly straightforward. Use public or SSO connections! Ok, so that may not be acceptable to everyone :-)
So what do you do? The answer is you can use a simple HTML form with a POST method to perform this action.

Let's see with an example:
Create this simple (really simple) html page as shown below.

This would display in your browser as a small and ugly looking page (but form is not the objective here, function is - and that was a really bad pun).

You will see two things of interest in the HTML code:
- All the required fields have their values pre-filled: the username, password, EUL, database, workbook, and the worksheet to open. Instead of using workbook and worksheet names I am using their identifiers - this is simply good practice IMO.
- All these fields are hidden. You could make them visible also (as we will see later), but if the objective is to simply provide a button a user can click to open a worksheet in Viewer, this will do just fine.
On clicking this button Discoverer Viewer launches, runs, and displays the ProfitSales worksheet from the RegionAnalysis workbook.

You could obviously make this prettier if you wanted. Another interesting tweak you can do is to also pass in the required page item value so that the worksheet opens with the required page item. To pass this page item value you use the URL parameter pi_[page item name]=[page item value]. Here my page item is called 'Region' and the value I am interested in is 'West'. The URL parameter name-value pair would therefore be 'pi_Region=West'. The HTML source is shown below.
Note that the page item parameter is case sEnsiTivE. Therefore pi_region will not work.

And the page as displayed in the browser will look like this:

The resultant Viewer page looks like this:

Note: this can just as easily be applied to Discoverer Plus. You would need to use the appropriate URL parameters in the HTML form.
[Thanks to Jonathan]

OLAP 10g R2 web seminar

There is a short internet seminar available on OTN on the new features available in Oracle OLAP 10g Release 2 (10.2) at http://www.oracle.com/technology/products/bi/olap/index.html - or click here. It is in the form of a short Q&A session between Nichelle Rhone-Alford from BI product marketing and Bud Endress, Director of OLAP product management.

Some of the highlights are the performance improvements (dramatic improvements actually!) in R2 over R1 and Oracle9i when building and updating cubes, thanks to Oracle's compressed cubes technology. Extensions to this technology include support for partitioned compressed cubes, incremental aggregation, and support for non-additive aggregation methods. Also new are support for MLS, and AW support for transportable tablespaces.

Performance improvements are dramatic in some cases. Some examples (these are taken from the slides):

• A 98% decrease in time taken to build a complete AW. 12 minutes in R2 compared to 660 minutes in 9i (and 18 minutes in R1).
• A 90% decrease in the size of the AW. (1.4GB in R2 compared to 118 GB in 9i and 1.8 GB in R1)
• OLAPI optimizations bring about a 80% reduction in the time taken to run batch queries (1487 minutes in R2 compared to 7171 minutes in R1)

The presentation is available for download from the seminar page, or you can download it here.
Here are a few screenshots of the performance comparison slides from the seminar...

A first look at installing Discoverer using the Oracle Application Server - 10.1.2.0.2 (Phase 2)

Now that phase 2 of the Oracle Application Server 10g R2 is nearing release, it would be useful to take a look at the installation procedure. For the most part the installation is the same as previous releases. However, those who have installed the 10.1.2.0.0. release, aka 'Drake', would have noticed that the BI & Forms installation option was no longer available when installing the Enterprise Edition of the Oracle Application Server. To install Discoverer one had to install it in a standalone mode, using Oracle Business Intelligence. This was the good thing - as it meant that Discoverer Plus and Viewer could be installed without the additional (and substantial) overhead of an infrastructure. Of course, it also meant that such functionality as public and private connections, SSO, OID, Portlets, etc... would not be available as these rely on an infrastructure (Identitiy Management and Metadata Repository). To get these benefits one had to associate the middle tier with an infrastructure. And if you wanted Discoverer portlets, you would have to install Oracle Application Server in another Oracle home; which meant that you could end up with three different Oracle homes. In the phase 2 release, one of the biggest changes in the installation over 10.1.2 phase 1 is that the familiar BI & Forms install type makes a reappearance, so that you do not have to install Discoverer in a separate Oracle Home and then associate it with an infrastructure. One single Oracle home will give you Discoverer as well as Portal. Note that the option to install Discoverer in a standalone mode still remains: you can always download Oracle Business Intelligence and install it.

Instead of having a separate home for the rest of the app server components like Portal, and another home for Discoverer, you can now install both these components in a single Oracle Home (as in previous versions like 9.0.2 and 9.0.4).

Assuming that you have already installed your infrastructure, you can straightaway select the first option - Oracle Application Server 10g - as the product to be installed.

The Business Intelligence and Forms install type is back in phase 2!

Since I need to use Discoverer portlets I have checked Portal. I don't plan on using Wireless or Reports or Forms functionality on this instance, hence I have kept them unchecked. Note that I can always go back and install these components after installation. I don't need to do an uninstall and re-install.

Since this installation will require to be associated with an infrastructure, I have to specify the host name where the infrastructure is installed.

I can now specify the OID login (my infrastructure orcladmin password) to allow the middle tier to be associated with the infrastructure's IM.

Nothing new in this screen: you specify a unique instance name and a password.

Before the installation begins you are shown a list of all products and components that would be installed.

Notice the Discoverer version - it is not 10.1.2.0.2 as one may expect, but 10.1.2.1.0.
Ready for some version soup? Here goes: the Discoverer Plus / Viewer version itself is different! At the time I took these screenshots we were testing the 10.1.2.48.16B shiphome.
So the Oracle Application Server version is 10.1.2.0.2. The Discoverer version is 10.1.2.1.0. And the actual Plus / Viewer version is 10.1.2.48.16 (or 17 0r 18 etc...)!
Here is how it works: basically any component that was present in 10.1.2.0.0 but has been change because of bug fixes or UI changes, etc... is renamed to 10.1.2.1.0. Any components that are new to phase 2 are named 10.1.2.0.2. Unchanged components still carry the 10.1.2.0.0 version number. Trust me, I have to refer to my emails and notes to get this right! Who says the tough part of software is writing it?!

If your standard web port 80 is free, then the middle tier will use this port. Makes life much simpler as you do not have to type in the port number after the host name. Asking users to type in a port number like 7777 or 7779 is simply asking too much from end users.

Note: these screenshots are based on pre-production software, so all the usual caveats about this being subject to change, etc... apply.

More on OTN Forums

Clicked 'publish' too soon :-(

There are a number of BI forums on OTN that are kept fairly active by users of Oracle BI tools. Here are some of the BI related forums:

• Discoverer
  
• BI Beans
  
• Oracle OLAP
  
• Oracle Warehouse Builder
  
• Oracle Data Mining
  

All these forums except BI Beans are placed under the Data Warehousing and Business Intelligence category, while BI Beans is placed under the Developer Suite category.

BI Forums on OTN

OTN forums - http://forums.oracle.com/- updated recently, on July 20th to be precise. The upgrade took the version from 2.6 to 4.1. Some of the new functionality that became available as a result:

• Standard integration with Oracle's Single Sign-On functionality, so that if you log once into any Oracle site that uses SSO, you won't need to login again into Forums.
  
• A rewards system, that gives users a different rating based on the number of posts - 50 or more posts gives you 'Member' status, while more than 150 posts gives you an 'Active Member' status.
• RSS subscriptions - my favourite.
  

Why You Should NOT Publish Large Worksheets to Portal

The Discoverer Portlet Provider allows users to publish Discoverer worksheets to Oracle Portal - tables, crosstabs, graphs, and as gauges. One mistake that I have seen users do, though less frequently now than before, is to assume that what does the job as a worksheet in Plus or Viewer will also work just as well when published to Portal. Specifically, I am talking about taking large worksheets and publishing them to Portal. This is bad design and even worse for performance. Let's see with a simple example. Below is a simple crosstab that ships with the Video Stores schema in Discoverer, 'Conditional Formats', from the 'New Features Examples' workbook. I have published this to a Portal page as as worksheet portlet.

This is the OC4J_BI_Forms memory usage before publishing the worksheet as a portlet.

And this is the memory usage after publishing the worksheet to Portal. The memory increase is only marginal.

Now let's look at a large worksheet. This is a worksheet I have created using Discoverer Plus. Yes, it is a deliberately bad design for a worksheet: too many dimensions and hierarchies, no use of page-items, no parameters to reduce the data, and for all practical purposes a very unusable sheet. It returns some 3,300 rows of data: quite useless if you want to do any analysis on the data.

I will now publish this to Portal as a worksheet portlet.

After I have published this to my portal, take a look at the portlet itself. The error message at the bottom of the portlet. No more than a thousand rows of data are cached by Discoverer in the metadata repository, so obviously 3,000 rows of data are not going to get cached. Page items would have been a much better alternative; or parameters.

But more interestingly, look at the memory consumption of the OC4J_BI_Forms component, before and after publishing the portlet. It is obbious that a huge amount of memory has been taken up to publish this single portlet, right? Why?

In the Discoverer Portlet Provider, portlet data is cached in the DISCOVERER5.PTM5_CACHE table in the metadata repository database. The format of this cahce is XML. The BIBeans (table, crosstab, graph, or gauge) object that you see in Portal is created out of this cached data. It requires data to be passed to it as a DOM object.
The retrieved XML is therefore parsed to create the DOM structure. This parsing of data and creation of DOM object is a resource intensive operation. More the data, more costly is the parsing operation. So if the number of rows is huge then the amount of data parsed is a lot and can cause problems.

[Thanks to Gautam for his help on this post]