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 10.1.3.4.1 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;

saw.dll?Go&SQL=select+Region,Dollars+from+SupplierSales

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:

1.

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

2.

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

3.

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)))

Oracle BI Applications – Security

I recently had to digg into the standard Oracle BI Applications Security Oracle delivers out of the box. The clients had two security requirements.

The first one was a Data Security requirement. When a user logs in he is presented with his / her organization’s data only. So a user from organization ‘001’  only sees data from organization ‘001’ . Organization user ‘002’ only sees organization ‘002’ and so on.

The second requirement was Object Security. Each function has access to a group of objects, regardless of their organization. So all ‘General Ledger Super Users’ have access to the same objects whether they are in organization ‘001’ or ‘002’

 

The client has the following installation:

  • Oracle eBS R12 (12.1.1)
  • Oracle BI Apps  (7.9.6)
  • Oracle BI EE (10.1.3.4.1)

In general the standard Oracle BI Applications security solution is built around;

  • Groups (Repository, Web Catalog)
  • Session Variables
  • Business Model Filters
  • Permissions
  • Priviliges

In more detail the following steps have to be performed:

Set the application context

The Oracle BI Applications session should get the same security context as Oracle eBS, where you navigate from.

 During logon the ‘EBS Security Context’-Initialization Block is called and executed. The Oracle eBS session cookie is used to set the context. The Initialization block ‘fills’ the variables with information about which user / responsibility combination is logged on. These variable will be used in other Initialiation Blocks along the road.

call APP_SESSION.validate_icx_session('valueof(NQ_SESSION.ICX_SESSION_COOKIE)')

 If all goes well, the Oracle BI Apps session will get the same context as Oracle eBS. Otherwisse there are 3 options :

  • SESSION_DOES_NOT_EXIST,
  • SESSION_NOT_VALID,
  • SESSION_EXPIRED.

Repository Groups

There are two important Groups;

  • Responsibility Groups (Should the Responsibilities in Oracle eBS)
  • Security Groups (These will be used for the Data Security)  
    • –> Examples:
    • Ledger-based Security
    • Inventory Org-based Security
    • Operating Unit Org-based Security

 Variables

The security group someone belongs to is detemined by session variables, which are set during logon.

Initialization Blocks and Variables are the necessary objects to examine. If we relate to the example in the previous section, we could state that the following three Initialization Blocks are most important;

  • Ledgers
  • Inventory Organizations
  • Operating Unit Organizations
  •  

    Data Security

    Data Security is being set up via, Security Groups and Business Model Filters.

     

    As from now on, each query which is composited with a table linked to a Security Group a “Where-clause” is added.

    Presentation Catalog Groups

    The groups as they are created in the repository should also be created in the Web Catalog.

     

     Object Security

    You can use the Catalog Groups to grant or revoke acces to certain objects (Folders, Answers, Dashboards, etc) in the Web Catalog. The same groups an be used to mange the privilliges within the Web Catalog. Use the Security Groups in the Repository to control the Access to the Subject Area’s in the Presentation Layer.

    Check my previous post about navigating from Oracle eBS to Oracle BI EE.

    Oracle Business Intelligence Enterprise Edition (OBIEE) Product Information Center (PIC)

    For those of you interested in Oracle BI EE and with access to My Oracle Support; The Oracle Business Intelligence Enterprise Edition (OBIEE) Product Information Center (PIC). Click here for more details (docId 1267009.1).