Skip to Content
Use SQL2005 to Connect and Present SAP on SharePoint / Business Scorecard Manager – Part III

Keywords: SAP R/3 4.6c, SQL2005, SharePoint Portal Server 2003, Business Scorecard Manager 2005

In this tip, I will demonstrate how to extract SAP data via .NET Provider for mySAP into SQL 2005 database and then use SQL2005 business intelligence – analysis service to create cubes and then use reporting service to create report model and finally create reports and present on reporting service web page.

Architecture

The demo is to use SQL 2005 Integrating Service (SSIS) to extract sales data (Customer Info, Product Info, Sales Items Info) from related SAP database into SQL 2005. Then use Analysis Service (SSAS) to do business intelligence on these tables to create a Sales Item cube with two dimensions of Product and Customer. Then use reporting service (SSRS) to create a reporting model. Finally use report builder client tool to create a sales report/graph and publish them on report server. The data flow is shown in below figure.

The key fields of these SAP tables see below reference table:

MARA MATNR Material number
General Material Data MTART Material type
MATKL Material group
MAKT MATNR Material number
Material Descriptions MAKTX Material description
T134T MTART Material type
Product Type Description MTBEZ Description of material type
VBAK VBELN Sales document
Sales Header KUNNR Customer ID
VBAP VBELN Sales document
Detailed Sales Item POSNR Sales item ID
MATNR Material number
KWENG Cumulative order quantity in sales units
NTGEW Net weight of item
NETPR Price
VOLUM Volume of the item
ABDAT Reconciliation date for agreed cumulative quantity
STADAT Statistics date

Extract SAP Data to SQL 2005 – Integration Service

1. Go to SQL 2005 server

2. Start -> Programs ->Micosoft SQL 2005 -> SQL Server Management Studio

3. Connect to database engine-> Create a SQL database for data storage after extracted from SAP.

4. Start -> Programs ->Micosoft SQL 2005 -> SQL Business Intelligence Development Studio

5. Create a new Integrating Service project

6. The integration project in Solution explorer has three folders: Data Source, Data Source View and SSIS Package. Right click the SSI package -> Add a new SSIS package.

7. As above figure label 2, right click in the Connection Manager pad, add a new connection, in a pop define window, select .Net Provider for mySAP from the type dropdown menu and then define properties as shown in label 3 of above figure.

8. Then drag and drop controls from left control items panel to the control flow Tab as shown in label 4 of above figure.

9. Each data flow task such as “Customer Info from SAP”, “Product Info from SAP” and “Sales Items Info from SAP” has a data flow respectively in the tab “Data Flow”

10. Just use “Customer Info from SAP” data flow as an example, first draw the control items from left panel into the design panel. right click the Data Reader Source, say, “SAP -KNA1 Customer Data”, select SAP Data Connection(created in Step 7) as the connection manger

11. Continue to define the select statement to query tables from SAP.

12. The definition example for “Customer” please see below figure.

13. So finally the SSIS package has components of Control Flows, Data Flows(including Data Reader Source from SAP, Data Destination – sql database or flat file, action like Merge join, sort etc.) and Connection Managers.

14. Build and run this SSIS package. And then go to open SQL Server Management Studio. You can see the data has been extracted from SAP into SQL operational database.

Analyze Data – Analysis Service

15. Start -> Programs ->Micosoft SQL 2005 -> SQL Business Intelligence Development Studio

16. Create a new Analysis Service project

17. Add a new data source like below figure.

18. Add a new Data Source View and add all tables we created in sql operational database, Customer, Product, ProductType, Sales Item. And create their table¡¯s key and build relation between them like below figure.

19. Add a new cube.

20. At last the cube is built like below figure.

Generate Report Model – Reporting Service

21. Start -> Programs ->Micosoft SQL 2005 -> SQL Server Management Studio

22. Connect to reporting service-> Right click the new created Analysis Service -> generate model like below figure.

23. Finally a new node inserted under Data Source node in object explorer of SQL Management Studio.

Create Reports – Report Builder

24. Go to reporting services web site such as http://yourreportservername/reports

25. Click ?gReport Builder”

26. If it is your first time to run report builder, it will download this client tool.

27. A report builder window opened and it bit looks like other office interface such as word. It has a Get Started task pane like below figure.

28. Select the report model created in Step 21-23.

29. Select the type of reports, say, table.

30. Then design the table as you like.

31. Save the designed report to report server.

32. Users can look at the table you created on report web site.

Reference

