Dynamics GP Data Conversion via Integration Manager Advanced ODBC

Historical data export, cleansing and importing might be obviously done in text files but large volume of documents might make that method challenging.  Our consulting team did numerous successful historical data migrations and we would like to give you technical highlights.  Let’s begin with describing text file based conversion limitations and the way to break through them:

1. CSV and comma delimited files.  Sample integrations that you can find and research in Integration Manager are dealing with text files.  If you have to bring over GL chart of accounts or customer records text file is natural choice assuming that the number of customers in conversion scope doesn’t exceed ten or maybe hundred thousand.  This assumption is natural as Dynamics GP is recommended as mid-market generic Corporate ERP platform

2. Excel data cleansing limitation.  Now let’s assume that you have fifty thousand customers and each customer placed hundred orders per year in average.  What is total number of historical orders?  You are right – there are around five million orders.  If every order in average has two items in detail section then order detail table should have ten million rows.  When you are exporting data from legacy ERP the natural step is data cleansing and if cleansing is supposed to be done in Excel you should not expect Excel to able to open the CSV file with million or more rows.  Excel is designed to be powerful spreadsheet tool to be operated by human being.  And human being is not expected to read through millions of text lines.  Well if this is not possible in Excel then what is that we got to do to find the work around?

3. ODBC and the power of SQL data cleansing and processing.  It is usually difficult to open legacy MRP database for ODBC connection and doing data cleansing directly there.  What we recommend is exporting legacy accounting tables into CSV text files and then importing them into MS SQL Server custom tables via data import wizard (SSIS in 2008 and 2005 versions).  When you got your text file transformed into custom SQL table the limitation of Excel goes away and you have unlimited SQL aggregation power.  In the case of Microsoft SQL Server we recommend SQL View data cleansing where you are weeding out page number, report header, trailer, empty rows and summary lines by placing where conditions

4. eConnect connectors consideration.  IM has two connection options.  First one is traditional OLE Server.  Here you have to have Great Plains workstation running as OLE Server and this consumes one concurrent user license and plus you have to have user in specific company.  Second option is eConnect based on encrypted SQL Stored Procedures.  If you are familiar with SQL aggregation technology you may expect eConnect to work faster comparing to multi layered OLE Server.  And this assumption is correct

5. Data Migration Iteration Cycles.  If you are trying to live in perfect world then you might expect data import to be done perfectly at the first step.  But the rest of us are ready to accept such discoveries that some documents were actually historically voided and they were brought into in the first iteration.  So now we have to modify our SQL cleansing view to exclude these documents.  Data import requires several iterations and QA job from your accounting department to determine what went wrong.  Microsoft Business Solutions designed the whole ERP Project Management and Implementation technology named Sure Step

6. For additional information, please feel free to call us 1-866-528-0577 or 1-630-961-5918 (this number works for international customers) or email us help@albaspectrum.com We serve you USA/Canada nationwide via remote support (web sessions and phone/Skype conferences). Local service is available in Western Michigan, Chicagoland, Southern California (LA, Orange County and San Diego) and Houston area of the state of Texas