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

August 7th, 2009 by

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.

Custcats-1

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

1) Make sure your customizations survive upgrades to the mapping repository,
2) Follow the Oracle methodology as closely as possible, so that Oracle (and others) can work out what you’ve done, and
3) 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:

Version1

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

Version2

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.

Customization1

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.

Generate Ddl2

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.

Customization2

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.

Customization3

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

Customization4

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.

Customization5

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.

Customization6

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.

Comments

  1. Krishna Prasad Says:

    Hi Mark,

    Firstly Thank you providing us with such valuable information.

    Related to Category 2 changes like adding a new fact table. How would we be adding it to oracle bi configuration manager for loading it ?

    Lets say i have a created a new fact table at AP lines level which needs to be loaded along with AP financials.

    How can i achieve this ?

  2. Mark Rittman Says:

    Krishna,

    Are you actually using this release of the BI Apps? It was a one-off, no further releases in this stream will be made, and the sources and targets it supports are very limited. We haven’t used it past this technology evaluation, so if you are deploying you’ll need to get the details you’re looking from the docs, unfortunately.

    Mark

  3. Krishna Prasad Says:

    Hi Mark,

    Thank you for the information. I was using this release of BI Apps to learn the ODI techonology. Implementing all the various types of changes we would be required to do in the BI Apps with ODI technology. I will try to check from documents.

  4. Mark Rittman Says:

    This would be interesting, but it’s not 100% how ODI will be implemented in the upcoming BI Apps 11g, and the version of ODI that this system uses is pretty out of date now, just so you’re aware.

  5. Krishna Prasad Says:

    Yeah ODI10g is outdated.. but BI Apps with ODI is not available with 11g. I tried to upgrade the existing 10g repository to 11g but had some issues.

    Any info when we can expect to have another apps release with ODI 11g ?

  6. Mark Rittman Says:

    Krishna,

    See today’s post on the BI Apps roadmap : http://www.rittmanmead.com/2012/07/looking-towards-the-bi-apps-11g-part-1-bi-apps-11g-product-roadmap/

    Mark

  7. Krishna Says:

    Mark,

    Thanks for these posts.. They are really wonderful.

    For BI APPS 7.9.5.2 while I was trying to do data loading through oracle configurations manager using weblogic server, I was specifically loading HR Payroll. But when the load starts, it fails at po line type dimension. The issue is that the source tables are not replaced(instead they are still the EBS names ) in the query generated in the integration module which loads to invalid identifier error. I did not make changes to KM’s. Did get the same issues earlier. Do u know any resolution.

  8. prateek Says:

    HI, this is just a query not a post
    I’ve been looking for the info as to in which snp_* tables is the info regarding tmp intf (inline view/yellow intf) is stored my basic objective is to find src tab/query and subsequent intf for a yellow intf in question kindly help

    thanks
    P

Website Design & Build: tymedia.co.uk