Data,Cleansing,and,Templates,P computer Data Cleansing and Templates Preparation prior to Migration

----------------------------------------------------------Permission is granted for the below article to forward,reprint, distribute, use for ezine, newsletter, website,offer as free bonus or part of a product for sale as longas no changes a Gone are those times when the companies and the organisations didn't need a hi-tech system to handle them. Owing to the considerable increase in the business sector and thus, an enormous increase in the complexity of the organisational struc

It is easier and budget wise cheaper to keep the old accounting online for data querying only purposes.  Sometimes you have no way of keeping the old application online due to subscription model or maybe another reason.  We would like to share with you advanced methods of migrating in Data Transfer Workbench.  This method doesn’t require expensive third party tools.  However it requires some creativity from your IT department folks and especially SQL guru.  Let’s begin:1. Data Transfer Workbench and CSV templates or also referred as Excel templates.  DTW is pretty straightforward tool, which doesn’t have flexibility in the sense that it requires you to provide documents in CSV template for the document origin.  If you have to import high volume of historical transactions you typically expect this process to be done with several or maybe better say numerous iterations.  And iteration requires CSV files repopulation in Excel.  Plus if you are about to migrate million plus documents and their details Excel might be too slow or even not ready to handle such a job.  The sad news might be the future understanding that you have already spent the budget allocated for data conversion project without positive results.  Are we done with this consulting team and migration project or should we just look for second opinion?  The answer is probably second opinion is a good idea2. Instead of filling up CSV templates consider connection to your legacy data via ODBC connection.  ODBC connection is the option and you can connect to your legacy database directly.  However we figured that better idea is to import your legacy ERP tables into MS SQL Server Custom database via data import wizard/SSIS.  Documentation is pretty limited and we did our own experiment and figured out that SQL View is the answer.  There is no need to do data cleansing in tables directly as it could be done directly in SQL View and this View could be changed with each iteration as you are progressing with migration and data massage.  Now we are moving to the discovery itself3. SQL View and CSV template format.  It seems like you have to use Union statement and imitate two exactly the same lines containing template column headers.  There is no need to represent all the headers just required and optional at your discretion.  And second Select statement should provide the rows of data.  Please note that SQL View is not limited to the volume of records.  It can reasonably handle millions while Data Transfer Workbench is good in its performance to move in your huge data poll into SAP B1.  We assume that you do iterations in the test copy of your production company where settings are present and you are restoring this test company over and over again4. Cross-Platform View in SQL Server.  If you feel that you are comfortable to connect via ODBC or native driver to your legacy database then it is possible to produce SQL View working with such constructions as Linked Server or Open Row Set.  It looks like Microsoft is trying to switch accents and recommend SQL Server Integration Services in SQL Server 2005 and 2008.  We also discovered that Linked Server and Open Row Set didn’t work as expected on SQL Server 2008 on 64 bit computing platform5. Exporting data from your legacy accounting.  There are two popular methods.  First one is ODBC connection assuming that you know tables structure.  If you are walking away from such popular accounting systems as Great Plains Dynamics on Pervasive SQL Server, Great Plains DOS on Btrieve it is possible to create ODBC connection based on so-called Data Definition Files (DDF).  If you are exporting data from such small business accounting packages as QuickBooks, Peach Tree (on proprietary database not on MS SQL Server), MYOB it is probably easier to print reports into text files and begin data cleansing from there.  When you have to print reports into text files we recommend you to import these files into SQL Server custom database and abstract your cleansing in SQL Views.  Your custom SQL View should weed out such human interface supporting rows as Report Header, Page Number, Blank Line, Report Footer, Summaries, etc.6. Message to SAP BO customers in the country side area where local support might be limited and lacking experience.  Data migration projects are not a rocket science and technical consultant or programmer does not have to be present in your country side office to do the job.  Web sessions, phone conference and remote desktop connections give you a good advantage to invite remote support.  If you are at the point where you feel that your SB1 implementation project was screwed due to data import limbo feel free to give us a call or better email us7. Please call us 1-866-304-3265, 1-269-605-4904 (for international customers, where our representatives pick up the phone in Naperville and St. Joseph, MI call center).  [email protected].  We have local presence in Chicagoland, Southern California, South West Michigan, Houston and Dallas areas of Texas. We serve customers USA, Canada, Mexico, Brazil nationwide and internationally via web sessions and phone conferences (Skype is welcomed). Our consultants speak English, Spanish, Portuguese, Russian, Chinese.  One of our experiences is international Corporate ERP and Consolidated Financial reporting