1. Download: Feature Pack for Microsoft SQL Server 2005 – November 2005 http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en , accessed in Dec., 2005
2. Configuring Reporting Services to Use Integration Services Package Data http://msdn2.microsoft.com/en-us/library/ms345250.aspx, accessed in Dec., 2005
3. Use SQL2005 to Connect and Present SAP on SharePoint / Business Scorecard Manager – Part I, Ted Teng, /people/ted.teng/blog/2005/12/28/use-sql2005-to-connect-and-present-sap-on-sharepoint-business-scorecard-manager-150-part-i, accessed in Dec., 2005.
4. Use SQL2005 to Connect and Present SAP on SharePoint / Business Scorecard Manager – Part II, Ted Teng, /people/ted.teng/blog/2005/12/28/use-sql2005-to-connect-and-present-sap-on-sharepoint-business-scorecard-manager-150-part-ii , accessed in Dec., 2005.

To report this post you need to login first.

18 Comments

You must be Logged on to comment or reply to a post.

  1. Lumir Hlavac
    I try export data from SAP with .NET Provider for mySAP and I get the message:

    TITLE: SQL Server Import and Export Wizard
    ——————————
    The statement could not be parsed.

    ——————————
    ADDITIONAL INFORMATION:
    RFC Communication link error. Error Message ‘Function module “Z_READ_FUNCTION_METADATA” not found.’. (Microsoft.Adapter.SAP.SAPProvider)

    Any idea what causes this.

    Thanks,

    lumir

    (0) 
    1. Anton Wenzelhuemer
      To me it sounds like it doesn’t find the function module Z_READ_FUNCTION_METADATA. Just that :-))

      I’d have a look if the fm exists and if it is rfc enabled.

      regards,
      anton

      (0) 
      1. Lumir Hlavac
        I try install the rfc from customRFC.zip (..\Program Files\Common Files\Microsoft Shared\Adapters\SAP) and the instalation failed. In transport logs are errors.

        I have SAP version 4.6C on UNIX and ORACLE database.

        Is possible install .net data provider on this system?

        Thanks,

        lumir

        (0) 
        1. Ted Teng Post author
          I have no experience on Unix – SAP. Our SAP IDES runs on windows platform. I requested our SAP people to do as readme.html defined:

          4.2 Installing the RFCs
          To install the two RFCs, follow these steps:

          1. Copy files from the computer running SQL Server to the SAP application server
          Log in as the SAP R/3 system administrator to the SAP application server of your development system.

          Copy the transport file with the naming pattern K9*.BI1 from the installation directory on the computer running SQL Server to the following directory on the SAP application server:

          DRIVE:\usr\sap\trans\cofiles

          Copy the transport file with the naming pattern R9*.BI1 from the installation directory on the computer running SQL Server to the following directory on the SAP application server:

          DRIVE:\usr\sap\trans\data.
          2. Load the transport into the transport buffer on the SAP application server
          At the command prompt, navigate to the transport program directory on the SAP application server:

          DRIVE:\usr\sap\trans\bin

          To load the transport into the transport buffer, execute the following command at the \usr\sap\trans\bin directory and replace sysid with the system ID of your development system:

          tp addtobuffer TransportNumber sysid pf=TP_DOMAIN_sysid.PFL

          where TransportNumber is the actual transport number (for example BI1K900201).
          After the tp command finishes, you will see a report similar to the following:

          This is tp version 320.56.66 (release 620)
          Addtobuffer successful for TransportNumber
          tp finished with return code: 0

          Return code “0” means that everything is OK.

          A return code of 0 or 4 is acceptable. Contact Microsoft Product Support Services (PSS) if you receive a return code of 8 or above.

          Note: You can easily derive the actual transport number from the cofile file name. For example, a cofile named K900201.BI1 provides a transport number of BI1K900201.

          3. Import the transport into SAP
          At the command prompt, execute the following command:

          tp import TransportNumber sysid client=clientnumber pf=TP_DOMAIN_sysid.PFL

          Replace sysid with the system ID of your development system. Replace clientnumber with the client number of your development system.

          You can use the U2 parameter to overwrite previously installed objects, as follows:

          tp import TransportNumber sysid client=clientnumber U2

          – OR –

          tp import TransportNumber sysid client=clientnumber pf=TP_DOMAIN_sysid.PFL U2

          Note: You can easily derive the actual transport number from the cofile file name. For example, a cofile named K900201.BI1 provides a transport number of BI1K900201.

          After the tp command finishes, you will see a report similar to the following:

          This is tp version 320.56.66 (release 620)
          This is R3trans.exe version 6.08 (release 620 – 04.02.03 – 14:54:00).
          R3trans.exe finished (0000).
          This is R3trans.exe version 6.08 (release 620 – 04.02.03 – 14:54:00).
          R3trans.exe finished (0000).
          tp finished with return code: 0

          Return code “0” means that everything is OK.

          A return code of 0 or 4 is acceptable. Contact Microsoft Product Support Services (PSS) if you receive a return code of 8 or above.
          4. Check the transport log
          Check the transport log in SAPGUI Transport Organizer using transaction SE09 to verify that there are no errors.

          Just that is it.

          br
          ted

          (0) 
  2. Lumir Hlavac
    I try export data from SAP with .NET Provider for mySAP and I get the message:

    TITLE: SQL Server Import and Export Wizard
    ——————————
    The statement could not be parsed.

    ——————————
    ADDITIONAL INFORMATION:
    RFC Communication link error. Error Message ‘Function module “Z_READ_FUNCTION_METADATA” not found.’. (Microsoft.Adapter.SAP.SAPProvider)

    Any idea what causes this.

    Thanks,

    lumir

    (0) 
    1. Anton Wenzelhuemer
      To me it sounds like it doesn’t find the function module Z_READ_FUNCTION_METADATA. Just that :-))

      I’d have a look if the fm exists and if it is rfc enabled.

      regards,
      anton

      (0) 
      1. Lumir Hlavac
        I try install the rfc from customRFC.zip (..\Program Files\Common Files\Microsoft Shared\Adapters\SAP) and the instalation failed. In transport logs are errors.

        I have SAP version 4.6C on UNIX and ORACLE database.

        Is possible install .net data provider on this system?

        Thanks,

        lumir

        (0) 
        1. Ted Teng Post author
          I have no experience on Unix – SAP. Our SAP IDES runs on windows platform. I requested our SAP people to do as readme.html defined:

          4.2 Installing the RFCs
          To install the two RFCs, follow these steps:

          1. Copy files from the computer running SQL Server to the SAP application server
          Log in as the SAP R/3 system administrator to the SAP application server of your development system.

          Copy the transport file with the naming pattern K9*.BI1 from the installation directory on the computer running SQL Server to the following directory on the SAP application server:

          DRIVE:\usr\sap\trans\cofiles

          Copy the transport file with the naming pattern R9*.BI1 from the installation directory on the computer running SQL Server to the following directory on the SAP application server:

          DRIVE:\usr\sap\trans\data.
          2. Load the transport into the transport buffer on the SAP application server
          At the command prompt, navigate to the transport program directory on the SAP application server:

          DRIVE:\usr\sap\trans\bin

          To load the transport into the transport buffer, execute the following command at the \usr\sap\trans\bin directory and replace sysid with the system ID of your development system:

          tp addtobuffer TransportNumber sysid pf=TP_DOMAIN_sysid.PFL

          where TransportNumber is the actual transport number (for example BI1K900201).
          After the tp command finishes, you will see a report similar to the following:

          This is tp version 320.56.66 (release 620)
          Addtobuffer successful for TransportNumber
          tp finished with return code: 0

          Return code “0” means that everything is OK.

          A return code of 0 or 4 is acceptable. Contact Microsoft Product Support Services (PSS) if you receive a return code of 8 or above.

          Note: You can easily derive the actual transport number from the cofile file name. For example, a cofile named K900201.BI1 provides a transport number of BI1K900201.

          3. Import the transport into SAP
          At the command prompt, execute the following command:

          tp import TransportNumber sysid client=clientnumber pf=TP_DOMAIN_sysid.PFL

          Replace sysid with the system ID of your development system. Replace clientnumber with the client number of your development system.

          You can use the U2 parameter to overwrite previously installed objects, as follows:

          tp import TransportNumber sysid client=clientnumber U2

          – OR –

          tp import TransportNumber sysid client=clientnumber pf=TP_DOMAIN_sysid.PFL U2

          Note: You can easily derive the actual transport number from the cofile file name. For example, a cofile named K900201.BI1 provides a transport number of BI1K900201.

          After the tp command finishes, you will see a report similar to the following:

          This is tp version 320.56.66 (release 620)
          This is R3trans.exe version 6.08 (release 620 – 04.02.03 – 14:54:00).
          R3trans.exe finished (0000).
          This is R3trans.exe version 6.08 (release 620 – 04.02.03 – 14:54:00).
          R3trans.exe finished (0000).
          tp finished with return code: 0

          Return code “0” means that everything is OK.

          A return code of 0 or 4 is acceptable. Contact Microsoft Product Support Services (PSS) if you receive a return code of 8 or above.
          4. Check the transport log
          Check the transport log in SAPGUI Transport Organizer using transaction SE09 to verify that there are no errors.

          Just that is it.

          br
          ted

          (0) 
  3. Lumir Hlavac
    I try export data from SAP with .NET Provider for mySAP and I get the message:

    TITLE: SQL Server Import and Export Wizard
    ——————————
    The statement could not be parsed.

    ——————————
    ADDITIONAL INFORMATION:
    RFC Communication link error. Error Message ‘Function module “Z_READ_FUNCTION_METADATA” not found.’. (Microsoft.Adapter.SAP.SAPProvider)

    Any idea what causes this.

    Thanks,

    lumir

    (0) 
    1. Anton Wenzelhuemer
      To me it sounds like it doesn’t find the function module Z_READ_FUNCTION_METADATA. Just that :-))

      I’d have a look if the fm exists and if it is rfc enabled.

      regards,
      anton

      (0) 
      1. Lumir Hlavac
        I try install the rfc from customRFC.zip (..\Program Files\Common Files\Microsoft Shared\Adapters\SAP) and the instalation failed. In transport logs are errors.

        I have SAP version 4.6C on UNIX and ORACLE database.

        Is possible install .net data provider on this system?

        Thanks,

        lumir

        (0) 
        1. Ted Teng Post author
          I have no experience on Unix – SAP. Our SAP IDES runs on windows platform. I requested our SAP people to do as readme.html defined:

          4.2 Installing the RFCs
          To install the two RFCs, follow these steps:

          1. Copy files from the computer running SQL Server to the SAP application server
          Log in as the SAP R/3 system administrator to the SAP application server of your development system.

          Copy the transport file with the naming pattern K9*.BI1 from the installation directory on the computer running SQL Server to the following directory on the SAP application server:

          DRIVE:\usr\sap\trans\cofiles

          Copy the transport file with the naming pattern R9*.BI1 from the installation directory on the computer running SQL Server to the following directory on the SAP application server:

          DRIVE:\usr\sap\trans\data.
          2. Load the transport into the transport buffer on the SAP application server
          At the command prompt, navigate to the transport program directory on the SAP application server:

          DRIVE:\usr\sap\trans\bin

          To load the transport into the transport buffer, execute the following command at the \usr\sap\trans\bin directory and replace sysid with the system ID of your development system:

          tp addtobuffer TransportNumber sysid pf=TP_DOMAIN_sysid.PFL

          where TransportNumber is the actual transport number (for example BI1K900201).
          After the tp command finishes, you will see a report similar to the following:

          This is tp version 320.56.66 (release 620)
          Addtobuffer successful for TransportNumber
          tp finished with return code: 0

          Return code “0” means that everything is OK.

          A return code of 0 or 4 is acceptable. Contact Microsoft Product Support Services (PSS) if you receive a return code of 8 or above.

          Note: You can easily derive the actual transport number from the cofile file name. For example, a cofile named K900201.BI1 provides a transport number of BI1K900201.

          3. Import the transport into SAP
          At the command prompt, execute the following command:

          tp import TransportNumber sysid client=clientnumber pf=TP_DOMAIN_sysid.PFL

          Replace sysid with the system ID of your development system. Replace clientnumber with the client number of your development system.

          You can use the U2 parameter to overwrite previously installed objects, as follows:

          tp import TransportNumber sysid client=clientnumber U2

          – OR –

          tp import TransportNumber sysid client=clientnumber pf=TP_DOMAIN_sysid.PFL U2

          Note: You can easily derive the actual transport number from the cofile file name. For example, a cofile named K900201.BI1 provides a transport number of BI1K900201.

          After the tp command finishes, you will see a report similar to the following:

          This is tp version 320.56.66 (release 620)
          This is R3trans.exe version 6.08 (release 620 – 04.02.03 – 14:54:00).
          R3trans.exe finished (0000).
          This is R3trans.exe version 6.08 (release 620 – 04.02.03 – 14:54:00).
          R3trans.exe finished (0000).
          tp finished with return code: 0

          Return code “0” means that everything is OK.

          A return code of 0 or 4 is acceptable. Contact Microsoft Product Support Services (PSS) if you receive a return code of 8 or above.
          4. Check the transport log
          Check the transport log in SAPGUI Transport Organizer using transaction SE09 to verify that there are no errors.

          Just that is it.

          br
          ted

          (0) 

Leave a Reply