SAP Business One Interconnection with External Application or Database

Dec 16
08:49

2011

Andrew Karasev

Andrew Karasev

  • Share this article on Facebook
  • Share this article on Twitter
  • Share this article on Linkedin

By one reason or another there is opinion in consulting community that when you need ongoing data move to SAP B1 from external database then you have to appeal to third party tools such as iBolt for example.

mediaimage

We would like to review how one-way integration could be done with native tool Data Transfer Workbench and then two way integration with Software Development Kit programming.  There is no need to pay to ISV partners in the case of DTW deployment however you should know the method of connecting to external database via ODBC.  SDK programming is recommended to be outsourced to consulting firm specializing in technology challenging projects which has SDK software developer in staff.  Let’s begin with Data Transfer Workbench:

1. DTW is commonly used in initial data migration to Business One.  Certified consultants know how to fill CSV templates in Excel and then via Data Transfer Workbench data is moved to your company database.  Workbench method is safe as all business logic is validated and if there is an error documents or master records are not integrated until you fix data inconsistency directly in CSV file.  Tool is pretty simple and it depends on CSV templates format.  However you can switch to ODBC connection instead of pulling data from CSV file.  In this case you can base your ODBC on SQL View.  The trick is in the view you have to imitate template file precisely and the first row should include column names.  We also tried to schedule ODBC based integration but it didn’t work for version 2007A and SAP tech support indicated back in 2009 that scheduled integration is not supported at that time.  Currently we have version 8.82 and we have not had a chance to test if scheduled ODBC integration works or not.  Keep reading our blogs as we are working with technically challenging projects including DTW ongoing integrations.  DTW is one-way integration tool however you can combine it with SQL Select statements from your SAP BO company database to make it ‘two-ways’ or bidirectional.  And please note that Workbench is not a real-time integration tool it could imitate quasi real time integration but in order to make it real time we recommend you to deploy Software Development Kit coding integration method

2. SDK has code samples in VB and C# and one of the recommended tools is Microsoft Visual Studio.  Using SDK DLL libraries you can program bidirectional data interchange.  Software Development Kit customization also validates business logic and is safe in the sense of possible data compromising.  In programming routine you are logging as a user with ID and password and then you are free to retrieve master records and documents,SAP Business One Interconnection with External Application or Database Articles edit them or create new ones.  There is possibility to resolve cross-platform integration by selecting Web Service project type.  Web service could be called from non-Microsoft operating systems such as Linux, Unix or Macintosh.  Linux platform with PHP and MySQL is popular among B2B or B2C Ecommerce merchants.  In our experience some Linux shops prefer ODBC bridge to move data to Business One via cross platform ODBC View in Microsoft SQL Server but others in majority prefer text files to be exported from MySQL database and then picked up by Data Transfer Workbench or custom Software Development Kit application.  As you understand in SDK it is up to you to make your data interconnection scheduled or in real time

3. Additional Ideas and let’s begin with Crystal Reports.  Business Objects was acquired by SAP and at this time Crystal Reports is one of the recommended tools where SAP is making all the possible efforts to enable managerial reporting option via Crystal.  There is large stratum of CR designers available on the consulting and employment markets.  Important question to pose on the job interview is familiarity with SAP BO table structure and business processes.  Crystal Reports design is recommended to be based on SQL Stored Procedure or View

4. FRx fate.  Several consultants serving Small Business ERP community since late 1990th indicated that they would like SB1 to be compatible with FRx financial reporting.  At this time it is not really compatible.  To make information more dramatic Microsoft introduced Microsoft Management Reporter to succeed FRx.  We would like to pose it this way that FRx is rather in Microsoft hands to decide on the future

5. Where SAP Business One is now prospering?  We think that emphasis is in Brazil, Russia, China, India, Mexico and Canada.  United States are in progress

6. Please call us 1-866-304-3265, 1-269-605-4904, help@efaru.com.  We have local presence in Chicagoland, Southern California, South West Michigan, Houston and Dallas areas of Texas. We serve customers USA, Canada, Mexico nationwide and internationally via web sessions and phone conferences (Skype is welcomed). Our consultants speak English, Spanish, Portuguese, Russian and Chinese