The new cumulative patch for OracleAS Discoverer 10.1.2.55.26 has been just released. You can connect to http://metalink.oracle.com and download it, the id is 7595032.
Opatch is used to install Discoverer Cumulative Patches. The patch readme has all the install/rollback instructions.
I'd like to remind that OracleAS Discoverer 10.1.2.55.26 is a release version obtained by installing the Oracle Application Server PLACEHOLDER patch: 5983622.
Tuesday 28 April 2009
Friday 3 April 2009
Create or Manipulate directly the SQL produced by Discoverer User Edition
In Discoverer User Edition (Desktop, Plus, Viewer) it is not possible to alter directly the SQL produced. The query is dynamically created from Discoverer, depending on items, joins, condition, calculation ... selected during the workbook creation.
The only chance I see here is to create a custom folder in Discoverer Administrator and then create a workbook based on it:
"Custom folders are folders based on a SQL statement which could include SET operators (e.g. UNION, CONNECT BY, MINUS, INTERSECT) or a synonym that you type directly into a dialog.
By defining a custom folder, you can quickly create a folder that represents a complicated result set. When you save the custom folder, Discoverer Administrator creates items using the 'Select' part of the SQL statement you have entered.
In Discoverer Plus, there is no distinction between a custom folder and a simple folder. A Discoverer end user can use a custom folder to build queries in the same way as any other type of folder."
Extracted from :
Oracle® Business Intelligence Discoverer Administration Guide
10g Release 2 (10.1.2.1)
Chapt. 6 Creating and maintaining folders
-- What are custom folders?
(http://download.oracle.com/docs/html/B13916_04/folders.htm#sthref318)
Wednesday 1 April 2009
How to change CSV Delimiter in Discoverer
Discoverer does not have the feature to change the delimiter when you export CSV format from it. This has been rejected by Development with the enhancement request :
Bug 3179364 ENH EXPORT CSV FORMAT SHOULD BE ABLE TO SPECIFY THE SEPARATOR
So, Discoverer will always use the comma as delimiter when you export to .CSV format.
Now, to complete the picture, the delimiter is defined on Microsoft Operating System in the Control Panel >> Regional Settings => Numbers tab => List Separator and this is taken for example from Excel, so if you save from excel in .csv the delimiter will be the one defined in the List Separator. In any case, this is not a standard, so the point that it is working this way on Excel does not means that it should works the same on all other softwares, like Oracle Discoverer, this is not a bug in functionality. Generally .csv delimiter is always associated with comma :
http://tools.ietf.org/html/rfc4180
http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm
Wednesday 25 March 2009
How to Maintain Alignment for Collapsed Items through Sessions ?
Some questions arrived on why if items which are part of an hierarchy are collapsed, they loose the format definition like alignment ?
Well, this is expected behavior. Indeed, every time an item is removed from the layout and added back in a different session it's treated as completely new item and doesn't matter whether it is part of an hierarchy or not. Indeed, this is because the format layout of items is defaulted in the EUL, and in Discoverer Desktop one can maintain control of the format only on the session not on the default properties like alignment.
So, to make alignment persistent on Discoverer Desktop, the default properties for that item should be changed in Discoverer Administrator :
1.) Open the business area on Discoverer Administrator ;
2.) Expand the folder where the item reside ;
3.) Right Click on that item and select Properties ;
4.) On properties dialog box, select the wanted alignment
Well, this is expected behavior. Indeed, every time an item is removed from the layout and added back in a different session it's treated as completely new item and doesn't matter whether it is part of an hierarchy or not. Indeed, this is because the format layout of items is defaulted in the EUL, and in Discoverer Desktop one can maintain control of the format only on the session not on the default properties like alignment.
So, to make alignment persistent on Discoverer Desktop, the default properties for that item should be changed in Discoverer Administrator :
1.) Open the business area on Discoverer Administrator ;
2.) Expand the folder where the item reside ;
3.) Right Click on that item and select Properties ;
4.) On properties dialog box, select the wanted alignment
Monday 9 March 2009
Aggregation: DECODE & CASE calculations returns different values between Discoverer Plus and Discoverer Desktop 10.1.2
(This post does not want to discuss how aggregation works, it assume that you already knows aggregation behavior, but it gives you solution to most known scenarios)
From Discoverer 9.0.2 and above i.e Discoverer 9.0.4 and Discoverer 10.1.2 has enhanced roll-up and calculation capabilities a feature called Enhanced Data Aggregation where when connected to a 9.0.1.4 or higher database, the Discoverer server will rely on the CUBE, ROLLUP and GROUPING SET database functionality to compute roll-up values. This new capability correctly handles many of the restrictions described in the provided note, so that a roll-up value can be computed where the 4i server would have to resort to using the "N.A" string.
Additionally, enhanced Data Aggregation is present only in the Discoverer Middle-tier(Plus and Viewer) server -- Not Discoverer Desktop. You will see aggregations in Plus and Viewer that show up as "N/A" in Desktop.
By the way, we can define:
Linear Calculation
A calculation for which the aggregate value is same as the value obtained by summing up the individual values is a linear calculation.
Locally computable Calculation
Discoverer can locally compute all primitive arithmetic operations (+, -, *, / etc), a subset of aggregates (Sum, Sum_Squares, Count, Max, Min, Average and Variance), some string operators, and Decode. Discoverer can locally compute most calculations that can be broken down into linear parts and that involve the above set of operators.
Calculations such as Sum (Profit) / Count (Profit) can be locally computed as it can be broken down into two Linear Parts Sum (Profit) and Count (Profit) combined by the division operator. Calculations such as Sum (Profit) / Count_Distinct (Profit) cannot be locally computed as one of the parts (Count_distinct (Profit)) is not Linear.
Non-Aggregable Calculation
A pre Oracle 9i database allows data to be fetched at only one level of aggregation. From this data discoverer has to compute values at higher levels of aggregation to support things like totals, outline values etc. This is possible only if the calculation is either Linear or Locally computable.
If a calculation is neither linear nor locally computable it will be marked as non-aggregable. The value specified in the option “Show non aggregable values as “ is displayed for such cells.
Case / Decode
Totals on aggregate calculations involving a case expression might yield non-intuitive results. This is because the item values involved in the condition part of the case expression will become NULL when they are rolled up on that item.
In most of cases the solution is to add in the PC Client where Discoverer Desktop is installed regedit entries, like:
o Add two DWORD values named: AggregationBehavior & AllowAggregationOverRepeatedValues = 1 under the key: HKEY_CURRENT_USER\Software\ORACLE\Discoverer 10\Application\ and set both to 1;
Then, Discoverer Desktop should be closed and re-launched.
From Discoverer 9.0.2 and above i.e Discoverer 9.0.4 and Discoverer 10.1.2 has enhanced roll-up and calculation capabilities a feature called Enhanced Data Aggregation where when connected to a 9.0.1.4 or higher database, the Discoverer server will rely on the CUBE, ROLLUP and GROUPING SET database functionality to compute roll-up values. This new capability correctly handles many of the restrictions described in the provided note, so that a roll-up value can be computed where the 4i server would have to resort to using the "N.A" string.
Additionally, enhanced Data Aggregation is present only in the Discoverer Middle-tier(Plus and Viewer) server -- Not Discoverer Desktop. You will see aggregations in Plus and Viewer that show up as "N/A" in Desktop.
By the way, we can define:
Linear Calculation
A calculation for which the aggregate value is same as the value obtained by summing up the individual values is a linear calculation.
Locally computable Calculation
Discoverer can locally compute all primitive arithmetic operations (+, -, *, / etc), a subset of aggregates (Sum, Sum_Squares, Count, Max, Min, Average and Variance), some string operators, and Decode. Discoverer can locally compute most calculations that can be broken down into linear parts and that involve the above set of operators.
Calculations such as Sum (Profit) / Count (Profit) can be locally computed as it can be broken down into two Linear Parts Sum (Profit) and Count (Profit) combined by the division operator. Calculations such as Sum (Profit) / Count_Distinct (Profit) cannot be locally computed as one of the parts (Count_distinct (Profit)) is not Linear.
Non-Aggregable Calculation
A pre Oracle 9i database allows data to be fetched at only one level of aggregation. From this data discoverer has to compute values at higher levels of aggregation to support things like totals, outline values etc. This is possible only if the calculation is either Linear or Locally computable.
If a calculation is neither linear nor locally computable it will be marked as non-aggregable. The value specified in the option “Show non aggregable values as “ is displayed for such cells.
Case / Decode
Totals on aggregate calculations involving a case expression might yield non-intuitive results. This is because the item values involved in the condition part of the case expression will become NULL when they are rolled up on that item.
In most of cases the solution is to add in the PC Client where Discoverer Desktop is installed regedit entries, like:
o Add two DWORD values named: AggregationBehavior & AllowAggregationOverRepeatedValues = 1 under the key: HKEY_CURRENT_USER\Software\ORACLE\Discoverer 10\Application\ and set both to 1;
Then, Discoverer Desktop should be closed and re-launched.
Monday 2 March 2009
How To Specify The Owner Of Workbooks On The Viewer Url Parameters ?
On Discoverer Viewer/Plus 9.0.4 it was possible to specify the Owner and Workbook Name on the same url parameter :
wb=. In 10.1.2 this URL parameter is not getting the Owner anymore, just the workbook name.
This is because, the Discoverer Viewer is not checking whether owner name is prefixed to the workbook.
The fix has introduced a new URL parameter "wbkowner" to specify the Owner's name explicitly.
Hence if the owner needs to be specified then instead of prefixing the owner's name to workbook name the user needs to append new URL parameter "wbkowner=Owner's name".
Since, the "." a is valid character for workbook name, the Discoverer Viewer 10.1.2 wouldn't be able to differentiate whether the string of characters before "." in workbook name is owner's name string or substring of workbookname.
The fix is available on top from 10.1.2.3 + Cumulative Patch 2 onwards, where the parameter "wbkowner" can be used on the URL for Discoverer Viewer.
Discoverer Plus users should wait for internal bug to be fixed.
Wednesday 25 February 2009
How to Change the Alignment of Page Item in HTML Exported Report Arabic Workbooks From Left To Right
Incredible grow in IT area has been observed in Arabic countries. That's why I've decided to post this info:
Exporting Workbooks from Discoverer Viewer with Arabic Locale in "Hyper-Text Markup
Language)archived)(*.zip)" format, does not align Page Items with the rest of the report. Indeed,
the body of the report (datapoints) are aligned to the right, page items instead are shown on the
left. How to change alignment for Page Items ?
Open the workbook with Discoverer Plus, highlight the Page Items then right click and select
"Format Heading". When dialog window shows, select right alignment icon and save the workbook.
Exporting Workbooks from Discoverer Viewer with Arabic Locale in "Hyper-Text Markup
Language)archived)(*.zip)" format, does not align Page Items with the rest of the report. Indeed,
the body of the report (datapoints) are aligned to the right, page items instead are shown on the
left. How to change alignment for Page Items ?
Open the workbook with Discoverer Plus, highlight the Page Items then right click and select
"Format Heading". When dialog window shows, select right alignment icon and save the workbook.
Wednesday 28 January 2009
Download Disco5i.jar File with HTTP Protocol While Discoverer Plus is Configured to Work with HTTPS
Recently I've been requested, due to a configuration restrictions with firewalls, whether it is possible to use Discoverer Plus with SSL (https) and yet have the disco5i.jar download occur through http protocol ? The answer is yes, let me show you how :
Pre 10.1.2.2 + CP7 (and 10.1.2.1.0)
Pre 10.1.2.2 + CP7 (and 10.1.2.1.0)
- Modify the $ORACLE_HOME/discoverer/config/configuration.xml file.
- Change the 'disco_archive' parameter (for relational) to point to the disco5i.jarjar file via an
HTTPS URL and 'd4o_double' (for Discoverer for OLAP applet)
Example:
FROM:plugin_setup="https://machine.us.oracle.com:443/jpi/j2re.exe" version="1.4.2" versionie="1,4,2,mn"
type="application/x-java-applet"
plugin_page="http://java.sun.com/products/archive/j2se/1.4.2_04/index.html"
disco_archive="disco5i.jarjar" d4o_archive="d4o_double.jarjar"/>
TO:plugin_setup="https://machine.us.oracle.com:443/jpi/j2re.exe" version="1.4.2" versionie="1,4,2,mn"
type="application/x-java-applet"
plugin_page="http://java.sun.com/products/archive/j2se/1.4.2_04/index.html"
disco_archive="http://machine.domain/disco/plus_files/disco5i.jarjar"
d4o_archive="http://machine.domain/disco/plus_files/d4o_double.jarjar"/>
For environments that include iAS/Discoverer 10.1.2.2 with Cumulative Patch 7 or 8 (CP7 or CP8) and/or iAS/Discoverer 10.1.2.3
- Modify the $ORACLE_HOME/discoverer/config/configuration.xml file jar parameters as shown:
disco_archive=" http://machine.domain/discoverer_disco/plus_files/disco5i.jarjar"
d4o_archive=" http://machine.domain/discoverer_disco/plus_files/d4o_double.jarjar" - This change will force the disco5i.jarjar to downloaded using the HTTP protocol.
Subscribe to:
Posts (Atom)
