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. W3Schools.com 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’.

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]

statement:

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.

   <Auth>
   <ExternalLogon enabled="true">
      <ParamList>
      <Param name="NQ_SESSION.ICX_SESSION_COOKIE"
         source="cookie"
         nameInSource="VIS"/>
      <Param name="NQ_SESSION.ACF"
         source="url"
         nameInSource="acf"/>
      </ParamList>
   </ExternalLogon>
   </Auth>  

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.