Getting Started With Oracle Fusion Application Business Intelligence

Getting Started With Oracle Fusion Application Business Intelligence

About Report Customization
Reports extract the data from Business applications and present it in the formats required for the organization. Reports provide the information needed for internal operations and statutory compliance; reports also provide the business documents for communicating with customers. Many product-specific reports are provided with Oracle Fusion Applications; for example, the invoice register, the pick slip report, the payroll summary, the journals report, and the customer credit memo. To meet the specific needs of the enterprise, we may need to customize the reports provided or we may need to create new reports to capture and present different data.
Understanding the BI Publisher reporting architecture will help us to understand the report customization scenarios and tasks. A report in Oracle BI Publisher consists of the following components:
A data model that defines the data source, data structure, and parameters for the report. A data model can be used by multiple reports. Each report has one data model.
One or more layouts to define the presentation, formatting, and visualizations of the data. A report may have multiple layouts of the data model.
A set of properties that specify run-time and formatting options
Optionally, a report may also include:
Translations to provide localized versions of a report

What We Can Customize
In many cases, Oracle BI Publisher reports delivered with Oracle Fusion applications will contain the appropriate data elements, but may not provide the presentation of the data just as required by the business. Oracle BI Publisher enables to customize the layouts for reports leveraging the built in data models. If the reports provided by Oracle Fusion applications do not include the data we require, we can create a new report based on a custom data model.
Note:
Do not edit the predefined report objects. If you change a report and a subsequent patch includes a new version of the report, the patch overwrites any customization. If subsequent patches do not include a new version of the report, the customization are retained. When customizing reports, always make a copy of the original object and edit the copy.
Some common report customization scenarios are shown in the following table.

Customization Use Case	Described in
Edit the layout of a report provided with an application For example: Add your company logo to the Receivables Credit Memo report	"Customizing Layouts"
Add a new layout to a report provided with an application For example: Design a new form letter users can select when they run the Receivables Credit Memo	"Customizing Layouts"
Create a new report based on a data model provided with an application	"Creating Custom Reports"
Edit a data model provided with an application For example: Add a field to a data model	"Editing Existing Data Models"
Create a new data model For example: Define a new query against Oracle Fusion applications tables	"Creating a New Data Model"
Create a new report based on a custom data model	"Creating Custom Reports"

Report Customization Tasks
Depending on how a report is implemented in Oracle Fusion Applications and the type of customization you make you may also have to perform additional tasks to implement your custom report in the system.

If you create a new report and you wish to schedule this report through Oracle Enterprise Scheduler Service, you must create an Oracle Enterprise Scheduler Service job for the report. If you require Oracle Enterprise Scheduler Service to send parameter values to the Oracle BI Publisher report via a parameter view object, you must also create the view object.

If you create a custom layout and you require translations of the layout, you must also provide the translations. Oracle BI Publisher provides a tool for extracting the translation file for some layout types. The translation file can be translated into the required languages then uploaded to the report.

Tools for Customizing Reports
Customize reports either within the Oracle BI Publisher application or using one of the tools or applications.
Tools for Customizing Report Components
Report Data Model -> BI Publisher's data model editor
Report properties -> BI Publisher's report editor

Tools for Customizing Layouts
RTF template -> Microsoft Word with BI Publisher's Template Builder for Word
BI Publisher template (XPT) -> BI Publisher's layout editor
PDF template -> Adobe Acrobat Professional
Excel template -> Microsoft Excel with BI Publisher's Template Builder for Excel
eText Template -> Microsoft Word

Before You Begin Customizing Reports
Familiarize with the Oracle Fusion application architecture that enables customization, as described in Chapter 1, "Customizing and Extending Oracle Fusion Applications.". Also understand the typical workflows for working with runtime customizations, as described in Chapter 2, "Understanding the Customization Development Lifecycle."

