Pages

Blogger news

Monday 3 February 2014

To Eliminate the DISTINCT Clause in the OBIEE backend query

As a default, OBIEE will give you the distinct rows in the output. In order to avoid and to show the duplicated rows


OBIEE will send the query to database for selecting the distinct values.





Result:


To show the distinct rows in OBIEE output. the database feature DISTINCT SUPPORTED has to be uncheck as shown below.


After the change, OBIEE will send query to the database with out the distinct clause.


Result:



Note: As per OBIEE, if we change defaults there would be a possibilities of variation in the reports.Depends on the requirement from the business we have to play with defaults.




Regards,

Zafar Habib

Thursday 23 January 2014

ODI Load Plan Types in BIACM

Load Plan Types:

There are 3 main phases: 
  1. Source Data Extract (SDE)
    The SDE phase consists of tasks extracting data from your different source systems. This phase loads all your staging tables and requires source system downtime
  2. Source Independent Load (SIL)
    The SIL phase loads data from your staging tables into their respective dimension or fact tables.
  3. Post Load Process (PLP)
    The PLP phase loads data into aggregate tables or some other facts requiring additional data processing. The SIL and PLP phase requires data warehouse downtime to complete the load process.





Regards,
Zafar Habib

Tuesday 21 January 2014

BI APPS 11.1.1.7.1 Configuration Document



1)  Introduction

1.1)  Introduction

Oracle BI Applications 11.1.1.7.1 is set of packaged data extraction and loading routines designed to load a pre-built Oracle data warehouse, together with a set of dashboards, reports and other business metadata objects designed to provide customers with a quick-to-deploy, best-practice BI environment for Oracle's ERP and CRM applications. Whilst a full installation of Oracle Data Integrator is provided with Oracle BI Applications 11.1.1.7.1, its role is as an "embedded" data integration engine with administrators mainly interacting with it using web-based administration and configuration tools.


2)  Configuration

2.1)  BI Applications 11.1.1.7.1 Data Load:

At this point our environment consists only of installed software and an empty data warehouse. The next step in the process is to run Oracle's pre-packaged data extraction and load routines against a supported source system, which for the purposes of this article will be Oracle E-Business Suite 12.1.3. To do this, we will use a combination of three tools: the aforementioned Oracle BI Applications Configuration Manager and Oracle Data Integrator, along with another application called Oracle BI Applications Functional Setup Manager.
Configuration Manager and Functional Setup Manager are web-based applications that run in the BI Managed Server and replace many of the configuration and administration capabilities of the DAC in the prior release. Where Configuration Manager serves as the interface for administering the entire BI Applications environment and configuring Oracle Data Integrator, Functional Setup Manager acts as a lightweight project management tool for configuring a specific BI Application, like Supply Chain and Order Management Analytics, as shown in Figure.







2.2)  BI Applications 11.1.1.7.1 Instance:

1. Our first task is to make sure the Oracle Data Integrator Agent is running, as that will be the "air traffic controller" to allow our Load Plans to execute. To do this, first ensure that the WebLogic Node Manager Service is running, and then use your Web browser to navigate to the WebLogic Server Administration Console at http:/biappseup.inboxbiz.com:7001/console , logging in as weblogic/welcome1.
On the console Home Page, click the Servers link under Domain Structure > Environment. The Summary of Servers page will list the three servers in this environment: the Admin server (AdminServer), the managed BI server (bi_server1), and the managed ODI server (odi_server1), and while the state of first two will show as 'RUNNING', odi_server1 will show as 'SHUTDOWN'.
Click the Control tab and then check the checkbox next to the odi_server1 managed server. This should enable the Start button above and below the table of servers. Then, click the Start button and press Yes when prompted. The odi_server1 will start, though it may take several minutes to do so. You can click the double circular arrows icon to have the status refresh automatically. Once the state of odi_server1 is 'RUNNING', you may continue.


2. Now that the ODI Agent is ready to do our bidding, we can begin the process of configuring our BI Applications environment in Configuration Manager.
Using your Web browser, navigate to http://biappseup.inboxbiz.com:9704/biacm, and login as biadmin/welcome1.

