, we’re going to add a new attribute to an existing dimension table in the Oracle Business Analytics data warehouse. If you’re new to this, this is part of a series of postings on the Oracle BI Applications that includes:
- An Introduction to the Oracle BI Applications
- Installing and Configuring the Oracle BI Applications 7.9.5
- Performing Initial Data Loads into the Oracle BI Apps 7.9.5 Data Warehouse
- Getting Started with Informatica PowerCenter 8.1.1
- So What’s in the Oracle BI Apps Data Warehouse?
- Simple Customization of the Oracle BI Apps Data Warehouse
This is going to involve customizing both the applications data warehouse tables and the load routines that are used to populate them, and so it’s a bit more complicated than yesterday’s posting where we just created a new mapping and plugged it into the DAC framework. Today, we’re going to have to amend a “Source Independent Load” routine that loads data into the warehouse fact table, and the “Source-Dependent Extract” mapping that takes data out of E-Business Suite. As such, it’s a good “real-world” example of what Oracle call in the BI Apps documentation a “Category 1″ customization, with Category 2 ones being the adding of completely new fact and dimension objects to the warehouse either sourced from E-Business Suite or other applications.
In this example, we have a warehouse table called W_ORG_D that holds dimension information for organizations and customers. What we want to to is to add two new columns from E-Business Suite into the table and do so in a way that builds on the existing mappings in the BI Apps load process rather than creating our own from scratch. As such, we’ll be amending the SDE_OrganizationDimension mapping that takes data out of a bunch of E-Business Suite tables and loads it into the W_ORG_DS staging table, and then we’ll be amending the SIL_OrganizationDimension mapping that takes this staging data and copies it into the W_ORG_D dimension table. All of this will be done with copies of the existing mappings so that the standard code is left in place, should we need to revert to it.
The first thing I need to do is create a new folder within the repository to hold my custom mappings.
Then I copy across the mapping that I wish to customize together with the workflow that runs it.
Then, just to make sure everything’s working before I start customizing the code, I run the workflow and make sure it still loads data into the staging table.
So far, so good. The next step is to go into the Target Designer part of Informatica and add the two new columns to the staging table, making sure I change them in the database (using SQL*Plus) as well.
The standard for naming new columns is to add them to the end of the table, after the X_CUSTOM column that’s in all the warehouse tables, and prefix the column names with an X_ so that it’s clear that they are customizations.
Now we’re ready to start the customization. I open up the SDE_OrganizationDimension mapping that I copied to the new folder and right-click on the mapplet at the start to edit it. Mapplets are like pluggable mappings in OWB and play the role of “business components” within the various source system adapters in the BI Apps, they represent encapsulated logic around extracting data from the source system (a rather fancy way to create a view).
I now need to amend this mapplet to bring in the two new columns from my data source. I do this by dragging and dropping the columns from the source tables to the source qualifier, a sort of SQL-based join over various tables that determines the set of data that goes into the mapping.
After I drag the new columns in, I need to update the SQL to make reference to these new columns, taking care that I add the new columns to the correct place in the SQL statement.
I then drag these new source qualifier columns through to the output of the mapplet, so that they are exposed to the calling application.
Now that my mapplet business component has been amended to provide the two new columns, I can close the mapplet editor and amend the calling mapping to make use of these new columns. I do this by adding a new custom transformation to the mapping and then connecting the new columns through this transformation and through to the two new staging table columns, so that the new data is now ready to be loaded into the dimension table once I run the mapping.
So that’s the “source to staging” part of the load process complete. Now I need to repeat the process for the SIL_OrganizationDimension mapping. I start this off by creating a new CUSTOM_SILOS folder within the repository, copying the SIL_OrganizationDimension mapping to it from the SILOS folder and amending the source definition of the W_ORG_DS table to reflect the two new columns.
I then repeat this step this time amending the target warehouse table, changing the definition in the repository and then in the target warehouse, using SQL*Plus.
I now open up the SIL mapping and introduce the new columns from the staging table to the source qualifier.
As before, I then need to edit the SQL statement behind the source qualifier to bring in the new columns.
I then copy the new columns through to the end of the mapping, amending objects as necessary to incorporate the new columns.
Then it’s just a case of putting together the new workflow to reference these two customized mappings, and then running the workflow to make sure it’s all OK. Once you’ve done this and it all runs fine within Informatica, you then need to register the new mappings within the DAC Repository as I did in yesterday’s posting.
So there, for the time being at least, you have it. We’ve taken two existing mappings and, following the recommended Oracle methodology, customized them to bring in new data. Going on from here the next step is to create your own SDE and SIL mappings from scratch to bring new data either in to existing fact and dimension tables (where adapters aren’t available, as with Oracle CRM and the various CRM Analytics modules) or to create entirely new facts and dimensions; but for me now though, that’s enough blogging about BI Apps for a while, and I’ll finish the series off with this final posting in a week or so’s time.
Very good blog..The blogs are very informative.
I have a small doubt. one of our requirement is dac needs to send the mail xml format.
how can we achieve this?
any help is ppreciated.
Thanks
Joe
Premkumar Varadaiah