Historical Data Conversion: Implementing Oracle BI Applications after an ERP upgrade
Many companies embark into the deployment of Oracle BI Applications soon after they go live with a 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 of over-promising and under-delivering.
The biggest such challenge is historical data conversion. The best way to define this challenge is to break it down into 3 seperate areas.
Top 3 Historical Data Conversion Challenges
- How do I bring my data from the new ERP system into business analytics warehouse?
- How do I bring my data from the old ERP system into business analytics warehouse?
- 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 challenges is to use the Oracle out-of-the-box ETL (extract-transform-load) adaptors. Essentially, Oracle provides pre-built ETL adaptors for multiple versions of the major ERP systems such as Oracle E-Business Suite (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 pre-built 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 readers of this article, the following customizations at high level would be required with this approach:
- Assigning different data source number identifiers to the new and the old ERP system.
- Minor changes in the OOTB Informatica code to decouple the configuration files that are shared between the new and the old ERP systems.
- A few changes in DAC code to building a consolidated multi-source execution plan for both the new and the old ERP systems.
How do I integrate my old ERP data with new ERP data such that I can seamlessly report on the combined dataset?
The diagram summarizes the historical Data Conversion approach described in this article. Oracle E-Business Suite (EBS) R11 is used as an example of an old ERP system. Oracle E-Business Suite (EBS) R12 is used as an example for the new ERP system.
Simply bringing-in data from the old and the new ERP system into data warehouse does not solve the problem. In fact, in the Oracle BI Apps world this is among the toughest problem to crack and unfortunately there is no out-of-the-box code available.
Management needs to take a hard look at the available time, budget, and expertise before deciding on the approach to solve challenge #3 listed above. Embarking on a system-wide integration of the old and the new ERP data without doing a thorough analysis of the associated ROI and the risk is not a good idea.
Ask the following questions before deciding on the approach to solve challenge #3 listed above listed above:
- Can my most critical business questions be solved by querying just the old ERP or the new ERP-only dataset?
- What percentage of my critical business requirements demand an integrated view of the data from the old and new ERP systems? [document these requirements]
- A year or two down the line... How many of the requirements identified above can be fulfilled using the data from just the new ERP system?
These questions will help management to articulate more precisely the scope and the ROI associated with challenge #3 listed above.
Generally you will discover that a full system-wide integration of the new ERP and the old ERP data will not be required. This problem-solving exercise will also produce less than a handful of requirements which require integrated view of the old and the new dataset (mainly, in form of reports).
The requirements that are identified usually can be fulfilled cost-effectively by simply creating additional views in the data warehouse. The reports created on these views can then leverage Oracle Business Intelligence Edition (OBIEE) drill-down and navigation features perform more of the detailed analysis that would require only the new or only the old dataset.
Puneet Aggarwal is Consulting Manager at KPI Partners. His project work for KPI has been recognized by industry experts with an Oracle Excellence Award. Before joining KPI, he was member of the Siebel Analytics Core Engineering Development team responsible for the original development of Oracle Human Resource (HR) Analytics. Check out Puneet's blog at KPIPartners.com.