SAP Business ONE Implementation — Save 50%
Bring the power of SAP Enterprise Resource Planning to your small-midsize business with SAP Business ONE using this book and eBook
In this article by Wolfgang Niefert, you will learn about the Advanced data migration tools, xFusion Studio in particular. You will also get to know a few tips and recommendations which you could use in your own project.
Advanced data migration tools: xFusion Studio
For our own projects, we have adopted a tool called xFusion. Using this tool, you gain flexibility and are able to reuse migration settings for specific project environments. The tool provides connectivity to directly extract data from applications (including QuickBooks and Peachtree). In addition, it also supports building rules for data profiling, validation, and conversions. For example, our project team participated in the development of the template for the Peachtree interface. We configured the mappings from Peachtree, and connected the data with the right fields in SAP. This was then saved as a migration template. Therefore, it would be easy and straightforward to migrate data from Peachtree to SAP in any future projects.
xFusion packs save migration knowledge
Based on the concept of establishing templates for migrations, xFusion provides preconfigured templates for the SAP Business ONE application. In xFusion, templates are called xFusion packs. Please note that these preconfigured packs may include master data packs, and also xFusion packs for transaction data. The following xFusion packs are provided for an SAP Business ONE migration:
- Business partner
- Inventory and production
- Marketing documents and receipts
You can see that the packs are also grouped by business object. For example, you have a group of xFusion packs for inventory and production. You can open the pack and find a group of xFusion files that contain the configuration information. If you open the inventory and production pack, a list of folders will be revealed. Each folder has a set of Excel templates and xFusion fi les (seen in the following screenshot). An xFusion pack essentially incorporates the configuration and data manipulation procedures required to bring data from a source into SAP. The source settings can be saved in xFusion packs so that you can reuse the knowledge with regards to data manipulation and formatting.
Data "massaging" using SQL
The key for the migration procedure is the capability to do data massaging in order to adjust formats and columns, in a step-by-step manner, based on requirements. Data manipulation is not done programmatically, but rather via a step-by-step process, where each step uses SQL statements to verify and format data. The entire process is represented visually, and thereby documents the steps required. This makes it easy to adjust settings and fine-tune them.
The following applications are supported and can, therefore, be used as a source for an SAP migration: (They are existing xFusion packs)
- SAP Business ONE
- Sage ACT!
- SAP BW
- Microsoft Dynamics CRM
The following is a list of supported databases:
- OLE DB
- SQL Server
Working with xFusion
The workflow in xFusion starts when you open an existing xFusion pack, or create a new one. In this example, an xFusion pack for business partner migration was opened. You can see the graphical representation of the migration process in the main window (in the following screenshot). Each icon in the graphic representation represents a data manipulation and formatting step. If you click on an icon, the complete path from the data source to the icon is highlighted. Therefore, you can select the previous steps to adjust the data.
The core concept is that you do not directly change the input data, but define rules to convert data from the source format to the target format. If you open an xFusion pack for the SAP Business ONE application, the target is obviously SAP Business ONE. Therefore, you need to enter the privileges and database name so that the pack knows how to access the SAP system. In addition, the source parameters need to be provided.
xFusion packs come with example Excel fi les. You need to select the Excel fi les as the relevant source. However, it is important to note that you don't need to use the Excel files. You can use any database, or other source, as long as you adjust the data format using the step-by-step process to represent the same format as provided in Excel.
In xFusion. you can use the sample files that come in Excel format.
The connection parameters are presented once you double-click on any of the connections listed in the Connections section as follows:
It is recommended to click on Test Connection to verify the proper parameters. If all of the connections are right, you can run a migration from the source to the target by right-clicking on an icon and selecting Run Export as shown here:
The progress and export is visually documented. This way, you can verify the success. There is also a log file in the directory where the currently utilized xFusion pack resides, as shown in the following screenshot:
Tips and recommendations for your own project
Now you know all of the main migration tools and methods. If you want to select the right tool and method for your specific situation, you will see that even though there may be many templates and preconfigured packs out there, your own project potentially comes with some individual aspects. When organizing the data migration project, use the project task skeleton I provided. It is important to subdivide the required migration steps into a group of easy-to-understand steps, where data can be verified at each level. If it gets complicated, it is probably not the right way to move forward, and you need to re-think the methods and tools you are using.
The most common issue I found in similar projects is that the data to be migrated is not entirely clean and consistent. Therefore, be sure to use a data verification procedure at each step. Don't just import data, only to find out later that the database is overloaded with data that is not right.
Separate the master data and the transaction data. If you don't want to lose valuable transaction data, you can establish a reporting database which will save all of the historic transactions. For example, sales history can easily be migrated to an SQL database. You can then provide access to this information from the required SAP forms using queries or Crystal Reports.
During the course of evaluating the data import features available in the SAP Business ONE application, we have already learned how to import business partner information and item data. This can easily be done using the standard SAP data import features based on the Excel or text files.
Using this method allows the lead, customer, and vendor data to be imported. Let's say that the Lemonade Stand enterprise has salespeople who travel to trade fairs and collect contact information. We can import the address information using the proven BP import method. But after this data is imported, what would the next step be? It would be a good idea to create and manage opportunities based on the address material. Basically, you already know how to use Excel to bring over address information. Let's enhance this concept to bring over opportunity information. We will use xFusion to import opportunity data into the SAP Business ONE application. The basis will be the xFusion pack for opportunities.
Importing sales opportunities for the Lemonade Stand
The xFusion pack is open, and you can see that it is a nice and clean example without major complexity. That's how it should be, as you see here:
eBook Price: $29.99
Book Price: $49.99
Specifying source and target
The dataflow in the previous example is top down, and that's the case for most xFusion packs. There, you can see that the data source is on the top, while the data destination is at the bottom. In this case, the source is an Excel file. I opened the Excel fi le to investigate the data structure and columns. You can see that the Excel document has two sheets. One is called SalesOpportunities and the other is called SalesOpportunitiesLines:
Please review the SalesOpportunitiesLines tab sheet here:
The first sheet essentially has the required information to establish a new opportunity with the salesperson ID, the customer CardCode, and the potential amount. The second sheet has information about the sales stages.
Remember that in xFusion, we need to adjust the source and target information:
Clicking on the Test Connection link confirms that we are connected.
Selecting the target "business object"
By clicking on Next, you will see a drop-down box that allows specifying the business object that we will be transferring the data to. In this example, Sales Opportunities was selected in the Insert mode.
If you closely investigate this example, you can see that the migration can also run in the Update or Delete mode. Furthermore, you can also do a Test Run first. This is often used if your data migration is complex, and you need to potentially wait many hours only to find out that there were errors. Using this mode, you can save time and test the data first:
The crucial part during migration is the correct mapping of fields. Since we selected the Excel file with its two sheets, we can now map the columns in those sheets to the target fields provided by the Sales Opportunities business object. Check out this screenshot:
When you click on the Map button, a field-mapping tool opens up. As you click on the columns, you can drag a line from the source to the target. This would map the fields, which means that the source column called CardCode will be mapped to the actual SAP column CardCode. The mapping process is often not so obvious when the names are not identical and many columns with similar content exist. As shown in the following screenshot, I am mapping two Excel sheets: Sales Opportunities Header and Sales Opportunities Detail:
When this is complete, all of the source data fields will have a proper destination in the target SAP database:
When you click on Next, a new form opens (seen below) which allows you to start the export via the Run Export button. The data is now transferred from the Excel sheets to SAP. A status window and a log fi le will document the successful transactions or any errors that have occurred. This is helpful if you have large data sources because often, 99% of the data comes through while a set of records does not. You can make notes of the problem records and use this information to manually enter the right information.
Review the data transfer in SAP
The final step for data migration is the verification of the data in SAP. We now have a new opportunity, which corresponds to the data that previously only existed in Excel. The Potential tab page shows the potential amount. The Stages tab page shows the stages as specified in the second Excel sheet:
We can conclude that the data migration was successful. The migration templates can be used to easily bring in the lead data. In addition, we can migrate opportunities and continue managing our sales according to the established sales stages.
Essentially, you can see that even if your field salespeople don't have access to the SAP Business One application, they can collect data using the Excel templates. These templates can then be used to easily import data into SAP.
In this article, the advanced tool, xFusion, was used to further expand on the examples and introduce preconfigured migration projects called xFusion packs. With the knowledge of the right tools and process, we performed a data migration to add opportunities for our case study. Overall, the tools and methodologies provided in this article series will help you manage your own project, and also make the right decisions to keep the data migration simple and successful. In addition, you can get started quickly by using the SAP import tools, or plan a project if your requirements are more challenging.
If you have read this article you may be interested to view :
- Competitive Service and Contract Management in SAP Business ONE Implementation: Part 1
- Competitive Service and Contract Management in SAP Business ONE Implementation: Part 2
- Data Migration Scenarios in SAP Business ONE Application- part 1
eBook Price: $29.99
Book Price: $49.99
About the Author :
Wolfgang Niefert studied "Wirtschafts-Informatik" at the European Business School in Oestrich Winkel – Germany. The international management program included programs in London and San Diego with focus on Computer Science and Economics. He has more than 15 years experience with international SAP Implementations. A certification for SAP Production Planning allows him to relate larger SAP solutions to SAP Business ONE. Wolfgang also designed the N2ONE Portal for SAP Business ONE that allows the real-time management of e-commerce functionality.