In addition, be familiar with the following Oracle BI Publisher-specific requirements:
Ensure that you have proper permissions for editing and creating Oracle Business Intelligence Publisher objects
To create or edit reports and report layouts requires the BIAuthor Role (or a role that includes the BIAuthor Role) as well as write permissions on the objects in the catalog to be edited.
To create or edit data models requires a custom role that includes the BIAuthor Role and the developDataModel permission (oracle.bi.publisher.developDataModel). Note that the ability to create Oracle BI Publisher data models allows the user to write and execute SQL, therefore implementors must consider carefully to whom they grant the developDataModel permission, and on which environments.
To create the custom role for editing data models, follow the guidelines in the "Configuring Roles" section in the Oracle Fusion Applications Administrator's Guide.
For more information about setting permissions in the catalog, see the "Managing Objects in the Oracle BI Presentation Catalog" chapter in the Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition (Oracle Fusion Applications Edition).

Understand how the patching process for catalog objects impacts customizations
If a patch includes an update to a catalog object that was delivered with an Oracle Fusion application (for example, the Payables Invoice Register report) the patch will overwrite any customizations applied to the original report. To avoid overwriting a customization, do not customize a predefined Oracle Fusion application object in place; create a copy of the object and customize the copy.

Understand how permissions are set for and inherited by catalog objects
For a user to view a report, the user's role must have read permissions on every object referenced by a report. Permissions can be inherited from the folder in which the object resides.
For ease of maintenance, Oracle recommends that you place customized reports within the same folder as the original; or, if creating a new report that you place it within the same folder as other reports for the same job role.
If you choose to create new folders, bear in mind the catalog security permissions required (see the "Managing Objects in the Oracle BI Presentation Catalog" chapter in the Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition (Oracle Fusion Applications Edition)).

Be aware of any application-specific guidelines for customizing reports
See application-specific documentation in the Oracle Enterprise Repository for Oracle Fusion Applications.

Be aware of property settings that determine how the report can be run and viewed
Some reports are configured to run only through an external application or through the Oracle Enterprise Scheduler. While customizing a report, you may want to configure it temporarily for viewing online to facilitate testing. See Task: Review Report Settings for Online Viewing for information about these settings.

Know how to navigate to Oracle BI Presentation Catalog objects
Navigate to the Oracle BI Presentation Catalog as follows:
On the Navigator, under Tools, click Reports and Analytics. In the Reports and Analytics pane, click Browse Catalog.