Business Intelligence Application Configuration Manager Home Screen shown below:
Note: When you login BIACM first time, the overview section has showing empty.
Now, we will setup BI Instance in BIACM.
There are several global configuration steps we must perform first in Configuration Manager, and they are grouped under Tasks>System Setups on the left-hand side of the screen. Click our first task, Define Business Intelligence Applications Instance, to start this configuration process.
Clicking on this task then presents three tabs for configuration: Source Systems, Target Warehouse, and Environment Configuration. If you look at the Target Warehouse and Environment Configuration tabs, you'll see that the BI Applications Configuration Utility has already configured these for you. However, there are no Source Systems defined, so that becomes our first task.
Click the Source Systems tab (if it isn't selected already) and click the green plus symbol to add a new source system. Enter the following details, when prompted:
Product Line: Oracle E-Business Suite
Product Line Version: Oracle E-Business Suite R 12.1.3.x
Source Instance Name: EBSVISION
Description: EBS VISION instance
Data Source Number: 1000
(The above fields can be altered based on your source system.)
Click the Next button to register the source in the ODI Topology. It's worth noting here that Configuration Manager writes configuration details directly into the ODI Repository. This saves an administrator valuable time because Configuration Manager consolidates multiple ODI configurations into a single Configuration Manager screen.
Start by selecting Global in the Context dropdown, and then in the Technology: Oracle tab, enter the following information:
ODI Data Server Name: fctraining
JDBC Driver: oracle.jdbc.OracleDriver
JDBC URL: jdbc:oracle:thin:@fctraining:1523:VIS
Database Username: APPS
Database Password: apps
(The above fields can be altered based on your source system.)
Now click the Test button under Test Connection to make sure you've entered the connectivity information properly and your OBIA environment can communicate with your source system.

The BI Applications have always made use of text files as sources for some key content not stored in the source system. While text files are flexible, they aren't the best enterprise solution, so with BI Applications 11.1.1.7.1, Oracle started the process of moving some of this content to Configuration Manager. However, there are still text files to be managed and maintained, so before we can complete the next tab, use Windows Explorer to copy the 'etl' folder in C:\Middleware\Oracle_BI1\biapps\ to a new directory called C:\biappsfiles\. Note that you can name the target folder anything you want (no spaces) and it can be in any location you want, but it's recommended to move them to another location to avoid them being overwritten during an upgrade. On a production server, a SAN or NAS server is preferable.

We also need to update this location in ODI, so before continuing with Configuration Manager, open ODI Studio, navigate to the Topology tab, and expand Technologies: File: BIAPPS_DW_FILE tree until you see the following object:
BIAPPS_DW_FILE.c:\Middleware\Oracle_BI1/biapps/etl/data_files/src_files/BIA_11
Double-click this object to open it and update the following two fields in the Definition tab:
Directory (Schema):
C:\biappsfiles\etl\data_files\src_files\BIA_11
Directory (Work Schema):
C:\biappsfiles\etl\data_files\src_files\BIA_11
Click the Save icon in the toolbar (notice the object name changes) and you can close the object in ODI Studio.



Then, in the Technology: File System tab back in Configuration Manager, enter the following information:
ODI Data Server Name: EBSVISIONFILES
Host Name: obia111171w64
Path to File: C:\biappsfiles\etl\data_files\src_files\EBS_12_1_3
Finally, click the Save and Close button, and then done, to finish this configuration process.



2.3)  Manage BI Applications 11.1.1.7.1:

The next task is to inform Configuration Manager of which BI Applications "offerings" are licensed to be used in this environment. The key emphasis here is what is licensed to be implemented, not what you're actually implementing at this time. Note that for demo or personal training purposes, you can select any offering from this list, but you should ensure that you only select those that you have licensed when working on a full implementation project – see your local Oracle representative, or an Oracle partner, if you need your licensing to be clarified or extended to include the BI Applications.
Moving back to your Web browser [BIACM Screen], click System Setups > Manage Business Intelligence Applications on the left-hand sidebar in Configuration Manager. Then, within the Manage Business Intelligence Applications page, locate the Oracle Financial Analytics, Order Human Resource Analytics and Oracle Procurement & Spend Analytics entry and check the Enabled checkbox next to it to select that BI Applications offering (you may need to use the scroll bar to see this checkbox). Then, press the OK button in the dialog then is then displayed, and once it closes, press Save.
Next, select the Business Intelligence Application Offerings and Associated Sources tab and expand Oracle Financial Analytics, Order Human Resource Analytics and Oracle Procurement & Spend Analytics. The 'EBSVISION' checkbox in the Enabled column should be checked; once confirmed, press the Done button to return to the main Configuration Manager page.

   

2.4)  Manage Preferred Currencies:

To view the preferred currency names that are used on Oracle Business Intelligence dashboards in the Currency drop-down on the My Account dialog\Preferences tab for a user logged into Oracle Business Intelligence. You can edit preferred currency names by clicking the Edit icon. For example, if your organization prefers to use the term 'Local Currency' instead of 'Ledger Currency', you can use this dialog to change the default value 'Ledger Currency' to 'Local Currency'.
Element
Description
Preferred Currencies
Use this pane to view the preferred currency names.
To change a preferred currency name, click the Edit icon to display the Edit Preferred Currency Name dialog.



       

2.5)  Manage Warehouse Languages:

Use this tab to specify the languages for which data will be loaded into the Business Analytics Warehouse during the ETL process. When you install Oracle BI Applications, the American English Language is enabled by default. You must enable the languages that you want to deploy.
You can also specify a Base Language. The Base Language is used if the ETL process cannot locate data in any one of the enabled languages. For example, if French is an enabled language and American English is the Base Language, there will be two rows, one for each language. If French values are not available, then American English values will be substituted.
Element
Description
Manage Business Analytics Warehouse Languages
Use this list to view the available languages, and enable the languages that you want to support.
Use the Installed column to change the default value of 'Disabled' to 'Installed', which enables the selected language.
The other table columns display read-only data.
Set Base Language
Use this option to specify the Base Language, which is marked with a blue dot. You can only specify one Base Language.
American English is the default Base Language. To select a different Base Language, select a record in the table, then click the Set Base Language icon. If the current Base Language record is selected, then this icon is grayed out.



2.6)  Manage Load Plans:

While there are other configurations that could be done at this time, we've completed the minimum configurations required to move to the next phase, which is to generate and execute a Domain Load Plan. Load plans are the steps that execute a data load and are similar to execution plans in earlier, DAC-based versions of the BI Applications. The difference is that BI Applications 11.1.1.7.1 contains metadata about what steps are required to load every application and you must generate an executable load plan based on what you specifically plan to load. This potentially reduces the amount of step pruning you need to do after the fact.
There are also different types of load plans. The Domain-Only Load Plan will run some targeted data extractions from the source system to assist with configurations for specific applications. This stands in contrast to a Source Extract And Load Plan, which focuses on loading the facts and dimensions in the warehouse.

In the next step you will build your first load plan, in this instance a domain load plan to extract data from the source system to support loading the Inventory Transactions fact table group within the Supply Chain and Order Management offering. To start this process, using your Web browser and still within the Configuration Manager application, click Load Plans Administration > Manage Load Plans






Then, when the Manage Load Plans page opens, click the green plus symbol to add a new load plan, and enter the following details to define the new domain load plan:

Name: LP_HR_Absense&Accrual
Description: [optional]
Load Plan Type: Source Extract and Load (SDE,SIL & PLP)
Source Instances: EBS_VISION
(The above fields can be altered based on your source system except 'Load Plan Type'.)


Then, press the Next button to display the list of available fact table groups; check the checkbox in the Selected column for the Accrual Transactions (ACCRUALTRANS_FG) fact group, which you can find under the Oracle Human Resource Analytics > Absence & Accrual fact grouping.

Click the Save button and select Save and Generate Load Plan when prompted. This will create a custom load plan in ODI that is a subset of the master load plan based on the load plan type and fact groups we selected. This process will take a few minutes and can be monitored by clicking the blue circular arrows refresh symbol. Once the generation is complete, as indicated in the Generation Status column by a green checkmark next to the black circular arrows symbol, you may proceed to the next step, which is actually executing the load plan.

To execute the load plan, click the Execute button, signified by a green play button symbol in a white box, and select the following values when the Execute Load Plan dialog is shown:

Context: Global
Logical Agent: OracleDIAgent
Oracle Data Integrator Work Repository: BIAPPS_WORKREP

Then, click the OK button, and the ODI Agent will now execute the domain load plan we created. This process will take several minutes depending on your system and can be monitored by clicking the blue circular arrows refresh symbol. Once the execution is complete, as indicated in the Execution Status column by a large white checkmark in a green circle symbol, you may proceed to the next step.

3)  Configuration Functional Setup Manager

3.1)  Introduction:

Functional Setup Manager operates much like Configuration Manager does for ODI: a faster, simpler way to get to the configuration you need to make. More often than not, Functional Setup Manager opens a window to a specific parameter screen in Configuration Manager that is required for a particular offering you are implementing. Rather than hunt and peck through the many parameters in Configuration Manager, Functional Setup Manager notifies you that a parameter is important and takes you to the right place in Configuration Manager to configure it. So really, it's a helper application to make sure you know exactly what you need to do, and that's an important distinction, because you technically don't have to use it if you know what you're doing. So let's walk through the basics of Functional Setup Manager so you can see how it works.
1.       The first step, again performed through your Web browser, is to jump to Functional Setup Manager through Configuration Manager.
Click Perform Functional Configurations in Configuration Manager, and then login to Functional Setup Manager as biadmin/welcome1. Then, click Configure Offerings; here we will enable our licensed offering for implementation.
Check the Enable for Implementation checkbox beside Oracle Financial Analytics, Order Human Resource Analytics and Oracle Procurement & Spend Analytics and press Yes to continue.

Check the Enable for Implementation checkbox beside Oracle E-Business Suite-Oracle Supply Chain and Order Management Analytics and every box under it. Then, click Save and Close.



2.       Next we're going to create an implementation project for our enabled offering. An implementation project is nothing more than a collection of tasks required to configure the offerings you include in the project. Much like the load plan, BI Applications 11.1.1.7.1 contains a master task list for every offering, with some tasks having a one-to-many relationship to the offerings. Creating an implementation project is very similar to generating a load plan in that you only get a subset of the master based on your selections.

Click Implementation Projects tab and click the 'Create' icon, then enter and select the values below:
Name: IP_HR
Code: IP_HR
Description: IP_HR
Status: Not Started
Assigned To: biadmin
Start Date: [accept default]
Finish Date: [leave blank]
Click the Next button.

Check the Include checkbox beside Oracle Financial Analytics, Order Human Resource Analytics and Oracle Procurement & Spend Analytics, then check the Include checkbox beside Oracle E-Business Suite- Oracle Financial Analytics, Order Human Resource Analytics and Oracle Procurement & Spend Analytics and all checkboxes under it. Then, click the Save and Open Project button.




Regards,
Zafar Habib


Tuesday 7 January 2014

How to Invoke ODI Load Plan from OBIEE / OBIA 11g




Hello Guys.

I am going to breifly explain how to Invoke ODI Loan Plan from OBIEE / OBIA 11g with SCREENSHOTS.

Configuration:

Firstly you need to configure ODI Java EE Agent for OBIEE.

The name of the EAR file is oraclediagent.ear, and it is typically located by default in the following location:

<Oracle Middleware Home>\[ODI Domain Name]\setup\manual\oracledi-agent

Figure1.



At the root level of the ear file, locate a WAR (Web application ARchive) file called oraclediagent.war. Select and open the WAR file as shown in Figure 2.


At the root level of the WAR file, locate and select the WSIL (Web Services Inspection Language) file called OdiInvoke.wsil(see Figure 3). Open the WSIL file with a text editor (i.e. Notepad).




Add a new web service called “ODI Web Services”, and specify the name of the service (ODIInvoke) and its WSDL (XML document that describes the service). ODIInvoke is the ODI web service that can be used by other applications such as OBIEE to invoke ODI scenarios and ODI load plans.

If the WSIL file has never been modified, replace the content of the WSIL file with the following XML code (adjust the service name, URL location, and port number based on your requirements):


<?xml version = ’1.0′ encoding = ‘UTF-8′?>
<!–Generated by Oracle BI Services–>
<inspection xmlns=”http://schemas.xmlsoap.org/ws/2001/10/inspection/”>
<service>
<abstract>ODI Web Services</abstract>
<name>ODI Web Services</name>
<description referencedNamespace=”http://schemas.xmlsoap.org/wsdl/” location=”http://localhost:8001/oraclediagent/OdiInvoke?WSDL”/>
</service>
</inspection>


Save your changes in all 3 files: WSIL, WAR, and EAR file.

The next step is to redeploy your ODI Java EE agent. In Weblogic, launch the Weblogic Console and login as an Administrator. Locate your oraclediagent deployment, and proceed to update it with the new version of your oraclediagent.ear file as shown in Figure 4.


Select “Finish” in the Update Application Assistant screen. If you successfully updated the oraclediagent with the new EAR file, you should see a confirmation message as shown in Figure 5.


