Oracle Transactional Business Intelligence (OTBI) - Technology Overview Part II

In Oracle Transactional Business Intelligence (OTBI) - Technology Overview Part I I explained about OTBI architecture and it's OBIEE connections details. Many of you may be wondering how to get the the RPD file and  then open it in BI Administration Tool.

Here is the link where I have explained how to get the latest copy of the RPD file from server. This file is created during installation and is a huge in size. By default this has both OBIA and OTBI. Many customers might not be using all of the product families. Depending on the product family you can trim the RPD and keep only those which you are using or which you have installed. This makes it easy for maintenance. If you use default file then it takes too much time to download to local machine, open it and then upload it in offline mode. It also takes long time to open it on-line mode. Oracle has provided way to trim this file.Here is the link for trimming the RPD. In this example I have not used trimmed file. Use any FTP tool to copy the RPD file to your local m/c.

So now you have the file but you don't have the client tool to open it. You need to install the correct version of the tool to open it. The best way to do is to download it from OBIEE Home Page. Log on to the Oracle BIEE Home using the link http://yourcompany.com:10621/analytics/saw.dll?bieehome   and then click on Download BI Desktop Tools under Get Started 

Select the right installer depending on whether your OS is 32bit or 64bit.
















Once you have installed the client tool, go to Program Files->Oracle Business Intelligence Enterprise Edition Plus Client Tools -> Administration. Copy your file in the folder e.g C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orainst\bifoundation\OracleBIServerComponent\coreapplication\repository  and then open it from BI Administration Tool. It will prompt for password to open it as the file is password protected. Ask your system administrator or DBA for the same. This is the password specified during installation.

This is what you will see when you open the RPD file.










This covers Opening the RPD file from BI Administration Tool.

Cheers..................

How to Know On Premise RPD File Name and Location?

If you want create new Subject Areas in OBIEE for use in OTBI/OBIA then you have to modify the metadata repository file. To do that you need to know the latest copy of the RPD file and the location of the same.

How to find the latest copy and location?

Log on to BIDomain EM. Here is the link to know the domain details. So if your BIDomain is deployed on http://yourcompany.com:xxxx then EM would be http://yourcompany.com:xxxx/em.

Enter your user id and Password

Once you log on this is you will see. If you don't see this click on Oracle logo.

Expand Business Intelligence Node and select coreapplication. Once you have done this the screen should look like this.

Then click on Deployment and Repository tab. Under BI Server Repository Section you will see Default RPD. In this case the RPD file name is OracleBIApps_BI0008. Note that this is a shared repository and shared path is /u01/app/oracle/fusion/instance/BIShared/OracleBIServerComponent/coreapplication_obis1/repository. If you log in to that path from UNIX box you may find that the RPD file names are not matching with default RPD file mentioned in EM. There is a MOS document OBIEE 11g: How is the Repository File Updated in a Clustered Environment and How To Identify the Latest Repository File in the Publishing Directory [ID 1484831.1]. This document explains why the file names under this folder are different.

So if the RPD file does not match with the file names under shared folder then how will someone get the exact file name?? There is also a MOS document How To View User Permissions in the RPD for Fusion Applications Business Intelligence [ID 1408832.1]. Under this document there is a section called Download the current copy of your RPD file.  There it has been specified the exact location of the rpd file as /APPLTOP/instance/BIInstance/bifoundation/OracleBIServerComponent/coreapplication_obis1/repository. But I could not find the path to be valid one. Generally APPL_TOP is set to /u01/app/oracle/fusion/fusionapps/applications. But inside APPL_TOP this path does not exist. I believe this path would BASEPATH/instance/BIInstance/bifoundation/OracleBIServerComponent/coreapplication_obis1/repository.  Where BASEPATH is the mount point of Fusion Apps.I don't know whether this is typo or our system environment is set like this. Anyway Oracle has to confirm this....

So if I go to the BASEPATH/instance/BIInstance/bifoundation/OracleBIServerComponent/coreapplication_obis1/repository  and do ls -ltr I could see this

Here I could see the OracleBIApps_BI0008.rpd file. Similarly you can follow this and get your latest copy of the RPD file and start modifying.

