Integrating Oracle eBS and Oracle BI EE – Links

The setup for Integrating Oracle eBS and Oracle BI EE is complete. Now we can create links between the two. These links should make it possible to navigate from Oracle eBS to Oracle BI EE and vice versa. Both in context.

For additional details I would refer to the following document on My Oracle Support about Integrating Oracle Business Intelligence Applications with Oracle E-Business Suite.

First thing I want to look at is how to setup the navigation from Oracle eBS to Oracle BI EE. A standard Oracle eBS installation already contains some initial setup. How this is organized could be retrieved using the following query;

SELECT   fa.application_short_name
, fa.application_name
, fda.application_short_name data_application_short_name
, fda.application_name data_application_name
, fdg.data_group_name
, frt.responsibility_key
, frt.responsibility_name
, frt.description responsibility_description
, fff.function_name
, fff.description function_description
, fff.user_function_name
, fm.menu_name
, fm.user_menu_name
, fm.description menu_description
FROM apps.fnd_menus_vl fm
, apps.fnd_form_functions_vl fff
, apps.fnd_menu_entries_vl fme
, apps.fnd_responsibility_vl frt
, apps.fnd_application_vl fa
, apps.fnd_application_vl fda
, applsys.fnd_data_groups fdg
WHERE fm.menu_id = fme.menu_id
AND frt.menu_id(+) = fm.menu_id
AND frt.responsibility_key LIKE '%OBIEE%'
AND fme.function_id(+) = fff.function_id
AND fa.application_id = frt.application_id
AND fda.application_id = frt.data_group_application_id
AND fdg.data_group_id = frt.data_group_id
ORDER BY frt.responsibility_key

There are a few different ways to navigate to Oracle BI EE. As you could have seen in the pervious query, navigation to Oracle BI EE is controlled via functions in menus. The most easy way to navigate is to go directly to your default Oracle BI Dashboard or to the homepage for Oracle BI Answers.

The following query shows an example of the settings for this default navigation;

SELECT fff.function_name
, fff.user_function_name
, fff.description
, fff.type function_type_code
, fft.meaning function_type
, fff.web_host_name
, fff.web_html_call
FROM apps.fnd_form_functions_vl fff
, fnd_lookups fft
WHERE fft.lookup_code = fff.type
AND fff.function_name like '%OBIEE%F'
AND fft.lookup_type = 'FORM_FUNCTION_TYPE'

The ‘web_html_call’ can be adjusted according to your own specific needs. It’s good to have a good understanding of URL Parameters in Oracle BI EE. Venkat has written about this subject a while back. Check his spreadsheet on Google for more details.

Navigating to the default is controlled by the following entries:

  • OracleOasis.jsp?mode=OBIEE&function=Dashboard
  • OracleOasis.jsp?mode=OBIEE&function=Answers

If you want to navigate to a specific Dashboard(-Page) or a specific Answers Subject Area or report, you should extend the default navigation links to look something like the following:

** Dashboard

  • OracleOasis.jsp?mode=OBIEE&function=Dashboard&parameters=PortalPath~/shared/[Folder_Name]/_portal/[Dashboard_Name] –> This will navigate to a specific Oracle BI Dashboard
  • OracleOasis.jsp?mode=OBIEE&function=Dashboard&parameters=PortalPath~/shared/[Folder_Name]/_portal/[Dashboard_Name]%26Page=[Page Name] –> This will navigate to a specific Oracle BI Dashboard Page

The ‘%26’ is used instead of the ‘&’-sign. Make sure you replace all the spaces with ‘%20’. It’s even better trying to avoid using spaces.

I used a site created by Brian Wilson for the Url-encoding. could also be used as an HTML URL Encoding Reference.

** Answers

  • OracleOasis.jsp?mode=OBIEE&function=Answers&parameters=SubjectArea~[Subject Area Name] –> This will navigate to the Subject Area of your choice
  • OracleOasis.jsp?mode=OBIEE&function=Go&parameters=Path~/shared/[Folder_Name]/[Report_Name] –> This will directly navigate to the Answer of choice (Make sure you replace all the spaces with ‘%20’)

If you go back to the previously mentioned spreadsheet it’s good to take the following into account;

  • ‘function=’ –> is followed by one of the entries in the ‘Command’-column
  • ‘parameters=’–> contains the full url, including ‘Parameters’ if needed
  • after the ‘~’-sign comes the full url to the location of choice

Next in line could be investigating the possibilities of navigating from a Oracle eBS Form directly to an Oracle BI Dashboard including prompts. This way we could navigate ‘in context’.

Oracle MOS HTML Update 01082010

 Document ID   Title   Doc Type   Modified Date 
