SAP PI/PO Directory API: Extract detailed Communication Channel configurations into an Excel sheet **without custom codes/macros**

SAP PI/PO Directory API: Extract detailed Communication Channel : How many times have you wished that you could have a searchable and a sort-able data for all your PI/PO Communication Channels? So, you could answer certain burning questions like

1. How many JMS channels are using KeepAlive parameter?
2. List all channels which are using a service user to connect.
3. List JMS channels which are using header JMS properties.
4. List Sender File channels with file polling interval greater than 60 secs.
5. What Channels are using certain Adapter Modules?

There are couple of nicely created solutions using the Directory API by fellow SAP PI bloggers. One of them that I’ve used in the past is here. It is easy to use, you just have to plug in your PI/PO server hostname, user and password and the excel Macro will connect to your PI/PO server and read in all the information. Unfortunately, for us we weren’t able to use it because of security concerns related to using non-SAP Excel Macro, which would also require the hostname, user and password.

But, with some xml editing we were able to get the same information out of our PI/PO servers without any Excel Macro or custom programs. Once we had the data in an Excel sheet were able to format it to our liking.

To start, you would need one Java role for the queries we are going to use.

• SAP_XI_API_DISPLAY_J2EE (for the operations Query, Read, Check, GetState, CheckContent, GetCacheState, GetObjectIdentifier)

There is another role *DEVELOP* if you would like to update communication channel configs using the API queries. Usually, we don’t need these queries because everyone has access to Integration Builder and Enterprise Services Repository, but if you wish to make mass updates to your communication channels then role, SAP_XI_API_DEVELOP_J2EE, would come in handy.

So, for this exercise I will just use the SAP_XI_API_DISPLAY_J2EE role because we are going to invoke two API WSDL operations i.e. Query and Read. 

Let’s talk a little about these two operations and our high level steps.

1. Open NWA and go to Single Service Administration page.
2. We will use Query operation to get an XML file containing pairs of Party, Business System and Communication Channels. In short, this XML file will have names of your Communication Channels, its Party and Business System (i.e. Communication Component).
3. We will lightly edit this XML (from the previous step) and make it ready to plug into the Read (2nd query).
4. Now, we are ready to run the 2nd Query (Read) with our XML as an input.
5. 2nd query’s (Read) response will be a huge XML file containing details of ALL our Communication Channels. So, we should adjust our timeout value to accommodate a longer response time. I usually use 600 sec.
6. Once we get the XML with all the details, we can import it in an Excel sheet and format as much as we like to make it more readable and searchable.

So, these were the high level steps. Let’s dive into some screenshots and details.

Note: I am using PO 7.5, but the steps are equally valid in PI 7.11 and above.
Please increase your browser zoom to view some of these images 

1. Login to PO, go to NWA -> …. -> Single Service Administration
2. Search for CommunicationChannelIn in WSDL Port Type Name. Select and go to WSDL and click on Test button.
3. In Operation step , select Query operation and click on Next button.
   
   
4. In Input Parameters step uncheck Skip boxes and click on Next button. This would give us a high level information of all of our Communication Channels, Party and Business Systems (Component ID)
5. Type in user/pass and click Ok
6. In the Result window (Right side) -> Tree View tab we should see the number of Communication Channels and should be able to expand and traverse it further. Here I have 118 communication channels.
7. Download the Results in an XML file.
8. Open the downloaded XML (Read Query Response) for editing. We would follow below screenshots to
   1. Replace text CommunicationChannelQueryResponse with CommunicationChannelReadRequest
   2. Note: PI 7.11 required me to take care of few more tags like <SOAP…> and . If your xml is not properly formatted then you will get an error. See step 13.
      
9. Save the edited xml as “ReadQueryRequest.xml”
10. Let’s go back to the main operations screen (by clicking on Previous button) and select Read operation and click Next
11. On the Input Parameters screen. Click on the downward arrow next to Import button and select Test Data.
12. Select the edited XML file (from step 8) and upload it.
13. Edited XML will be imported with all its data. You might see some errors if the XML is not formatted properly. I had to correct (see Note in step 8) few more tags in PI 7.11 but, PO 7.0 seems to be more forgiving.
14. Click on the Invocation Parameters button and increase the Timeout value from 60 to 600 and click Ok.
15. Enter user/pass at the prompt.
16. It might take few mins to fetch the data. We should see following screen with the Results
17. Click on the XML tab and download the .xml file with all the results.
18. Read operation Results XML file will look like this with details of all the Communication Channels
19. Open MS Excel and import the .xml Read operation Results file.
20. Follow the Excel import prompts
21. Once imported the raw imported data would look like below.
22. Data can be formatted to make it easier to sort and search. I only had to work on main 4 columns to make it easy to search and read.

As you can see we were able to completely extract this important Communication Channel data without any custom code or Excel Macros. Also, we could follow same steps to extract other important information like ESR object list, Mapping details etc out of ESR.

Please let me know if you have questions or suggestions. Good Luck!!