A Cost Effecive Approach To Historical Data Conversion
Many customers embark on the process of deploying Oracle BI
Applications soon after they go live with the new or upgraded ERP system. A successful
BI Apps deployment can bring in numerous benefits – the most notable is the
data driven decision making in the organization. However, the success of the
project depends a lot on having a good understanding of the challenges that are
endemic to such BI deployments. These challenges require business decisions
that must be made at the onset of the project to avoid the situation in which
too much is promised and little is delivered.
The biggest such challenge is Historical Data conversion. The
best way to define it is to break it down into 3 problems.
1.
How do I bring my data from the new ERP system into
business analytics warehouse?
2.
How do I bring my data from the old ERP system
into business analytics warehouse?
3.
How do I integrate my old ERP data with new ERP
data such that I can seamlessly report on the combined dataset?
A cost –effective and low risk approach to solve the first
two problems is to use the Oracle Out of the Box ETL adaptors. Essentially, Oracle
provides prebuilt ETL adaptors for multiple versions of the ERP systems such as
EBS R12, EBS R11, PeopleSoft, etc. Oracle also provides a universal adaptor that
can be used to bring data into the warehouse from the source systems for which
there is no prebuilt ETL adaptor available. These adaptors with some
customizations can be used to bring in the data set from both the new and the
old ERP systems.
For the benefit of more technical audience, the following customizations
at high level are required, should this approach is followed.
1.
Assigning different data source number identifiers
to the new and the old ERP system.
2.
Minor changes in the OOTB Informatica code to
decouple the configuration files that are shared between the new and the old
ERP systems.
3.
A few changes in DAC code to building a
consolidated multi-source execution plan for both the new and the old ERP
systems.
However, simply bringing in data from the old and the new
ERP system into data warehouse does not solve the problem 3 - How do I integrate my old ERP data with new
ERP data such that I can seamlessly report on the combined dataset?
In fact, in Oracle BI Apps world this is among the toughest
problem to crack and unfortunately, there is no OOTB code available. Here the management needs to take a hard look at
the available time, budget, and expertise before deciding on the approach to
solve the problem 3. Simply, embarking on the system wide integration of the
old and the new ERP data without doing a thorough analysis of the associated
ROI and the risk may not be a good idea. Here one needs to ask the following
questions before deciding on the approach to solve the problem 3.
1.
Can my most business questions be solved by
querying the old ERP only or the new ERP only dataset?
2.
What percentage of my critical business
requirements demands an integrated view of the data from the old and new ERP
systems? List these requirements.
3.
A year or two down the line, how many of requirements
identified in the step 2 can be fulfilled using the data only from the new ERP
system?
These questions will help management to articulate more precisely
the scope and the ROI associated with Problem 3.
Generally, it turns out that the full system wide
integration of the new ERP and the old ERP data is not required. This exercise
also produces less than a handful of requirements (mainly, in form of reports) which
require integrated view of the old and the new dataset. These requirements a can be fulfilled cost effectively
by creating additional views in the data warehouse. The reports created on these views can then
leverage OBIEE drill down and navigation feature do to more detailed analysis that
requires only the new or only the old dataset.
The following diagram summarizes the approach described in
here using EBS R11 an example for the old ERP system and EBS R12 as an example
for the new ERP system.
A couple of questions that are left unanswered in this blog
are :
1)
Why integrating datasets from the new and the old ERP systems in data
warehouse is so hard?
2)
What do I do, if I absolutely need to have the full
system wide or say the whole module wide integration of the old and the new ERP
data set?
These would be the topics of my
next blog.
Thanks
Puneet Aggarwal
Consulting Manager,
KPI Partners.
No comments:
Post a Comment