Tuesday, May 26, 2020

How to install and make a report with BI publisher in OBIEE 11G


This Document explains about the configuration of  BI Publisher in OBIEE 11g.

Contains,
   
> SERVER CONFIGURATION.

> JNDI CONNECTION FOR BI PUBLISHER.

> REGISTER THE AUDIT-STORING DATABASE TO DOMAIN.

> REGISTER AUDITING DATA SOURCE AS JNDI DATA SOURCE.

> CREATE DATA MODEL.

> CREATE REPORTS.


Server Configuration: 
  •      Login in to Oracle Business Intelligence
  •      click on the Administration Tab
  •      select BI Publisher (Manage BI Publisher)
  •       Select Server Configuration as shown in the above snapshot.
  •      Select check mark on “Enable Monitor and Audit”

Note: BI Publisher performance monitoring enables you to monitor the performance of queries, reports and document generation and to analyze the provided details. User auditing provides information about what users logged in, when, how many times, what reports they accessed, and other actions they took within the application.

  


   >  Login to Web Logic Server Administration Console.
   > Under Services, click Data Sources.


  • Click Lock and Edit button to enable editing.
  • On the Summary of JDBC Data Sources page, click New and click Generic Data Source.

Enter the following details for the new data source;
  •     Name (example: BIP_AuditDB)
  •     JNDI Name (example: jdbc/AuditDB)
  •     Database Type (example: Oracle)




>  Click Next and select the database driver (Thin XA) for Instance Connections; Versions:9.0.1 and later.
>  Enter the required connection properties like Database name, host name, port, database user name (for our exercise it’s OBI_IAU) and the password.
> Click next and accept the default setting and then click Test Configuration button as show below.

 > Click Next, Check listed servers where you want to make this JDBC connection available.






> If the connection was successful, click Activate Changes to make the changes available.




Register the Audit-Storing Database to Domain:
  • Login to Web Logic Server EM (Enterprise Manage).
  • Navigate to the Web Logic Domain, right clickbifoundation_domain, and then select Security, then Audit Store.

> Click Search Data Sources. From the Select Data Source dialog, select the data source you created and click OK


> Navigate to the Web Logic Domain, right clickbifoundation_domain, then select Security, then Audit Policy.

> The Audit Policy table displays all the audited applications under the bifoundation_domain. Set the Audit Level to Medium to enable auditing for BI Publisher.
Restart the Web Logic Servers/domain/Applications etc...
After this, now the BI Publisher should start feeding all the auditing data into the database table called ‘IAU_BASE’. Try login to BI Publisher and open a couple of reports, you should see the activity audited in the ‘IAU_BASE’ table. If not working, we might want to check the log file, which is located at BI_HOME/user_projects/domains/bifoundation_domain/servers/AdminServer/logs/AdminServer-diagnostic.log

Register Auditing Data Source as JNDI Data Source:

First thing you need to do is to register the audit data source (JNDI/JDBC connection) you created in the previous step as JNDI data source at BI Publisher. It is a JDBC connection registered as JNDI that means you don’t need to create a new JDBC connection by typing the connection URL, username/password, etc. You can just register it using the JNDI name. (E.g. jdbc/AuditDB)
   Ø  Login to BI Publisher as Administrator (e.g. Web Logic)
Ø  Go to Administration Page
Click ‘JNDI Connection’ under Data Sources and Click ‘New’
 
  •     Type Data Source Name and JNDI Name. The JNDI Name is the one you created in the Web Logic Console as the auditing data source. (e.g. jdbc/AuditDB)
  •     Click ‘Test Connection’ to make sure the data source connection works.
  •     Provide appropriate roles so that the report developers or viewers can share this data source to view reports.
  •     Click ‘Apply’ to save.


Create Data Model:





  •     Select Data Model from the tool bar menu ‘New’
  •     Set ‘Default Data Source’ to the audit JNDI data source you have created in the previous step.

       > Select your data set in the left hand pane and click on ‘SQL Query’


> Use Query Builder to build a query or just type a SQL query. Either way.
Ø  Here is sample SQL query looks like.

> Click OK and save the data model. Once saved the data model then click on XML option.

     >  Test the sample with Get XML Output and save XML to your data model.



Create Reports:

Now you can use one of the BI Publisher’s layout options to design the report layout anD visualize the auditing data. Here are some sample screenshots of my report design with Online Layout Editor.