Alternatively, log in to Oracle Business Intelligence directly (example: http://host:port/analytics/saw.dll).
Oracle Fusion Applications reporting objects are organized by product family in the catalog typically as follows:
   Top Level: Shared Folders
    Product Family Folder (example: Financials)
       Product folder (example: Payables)
       --Report group folders (example: Invoices)
       -- Reports
       -- Data Model folder
      

Changing EBS Datasource APPS Password in OBIA/BIAPPS

Changing EBS Datasource APPS Password in OBIA/BIAPPS

Step 1 Drop and recreate Database Link with New APPS Password
Drop DBLINK
DROP PUBLIC DATABASE LINK "ERPPROD.WORLD@DSN_90";

Create New DBLINK
CREATE PUBLIC DATABASE LINK "ERPPROD.WORLD@DSN_90"
CONNECT TO APPS
IDENTIFIED BY newpassword
USING 'ERPPROD';

Step 2 Update EBS Datasource with new APPS Password in BI Configuration Manager.
To update the new password in source system (EBS Datasource).
Login to BI Application Configuration Manager  http://bi.mydomain.com:9704/biacm
Click on Define Business Intelligence Applications Instance

Click on Edit Button

Click Next

In the Edit Connection Details, Update new Apps Password and Click on Test

Save and Close the Window

Step 3 Update ODI EBS DataSource with new APPS Password
Login the ODI studio
Update apps password for ODI_DS Data source

Update apps password for ERPPROD Data source

Step 4 Update new APPS Password in Repository (RPD) file
Required for analytics 

Login to BI Administration Tool
Open Repository online

Select Oracle EBS OLTP in the Physical Layer

Right Click on Oracle EBS OLTP and select Properties

Click Yes for Check-Out

Select Oracle EBS OLTP InitBlock Connection Pool and Click Edit Button

Click Yes for Check-Out

Type new apps Password  and click Ok

End Dating Seeded Responsibilities

End dating seeded responsibilities that are not assigned to user, meaning no users are assigned to the responsibility

--Get List of responsibilities

SELECT fr.responsibility_id,
               fr.application_id,
               fr.data_group_application_id,
               fr.data_group_id,
               fr.menu_id,
               fr.web_host_name,
               fr.web_agent_name,
               fr.group_application_id,
               fr.request_group_id,
               fr.responsibility_key,
               frt.responsibility_name,
               frt.description,
               fr.start_date,
               fr.version
          FROM fnd_responsibility_tl frt, fnd_responsibility fr
         WHERE frt.responsibility_name in ('India Local Order Management')
               AND frt.language = 'US'
               AND fr.responsibility_id = frt.responsibility_id;
             
             
--Run Below API to end date responsibility

/* Responsibility End Date */
DECLARE
    CURSOR c1
    IS
        SELECT fr.responsibility_id,
               fr.application_id,
               fr.data_group_application_id,
               fr.data_group_id,
               fr.menu_id,
               fr.web_host_name,
               fr.web_agent_name,
               fr.group_application_id,
               fr.request_group_id,
               fr.responsibility_key,
               frt.responsibility_name,
               frt.description,
               fr.start_date,
               fr.version
          FROM fnd_responsibility_tl frt, fnd_responsibility fr
         WHERE     frt.responsibility_name IN
                       ('India Local Order Management')
               AND frt.language = 'US'
               AND fr.responsibility_id = frt.responsibility_id;
BEGIN
    FOR i IN c1
    LOOP
        BEGIN
            FND_RESPONSIBILITY_PKG.UPDATE_ROW (
                X_RESPONSIBILITY_ID           => i.responsibility_id,
                X_APPLICATION_ID              => i.application_id,
                X_WEB_HOST_NAME               => i.web_host_name,
                X_WEB_AGENT_NAME              => i.web_agent_name,
                X_DATA_GROUP_APPLICATION_ID   => i.data_group_application_id,
                X_DATA_GROUP_ID               => i.data_group_id,
                X_MENU_ID                     => i.menu_id,
                X_START_DATE                  => i.start_date,
                X_END_DATE                    => SYSDATE - 1,
                X_GROUP_APPLICATION_ID        => i.group_application_id,
                X_REQUEST_GROUP_ID            => i.request_group_id,
                X_VERSION                     => i.version,
                X_RESPONSIBILITY_KEY          => i.responsibility_key,
                X_RESPONSIBILITY_NAME         => i.responsibility_name,
                X_DESCRIPTION                 => i.description,
                X_LAST_UPDATE_DATE            => SYSDATE,
                X_LAST_UPDATED_BY             => -1,
                X_LAST_UPDATE_LOGIN           => 0);

            COMMIT;

            DBMS_OUTPUT.put_line (
                i.responsibility_name || ' has been updated !!!');
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);
        END;
    END LOOP;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
/

Verify Below Documents and take decision based on usage of applications/modules in your Organization.
What Is The Impact Of Disabling Oracle Seeded Users? (Doc ID 418767.1)
Is it Safe To End Date Some Applications Users like GUEST (Doc ID 783428.1)
End Dating Seeded Responsibilities (Doc ID 390448.1)

Configuring Active Directory as the Authentication Provider (OBIEE 11.1.1.9)

Configuring Active Directory as the Authentication Provider (OBIEE 11.1.1.9)

Purpose:

