July 31st, 2009 by Mark Rittman
If you’ve been wondering why I’ve not been posting to the blog recently, it’s because I’ve been grappling with the new release of the Oracle BI Applications that uses Oracle Data Integrator as the ETL engine. This is an interesting release as it does away with Informatica and even the DAC, but it uses the same approach of SDE and SIL mappings but mapped on to ODI and a new tool called the Confguration Manager. So how does it work and how does it compare to the 7.9.5 and 7.9.6 releases of the BI Apps?
First of all it’s worth setting out what this 22.214.171.124 release does, and what it doesn’t do. First of all, the sources and targets that it supports are only a small subset of those covered by the 7.9.5 and 7.9.6 releases. The only source supported is Oracle E-Business Suite 11.5.10 which necessarily is only on the Oracle Database, and the only target supported is Oracle 10gR2. Within the BI Apps, the only modules that are supported are Financials, Human Resources, Supply Chain & Order Management, and Procurement and Spend and moreover, as this is the 126.96.36.199 release – a branch of the 7.9.5 main release – you don’t get any of the new subject areas that are in the 7.9.6 release which continues to use Informatica. One other important thing to bear in mind is that there is no guarantee that there’ll be a 7.9.6.x release using ODI, and therefore you might need to wait until the 11g BI Apps release before you can take advantage of areas like Projects, a wait that may well be twelve to eighteen months or so (assuming that BI EE 11g is out in the first half of 2010). Finally, if you’re a customer looking to implement the BI Apps, there are going to be far fewer people out there with 188.8.131.52 experience than with mainstream BI Apps experience, and so you’re going to really have to want to use ODI with the BI Apps to go for this version – for most customers I’d still recommend going down the Informatica route at least until the 11g release.
If you take a look at the architecture of the 184.108.40.206 release of the BI Apps, you can see that pre-built ODI interfaces, packages and transformations take the place of the equivalent mappings, workflows and transformations that come with the 7.9.5 and 7.9.6 version of the BI Apps. Knowledge Modules within ODI handle the extracts, loading and transformation of data between source and target and the BI Apps ships with a number of custom knowledge modules to replicate, for example, the mapplets feature in Informatica.
The stated aims for Oracle in this ODI-backed release are firstly, to speed up data loading through the use of database set-based transformations; secondly, to make deployment quicker and simpler, and thirdly, to make all the technologies more tightly integrated. For the first objective, we’ll have to wait and see until we can run the same execution plans using both Informatica and the DAC, and I’m hoping to have these test results in place for my session on this release at the forthcoming Open World in San Francisco, and for our Training Days event just after this in London. Deployment and Integration is however better (at least once you work out how it all installs, it took me about six attempts before I got it all working).
If you look at this topology diagram from the Oracle documentation, there are three servers involved in the Informatica-driven 7.9.5 and 7.9.6 architecture, made up of the repository and integration servers for Informatica, and the DAC server that controls the Informatica workflows. There are also Informatica and DAC clients which are used for customizing mappings, and for controlling and monitoring ETL execution plans respectively.
If you take a look at the corresponding topology diagram for 220.127.116.11, the number of servers has gone down to one, as has the number of clients.
Now I think this is slightly disingenuous as you typically use two ODI agents – one for workflow, one for executing the packages – and there are also two clients, the ODI client and also the Configuration Manager which takes the place of the DAC. But it’s still definately the case that the 18.104.22.168 release seems to have less “moving parts”, there is no DAC server (with all of it’s technologies to learn) controlling Informatica (another, non-Oracle technology to learn) and there’s no DAC repository as all of it’s functionality has now been moved into the ODI repository or into a set of tables used by the Configuration Manager. It’s not quite the simplification that Oracle claim but if you’re already an Oracle shop and use ODI, it’s just another set of ODI projects and models that you need to run.
So once you’ve got it all installed, what does it all look like? Well once you’re in the ODI Designer application, the Projects view shows you all of the PLP, SDE, SIL and so on folders that correspond to the various adapters in the Informatica repository, except of course they only cover EBS 11.5.10 as a source.
You can also see the area where execution plans are stored; the concept around these is a little different to how they worked in the DAC before, but you still select subject areas to load and let the Configuration Manager (the DAC replacement) turn these into calls to the equivalent to Informatica workflows in this release, ODI “packages”.
The ODI Designer application also holds the data definitions for the source and target databases, and you use this tool to create the data warehouse tables in the target database before you commence your first load. If you’ve read my posting on adding partitioning to the 7.9.5 release of the Oracle BI Applications you’ll be interested to note that the 22.214.171.124 release supports range-based partitioning on tables (though not compression, though this should work if you manually apply it after the initial table creation), presumably creating bitmap indexes “local” rather than the default “global”.
So with the DAC gone, what’s it’s replacement? Well much of the functionality has moved into the various ODI client tools and the DAC repository itself is now just part of the ODI project metadata, but you still need a user-friendly tool to select and then run the data warehouse load routines and the tool that does this is called the Configuration Manager. This is a web (ADF)-based application that runs on Weblogic that currently you need to separately install after the main BI Apps installation (I understand in future this will be automatic, at least for Windows platforms). This has a subset of the functionality of the DAC Console, allowing you for example to construct and then run execution plans, however it doesn’t have the data lineage and metadata exploration features that I found useful in the DAC Console, presumably this functionality will be (or already has been) replaced by packaged reports that run against the ODI repository for use with Oracle BI EE.
The Configuration Manager interface is quite easy to use, once you log in you are presented with a list of options where you can define parameters, build and then execute an execution plan, and then monitor the execution of those plans.
Creating execution plans is a simpler process than with the DAC, as you don’t need to define and populate the various parameters that are passed between the DAC Server and the Informatica Server, and there is no “create orderered tasks” step either that sequences all of the mappings that are required to load your chosen subject areas. In the screenshot below, you can see where I’m choosing the various subject areas that I want to see in my execution plan.
In future postings in this series, I’ll look in more detail at the technical differences under the covers in this release, how you go about doing the initial load and how the customization process works. For now though, you can download BI Apps 126.96.36.199 from OTN, and if you’re going to use it you’ll need a full license copy of ODI 10.1.3.5 as well in order to use the mappings.