Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

How to integrate SAP PI with Teradata (Sync Macros without mapping pass through trick in ¡just 1 service interface out!)

azael_navarro
Active Participant
‎04-27-2016 10:53 AM
0 Kudos

Now a days we can see that there are a lot of requirements regarding to implement our Big Data, ERP interoperability or Data Warehousing needs, and some companies use SAP PI independent if it is or not a best practice. So for this reason i share some interesting topics:

  1. One Trick: Implement just one service outbound interface, without inbound interfaces in Synchronous way,
  2. Share just one datatype and messagetype,
  3. Implement an ICO including just your outbound interface, forcing the configuration without requiring the inbound interface,
  4. Request Information from Teradata via Macro, but with this you can get different tips in order to implement other SQL Logics like if you were working with SQL Server, MySQL, etc…
  5. Here you can find interesting information about Teradata and SAP Architecture Proposal: ftp://ftp.ucg.com/boston/2008%20BI%20&%20Netweaver%20Portal__Dynamic%20Items%20Report/Teradata_partn...

A. What Teradata is?:


B. After that, you will be able to implement different SAP PI Integrations using SQL Best Practices:


C. SAP PI Configuration:


1. Enterprise Service Repository:


You can implement just ONE Service Interface Outbound simulating the Inbound service interface, just if Metadata (Data Types) are equals…


We implement the structure to request the information via Macro and from the clienttest (sender) we will add the data requeried, and will be delivered the same structure to the receiver:

For the response we create the next structure, just adding that we need to include “_response” after the name of the above DT_name, now called DT_name_response.

2. Integration Directory:


Now we can implement a classic interface or an ICO (Integrated Configuration) as bellow, and the important logic here is that the Service Interface Outbound must to be configured in the Receiver Interfaces following the “trick”. This is because we have the same structure in the sender and the receiver metadata.

We can implement a SOAP Sender Communication Channel, to play with SOAPUI and to confirm the functionality.

And the important configuration is the JDBC Receiver, to connect to Teradata via JDBC Driver and the important parameters.

    • JDBC Driver: com.teradata.jdbc.TeraDriver
    • Connection: jdbc:teradata://IP_NUMER/DATABASE=NAME,PORT=1025,CHARSET=UTF8
    • Username & Password

    • Maximum Concurrency: 15

    • Advanced Mode: you can set number of retries of database transaction on SQL Error a default transaction isolation Level and you can set Disconnect from Database After Processing Each Message.

D. Teradata Structures:


The SQL Macro Sentence that execute the macro, will be:


EXEC MACRO_TERADATA_NAME (3, '2017-03-03');

And the Macro maybe call a select statement, with some structure like this, that will return in our example the rows that match with Id=3 and Date=2017-03-03:

Table: Structure to be called by MACRO_TERADATA_NAME

Id

Date

Amount

3

2017-03-03

  1. 100000.0000

3

2017-03-03

  1. 200000.0000

3

2017-03-03

  1. 300000.0000

2

2017-03-03

  1. 300000.0000

1

2017-03-03

  1. 300000.0000

E. Test via SOAPUI to SAP PI SOAP to SAP PI JDBC Teradata driver in Synchronous Mode.


Now we can request the following information:




<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="HERE YOUR NAMESPACE">

   <soapenv:Header/>

   <soapenv:Body>

      <urn:MT_Teradata>

         <SAPStandard4SQL>

            <Macro action="SQL_QUERY">

               <access>EXEC MACRO_TERADATA_NAME (3, '2017-03-03');</access>

            </Macro>

         </SAPStandard4SQL>

      </urn:MT_Teradata>

   </soapenv:Body>

</soapenv:Envelope>

In SOAPUI we confirmed that we consumed the web service, requesting the EXEC Statement and we received the response whit the 3 rows that matched:

And we will receive something like this:




<SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">

   <SOAP:Header/>

   <SOAP:Body>

      <urn:MT_Teradata_response xmlns:urn="USE HERE URE NAMESPACE">

         <SAPStandard4SQL_response>

            <row>

               <Id>3</Id>

               <Date>2017-03-03</Date>

               <Amount>100000.0000</Amount>

            </row>

            <row>

               <Id>3</Id>

               <Date>2017-03-03</Date>

               <Amount>200000.0000</Amount>

            </row>

            <row>

               <Id>3</Id>

               <Date>2017-03-03</Date>

               <Amount>300000.0000</Amount>

            </row>

         </SAPStandard4SQL_response>

      </urn:MT_Teradata_response>

   </SOAP:Body>

</SOAP:Envelope>

With this we can confirm the easy way to integrate Teradata and SAP PI in other ways...


Thank you!,

Azael

Labels in this area
Popular Blog Posts