Wednesday, August 31, 2005
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:
Tuesday, August 30, 2005
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
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.
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
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 have to delete a lot of lines now, as shown below.
Start with the line that begins with <rowLayout width="90%">
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.
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.
- 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)
Monday, August 29, 2005
Friday, August 26, 2005
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?')
Thursday, August 25, 2005
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).
Oracle Application Server 10g Release 2 (10.1.2.0.2)
The Windows version is not yet available, but should be out soon enough (don't have dates yet).
Wednesday, August 24, 2005
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
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!
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]
Monday, August 22, 2005
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)
Here are a few screenshots of the performance comparison slides from the seminar...
Friday, August 19, 2005
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.
Thursday, August 11, 2005
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:
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.
- 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.
Wednesday, August 10, 2005
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.