In the next series I will explain how to verify the connection and the database details to which OTBI report connects.

Keep reading................

Cheers

Oracle Transactional Business Intelligence (OTBI) - Technology Overview Part I

Oracle Transactional Business Intelligence(OTBI) is one of the reporting tools that Oracle gives access to it's customer for their reporting in SaaS model. Like me many of the people are coming from eBusiness Background. When they see SaaS access constraints they will wonder what this OTBI is.

OTBI is exclusively for Oracle Fusion Applications. It has been designed to meet real-time, ad-hoc, current state reporting and analysis of operational data. It combines Oracle ADF and OBIEE technologies. Below is the picture which tells everything about it.

OTBI Architecture

As can be seen from the picture that OBIEE uses ADF Model Layer View Objects for its meta data source. In ADF Model Layer Entity Objects(EOs) are created and VO objects are created from these EOs. These VOs represent the SQL query for the physical layer in OBIEE. These view objects simplify access to data and abstract the underlying data model and business logic complexities. By doing this oracle has made sure to keep only one source of truth. Changing business logic in one place makes it available in Fusion Applications as well as in OTBI.

Another fact is that OTBI and OBIA use the single logical definition of the object in the OBIEE Business Model and Mapping layer. This is a unifying factor that allows users to see the Oracle Fusion applications data from either a transactional or a historical perspective. Most of the common dimensions and a few facts in the Business Model and Mapping layer of Oracle BI repository are shared between Transactional Business Intelligence and BI Applications.

Once you open the RPD file in OBIEE Administration Client you will see that there are three connections of type OracleADF11g. Financials, Oracle Fusion Supply Chain Management Connection Pool, Projects, and Incentive Compensation use the FSCM connection; CRM and HCM use thier own connection Pools. So basically they are

1. FSCM
2. CRM
3. HCM

Marked in RED are those three connections. If you double click any one of those connections you will see the something similar to the next screen.

This is what tells OBIEE to query ADF View Objcts in stead of tables. There is also a concept of SQL Bypass. This extracts the SQL query from the VOs and executes them in the database. In this process it reduces the burden of executing the VO objects.

Each of the three connection pools has one connection pool containing Oracle ADF server connection information, such as application server, application module name, and user information. Oracle BI Server uses this information in connecting to the data source.

Let's see how does SQL bypass works. Following is the picture from Oracle documentation which explains

You must set the SQL Bypass Database Option. To do that double click HCM(oracle.apps.hcm.model.analytics.applicationModule.HcmTopModelAnalyticsGlobalAM_HcmTopModelGlobalAMLocal)  in Physical layer; Click on Connection Pools Tab, select the Connection Pool and click on edit.

Once you click on edit button the next screen will pop up

Select the Miscellaneous tab.
Select Set.
Available database connections are displayed.
Select the database connection that is pointing to the Fusion Applications database, as shown in Figure, Connection Pool Miscellaneous Tab.
Select OK.

Now let's see how the View Objects are specified in the three connections - FSCM, HCM & CRM. The next screen will give a snapshot of some view objects in HCM connection

View objects are named using the following convention, which can help in tracking back to the corresponding module in Oracle Fusion applications:

Global Application Module Name. Sub-Application Module Name.view object-Name.For example:
HCMTopModelAnalyticsGlobalAM.AnalyticsServiceAM.CurrenciesPVO, where HCMTopModelAnalyticsGlobalAM is the application module name, AnalyticsServiceAM is the sub-application module name, and CurrenciesPVO is the view object name.Similarly, all the alias physical tables are named as <Dim/Fact>__VO-Name_Role.
The next screen shows this.

This is all about Physical Layer in RPD. The Business Model and Mapping layer is the one where business or logical model is written. It is also the layer where Mapping between Model and Physical layers schemas are specified. Business Models contain dimensions, facts (Logical Tables in OBIEE) and hierarchies (Logical Dimension in OBIEE).

All OTBI objects are part of Business Model Core, which is common model for both OTBI and OBIA.

Let's stop here today. At the end of this series I will show how to get RPD File and and open it OBIEE Client tool.