1154313.1 Is It Possible to Perform A Silent Install Of BIEE Office Plugin (Client Side) HOWTO 30-JUL-10
1121844.1 Client Software Compatibility With Obiee and JDK 64 bit (Java 64-bit) HOWTO 25-JUL-10
942852.1 VMWare Certification for Oracle Products HOWTO 29-JUL-10
1162977.1 Formatting Seen In BIEE Pivot Table Requests Not the Same After Download To Excel 2003/2007 PROBLEM 29-JUL-10
1159334.1 Ibots Do Not Send Emails To All Recipients – Invalid Subscribers Skipped Message In Ibot Log PROBLEM 26-JUL-10
864044.1 ibots Delivering Empty pdf Files When Sent to More than One Recipient – Found Undetermined AttrKey: desiredWidth – Found duplicate AttrKey desiredWidth RETHINK HASHING ALG PROBLEM 29-JUL-10
1118868.1 Newly created Ibot does not get triggered by WebServices PROBLEM 25-JUL-10
738678.1 More information about the queueing of iBot jobs is required. HOWTO 28-JUL-10
1156073.1 Customise the sort order of Dashboards names which are coming under single Dashboard link (Dashboard Group) HOWTO 29-JUL-10
1162776.1 How To Seamlessly Pass Userid And Password From Excel to BIOffice Plugin and Presentation Server HOWTO 30-JUL-10
1154623.1 How To place report the centre while navigating from one report HOWTO 29-JUL-10
1162354.1 Add Modify, Print, Download Links To Report By Default HOWTO 29-JUL-10
1162373.1 How to remove the ‘Download to Excel 2000’ option from Dashboard report link HOWTO 29-JUL-10
1159721.1 Answers Requests Are Failing With Essbase Cubes PROBLEM 26-JUL-10
1160287.1 Obiee Presentation Layer Column Renaming HOWTO 27-JUL-10
480516.1 Proxy Users PROBLEM 26-JUL-10
1119434.1 Where Does the Saved Selection Stored at the BIEE Catalog HOWTO 25-JUL-10
1142574.1 How To Configure A Default OBIEE Dashboard Or A Welcome Page For All Users Using PORTALPATH Variable HOWTO 29-JUL-10
733137.1 Oracle EBS integration with OBIEE PROBLEM 27-JUL-10
523072.1 Evaluate Function in OBIEE PROBLEM 27-JUL-10
1148013.1 Is OBIEE certified against Red Hat Enterprise Linux 5 Update 4 64bit HOWTO 29-JUL-10

Integrating Oracle eBS and Oracle BI EE – Getting started

I have been playing around with Oracle eBS Release 12 and Oracle BI EE 10g. 

In Release 12 the integration is very straight forward. If you would like to achieve the same functionality in Release 11, you need to do some patching first.

Lucky enough for me I am able to use an Release 12 environment.

select release_name
from   applsys.fnd_product_groups

All I had to do was set the ‘FND: Oracle Business Intelligence Suite EE base URL’-profile in Oracle eBS.

This would be something like; http://[hostname.domain_name]:[port_number]


select fnd_profile.value('FND_OBIEE_URL') oracle_bi_url
from   dual

Now Oracle eBS nows where to find Oracle BI.

Secondly I had to change the way Oracle BI authenticates in the instanceconfig.xml file. This way you can login to Oracle BI via Oracle eBS. You use your Oracle eBS credentials to login to Oracle BI directly.

   <ExternalLogon enabled="true">
      <Param name="NQ_SESSION.ACF"

Last but not least, you change your connection settings in the Repository of Oracle BI and you are good to go.

If you would like to see how the integration between Oracle eBS and Oracle BI works, I can recommend a blogpost of Gerard Braat.

Robin Moffat has written a good explanation about how to validate EBS-BI authentication, without BI.

Oracle MOS HTML Update 25072010

 Document ID   Title   Doc Type   Modified Date 
