A First Look at the BI Apps 7.9.5.2 Part 4: Customization

In the first three parts of this series, I gave an overview of the new 7.9.5.2 release of the Oracle BI Applications, went through how the technology differs now that it uses Oracle Data Integrator rather than Informatica, and then went through what you need to do to perform an initial load and debug failed mappings. In this final posting, I'll look at what's involved in performing a customization.

Customizations are necessary in the BI Applications when you need to bring in some new data into the data warehouse. This might be because the predefined mappings miss one of the columns you are interested in, or it might be because at the moment it filters out some of the rows you are interested in. You can even add new facts and dimensions to the BI Applications data warehouse, using and extending the framework that comes with the prebuilt content to add your own content. Now as the 7.9.5.2 release uses Oracle Data Integrator and a new Web-based tool called Configuration Manager to replace Informatica and the DAC, the means by which you effect the customizations is a bit different, but the type of customization that you make is divided into the same categories.

Category 1 customization are where you need to add a new column to a mapping, to bring in source system data that was missed out by the predefined mappings. Category 2 customizations are where you want to add new facts or dimensions, whilst category 3 customizations involve bringing in new rows of data from non-packaged sources (I don't know why bringing in new rows from sources with adapters is not also Category 3, but there you go.)

Now if you followed my postings on this topic for previous versions of the BI Apps, you'll have picked up on the three most important considerations when customizing the BI Apps, namely

Make sure your customizations survive upgrades to the mapping repository,
Follow the Oracle methodology as closely as possible, so that Oracle (and others) can work out what you've done, and
If you change something that's "out of the box", make sure you keep a copy of what it was originally.

Now with previous versions of the BI Apps that use Informatica, this was accomplished by only customizing copies of prebuild mappings and putting them into "custom" folders, and by either following the "safe path" through mappings for Category 1 customizations and replicating the functionality of existing mappings when creating your own ones. With this ODI release of the BI Apps, following the methodology is still more or less the same, but the way that you preserve changes through upgrades is a bit different.

Whereas in Informatica versions of the BI Apps you firstly created copies of the SDE_ORA_xxx and SILOS folders and then copied the original mappings into them before customizing them, with ODI you create "versions" of the mappings which are then stored in the repository so that you can recall them later on. To take the same example that I used in this previous posting on the BI Apps 7.9.5 where we are adding columns to the W_ORG_D and W_ORG_DS table, the mapping we need to customize is called SDE_ORA_OrganizationDimension_Customer which we version by selecting this from the right-click menu in the ODI Designer application:

This then presents you with a dialog, where you can enter the version number.

This much like the versioning (snapshot) feature you get in OWB, except for some reason it doesn't take ten minutes to run ;-)

Then, assuming you've got some new fields to add into your warehouse tables,, you should version the Model folder that contains all of the warehouse table definitions (you can only version the folder, not the individual table), then go and add the columns into the table that you need to add.

Then, still within the Model view of the ODI Designer application, you right-click on the BI Applications model folder and choose the Generate DDL option. Then after selecting this table from the sync list, ODI deploys the additional columns to the warehouse table.

This is a big improvement over the releases that use Informatica, as you need to change the table definition in both of those tools rather than just the one, the ODI Designer application.

Next it's time time to edit the interfaces. Mappings in the ODI release of the BI Apps consist of a folder, containing a package and a number of interfaces to implement full and incremental loads. Some of these interfaces use a new knowledge module that implements inline views, that are then used to replicate the mapplet and source qualifier features in Informatica so that access to the underlying EBS tables can be abstracted into a simple interface.

We then edit the SDE_ORA_OrganizationDimension_Customer.SQ_BCI_CUSTOMERS interface as it's this one that will map onto the relevant EBS tables. The first step in editing it is to add new columns to the interface target datastore to hold the new columns.

Then you link into this new column the source column that you want to bring through.

After this the process is very similar to when you are using Informatica and the DAC - you feed the new column across from the SQ_BCI_ interface into the main interface that loads the dimension staging table, then you repeat this for the SIL mapping that actually loads the dimension. It's just a different mapping approach and set of tools to use.

Category 2 customizations, where you define and then load your own facts and dimensions, are of course a bit trickier. Like BI Apps customizations that use Informatica and the DAC, you need to add DATASOURCE_NUM_ID and INTEGRATION_ID columns to your dimension staging tables, and these plus ROW_WID and ETL_PROD_WID to the SIL mappings.

In addition, the same sorts of variables, such as IS_INCREMENTAL, TYPE2_FLG and so on, are passed to the master package for each set of interfaces but this time, instead of you implementing these features using packaged Informatica transformations, most of them are implemented through features of the model datastores (SCD2 type 2 handling, for example) and the new BI Apps-specific knowledge modules that ship with this release. So again logically, the process is the same as before, but there's a whole new set of tools and techniques to learn if you want to do this.

So what happens then when a new release of the BI Apps comes along? This is where versions come in. What you need to do is to make sure you create a new version of this interface before you apply the update, then apply the update and create a new version, and then compare the two versions.

By doing this you can see what's changed in this new release (presumably your customization is no longer there), and so you can either restore your old version, or go and apply the customization again if there's something you want to preserve in the new release of the interface. Of course it'd be nice if you could automatically reapply just your customization but for now, this is the best approach.

So there you go. In four parts, there's an introduction to the BI Apps 7.9.5.2, a look at the underlying technology, steps on how to do the first load and now a look at customizations. If you're interested in learning more about this release, come along to my session at this year's Open World, or even better come along to our Training Days event just after in London where we'll cover this release in more detail.