Connecting the OBIEE WebLogic LDAP server to Microsoft Active Directory, so users can log-into the dashboard using their Windows Active Directory username and password, and retrieve group membership.

Whilst OBIEE comes with the embedded WebLogic LDAP server to hold users and groups, the license for this is restricted such that you can't just move all users from other applications into the LDAP server.

If you search in the internet and Oracle docs for instructions on how to integrate OBIEE 11g with Active Directory, there are many different ways to do it with set of instructions.

A lot of this is because Active Directory is highly-configurable, and a lot depends on how much you want to replace, or just work alongside, the existing WLS LDAP server.

Our objective is to keep the WLS LDAP server and the user accounts within it and then make it possible for Active Directory users also log in and be assigned to the standard application roles that the WLS LDAP users have.

Procedure:

This procedure illustrates how to configure Oracle Business Intelligence to use Active Directory.

 Before starting the configuration, we need the following Active Director users and groups as below.

    ADbiapps, will be used as the principal that OBIEE uses to connect to the Active Directory server

    ADBISystemUser a user on Active Directory who wants to have administration rights in the Presentation Server and BI Server

Above users are organized into three groups in the AD server:

    ADBIAdministrators, analogous to the BIAdministrators group in the WLS LDAP server

    ADBIAuthors, analogous to the BIAuthors group in the WLS LDAP server

    ADBIConsumers, analogous to the BIConsumers group in the WLS LDAP server

The groups have just got those users as members, and the users are just regular AD users, including the ADBISystemUser account.

Let's go into the WebLogic Server Administration Console http://[machine_name]:7001/console) and start configuring the system for Active Directory integration.

To configure Active Directory as the Authentication Provider:

1. Log in to Oracle WebLogic Server Administration Console, and click Lock & Edit in the Change Center.

2. Select Security Realms from the left pane and click myrealm.

The default Security Realm is named myrealm.

3. Display the Providers tab, then display the Authentication sub-tab.

4. Click New to launch the Create a New Authentication Provider page.

5. Enter values in the Create a New Authentication Provider page as follows:

• Name: Enter a name for the authentication provider. For example, ADAuthenticator.
• Type: Select ActiveDirectoryAuthenticator from the list.
• Click OK to save the changes and display the authentication providers list updated with the new authentication provider.

6. Now click on this new authentication provider in the list, and then when the Settings for ADProvider page is shown, set the Control Flag to SUFFICIENT, and press Save.

7. Click DefaultAuthenticator in the Name column to display the Settings page.
8. In the Common Authentication Provider Settings page, change the Control Flag from REQUIRED to SUFFICIENT and click Save.

 

Then, again click on Providers and with the list of authentication providers displayed, press the Reorder button. 

Change the order of the providers so that MSADProvider is first, followed by DefaultAuthenticator, DefaultIdentityAsserter and TrustServiceIdAsserter

Click ok.

With the list of authentication providers displayed Click on MSADProvider

Click on the Provider Specific tab

Enter the following details for your Active Directory installation, amending the settings as appropriate for your AD server.

Provider Specific
Connection
Host:	10.10.2.76
Port:	389
Principal:	CN=ADbiapps,DC=GLC,DC=COM,DC=SA
Credential:
Confirm Credential:
Users
User Base DN:	DC=GLC,DC=COM,DC=SA
User From Name Filter:	(&(sAMAccountName=%u)(objectclass=user))
User Name Attribute:	sAMAccountName
Groups
Group Base DN:	DC=GLC,DC=COM,DC=SA
Group From Name Filter:	(&(sAMAccountName=%g)(objectclass=group))
Static Groups
Static Group Name Attribute:	sAMAccountName

Click Save.

In the Change Center, click Activate Changes.

Restart Oracle WebLogic Server.

Once complete, log in again into the WebLogic Admin Console and select Security Realms > myrealm > Users and Groups. You should then see the Active Directory users listed alongside the WLS LDAP ones.

