Performing Initial Data Loads into the Oracle BI Apps 7.9.5 Data Warehouse

Mark Rittman

Jul 6, 2008 • 4 min read

In a couple of recent articles, (here, here) I've given an overview of the Oracle BI Applications and stepped through the installation and configuration of Oracle BI Applications Fusion Edition (7.9.5). In this posting I'll be walking through the initial load of the Oracle Business Analytics Warehouse, using Order Management data taken from Oracle E-Business Suite 11.5.10.
The first step in loading data into the BI Apps data warehouse is to log in to the DAC Console and create a new Source System Container to hold your DAC Repository objects. When you set up the DAC repository you import a number of source system containers into it, these contain details of the subject areas, mappings and so on that relate to a particular source system and release version. In my case, I imported the Universal container and the Oracle 11.5.10 container as this was my only data source, the new container I set up was therefore based on the 11.5.10 one.
Once you define your new container, you can take a look at the subject areas that it supports:

3-Subject Areas

Each subject area shows the fact tables that it contains, whilst the Tables, Task Groups and Tasks folders below the subject areas set out the ETL tasks that are available for this data source.

5-Task Groups

The DAC Console also shows the various predefined Execution Plans that you can use to load data into the warehouse. The execution plans roughly correspond to one or more subject areas and can load the whole, or a part, of the source data into the warehouse.

7-Execution Plans

In my case though I want to create my own, new, execution plan to bring in a selection of the subject areas. I create the new execution plan and set it to load all data into the warehouse, as opposed to incrementally extract and incrementally load, which is the usual way you'd refresh the warehouse.

8-Create Execution Plan

Now I select the subject areas that I want to include in the plan. Not all of the subject areas (at least in my installation) seem to work properly, I suspect I need to bring in more Source System Containers for some of the areas to work properly. In my case I selected a number of subject areas around sales and orders using the subject area selection dialog.

10-Select Subject Areas

Once your selection is confirmed you then need to generate a list of the relevant parameters, so that you can enter the connection details for the data warehouse, your E-Business Suite database and the filesystem location for the setup and reference files.

11-Enter Parameters

Now it's time to take the list of subject areas and use it to generate the list of actual ETL tasks and task groups. This stage can take up to 10 or 20 minutes to complete for the selection I've made, on other runs its taken an hour or so when I've got several source systems (Siebel, Oracle 11.5.9, Peoplesoft etc) to generate mapping requests for.

12-Build Ordered Tasks

And that's it. Now your mappings have been requested and subject areas selected, all it needs now is for you to run the list of mappings and wait for it to complete - for my selection of mappings the ETL took around four hours to complete.

15-Tasks Executing

Once everything's run, you're almost at the point where you can start analyzing your data. Before you do that though, you firstly need to set a few things up at the server end so that you can see your data. The first involves setting the values for a few static variables in the BI Administrator tool, to tell the BI Server what user account and TNSNAMES entry to use when connecting to your data warehouse, like this:

17-Set Variables

and then you have to unzip the Web Catalog files, that contain your dashboards and reports, to the Web Catalog directory under /OracleBIData/web/catalog. Once you've done this, you can log in as a user who has access to, in this case, some order management reports and take a look at the data you've just loaded in.

18-Dashboard

So now that we've done an initial load and checked that the dashboard works, the next step (at least for me, as a developer) is to start thinking about how we can customize the BI Apps data warehouse and bring both new columns and rows into existing warehouse structures, and how we can bring entirely new staging tables, facts and dimensions into the warehouse data model. Before we can do this though, we need to know a bit more about Informatica, and that's the subject of the next blog posting in this series.

Sign up for more like this.