SO Till now you have got idea about the OTBI Architecture. In the next few post will cover Model Layer & presentation layer and will show on the fly how to create OTBI report from OBIEE.

Keep reading...









Cheers..............................

Fusion Apps Custom BI Report Creation Part IV

This is the last post in this series of BIP Report Creation in Fusion Applications. In the last three posts I have shown how to create Data Model and Reports Definitions along with Bursting. You can find those here

1. Fusion Apps Custom BI Report Creation Part III
2. Fusion Apps Custom BI Report Creation Part II
3. Fusion Apps Custom BI Report Creation Part I

Create an ESS Jos Job to run this report. Login to Fusion Home. From Navigator choose “Setup and Maintenance”. You must have proper Role assigned in order to access this.

For my example I will use “Manage Custom Enterprise Scheduler Job for HCM and Payroll” Task to create our Job. In the FSM screen search this task

Click on Go To Task.

Click on Create Link to create a new Job.

Specify the Details as mentioned below

Click on “Show More” button to make all options available

Job Type must be of type ‘BIPJobType’

Check “Bursting Report” Check box

Report id is the absolute path of the report starting after “/shared/”. For our example we have entered “Human Capital Management/Payroll/DemoReport.xdo” in this field as full path of the report is “/shared/Human Capital Management/Payroll/DemoReport.xdo”

Check “Enable submission from Scheduled Processes” check box

Navigate to ‘User Properties’, add property ‘EXT_PortletContainerWebModule’ with value ‘Report’.

Define parameters in the “Parameter Tab”. As this report do not have any parameter we skipped this step.

Once the Job is Created, from Navigator choose “Scheduled Process”. 

After clicking the Scheduled Process this screen will appear. Click on Schedule New Process Button. Search your Job name from the List & Click OK

Click on Submit. On successful submission system will confirm.This will  submit a Job and system will give a Process ID.

Wait for the Process to Complete. This is almost same like Concurrent Requests in Ebusiness. Once the requests successfully completed you can see the output

As you can see a single request has created multiple reports. If  you click on any one you will see the delivery confirmation that the email has been sent. Open your mail box if you had specified your id in the Bursting definition.

For me I got the email with output attached.

References

• Oracle Fusion Middleware Data Modeling Guide (Release 11g)
• Oracle® Fusion Applications Extensibility Guide 11g (Release 5 - 11.1.5)
• Report Designer's Guide for Oracle Business Intelligence Publisher Release 11g
• Oracle Support Documents

This completes the series..Hope this will help the beginners who just now started Fusion Apps.

Cheers.......

Fusion Apps Custom BI Report Creation Part III

Till now I have completed how to create the data model of your report. I have covered that in the last two posts

1. Fusion Apps Custom BI Report Creation Part I
2. Fusion Apps Custom BI Report Creation part II

So now after completing the above two you have XML data file which you saved in your local machine. The next step is to Create Custom Reports.

Create a custom report when the reports delivered with your Oracle Fusion application do not provide the data you need; or, if you want to use a predefined data model and change other properties of the report.

Here we go

Click on the New Option and Choose “Report” and in the following screen do select the data model

As you have already created the data model, select Use existing data model

Browse through catalog and select the data model you have already created.

Select Use Report Editor to build your own report layout and click finish.

Specify the report name. This is the name by which it will be called from ESS if you want to run it from there.

Once done, click on upload the report template. In this example I have create a simple RTF template using MS Word with add on for XML Publisher.

Now if you want to add parameters create them in this step. Some parameters may be derived and some may be provided by users. So while creating parameters Check Show for those which you want users to enter.

Now specify the report properties. Those who are from EBS background may recall that this is almost same like Concurrent Program Creation Screen. Here you specify different controls about of the report.

Click on view report button to get the sample output.

Bursting is a process of splitting data into blocks, generating documents for each block,and delivering the documents to one or more destinations. The data for the report is generated by executing a query once and then splitting the data based on a "Key" value. For each block of the data, a separate document is generated and delivered. Using BI Publisher's bursting feature you can split a single report based on an element in the data model and deliver the report based on a second element in the data model. Driven by the delivery element, you can apply a different template, output format, delivery method, and locale to each split segment of your report. Example implementations include:

• Invoice generation and delivery based on customer-specific layouts and delivery preference
• Financial reporting to generate a master report of all cost centers, splitting out individual cost center reports to the appropriate manager

People who are from eBusiness Background may recall that we used to do this by writing a Burst Control File and attach it with the Data Definitions in XML Publisher Administrator Responsibility.
The burst control file looks like

What is the Bursting Definition?
A bursting definition is a component of the data model. After you have defined the data sets for the data model, you can set up one or more bursting definitions. When you set up a bursting definition, you define the following:
• The Split By element is an element from the data that will govern how the data is split. For example, to split a batch of invoices by each invoice, you may use an element called CUSTOMER_ID. The data set must be sorted or grouped by this element.
• The Deliver By element is the element from the data that will govern how formatting and delivery options are applied. In the invoice example, it is likely that
each invoice will have delivery criteria determined by customer, therefore the Deliver By element may also be CUSTOMER_ID.
• The Delivery Query is a SQL query that you define for BI Publisher to construct the delivery XML data file. The query must return the formatting and delivery details.

The bursting query is a SQL query that you define to provide BI Publisher with the required information to format and deliver the report. BI Publisher uses the results from the bursting query to create the delivery XML. The BI Publisher bursting engine uses the delivery XML as a mapping table for each Deliver By element. The structure of the delivery XML required by BI Publisher is as follows:

where
• KEY is the Delivery key and must match the Deliver By element. The bursting
engine uses the key to link delivery criteria to a specific section of the burst data.
• TEMPLATE - is the name of the Layout to apply. Note that the value is the Layout
name (for example, 'Customer Invoice'), not the template file name (for example,
invoice.rtf).
• LOCALE - is the template locale, for example, 'en-US'.
• OUTPUT_FORMAT - is the output format.
 Valid values are:

• HTML
• PDFZ - zipped PDF
• PDF
• RTF
• EXCEL
• EXCEL2000
• MHTML
• PPT
• PPTX - PowerPoint 2007
• XSLFO
• XML
• CSV
• ETEXT

SAVE_OUTPUT - indicates whether to save the output documents to BI Publisher
history tables that the output can be viewed and downloaded from the Report Job
History page.
Valid values are 'true' (default) and 'false'. If this property is not set, the output will
be saved.
DEL_CHANNEL - is the delivery method. Valid values are:

• EMAIL
• FAX
• FILE
• FTP
• PRINT
• WEBDAV

OUTPUT_NAME - is the name that will be assigned to the output file in the report job history.

Delivery parameters by channel. The values required for the parameters depend on the delivery method chosen. The parameter values mappings for each method are shown in the following table. Not all delivery channels use all the parameters.

Delivery Channel                                PARAMETER Values
Email                                                  PARAMETER1: Email address
                                                          PARAMETER2: cc
                                                          PARAMETER3: From
                                                          PARAMETER4: Subject
                                                          PARAMETER5: Message body
                                                          PARAMETER6: Attachment value ('true' or 'false'). If your output               format is PDF, you must set this parameter to "true" to attach the PDF to the e-mail.
                                                          PARAMETER7: Reply-To
                                                          PARAMETER8: Bcc
                                                          (PARAMETER 9-10 are not used)

I have used only Email. All of the above values are set using the query dynamically. So if the Key element is having same value as specified by Deliver By 

SELECT department_id AS "KEY“  ---- As Department ID is our Deliver By element

      ,'DemoReport' AS TEMPLATE ---- Report Name

      ,'en-US' AS LOCALE  ---- Locale

      ,'PDF' AS OUTPUT_FORMAT ---- Output file will be in PDF format

      ,'EMAIL' AS DEL_CHANNEL ---- Sending bursting Output Via EMAIL

      ,‘to_mail_id@abc.com' AS PARAMETER1 ---- TO

      ,‘cc_mail_id@abc.com‘ AS PARAMETER2 ---- CC

      ,' from_mail_id@abc.com ' AS PARAMETER3 ---- FROM

      ,'You Have got a Mail' AS PARAMETER4 ---- Subject

      ,'Hey!! Bursting Successfull!!' AS PARAMETER5 ---- Body

      ,'true' AS PARAMETER6 ---- We want to attach PDF output in mail

      ,' reply_to_mail_id@abc.com ' AS PARAMETER7 ---- REPLY TO