1152844.1 OBIA – Which Ports Is OBI Applications / DAC Using ? HOWTO 19-JUL-10
1155234.1 Obiee Cannot Connect To Bi Server On Port 9703 On Linux PROBLEM 21-JUL-10
1157613.1 Is Biee Support Ie8? HOWTO 23-JUL-10
757015.1 You do not have the permission to set the value of the variable ‘USER’. (08004) PROBLEM 19-JUL-10
1154196.1 Subject Line Of Delivered Content Email Matches Ibot Name Instead Of Headline PROBLEM 20-JUL-10
1066299.1 JAVA.LANG.UNSATISFIEDLINKERROR Error Running Net Configuration Assistant PROBLEM 19-JUL-10
578393.1 Saving Ibots: [nQSError: 68019] Authentication Failed PROBLEM 20-JUL-10
1153145.1 Refresh Problem With Obiee And IE PROBLEM 19-JUL-10
1155904.1 Arabic Charts In Obiee. Data Shown In Reversed Order & Not From Right To Left PROBLEM 21-JUL-10
1158383.1 Option to open Pivot Table Designer instead of Criteria tab HOWTO 24-JUL-10
1155855.1 Obi : Delete Link ‘Rafraichir’ (Refresh) When No Data Found HOWTO 21-JUL-10
751806.1 Guided Navigations show a blank area instead of the report PROBLEM 19-JUL-10
1155503.1 Date Format In Filter View Is Wrong When Using Dashboard Prompt After Applying Date Prompt Fix Patch:9492821 HOWTO 21-JUL-10
1154429.1 A brief explanation of tag Javahost config FAQ 20-JUL-10
1156912.1 Oracle Epm System Workspace Error When Obiee 10g Is intergrated With Hyperion Workspace PROBLEM 22-JUL-10
735249.1 Understanding Memory Management in Oracle OBI EE (Siebel Analytics) ANNOUNCEMENT 18-JUL-10
1152623.1 OBIEE Prompts Login Page While Navigating From BI Publisher to Dashboard PROBLEM 18-JUL-10
501649.1 BI Publisher login issue — PROBLEM 19-JUL-10
522029.1 How to reset the OC4J Administrator account password HOWTO 19-JUL-10
1090323.1 ‘Procurement & Spend Invoice Lines’ Subject Area has no join between ‘GL Segments’ and ‘W_AP INV_DIST_F’ Tables and no data is returned PROBLEM 21-JUL-10
1155464.1 Problem In Restarting Bi Presentation Server HOWTO 21-JUL-10
1158159.1 Charts as Images for iPhone HOWTO 23-JUL-10
563695.1 ORA-00942 on queries with Full Outer Join and WITH Clause on Oracle RDBMS PROBLEM 20-JUL-10
1152477.1 How To Enable ActAs functionality in SSO environment HOWTO 18-JUL-10
946076.1 How can I setup HP LoadRunner to test the performance of OBI EE FAQ 18-JUL-10

Oracle MOS HTML Update 18072010

 Document ID   Title   Doc Type   Modified Date 
1148781.1 Errors during the start up of OBI Server PROBLEM 13-JUL-10
870113.1 After Upgrade to OBIEE Get Error A Numeric Value Was Expected Error Codes: EHWH2A7E on Every Filter or Drill Over Numeric Values PROBLEM 12-JUL-10
864044.1 ibots Delivering Empty pdf Files When Sent to More than One Recipient – Found Undetermined AttrKey: desiredWidth – Found duplicate AttrKey desiredWidth RETHINK HASHING ALG PROBLEM 13-JUL-10
1140783.1 What Is The Difference Between Personalized And Not Personalized iBots HOWTO 13-JUL-10
1146823.1 Drill Down Not Working On Dimension Hierarchies HOWTO 11-JUL-10
1147424.1 Results Are Incorrect When Using A Case Statement In Answers PROBLEM 12-JUL-10
1148748.1 OBIEE Answers Connection Error: A general error has occurred. cannot open shared object file PROBLEM 13-JUL-10
521759.1 OBIEE server crash PROBLEM 15-JUL-10
743555.1 ThreadPool: Worker thread job errored out. HOWTO 16-JUL-10
1147734.1 Certain requests sent to the Teradata database are cancelled. PROBLEM 12-JUL-10
1149093.1 Analyzing Active Users Logged into the OBI System by IP Address / User / Time HOWTO 14-JUL-10
1151374.1 In OBI EE 10.x the ‘PortalPageNav’ Navigation Function navigates to the wrong Web Catalog Path when a trailing Forward Slash is used at the end of the Path to the Dashboard Argument PROBLEM 16-JUL-10
1149048.1 Notification for processing Briefing Books HOWTO 14-JUL-10
480511.1 Usage Tracking and User filter PROBLEM 16-JUL-10
1148819.1 OBI and Oracle Ebusiness Suite Integration (EBS) integration fails PROBLEM 13-JUL-10
1151924.1 With Clause Performance Issue HOWTO 16-JUL-10
605031.1 OBIEE – Would like improved import of changes to Analysis Services 2005 cubes HOWTO 12-JUL-10
1146033.1 Setting Default Date Format in Filters to DD/MM/YYYY rather than MM/DD/YYYY, for One or All Users HOWTO 15-JUL-10
820803.1 Admintool Check-in changes: Internal Assertion Error Condition FALSE , file server/Utility/Generci/NQThreads/SUGThread.cpp, line 515 PROBLEM 15-JUL-10
1150824.1 Unable to start Presentation Server PROBLEM 15-JUL-10
1149047.1 How to improve Briefing Books Performance HOWTO 14-JUL-10
1150624.1 Applying Patch 9577789 Generates Incorrect Mdx For The Prompt Filter HOWTO 15-JUL-10
477910.1 GoNav, PortalNav, and PortalPageNav Navigation in Siebel Business Analytics Versions 7.x BULLETIN 16-JUL-10
1147493.1 Nqserror: 59044 unable to create table when Trying To Load The Campaigns PROBLEM 12-JUL-10
1147454.1 Localize Presentation Catalog Captions Containing Presentation variables PROBLEM 12-JUL-10
483293.1 Client Connection Timeouts PROBLEM 15-JUL-10

