Transcript SAP .NET Connector: Case Study on Microsoft SAP BW Data Extract

SAP .NET Connector: Case
Study on Microsoft SAP
BW Data Extract
Bill Faison
[email protected]
Agenda
•
•
•
•
What is the SAP .NET Connector
Data Push Design Overview
Development Overview
Lesson Learned
What is the SAP .NET Connector
• Terminology
 SAP BW – SAP data warehousing solution
 Info Object – Building blocks of BW. This is where you
define your key figures and characteristics. It is also
where you store all of your master data
 ODS – Operational Data Store. This is where you store
you detailed level records in BW.
 RFC – Remote Function Call. This is a call to a function
module in a system different from the caller’s.
 Windows Service - Formerly known as NT services,
enable you to create long-running executable
applications that run in their own Windows sessions.
These services can be automatically started when the
computer boots, can be paused and restarted, and do
not show any user interface.
What is the SAP .NET Connector
• Terminology
 SQLXML – Enables developers to bridge the gap
between Extensible Markup Language (XML) and
relational data. You can create XML views of your
existing relational data and work with it as if it were an
XML file for example.
 SQLXML BULK LOAD – XML Bulk Load is a stand-alone
COM object that allows you to load XML data into
Microsoft SQL Server tables. Bulk load utility provides
higher performance when you need to insert large
amounts of XML data.
What is the SAP .NET Connector
• An SAP product released late 2002
• From the SAP website:
 The SAP Connectors allow the integration of different
applications and technologies with SAP Systems via
open standards
 The SAP .NET Connector offers developers to expose
BAPIs and remotely-enabled function modules to
any .NET application (inside-out). You now also can
access .NET components from with a mySAP.com
application (outside-in)
What is the SAP .NET Connector
• SAP client solution, the SAP system is the server
and the .NET application is the client that
interacts with the RFC
• Examples of some uses for the SAP .NET
Connector client application
 Windows form application that has a customized and
highly interactive user experience
 Console application to access information from the SAP
system as part of some NT batch processing
What is the SAP .NET Connector
• RFC server allows your SAP system to
execute .NET code as if the .NET code were
another SAP system
• Examples of some uses for the SAP .NET
Connector server application
 Getting information such as maps, stock prices or
weather from an external service to be used within a
SAP report
 Sending emails from your SAP system
Data Push Design Overview
• Business case: Supply data that had been
transformed in SAP BW to a non-SAP database
• Other solutions considered
 Create a custom ABAP to create flat files to be
consumed by the destination data warehouse
 Create a DTS package to extract and pump the data to
the non-SAP database
 Use MS SQL replication to extract and feed the data to
the non-SAP database
 Hub and Spoke
Data Push Design Overview
• Why we chose the SAP .NET Connector
Flexibility
Speed
Cost
Scalability
Leverage development expertise on the internal
Microsoft SAP development team
 Demonstrate SAP BW ability to be a enterprise data
distribution tool within Microsoft using SAP openness
tools





Data Push Design Overview
• Technologies used






SAP BW 3.0B
SAP .NET Connector 1.0
Window 2003
SQL 2000 SP3
SQL XML 3.0
Microsoft Visual Studio .NET
Data Push Design Overview
• Skill sets needed







Database Design
SAP Basis
SAP Security
ABAP developer
RFC and TCP/IP knowledge
MS SQL and DTS
C# developer
Data Push Design Overview
SAP BW Window 2003 Server
Windows Service
SAP ABAP
Program
Call RFC with
destination
Convert
Income
table to
XML
SM 59
Destination
information table
XML
Schema
SAP BW
Layer
SQL XML
BULK
INSERT
Windows 2003
Server
Layer
External SQL Server
Data Push Design Overview
• Process Flow
 Submit a ABAP program to run per the selection criteria
entered
 ABAP program executes a RFC that will either create the
tables on the non-SAP database, or push the SAP data
from the query that was run in the ABAP
 RFC calls the Windows service, which will either call a
stored procedure to create tables, or push data into the
tables it had just created using SQLXML BULK INSERT
 Pass back any success or failure message to the ABAP
program
Development Overview
• Project Overview
• Metadata Definition
• RFC Set Up
• ABAP
 Query over BW data
 RFC call
• Windows Service Overview
• SQLXML BULK INSERT
Project Overview
• Detailed Example – Company Code
• Business Requirement and Data Analysis
 Pass to non-SAP data warehouse company
