sap net connector case study on microsoft sap bw data extract bill faison bfaison@microsoft com l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SAP .NET Connector: Case Study on Microsoft SAP BW Data Extract Bill Faison bfaison@microsoft PowerPoint Presentation
Download Presentation
SAP .NET Connector: Case Study on Microsoft SAP BW Data Extract Bill Faison bfaison@microsoft

Loading in 2 Seconds...

play fullscreen
1 / 35

SAP .NET Connector: Case Study on Microsoft SAP BW Data Extract Bill Faison bfaison@microsoft - PowerPoint PPT Presentation


  • 401 Views
  • Uploaded on

SAP .NET Connector: Case Study on Microsoft SAP BW Data Extract Bill Faison bfaison@microsoft.com. 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SAP .NET Connector: Case Study on Microsoft SAP BW Data Extract Bill Faison bfaison@microsoft' - issac


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sap net connector case study on microsoft sap bw data extract bill faison bfaison@microsoft com

SAP .NET Connector: Case Study on Microsoft SAP BW Data ExtractBill Faisonbfaison@microsoft.com

agenda
Agenda
  • What is the SAP .NET Connector
  • Data Push Design Overview
  • Development Overview
  • Lesson Learned
slide3

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.
slide4

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.
slide5

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)
slide6

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
slide7

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
slide8

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
slide9

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
slide10

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
slide11

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
slide12

Data Push Design Overview

SAP BW Window 2003 Server

slide13

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
slide14

Development Overview

  • Project Overview
  • Metadata Definition
  • RFC Set Up
  • ABAP
    • Query over BW data
    • RFC call
  • Windows Service Overview
  • SQLXML BULK INSERT
slide15

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
slide16

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
slide17

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

slide18

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 Len TypName Text

TABLENAME CHAR 50 TEXT50 Text Field

TABLENAMECREATE CHAR 50 TEXT50 Text Field

slide19

Metadata Definition – SQL Table

/* Company Table build */IF @TableName = 'CompanyCode'BEGINSET @DropTableString = 'DROP TABLE ' + /* Build the name of the table. */ @TableNameCreateSET @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 BWSET @ErrCode=@@ERRORRETURN (@ErrCode)END

slide20

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>

slide21

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

slide22

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'

slide23

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.

slide24

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.

.

slide25

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.

slide26

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
slide27

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";

slide28

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);

slide29

Lesson Learned

  • Performance Results
  • Phase 2 Changes
  • Lagniappe
  • Useful Sites
slide30

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
slide31

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
slide32

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
slide33

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.
slide34

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

Thank you for attending!

Please remember to complete and return your evaluation form following this session.

Session Code: 412