Installing BI Apps 11.1.1.8.1: Part 3 – Loading Data

This is the third part of my example walk-through of performing an installation of the newly released Oracle BI Applications 11.1.1.8.1.

I’m performing a completely fresh installation on our Exalytics/Exadata servers using an E-Business Suite 12.1.3 database as the data source.

In part 1 (http://paulcannon-bi.blogspot.co.uk/2014/06/installing-bi-apps-111181-part-1-pre.html) I went through the pre-requisites required before BI Apps can be installed, including installing OBIEE, ODI and creating the warehouse and repositories using the RCU.

In Part 2 (http://paulcannon-bi.blogspot.co.uk/2014/06/installing-bi-apps-111181-part-2.html) I covered the installation of BI Applications itself, including its configuration and integration with OBIEE and ODI.

In Part 3 below I will demonstrate a simple example of load data into the warehouse and viewing it in OBIEE.

So having now performed Steps 1 and 2 I now have BI Apps fully installed and ready to use.

The loading of data is all controlled from the BI Applications Configuration Manager (BIACM).  So let’s start by logging in with the biapps administrator user-id & password created in step 2.

Open a browser and enter the following URL:

http://<host>.<port>/biacm

e.g. http://biapps_server:9704/biacm

To load data into the warehouse you need to create and run a Load Plan. 

Load Plans define what data is to be loaded. You can have one plan that loads the entire warehouse or many small plans which load specific parts. These plans can then be run manually or scheduled and be run as full or incremental loads. 

I’m going to create a load plan to load some of the HR data.

From the Tasks pane in the ACM, click on ‘Manage Load Plans’:

As this is the first time I’ve done this, there are no plans available. 

In the Load Plans toolbar click on the add (green cross) icon.

In the first screen to define a load plan you enter a name and description followed by the type of load – this can be a full end to end load (SDE, SIL and PLP) or just part loads (such as just the SDE extract) and finally the source for the load process.

Leave the Load Plan Type at the default ‘Source Extract and Load (SDE, SIL and PLP)’.

I’ve only setup the one data source in my environment – VIS, the E-Business Suite “Vision” demo database.

Then click Next (or the ‘Select Fact Groups’ link):

I could now just select the ensure Oracle Human Resource Analytics fact group, but I don’t want all modules with HR, so I’ve expanded it and select five specific modules.  You could if you want expand each module and select individual fact groups.

By clicking on the ‘Selected Fact Groups’ tab you can see a summary of what has been selected, along with any dependant Fact Groups:

Now the Load Plan has been defined click Save.

Before a Load Plan can be run it needs to be generated – which creates the Plan in ODI, setting up the scehdule of tasks required, ensuring all individual elements are processed in the correct order, creating the data load paramater list etc…

You can do this in one of two ways. You could click the Generate button in the above screen instead of Save (it will save automatically), or you can run the generation manaully as follows.

Back in the Manage Load Plans screen the new Load Plan appears:

In the Generation Status field there is the red-recycle icon indicating that the Plan has not yet been generated.

Because of this the icons in the task bar for running the Plan are greyed out. The only option available is to Generate the Plan.

So click this icon and the Generation will begin.

The Generation Status field will now change to the ‘starting ‘ icon:

Clicking the ‘blue recycle arrows’ refresh icon after a few seconds will see this switch to the ‘In Progress’ icon:

You must now wait for this to finish. Click the refresh icon regularly to refresh the display.  How long it takes to generate is of course dependant on how many fact groups are in the plan, in my case it only took a couple of minutes.

Once a Plan has been generated, the name field turns into a link which you can click on to access the ODI console to see the details of the Plan:

You’ll need to login with the BI Apps administrator user-id & password when the ODI console when the login screen appears:

Once in you’ll be able to see the details of the plan, including all of the steps (over 3,500 for my HR Load Plan), along with their current status, the parameter variable settings and details of executions (which so far there hasn’t been any).

You can visit this screen at any time, including during Load runs to see the status of the steps and how far the load has progressed.

For now though we need to run the load. Returning back to the Manage Load Plan screens we can run it by selecting the Load Plan and clicking the ‘Execute’ icon:

You need to specify the ODI detail to run the plan under:

Select the “Global” context, your local agent for ODI (which will be OracleIDAgent as setup during the BI Apps configuration) and the ODI work repository.  You can change the log level as required, the default 6 is fine. Click OK to start the load.

Use the refresh icon to update the Manage Load Plans screen to see the Plan is executing:

You can now return to the ODI Console (by clicking on the Load Plan name again) and view the current status of the load.

In the ‘Browse’ tab click the blue refresh icon then expand ‘Runtime’, then  ‘Sessions/Load Plan Executions’, then ‘Load Plan Executions’:

The current execution will appear with the executing status icon. Now click on this to select it and click the glasses ‘view’ icon in the menu bar above to see its details in the main pane:

For each individual step we can see the current status, the start, end and duration timings. If you scroll to the right you’ll also see  row/update/insert/delete counts and any error messages.

By scrolling down you can see how far the load has progressed so far:

So my load is currently less than 10% complete (there being over 3500 steps), with multiple steps currently being worked on.

Load Plan executions can also be viewed from the summary ‘Overview’ screen, which is probably the easiest place to keep an eye on it whilst it runs:

After an hour and a half the status of the job changed to failed:

To find out why click on the white-on-red error icon in the execution status field. This takes you straight to the error in the ODI Console:

The error messages are a list of failures, all which originated from the bottom error:

ODI-1228: Task TABLE_MAINT_PROC

(Procedure) fails on the target ORACLE connection BIAPPS_DW.

Caused By: java.sql.SQLException: ORA-20000: Error creating

Index/Constraint :W_EMPLOYEE_DS_U1 => ORA-01452: cannot CREATE UNIQUE

 INDEX; duplicate keys found

This error is actually caused by some dodgy data in the E-Business suite Vision demo database – there are some duplicate employee records. I’m not going to go through fixing this – that’s way beyond the scope of this blog! – I deliberately showed the error which I knew would happen just to demonstrate what happens with a failure and how a plan can be restarted.

Restarting a Plan can obviously only be performed if you are able to fix the problem without altering the ETL mappings – often in the early stages of deploying BI Apps errors occur because of customisations in the source system which BI Apps isn’t aware of, so a development phase is required to replicate the customisations in the BI Apps ETL code.

However in this case the error is caused by data and we can ‘fix’ the data and restart the Plan – allowing the plan to pick up where the error happened and carry on rather than starting the whole load from scratch.

As these particular errors are known issues with the data in the Vision database the following sql code can be run in the warehouse to fix the data that has been loaded already. Connect to the warehouse database via sql*plus or your preferred development tool:

DELETE FROM w_employee_ds WHERE integration_id='PER~2901' AND datasource_num_id=1;

DELETE FROM w_position_ds WHERE integration_id='PER~6272' AND datasource_num_id=1;

ALTER TABLE W_MCAL_PERIOD_D  MODIFY (ROW_WID NULL);

COMMIT;

Now back in the ODI Console screen, in the Browse tab expand ‘Runtime’ -> ‘Sessions/Load Plan Executions’ -> ‘Load Plan Executions’ and finally click on the failed Plan:

When you do this the ‘restart’ icon un-greys in the menu bar:

So click it and then select ‘Restart’ in the Execution dialog box that appears:

 Interestingly the log level changes in this screen – when we first ran the Plan above it defaulted to log level 6 and we left it at that, now the log level defaults to level 5 – I don’t know why!...change it if you want, but level 5 is probably fine.

Click passed the successfully restarted message. Oddly you are now taken back to the Execution dialog box above:

However the plan has restarted and you can cancel out of this screen.

In the browse window click the refresh icon and you’ll see a second execution has appeared:

This second Plan is simply picking up from where the first left off, i.e. skipping the successful steps and carrying on with the remaining.

If you return to the Manage Load Plans screen you’ll see the Plan execution status has returned to executing:

And then only takes another 15 minutes to finish the outstanding steps:

If you want to see exactly what the load process has done you can return to the ODI Console and view the execution, against each step is displayed the number of rows inserts, updated, delete etc…

In this plan the PLP_WORKFORCEEVENTFACT_MONTH inserted 589263 records to the table (W_WRKFC_EVT_MONTH_F) which now contains 589263 records. If we look in the database, sure enough that is what we see:

Ok, so we’ve loaded data, but we don’t load it for the fun of it (well ok, some of us do…),  we need to see the data in reports. Logging into OBIEE and viewing the HR dashboards nicely displays the data that was loaded:

So there we have it, we’ve performed a data load using the BI Applications Control Manager and can see the data in OBIEE. This is a very simply example of using BI Apps, I’ve barely scratched the surface of what would be required for a real-life installation, but this demonstrates some of the main steps required.

Enjoy!