Next we will switch over to Enterprise Manager, first to configure Fusion Middleware's Oracle Platform Security Services to accept users and groups from both WLS LDAP and Active Directory when logging into the dashboard, and then we'll map the Active Directory groups to their equivalent application roles.

Log into Enterprise Manager, and select the WebLogic Domain > bifoundation_domain menu item on the left. Right-click on it and select Security> Security Provider Configuration. When the Security Provider Configurationpage is displayed, expand the Identity Store Provider area and press the Configure… button.

The Identity Store Configuration page will then be displayed. Press the Add button next to the Custom Properties area, and add a new custom property with these settings :

Property Name : virtualize
Value : true

Press OK to close the page.

Update the system.user key points to an existing user BISystemUser in Active Directory, then the system.user Credential Key will have be updated from Enterprise manager Credential Store (to point to the correct user/password

Edit Key > Select map= oracle.bi.system 

Key = system.user
TYpe = Password
User name = this should be your AD user
Password= password for AD user

Now right-click on the Business Intelligence > coreapplication entry in the left-hand side menu, and select Security > Application Roles. As you may have done with the application role settings in yesterday's postings, edit the BIAdministrator, BIAuthor and BIConsumer application roles so that the new Active Directory groups are listed as members.

Doing this ensures that the Active Directory users get the same type of Presentation Server and repository privileges as WLS LDAP users, but they won't have administration access to WebLogic or Enterprise Manager. 

Add AD User ADBISystemUser are mapped to Application Roles
Log in to Weblogic Admin console > Security Realms from the left-hand menu > drill on your security realm in the main screen (e.g. myrealm)
> Roles and Policies Tab >expand Global Roles> then Roles, then click on the link marked View Role Conditions
For the OBIEE application role eg "Apptester" you should see the corresponding AD group in View Role Conditions eg Group=Apptester (created on AD)

·  Doing this ensures that the Active Directory users get the same type of Presentation Server and repository privileges as WLS LDAP users, but they won't have administration access to WebLogic or Enterprise Manager.

You can, if you want, grant these users the same sorts of domain administrator rights as the WLS LDAP users, and you can indeed remove all of the WLS LDAP users and groups and move over to Active Directory entirely. But in most cases I see, this level of integration is sufficient, as it still allows the OBIEE administrators to control their own user accounts and privileges.

·  You should now be able to log in as one of the Active Directory users. In the screenshot below, the AD User user has logged in, and has been granted the BIAuthor role through their membership of the ADBIAuthors Active Directory group. If Anne Administrator, an Active Directory user assigned to the ADBIAdministrator group, logs in she will be able to administer the Presentation Server permissions and privileges, but she won't be able to log into Enterprise Manager to change the repository, for example.

Useful Links and Oracle Doc ID’s

After Upgrade from OBIEE 10g Unable To Login To OBIEE 11g : "Unable to find user in identity store" (Doc ID 1482788.1)

OBIEE 11g: Alert: Users Unable to Log in to OBIEE 11.1.1.9 if Using MSAD or Other Third-Party LDAP as the Identity Store and Virtualization is Set to true. (Doc ID 2016571.1)

OBIEE 11g: How To Setup ADSI LDAP Security Provider (Doc ID 1273961.1)

https://www.rittmanmead.com/blog/2010/11/oracle-bi-ee-11g-security-integration-with-microsoft-active-directory/

https://www.rittmanmead.com/blog/2010/11/oracle-bi-ee-11g-security-integration-with-microsoft-active-directory/

http://paulcannon-bi.blogspot.com/2012/07/configuring-ldap-authentication-for.html

https://docs.oracle.com/middleware/11119/biee/BIESC/privileges.htm#BABBCEFH

https://www.rittmanmead.com/blog/2012/03/obiee-11g-security-week-connecting-to-active-directory-and-obtaining-group-membership-from-database-tables