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 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!