Stay in touch with Oracle BI EE

Yesterday I had a conversation with one of my colleagues about 3 of the biggest Business Intelligence tools around. Next to IBM Cognos and SAP Business Objects, we were of course talking about the Oracle BI Suite.

The success of the Oracle BI Suite is for a great deal influenced by the active Oracle BI community. That’s one of the reasons I started the Interview series on this blog, to get a picture of this community. Next to that Oracle itself is very open, when it comes to downloading and trying their software. Oracle OTN and Oracle eDelivery belong to my favorite sites.

With the coming release of Oracle BI 11g, there will be a lot of knowledge to gain and to share. I am confident that the Oracle BI Community will become very active when then next Oracle BI 11g is generally available.

If you want to get information about the Oracle BI products, where will you start looking? Alex has done a great effort to get all active Oracle BI bloggers in one central place; In his blogroll. He has one condition: You have to be active. So my advise will be: Keep on sharing!

For those of you who want to make a fresh start with Oracle BI EE, please check John’s blog. He has an up-to-date page with all the details on; How to start with Oracle BI EE.

Let us all keep the Oracle BI Community alive and kickin’.

Oracle MOS HTML Update 11072010

 Document ID   Title   Doc Type   Modified Date 
1142393.1 OC4J does not work with OBIEE on AIX OS with JDK 1.6 HOWTO 06-JUL-10
860112.1 Error Code: “Q4NU7XSN” Occurs After Upgrading OBIEE PROBLEM 07-JUL-10
960683.1 Catalog Manager Fails to Open Catalog in Offline Mode PROBLEM 08-JUL-10
867202.1 “VIEW DISPLAY ERROR INVALID HANDLE OF ‘CLASS SAW::CURRENCY CONST *’ Q4NU7XSN” Error Occurs in Sales Analytics Dashboard PROBLEM 07-JUL-10
946015.1 “java.lang.NoClassDefFoundError” Occurs While Installing OBIEE PROBLEM 07-JUL-10
530173.1 Direct Database Requests now working with iBots PROBLEM 05-JUL-10
954963.1 NullPointerException When Running Custom Java Program PROBLEM 08-JUL-10
884142.1 “There was a problem retrieving the XML data:Internal Server Error” Error Occurs While Accessing Answers PROBLEM 07-JUL-10
950273.1 Sort Icon in Answers is not Displayed Correctly With Fifth Sort. PROBLEM 08-JUL-10
1142778.1 How do “Max Rows” or “Filter” apply to queries when User is in Multiple Security Groups HOWTO 06-JUL-10
1143758.1 How to Hide the Filter Parameter When Some Reports In A Dashboard Don?t Return Data HOWTO 07-JUL-10
1145155.1 No Data In Request “Supplier Dpo Trend” In “Procurement And Spend/Supplier Performance/Overview” PROBLEM 08-JUL-10
866073.1 Modifying “VIEWS.CSS .PTSECTSTABLE” Padding 0PX Does Not have Any Impact. PROBLEM 07-JUL-10
513287.1 Error when accessing dashboard PROBLEM 05-JUL-10
857912.1 “No results” Message Displayed When User Drills Down on Double-byte Character in Chart PROBLEM 07-JUL-10
955844.1 How to Obtain Values of Presentation or Request Varaible in Answers or Dashboard . HOWTO 08-JUL-10
529885.1 Drilldown Error PROBLEM 05-JUL-10
950265.1 NLS Excel File Name and Sheet Name are Corrupted in Non-English Windows Using IE6 PROBLEM 08-JUL-10
958311.1 Drill Down Behavior After Applying the Patch 6633505 FAQ 08-JUL-10
882203.1 Data Format’s Default Setting HOWTO 07-JUL-10
868363.1 Performance Issue in IE6 While Using Multi-Select Feature in Dashboard PROBLEM 07-JUL-10
889116.1 Unable to Edit Caption Property for “Link or Image” Dashboard Object PROBLEM 07-JUL-10
859386.1 Width Column Property Does Not Work PROBLEM 07-JUL-10
967069.1 How to Hide Dashboards Tab in Answers HOWTO 08-JUL-10
1145846.1 How Can I Rename Report Views In A Report In Answers? HOWTO 09-JUL-10
962812.1 How to Enable SSL on Standard Installed OBIEE and Publisher Deployed to Standalone OC4J HOWTO 08-JUL-10