code number and name
 Full push of the data with each data push
 Data is stored in the BW info objects
0COMP_CODE
 0COMP_CODE data is stored in the BW tables
/BI0/MCOMP_CODE and /BI0/TCOMP_CODE
Project Overview



ABAP program will pull all company code data,
and place it in a internal table, then pass the
data in the internal table to a RFC
ABAP program will only pass the ‘Active’ records
ABAP program will use a TCP/IP connection to
talk to the windows service
Meta data definition – SAP itab
Structure for Company Table Interface
* Defines the company code table for ABAP and RFC
ZBPUR_COMPANY_TYPE
Short text:Structure of Company Table Interface
Structure
Field structure
Number of fields: 2
Sum of field lengths: 44
Component
Type Len TypName
Text
COMPANYCODE
CHAR 4 /BI0/OICOMP_CODE
Company Code
COMPANYCODETEXT CHAR 40 RSTXTMD
Med.
Desc
Metadata Definition – SAP itab
Structure for Tables to be created
* Used to hold the name of the tables will create
* in the destination database
ZBPUR_CREATED_TABLE_TYPE
Short text:bw dnc created table structure
Structure
Field structure
Number of fields: 2
Sum of field lengths: 100
Component
Type
TABLENAME
CHAR
TABLENAMECREATE CHAR
Len TypName
50 TEXT50
50 TEXT50
Text
Text Field
Text Field
Metadata Definition – SQL Table
/* Company Table build */
IF @TableName = 'CompanyCode'
BEGIN
SET @DropTableString = 'DROP TABLE ' +
/* Build the name of the table. */
@TableNameCreate
SET @CreateTableString = 'CREATE TABLE ' + 'dbo.' +
/* Build the name of the table. */
@TableNameCreate +
/* Build a table fields */
'(
CompanyCode
CHAR(4) NOT NULL,
CompanyCodeText
VARCHAR(40) NULL DEFAULT(" "),
CONSTRAINT ' + @TableNameCreate + '_PK PRIMARY KEY (CompanyCode)
)'
/* Check to see if the table exists, if yes delete it */
IF exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME =
@TableNameCreate) EXEC (@DropTableString)
/* Create the table */
EXEC (@CreateTableString)
/* Error message to pass back to BW
SET @ErrCode=@@ERROR
RETURN (@ErrCode)
END
Metadata Definition – XSD file
XML File - ZBPUR_COMPANY_TYPETable.xsd
<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
- Maps to RFC table structure name
- FRODO is a place holder for the C# code to fill in the SQL table name from
RFC
<xs:element name = "ZBPUR_COMPANY_TYPE" sql:relation="FRODO">
<xs:complexType>
<xs:sequence>
- Element name maps the RFC table field name, sql:field maps to the SQL
table
<xs:element name="COMPANYCODE" sql:field="CompanyCode"
type="xs:string" />
<xs:element name="COMPANYCODETEXT" sql:field="CompanyCodeText"
type="xs:string" />
</xs:sequence>
RFC Set Up
TCP/IP Set up – SM59
RFC destination
BW_NETCONNECT
Connection type T TCP/IP connection
Activation Type Section
Chose ‘Registered Server Program’
Registered Server Program Section
Program ID – <Window Service program executable>
Gateway Options
Gateway Host - < Gateway Host Name>
Gateway Service - < Gateway Service Name>
Logon Security Tab
Click on Actv., and enter in the name of the authorization to use the
destination
ABAP - Query over BW data
* t_comp_code ITAB to hold data from the ABAP query and is
passed
* to the RFC call
DATA: t_comp_code TYPE STANDARD TABLE OF
zbpur_company_type.
* c_company_function is the RFC name defined with SE37
* c_company is the name we pass to the windows service to
* identify which table we are processing
DATA: c_company_function(35) TYPE c VALUE
'Z_B_PUR_R_COMPANY_TABLE',
c_company TYPE tablename VALUE
'CompanyCode'
ABAP - Query over BW data
* Select all active company code records
* and move the results into the ITAB t_comp_code
SELECT t~comp_code t~txtmd
FROM /bi0/tcomp_code AS t JOIN
/bi0/mcomp_code AS m
ON t~comp_code = m~comp_code
INTO TABLE t_comp_code
WHERE m~objvers = 'A'
AND m~changed = ''.
* Call the RFC that will pass the data to the window
service
PERFORM rfc_call TABLES t_comp_code
USING c_company_function
c_company.
ABAP – RFC call
FORM rfc_call TABLES t_table “ Data from query
USING _function
“ RFC name
_tab_name.
“ Table identifier
* Get the name of the table to created with date/time stamp
READ TABLE t_tables INTO wa_tables
WITH KEY tablename = _tab_name.
* c_dest is the name of the TCP/IP connections defined in SM59
CALL FUNCTION _function DESTINATION c_dest
EXPORTING
table
= wa_tables-tablenamecreate “ Table to be created
server
= p_serv “ Destination Server from sel. screen
database
= p_db “ Destination database from sel. screen
IMPORTING
return_code
= return_text “ Message text
TABLES
table_name
= t_table “ Data from ABAP query
EXCEPTIONS
communication_failure = 1
system_failure
= 2.
.
ABAP – RFC call
FUNCTION Z_B_PUR_R_COMPANY_TABLE.
*"---------------------------------------------------------------------*"*"Local interface:
*" IMPORTING
*"
VALUE(TABLE) TYPE TEXT50
*"
VALUE(SERVER) TYPE TEXT50
*"
VALUE(DATABASE) TYPE TEXT50
*" EXPORTING
*"
VALUE(RETURN_CODE) TYPE ZBPUR_DNC_RETURN_CODE_TYPE
*" TABLES
*"
TABLE_NAME STRUCTURE ZBPUR_COMPANY_TYPE
*"----------------------------------------------------------------------
ENDFUNCTION.
Windows Service Overview
• Windows Service receives the RFC call and
process the incoming data
• Load predefined XML schema and load
incoming data into external SQLServer via
SQLXML interface using SQLXML BULK
INSERT
SQLXML BULK INSERT
// Example of how to write the SQLXML BULK INSERT Code
SQLXMLBulkLoad3Class objBL = new SQLXMLBulkLoad3Class();
// Set the connectation string
// ServerName and DataBaseName come from the RFC
objBL.ConnectionString =
"Provider=SQLOLEDB.1;Data Source=" + ServerName + ";Initial
Catalog=" + DataBaseName + "; Trusted_Connection=yes";
// Write any error to a error file
objBL.ErrorLogFile = appPath + "error.xml";
SQLXML BULK INSERT
BULK INSERT Setting
objBL.SGUseID = true;
// XML Bulk Load assigns the values that are specified in the source
file to the identity column
objBL.KeepIdentity = true;
// XML Bulk Load creates temporary files, one for each table that is
identified in the mapping schema
//objBL.Transaction = true;
//source data is not an XML fragment
objBL.XMLFragment = false;
// appPath is passed in from a .ini file
objBL.TempFilePath = appPath;
// Execute the BL
// xsdfile is the predefined XML schema
// dataStream is the data from BW
objBL.Execute(xsdfile, dataStream);
Lesson Learned
• Performance Results
• Phase 2 Changes
• Lagniappe
• Useful Sites
Performance Results
• 15 tables total
 10 master data tables
 4 transactional tables
 1 audit table
• 1 year worth of data
• Loaded 2K records per second
• Total run time is about 20 minutes
Phase 2 Changes
• Leverage the BAPI or BADI to format the
data
• Load data in parallel
• Add more robust error messages
• Automatically generate the XML schema
Lagniappe
• SQL XML BULK INSERT requires the data
to be in a set format depending on how
you have created your SQL tables, or it
will fail. Here are some hints on how to
debug the data format:
 Create a test excel spreadsheet, and load data
into the SQL tables using a DTS package BULK
INSERT task
 Watch out for how the data is stored in the
SAP, and the format that the SQL table
requires the data to be in
- Leading zeros could cause issues
- Date time fields need to be adjusted
Lagniappe
• When defining your Table definition,
consider the following for performance
 Do not put constraints in your tables unless
you have to
 If you have large datasets, commit the data in
batches instead of loading all of the records at
once
 Use varchar/nvarchar columns instead of
text/ntext columns whenever possible. Use
char/varchar columns instead of
nchar/nvarchar if you do not need to store
unicode data.
Useful Sites
• http://service.sap.com/CONNECTORS
 Requires a OSS log on
• http://www.microsoft-sap.com/
• http://www.sapgenie.com/interfaces/netconnector.htm
Thank you for attending!
Please remember to complete
and return your evaluation form
following this session.
Session Code: 412