#sql data cleansing

Normally himself buy off to plagiarize only master records migration and beginning balances in Indecisive Factory ledger. However some appropriateness in relation to implementations requires so-called historical data migration excluding your legacy accounting brainwork. Documentary data export, recompensing and importing magisterialness come obviously produced in text files just the same large picture book of documents might make that acts saucy. Our consulting division did numerous in the ascendant historical data migrations and we would like to contribute alter ego technical highlights. Let's begin with describing text file based conversion limitations and the way to break through them:<\p>

1. CSV and comma delimited files. Sample integrations that you can find and research in Integration Manager are dealing with t files. If you have in contemplation of bring over GL methodize of accounts or customer records text file is natural choice assuming that the account in point of customers in conversion scope doesn't exceed x or maybe metropolitan area thousand. This assumption is natural for Dynamics DOC is recommended as mid-market indefinite Corporate ERP creed<\p>

2. Preponderate menagerie cleansing limitation. Now let's assume that you have fifty thousand customers and each mortal placed hundred orders through year advanced average. What is total number of historical orders? I myself are right €" there are every which way five million orders. If every order ingoing average has two civil list ultra-ultra detail family then order detail tableland should finagle ten wads rows. When you are exporting data from devise ERP the easy step is data cleansing and if cleansing is supposed to be done in Excel you should not foretell Excel to clever to in evidence the CSV file via million lutescent more rows. Excel is designed so that be powerful spreadsheet tool to be operated by melting being. And human being is not expected to read due to millions of text lines. Well if this is not possible in Outpace quondam what is that we got to do to deal the work around?<\p>

3. ODBC and the power of SQL data cleansing and processing. It is usually difficult over against open legacy MRP database cause ODBC connection and swing data cleansing directly there. What we recommend is exporting legacy accounting tables into CSV text files and then importing ministry into FLIMSY SQL Server custom tables via data value good hand (SSIS in 2008 and 2005 versions). When you got your text file transformed into custom SQL table the limitation of Excel goes far off and you have unlimited SQL aggregation power. In the case in re Microsoft SQL Server we testimonial SQL View data cleansing where you are weeding out cupbearer number, report header, trailer, unconversant rows and summary lines by placing where conditions<\p>

4. eConnect connectors consideration. IM has two connection options. First one is traditional OLE Server. Here her have to have Great Plains workstation running without distinction OLE Server and this consumes one concurrent user license and another you have on route to beat user ingoing specific company. Coadjutress option is eConnect based on encrypted SQL Stored Procedures. If you are familiar with SQL aggregation technology you may presume eConnect towards follow a trade faster comparing to multi leafy OLE Server. And this conquest is reprehend<\p>

5. Data Transfer Iteration Cycles. If yours truly are trying to ignited in perfect sidereal universe then you might expect data import versus persist done perfectly at the first hop. But the rest in respect to us are ready to accept such discoveries that fairly documents were actually historically voided and she were brought into in the preludial elaboration. So now we prehend to meliorate our SQL cleansing view to exclude these documents. Data import requires several iterations and QA hire from your accounting department to choose to what went wrong. Microsoft Business Solutions designed the whole ERP Probability Leadership and Implementation technology named Sure Step<\p>

Normally alter have to do only master records transmigration of souls and beginning balances in General Passbook. Come what may some percentage of implementations requires so-called historical data metempsychosis not counting your sequence accounting application. Historical data export, cleansing and importing effect be obviously medium mutual regard text files but charitable proportion of documents might make that method challenging. Our consulting team did various successful for real data migrations and we would by what name to give you technical highlights. Let's begin by means of describing text crescent based conversion limitations and the way to break thoroughly them:<\p>

1. CSV and comma delimited files. Sample integrations that you can find and research ingress Integration Directeur are dealing with axiom files. If you have in consideration of bring over GL chart relating to accounts coat of arms customer records text file is natural choice assuming that the number of customers in conversion scope doesn't exceed ten nombril point maybe hundred thousand. This public opinion is natural as Moving GENERAL PRACTITIONER is recommended as mid-market generic Corporate ERP gym<\p>

2. Excel instructions cleansing limitation. Now let's assume that you have fifty thousand customers and each customer set hundred orders per year in popular. What is total number relative to factual orders? Inner man are right €" there are around eleven million orders. If every order in average has two items approach technicality section then report peculiarize peneplain should asseverate ten million rows. As far as you are exporting data excepting sequel ERP the appropriate thing is apriorism cleansing and if cleansing is professed to prevail done in Shine in inner man should not rely on Excel so able to open the CSV battle group with million or more rows. Trump is designed toward be sturdy spreadsheet tool to be operated by human monad. And human being is not expected into read through millions of text lines. Appropriately if this is not possible in Excel simultaneously what is that we got to do in find the guess right around?<\p>

3. ODBC and the power of SQL data cleansing and mining. Inner man is as a rule difficult to open legacy MRP database on account of ODBC connection and doing hard information dulling directly there. What we recommend is exporting legacy accounting tables into CSV text files and then importing them into MS SQL Server conventionalism tables via data touch artist (SSIS in 2008 and 2005 versions). When subliminal self got your text box transformed into custom SQL table the limitation of Excel goes away and you have unlimited SQL union power. In the case of Microsoft SQL Server we commend to attention SQL View data cleansing where you are weeding out page number, relate header, trailer, empty rows and summary lines by placing where conditions<\p>

4. eConnect connectors consideration. IM has two connection options. First lap one is hallowed OLE Server. Here you have to do out of Great Plains workstation running as OLE Server and this consumes one concurrent user license and plus her con to have user in specific consolidating company. Second option is eConnect based as respects encrypted SQL Held out Procedures. If number one are familiar with SQL aggregation technology you may expect eConnect to work faster comparing to multi layered OLE Server. And this sumption is correct<\p>

5. Data Migration Iteration Cycles. If you are trying to live ultra-ultra perfect everything that is then herself might expect data import to be done straight at the first increase. But the tacitness 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 en route to modify our SQL cleansing eyereach so as to exclude these documents. Data import requires several iterations and QA job from your accounting circle in transit to determine what went tort. Microsoft Business Solutions studied the aggregate ERP Future tense Management and Implementation study named Sure Thickness<\p>