27 February 2015 / Blog
How To Plan For A Successful Data Conversion In Your Move to Fusion
By: Jon Wakefield
Jon Wakefield is a Senior Consultant for the Oracle Line of Business at Velocity and has over 16 years of functional and technical experience in Oracle products, including PeopleSoft, Fusion, and Taleo. As part of Velocity’s Professional Services team, Jon is responsible for new development and implementation of Oracle solutions. He can be reached at firstname.lastname@example.org
Many organizations are moving to Oracle Fusion cloud services, taking advantage of the enhanced levels of support and overall reduction in cost of ownership the product offers. If your organization is preparing to implement Fusion (click here for some helpful tips on making that decision), there are, of course, a number of factors to consider and plan for accordingly. In this post, I will focus on one of the big ones, the impact of which can often be underestimated: Oracle data conversion.
There's no way around it. No matter which system you're moving away from (PeopleSoft, Oracle EBS, etc.), exporting the data you need and loading it to Fusion will be a tedious and time-consuming effort (just as any data conversion effort from one system to another would be). However, with proper planning it can be executed logically and methodically, minimizing potential roadblocks and the risk of unpleasant surprises.
File Based Loader
Because Fusion is a SaaS product, you will not have access to update any of the tables yourself and instead must leverage the data conversion tools Oracle provides for populating Fusion with your organization’s data. The primary tool you’ll work with is File-Based Loader (FBL), and it’s critical to take the time upfront to understand how the tool works and which business objects it supports (click here for Oracle’s user guide and the list of objects).
Loading Data Into Oracle Fusion
The key to successfully loading your data into Fusion is in how you extract that data from your old system. FBL requires you to create a series of pipe-delimited files (.dat or .csv) containing all the data you want loaded to Fusion, formatted according to Oracle’s specifications (click here for a spreadsheet of every supported field and its format). FBL then imports those files and populates the Fusion tables accordingly with the values you included for each field. You must design a process to carefully populate the files you deem necessary, making sure that each field value is formatted and positioned correctly. If you do that, you will greatly reduce your potential load errors and remove a lot of risk and stress from the data conversion process.
You can use any number of options to achieve this, but to get you started, I’ll provide three straightforward approaches that may work well for you. They are PeopleSoft-oriented, as that was my primary expertise before learning Fusion, but the basic concepts driving each are applicable to other ERP systems as well.
3 Data Conversion Strategy Options for Oracle Fusion Data Loading
1. SQR. In PeopleSoft, an SQR (or a series of them) could be built to extract and write the data to FBL files. This approach offers a lot of flexibility in how the data is extracted and formatted, which may be necessary for organizations planning to convert a large amount of complex data, and especially if historical data is to be included. (For the record, I recommend minimizing converting historical data as much as possible.) SQR also allows for the ability to create the correct type of files, minimizing the amount of manual manipulation that would need to occur. One downside to the SQR approach is the overhead involved in creating one or more new programs that need to be maintained and migrated across various code trees. Keep in mind that you will likely make frequent modifications to the SQRs as you are testing, troubleshooting and running them ad-hoc, often for variously filtered data sets. If the complexity level of your data conversion effort falls on the lower end, you may want to consider a simpler approach.
Which brings me to …
2. Queries. The PeopleSoft Query tool can extract your data as well. With this approach, you would need to create a series of queries (one or more per data file) and then download the results to Excel. The advantages are that (a) queries are easy to maintain, (b) are self-contained within PeopleSoft, and (c) can be written by either technical or functional analysts. The disadvantages are that (a) you would have to perform some manual file manipulation to download the query results to Excel and then convert them to data files for FBL, and (b) Query isn't as powerful as SQR and may not be able to extract all the data you need in the way you need it, especially if your data is complex and includes history.
If you don’t think Query can accommodate your organization’s conversion effort and you don’t want to create SQRs, a good alternative could be …
3. SQL Scripts. A series of scripts—at least one per data file—could be written to retrieve your data and format it appropriately. Some manual file manipulation will be required with this approach as well, but it offers greater flexibility than Query because you can write the SQL yourself, allowing for more complex data extractions. Also, you could simplify your scripts by creating PeopleSoft views to handle the main part of the selects and then write your scripts to pull from those views and apply whatever filters you may need on the fly in the scripts. This allows you to quickly create files for any subset of data you may need at any given time. A client I'm currently working with used this approach and it served them well.
So, I offer these options to help you think about how to best approach your Oracle Fusion data conversion effort. Every organization is different. o matter which option you choose—whether it’s one of these three, some combination of them, or something else entirely—you will greatly improve your chances for a successful conversion if you plan ahead and then execute a structured approach to extracting your data to files compatible with FBL. And by extension, as you minimize potential risk and reduce time spent on troubleshooting conversion issues when it could be better spent on other areas (such as configuration and writing interfaces), you will also greatly improve your chances for a successful Fusion implementation overall.