- Strata Choice
We do not expect you to read this publication just to reassure yourself that CSV templates are the way to go. We rather expect you to get some issues with CSV templates due to enormous number of historical documents that you plan to integrate or you do not know how to export data from your legacy accounting application. We’ll try to cover both scenarios and give you recommendation for your next phase of the homework. Let’s start with advanced DTW topics:
1. Millions of historical documents, such as Sales Orders, Purchasing Invoices and Inventory Adjustments. You are opening huge export file with Excel wizard and realize that it takes hours for Excel to open the file and suggest columns separators. This is right as Excel is designed to be operated by human with all intuitive graphical interface support. However it is unlikely that human being is capable to read through millions of rows. Then what we got to do now? Well, instead of Excel switch to ODBC connection and create something like SQL View in Microsoft SQL Server Development Studio where you are exactly imitating CSV file structure including column names in the first row. Data Transfer Workbench is pretty straight forward tool and it seems to be coupled with template files structure. Technically SQL View could be cross platform where you are joining records from something like Oracle, MySQL, MS Access or Pervasive SQL. However it might be easier to bring over data from external database into custom SQL Server Tables via data import wizard. In version 2008 and 2005 wizard is based on SSIS technology. Please note that the ideas discussed in this paragraph are typically not known to generic certified SAP B1 consultants. SQL View is not restricted by millions of lines and should be deployed in the scenario where you expect multiple attempts until you are satisfied with conversion quality or in the case of ongoing data import from external application such as eCommerce shopping cart or EDI\
2. Exporting documents from legacy accounting. We recommend you to consider two methods where the first one is simply printing standard reports into text file and then cleansing data in SQL custom table. This method doesn’t require ODBC connection to legacy database platform and guessing about table structure. Cleansing is a bit challenging but we think that it is simple enough to weed out such lines as carriage return, page number, report header or comments in custom SQL View. And of course this method is idea when your legacy accounting system sits on proprietary database platform which is not exposed via popular tools such as ODBC or OLEDB. And now second method is direct database access via SQL driver, OLEDB or ODBC and pulling records via SQL Select statement. This method requires some exposure to technology and SQL programming. ODBC connection is popular shell which could be based on additional layers of technology. For example in order to establish ODBC connection to Pervasive SQL/Btrieve database you need to create so-called Data Definition Files (DDF): File.ddf, Field.ddf and optionally Index.ddf. Let’s now take a look at popular migration routes3. From Great Plains Dynamics. This small to mid-market Corporate ERP application was terrifically popular in late 1990th and through the first decade of two thousand. It remains strong and even gains additional MRP market especially since the release of Dynamics GP version 2010. If you are walking away from Great Plains then likely that you are on historic version or even Great Plains Accounting for DOS 9.5 or earlier. We see popular migration routes from Great Plains 7.5, 6.0, 5.5 or earlier or Small Business Financials 9.0 or 8.0 to Business One typically due to business downsizing. Recent versions of GP are available on SQL Server platform exclusively (since release 8.0 and then 9.0, 10.0 and currently 2010). Table structure could be reviewed in user interface: Tools -> Resource Description -> Tables or you can print out tables relation diagram in Software Development Kit. Earlier versions were also available on Pervasive SQL 2000/Btrieve and Ctree/Faircom. Btreieve technology needs DDF files to establish ODBC DSN and these could be produced via GenDDF.set please see documentation
4. From Great Plains Accounting. It shares the same brand name however this application has different code base and table structure. It is a bit difficult to produce DDF files and the procedure is described in ODBC manual which could be opened in original GPA installation CD. However you have to have original floppies/diskettes. Now let’s assume that you had the way to break through DDF file creation and established connection to Btrieve GPA database in Pervasive SQL Control Center. You are likely to confront another challenge as GPA is known to host several relative tables in the same file especially in Sales Orders, Purchase Orders and other posted historical and work documents
5. From QuickBooks. Here we typically recommend printing reports into text files, importing them via SQL data import wizard and cleansing in SQL View. Connection to the database via ODBC might be complicated due to proprietary DB platform
6. From Peach Tree. Here it depends as you might be on luxury version hosted in MS SQL Server or on budget one hosted in proprietary DB platform
7. From MYOB Accounting Software. This package was popular on Apple Macintosh computing platform in 1990th in the United States. Currently it seems to be prospering in Australia. It seems to be moving toward MS SQL Server platform however there is large strata of customers here in the United States who are still on proprietary DB platform
8. Practical recommendations. Data import projects are often causing delays in implementation timeline due to the fact that your maestro consultant is doing this migration for you the first time in his consulting career and probably the only time due to data export and cleansing uniqueness. Good consultant should set your expectations and recommend conservative and realistic approach in data migration projects. Think about the option to keep old accounting system on one of the workstations for historical documents inquiry only. This method allows you to avoid massive historical data conversion and launch new system in timely fashion and we are sure that in couple of years the old computer could be shut down
9. Please call us 1-866-304-3265, 1-269-605-4904 (for international customers, where our representative pick up the phone in St. Joseph, MI call center). help@efaru.com. We have local presence in Chicagoland, Southern California, South West Michigan and Houston and Dallas areas of Texas. We serve customers USA, Canada, Mexico and Brazil nationwide and internationally via web sessions and phone conferences (Skype is welcomed). Our consultants speak English, Spanish, Portuguese, Russian and Chinese. We feature our expertise is in International Business. We provide second opinion in SB1 data migration, customization and reporting