’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:
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:
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.
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.
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.
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.
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.
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.
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.
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:
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.
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.
Good post ;)
Actually when I was installing and configuring BI Apps,I did have encountered many troubles.
eg.code page problem for non-english os(eg.Chinese GBK);from time to time I have turn back to informatica to check the workflows because the task failed in DAC…A big head ;)
I do believe that for the standard source system,eg Oracle EBS,Siebel CRM,there may not be so many configuration to do,but it is not true.
Im really interested about the CUSTOMIZATION of BI Apps,it seems more and more troublesome.
Looking forward such subjects in a short future;)
Tina
Excellent articles!!.
Fantastic articles. Great that you’ve put some timings in – on my first time load I was wondering whether something had died during the “Build” but it got there in the end.
One thing I still haven’t found out, but would like to know is approximate size of the source vs target databases. E.g. say you have 10Gb of Receivables EBS11i data, how big is the DataWarehouse DB going to be? Oracle to Oracle of course. Of course is dependent on data and summarization but assuming defaults.
Gareth
We have a problem and we need your advice. We are using EBS R12 as our data source.
When we are running initial ETL load from DAC, we get stuck in the first task which is loading data from source flat files. the problem is that we didn’t supply any value for that parameter in the data source systems and we didn’t create a connection to flat files in the workflow manager either. So how we set the connection to flat files?
Thanks in advance
Kumar
Version 7.9.6.1 Section 4.11.2 Importing Metadata into the DAC Repository).
We missed to select R12 application ,
so the R12 container is not visible in DAC.