SAP B1 ERP and MRP platform should be seamlessly integrated with your legacy applications, such as Oracle, Microsoft SQL Server custom databases, ODBC compliant sources: Excel, MS Access, CSV and tab delimited text files. If you got initial training in SAP BO Data Transfer Workbench, you should be already familiar with Microsoft Excel and templates concept. In this small publication we will cross the Excel limits and give you the highlights on SQL queries, and this means virtually unlimited ability to integrate any database platform and not in initial data migration and conversion, but even in ongoing data integration:
1. ODBC query anatomy. As Workbench is really tuned for Excel CSV templates, where field names are defined according to SB1 importing object rules, you should follow the same rules in creating SQL view - please in view creation define the same column names as in Excel template for the intended object. If you follow this rule, then your object integration will recognize your columns mapping automatically on the fly, and you will not need to change and save changes for DB schema - the topic we would like to avoid in this executive and IT programmer level publication
2. SQL View technology. If you move query design from Excel to SQL select statement, then you are virtually breaking all the boundaries. We recommend you to consider the following path - import your source master records or transactions into MS SQL Server based staging table (where you may consider adding identity insert column for uniqueness). Please consult your SQL DBA person to understand the limitations of Excel and advantages of the SQL Server Views and Stored Procedures
3. 64 and 32 bit Windows 2003 Server dilemma. If you are reading your integration from the text tab or comma separated values files, then you should be aware about 64 bit platform limitations. You can't use such popular driver as Microsoft.Jet.OLEDB.4.0. Instead you will have to import text file through Microsoft SQL Server DTS or data import wizard directly. If you are still on the old-good-days Windows 2003 Server 32 bit, enjoy the advantage to deploy select statement from Microsoft.Jet.OLEDB.4.0 compliant text file. On the Windows x64 OLEDB seems to be not yet available, or at least, there are technical challenges to use OPENROWSET, reading from the text file.
1. ODBC query anatomy. As Workbench is really tuned for Excel CSV templates, where field names are defined according to SB1 importing object rules, you should follow the same rules in creating SQL view - please in view creation define the same column names as in Excel template for the intended object. If you follow this rule, then your object integration will recognize your columns mapping automatically on the fly, and you will not need to change and save changes for DB schema - the topic we would like to avoid in this executive and IT programmer level publication
2. SQL View technology. If you move query design from Excel to SQL select statement, then you are virtually breaking all the boundaries. We recommend you to consider the following path - import your source master records or transactions into MS SQL Server based staging table (where you may consider adding identity insert column for uniqueness). Please consult your SQL DBA person to understand the limitations of Excel and advantages of the SQL Server Views and Stored Procedures
3. 64 and 32 bit Windows 2003 Server dilemma. If you are reading your integration from the text tab or comma separated values files, then you should be aware about 64 bit platform limitations. You can't use such popular driver as Microsoft.Jet.OLEDB.4.0. Instead you will have to import text file through Microsoft SQL Server DTS or data import wizard directly. If you are still on the old-good-days Windows 2003 Server 32 bit, enjoy the advantage to deploy select statement from Microsoft.Jet.OLEDB.4.0 compliant text file. On the Windows x64 OLEDB seems to be not yet available, or at least, there are technical challenges to use OPENROWSET, reading from the text file.
Sourcing for SAP system, contact Alenu Group Now! at (65) 6884 5030
No comments:
Post a Comment