The next step is to test your new WSIL. Launch a browser, and type the URL of your WSIL (http://localhost:8001/oraclediagent/OdiInvoke.wsil). If your WSIL was successfully configured, you should see the following XML code as shown in Figure 6:

In my case , localhost name is "biappseup.inboxbiz.com" and port is "15001"





The next step is to validate and test your WSDL document. Launch a browser, and type the URL of your WSDL (http://localhost:8001/oraclediagent/OdiInvoke?WSDL). If your WSDL was successfully configured, you should see the following XML code as shown in Figure 7:



At this point, no additional configuration is needed in ODI. The next steps are going to demonstrate how you configure OBIEE to call the ODI web services.



Registering ODI Web Services in OBIEE:

The Action Framework of OBIEE 11g is a component of the Oracle BI EE architecture that allows us to invoke web services that are deployed in other application servers. These services can be configured in OBIEE as Action Web Services, and they can be used within the Oracle BI Presentation Services. For more information about OBIEE Actions Framework, please see Using Actions to Integrate Oracle BI EE with External Systems.

Our discussion will be limited to what configuration is needed to enable and execute ODI web services from OBIEE. However, we strongly recommend that you secure your OBIEE actions. For more information, please see Overview of Action Security.

The first step is to register the ODI Web Services in the OBIEE Action Framework configuration file called ActionFrameworkConfig.xml. This configuration file is typically located by default in the following location:



<Oracle Middleware Home>\user_projects\domains\bifoundation_domain\config\fmwconfig\biinstances\coreapplication



In this file, we are going to add a new WSIL entry to specify the name and location of the ODI WSIL. OBIEE will use the WSIL to locate the WSDL, and retrieve available services based on the WSDL document. For more information on how to configure this file, please see Configuring the OBIEE Action Framework. Make a backup of this file before making any modifications.

Modify the ActionFrameworkConfig.xml file by adding a new WSIL registry (under the <registries> section) for ODI Web Services as follow (change the name of the WSIL registry, and the path of the WSIL based on your requirements):

<registry>
<id>ETLWS</id>
<name>ETL Web Services</name>
<content-type>webservices</content-type>
<provider-class>oracle.bi.action.registry.wsil.WSILRegistry</provider-class>
<description></description>
<location>
<path>http://localhost:8001/oraclediagent/OdiInvoke.wsil</path>
</location>
</registry>
Save your changes.

Restart your OBIEE server.



Invoking ODI Web Services in OBIEE:

Once your ODI Web Services has been configured with OBIEE Action Framework, BI users can login into OBIEE Presentation Services and access the ODI web services by creating a new OBIEE Action of type “Invoke A Web Service” as shown in Figure 9.




Click on "Invoke a Web Service" in Figure 10.



Continue in Figure 11.


Continue in Figure 12.


Select an ODI operation such as InvokeStartScen, and customize the web service screen as shown in Figure 13.




In my example above, I created an Action to run an ODI scenario called “PAYROLL”. This scenario is an ODI package that BI users like to invoke once a month. This Action will help us automate this task, so BI users can invoke this scenario when they are ready to process their monthly payroll.

  • In the “Edit Action” screen above, I modified the following parameters:
  • ODI Prompts: I replaced “ODI User” with “Payroll User”, “Scenario Name” with “Payroll Job Name”, and “Value” with “Enter Payroll Month”
  • ODI Values: I left User and Password blank, so the user will enter these values when he or she runs the Action. Also, I provided a syntax for parameter “Enter Payroll Month”: ”YYYY-MM”.
  • Other attributes: I defined some of the values as Fixed or Hidden because BI users should not worry about ODI Repository Names, Scenario Versions, Context, etc.
  • Options: I selected “Options” to customize “Dialog Title”, “Action Help”, and the “Execute Button Text” as shown in Figure 14.



Save your new OBIEE Action.

Now that my OBIEE Action has been fully configured, I decided to create a dashboard in OBIEE that allows users to execute the Action (the ODI scenario called “PAYROLL”), and check the status of the scenario, all in one screen. To do this, I had to bring two ODI tables, SNP_SESSION and SNP_USER, into OBIEE. I modified the OBIEE RPD to model these two tables as shown in Figure 15.


Finally, I created an OBIEE dashboard that includes 4 main areas: (1) an option to execute the Action, (2) when the action is invoked, it will prompt the user for necessary parameters, (3) an option to filter the ODI user(s), and (4) a table that shows the status of the ODI executions. Figure 16 illustrates my final dashboard.


In ODI, I can see the executions of the OBIEE Actions in the ODI Operator as shown in Figure 17:




Conclusion:

ODI Web Services is a great mechanism to execute ODI scenarios and load plans from other enterprise applications such as OBIEE. Now you can configure ODI and OBIEE to invoke the most complex ODI scenario with the click of a button!

For more ODI best practices, visit “Oracle Tuition”.