FROM   xxfusion.departments

Select Report Properties and enable the bursting and select bursting definition.

This completes report creation and associating bursting definition with it. In the next series of this topic I will cover how to create Enterprise Scheduler Services Job to run this report and schedule it.......

Cheers.....................

Fusion Apps Custom BI Report Creation Part II

Let me recall what I did in Part I. I gave details about how to create

• Data Model Data Sets
• Links between Data Models
• Aggregate Functions
• Parameters 

Here is the link which has the details Fusion Apps Custom BI Report Creation Part I

So let's start the report template generation using  XML data file.
To do that follow the steps mentioned below

Generate XML

Specify the Parameter and number of rows of data you want in your XML. To make generated XML light you specify the number of rows.







Now after that save the XML to your local machine. 

Now before going to next step, let's make the parameters dependent with the previous one. Those who are from EBS background make recall that this is done in concurrent program parameters by prefixing $FLEX$.




The same can be done here. Let's say we want to Create Employee First Name parameter such that, once Department is selected then only employees belong to that department will be listed. We will define LOVs as 

After creating the Dept LOV create a second Employee First Name LOV which refers the Dept LOV as shown. This binds the second LOV with the first one. Now we need to tell system that whenever user will select Dept, refresh the Employee LOV. This is done by PPR(Partial Page Rendering) in ADF. 

Here it is specified as shown in the picture. After doing this if you go to Get XML then it will like like 

Change the Dept and you will see the employee list is changing.










Now that we have developed the basic skeleton, let's make it little bit more user input data sensitive. E.g if you want your query WHERE clause to change based on input parameters then you can do it by creating report triggers. Those who are familiar with Reports6i they know that same can be done using report triggers

• Before Parameter Form
• After Parameter Form
• Before Report
• Between Pages
• After Report

Here in BIP, first we will create a parameter p_where_clause to append condition dynamically 

You can call it as Lexical Parameter. Now we will create a before_data Trigger. For that we need to create a DB Package first. All the parameters defined in the report must be defined In the package specification as global variable.

The logic we are going to implement is that

If Department ID is passed then All the Employees of the Department will be listed

Otherwise if any part of employee name is passed in the parameter First Name then those employees will be listed

If Department ID and First Name both are NULL then no Condition will be applied.

We build this logic inside function before_data of xx_demo_report_pkg package.

Any function used in Data trigger must have return type as BOOLEAN. 

Below is the sample package code you will create in FUSION schema.

CREATE OR REPLACE PACKAGE xx_demo_report_pkg

IS

   p_dept_id        NUMBER;

   p_first_name     VARCHAR2 (100);

   p_where_clause   VARCHAR2 (1000);

   FUNCTION before_data

      RETURN BOOLEAN;

END xx_demo_report_pkg;

CREATE OR REPLACE PACKAGE BODY xx_demo_report_pkg

IS

   FUNCTION before_data

      RETURN BOOLEAN

   AS

   BEGIN

      IF (p_dept_id IS NOT NULL)

      THEN

         p_where_clause   := ' AND emp.department_id = :p_dept_id ';

      ELSIF p_first_name IS NOT NULL

      THEN

         p_where_clause   := ' AND upper(emp.first_name) like ''%'||UPPER(p_first_name)||'%'' ';

      ELSE

    p_where_clause := NULL;

 END IF;

      RETURN TRUE;

   EXCEPTION

      WHEN OTHERS

      THEN

         RETURN FALSE;

   END;

END xx_demo_report_pkg;

After creating the package do the following

Choose the function and click the shuttle arrow. Now go ahead and change the WHERE clause

Specify a space and click Ok.

This completes the Data Model part. In the next post will cover Report Creation..It's 12'O Clock in the night....Time to sleep....