DAC Error Message – Error while executing : INFORMATICA TASK:SILOS:@DAC_SIL_PositionDimension_FULL_TD_CMD

Thanks to Frank Davis, I was able to solve the following error; ‘Error while executing : INFORMATICA TASK:SILOS:@DAC_SIL_PositionDimension_FULL_TD_CMD’

A patch (Patch 12968641: DAC CUMULATIVE PATCH FOR BI APPS – SEP 2011) for this error can be downloaded here.

Automatic login to Oracle BI via Oracle eBS

I have been blogging about the integration between Oracle eBS and Oracle BI. It’s possible to login to Oracle BI via Oracle eBS. In these situations it’s necessarily to have an account and a responsibility in Oracle eBS.

Some people make use of Oracle BI and not Oracle eBS. Normally the wouldn’t need an Oracle eBS account.

In cases of an integration between the two systems you have to login to Oracle eBS, select a responsibility and select a link to Oracle BI.

For some people these are to many steps to get to Oracle BI.

It’s possible to set a default start page when you login to Oracle eBS. You could eg. choose ‘Oracle BI Answers’ as your default start page.

This could be set per user via the Preferences of the user who has logged in.

Here you could set the start page

Be careful setting this option, because once you set it, you wont be able to return to Oracle eBS. In such a case when a user wants to get rid of this startpage use the Oracle eBS Profile Option; ‘Applications Start page’. This option could be set for a specific user or a responsibility via the ‘System Administrator’-responsibility.

Via ‘Profile’, ‘System’, you can find the specific ‘System Profile Value’

For the user; ‘DBAKBOORD’ the value of this profile is;

This value can be retrieved from the database via the following query;

select fpov.level_value user_id

, fu.user_name

, fpov.profile_option_value

from applsys.fnd_profile_option_values fpov

, apps.fnd_profile_options_vl fpo

, applsys.fnd_user fu

where fpo.profile_option_id = fpov.profile_option_id

and fpo.application_id = fpov.application_id

and fu.user_id = fpov.level_value

and fpo.profile_option_name = 'APPLICATIONS_START_PAGE'

and fpov.level_id = 10004

The output of this query is the following;

Using the Oracle eBS Profile Option; ‘Applications Start page’ could also be an easy solution to set a startpage for a whole group of user. After setting this option they all will login to Oracle BI Answers directly without selecting the menuoption in Oracle eBS first.

WEBGROUPS in Oracle BI 10g

While a lot of us are already focusing on Oracle BI 11g, I still also have to service some 10g clients. This one is a Oracle BIA 7.9.6 client with hundreds of responsibilities in Oracle eBS with access to Oracle BI. I used UDML to script the responsibilities into the repository. Via de weblog of KPI Partners I found the solution to assign the responsibilities to a Web Group.

Oracle eBS will be used to store the membership information. A group (responsibility) in the Oracle BI Repository belongs to a organization and a role.  This information can be retrieved from the responsibility in Oracle eBS. The organization is stored via a profile option (‘PER_SECURITY_PROFILE_ID’). In this case we specified an additional custom profile option to store the role (User, Superuser, Reportbuilder, etc.).  Via an Initialization Block the WEBGROUP-variable can be initialized.

Note: Do not forget to uncheck the ‘Use caching’ checkbox, to make sure that you retrieve the current values.

The only thing what’s left to do mis to create the roles and organizations as Webgroups in the Presentation Catalog.

Instead of making all the Oracle eBS responsibilities separately available in the Presentation Catalog, you’ll only have to define the Webgroups. In this case it was a difference of hundreds compared to about 10 to 15.

This could make your life a little bit easier.

Logical Sql in a GO Url

There are a lot of good posts on how to us the GO Url functionlity in Oracle BI EE. One thing which isn’t covered very often is the use of ‘OR’ in a prompted-GO Url.

If you want to filter sales <= 5000 or sales >= 15000 it seems not possible via the ‘standard’ GO Url structure. An alternative is using Logical Sql in the GO Url;


In this case ‘SupplierSales’ is the Subject Area.

It is possible to add a where-clause to the select statement. I created the following example:

The first link contains a Go URL with Logical Sql.

saw.dll?Go&SQL=SELECT+"Dim Time"."Calendar Month", "Fact Sales Amounts"."Sales Amount"+FROM+Sales

The result of this link;

The second link has a Go URL also, but this one includes Logical Sql with a where-clause.

saw.dll?Go&SQL=SELECT+"Dim Time"."Calendar Month", "Fact Sales Amounts"."Sales Amount"+FROM+Sales+WHERE+"Fact Sales Amounts"."Sales Amount"+<=+5000+OR+"Fact Sales Amounts"."Sales Amount"+>=+15000

The result of this second link;

Unfortunately these forms of the Go URL return tabular results only.

Connect to the Database from Oracle BI 11g

I ran into the following issue when I upgraded from Oracle BI 10g to Oracle BI 11g. Although the upgrade went smoothly, all of a sudden I was not able to reach the database anymore. After doing some research on the internet I stumbled upon the following thread on OTN.

The main difference between Oracle BI 10g and Oracle BI 11g is the fact that Oracle BI 11g has it’s own Oracle_Home.

Thanks to Dirk and Venkat for the possible solutions:


navigate to; {ORACLE_INSTANCE}\bifoundation\OracleBIApplication\coreapplication\setup,

alter user.cmd or user.sh depending on your OS,

set your TNS_ADMIN location to point to; {MIDDLEWARE_HOME}\Oracle_BI1\network\admin


Copy your tnsnames.ora to {MIDDLEWARE_HOME}\Oracle_BI1\network\admin directory


Use the full expanded tnsnames as shown below and use this one as the datasource name in your Connection Pool:

(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = host_name/ip_address)(PORT = port_number))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = service_name)))