Simple reporting over PI communications channels

If you want to overview details of your SAP XI systems, so it would help you in some cases – http://hixay.googlecode.com/files/XID_CC_REPORT_2009-05-28.xls

This is simple excel workbook with macros which can ask SAP XI systems through standard web-services. You can write down your login info and list of systems to the first sheet, select the second one and press the “Ask the XI” button. After some pause you’ll get the brief info about communication channels and full-featured info at the third worksheet.

h2. Limitations and prerequisites

On the client side, this excel macro works as VBA macro only in Excel/Windows and uses Microsoft’s library MS XML v6.0, which can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyID=993c0bcf-3bcf-4009-be21-27e85e1857b1&displaylang=en .

For the server side, XI/PI system with Integration Directory API is only required. These are XI3.0 SPS>=21, PI7.0 SPS>

=13 or PI7.1, according to Directory API Development – Part 1 of 3 . Currently this macro was tested on 70SPS16, 70SPS18 and 71SPS8 without any errors.

h3. What will you get as result?

It is a table, something like this:
!http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic08.png|alt=See result|src=http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic08.png!

For me and my colleguaes, there were need in report built on three PI systems, where we could search or filter all communication channels and their detail: who and when changed last time, what is the current state, most important address or URL, login name, etc.

h3. How to use, step-by-step

Download and open the macro with appropriate security level

Because of viruses, many computers ban spreadsheets with active content. You should allow macroses, either choosing appropriate button at start: !http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic00.png|alt=Excel security alert window|src=http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic00.png!

or with touching by menu (Service -> Macros -> Security levels): !http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic01.png|alt=Excel security settings|src=http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic01.png!

Of course you have to turn MSXML library on. For that, download and install it if required and swith to excel. Press Alt+F11, then Tools -> References, then select this library: !http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic02.png|alt=MSXML reference|src=http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic02.png!

h3. Configuring source systems

Initially after loading, you get this default sheet with list of source systems and some references to help information about adding required rights for username, as it was said in API documentation:
You should write required attributes of systems down, for example:
There are several columns which could be used for various needs:

• Disabled – mark here ‘X’ if you want to eliminate reporting from this system for while;
• Use cache – mark here ‘X’ if you want to use cached answers from XI system. All the requests are cached but results used only if this mark is switched on for given system;
• hostname, sysnr, user – it’s clear, yeah?
• password – you can write here password but it’s unsecure. So if the field is blank, excel will ask for the password in pop-up window;
• base port – you can use either 8xxx or 5xxxx-based requests;
• url – this is target automatically-built url which is really used for asking through web-services

h3. Request the data

Switch to second sheet and press the button down:
Then, popup will appear if password wasn’t set in first sheet:!http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic07.png|alt=Input password if it|src=http://hixay.googlecode.com/svn/trunk/doc/excel/excel_pic07.png!
After some pause (and if no errors in authorisation occurs) you’ll get the result as it was said before .
But additionally at the third sheet you can configure or just look at source (non-formatted raw) information about channels:       contains all answers for this system. For example:
One file is just ordinary XML which you can use your own way:
If you set ‘Use proxy’ field, then these files will be used as source data.

h2. How it works? How can you customize this?

Initially I tried to use MS Soap Client but it failed with SAP wsdl’s. So after some thinking I started make requests by hand and analyse answers with simple XPath. The macro sends ordinary HTTP request to XI using this code (see SendRequest function):

Private Function SendRequest(src As String, xis As TXISystem, Optional ByRef statusCode As Integer, Optional ByRef statusText As String) As String
Dim port As New MSXML2.XMLHTTP60
On Error GoTo 1
Call port.Open(“POST”, xis.url, False, xis.uname, xis.password)
Call port.setRequestHeader(“User-Agent”, “Excel”)
Call port.setRequestHeader(“SOAPAction”, “”””)
Call port.setRequestHeader(“Content-Type”, “text/xml;charset=UTF-8”)
Call port.send(src)
SendRequest = port.responseText
statusCode = port.Status
statusText = port.statusText
Exit Function
1:
SendRequest = “”
statusCode = -1
statusText = “Unknown”
End Function

So, it’s really easy just parse XMLed feedback in code — some handly-written WSDL proxy.

Here all the functions with some remarks:

1. init_wbk – setup templates of requests and parse list of systems
2. refresh_clist_manual – make human-readable report on second sheet from raw data on the third sheet
3. refresh_xi – loop at all systems, fill the third sheet down, call refresh_clist_manual
4. SendRequest – just make HTTP request
5. retrieve – retrieve information from XI or from file, applying user-defined XPath’s to file and put results to AChannelDetails array
6. publish_on_sheet – write AChannelDetails array to third sheet
7. ClaimError – to pop-up the error message

h3. XPath is very powerful tech

Well, why I have used XPath and told about it so much? Because it’s really good in XML addresses here. The third sheet (“raw”) contains just XPath expressions, for example: //pns0:AdapterSpecificAttribute[pns0:Name=’adapterStatus’ or pns0:Name=’file.adapterStatus’]/pns0:Value for getting the adapter’s state either non-file and file. Human-readable report is built from references to these fields and you can customize it with your own way.

h2. What the next?

SAP said that we can use these services so we really do that. Excel reporting and communication channels aren’t the only goal, but there are people who want to automate routine jobs and to make easier our work in XI.

[Hixay | http://code.google.com/p/hixay/] is project aimed for this ideas. Our first goal is to make many reports (as much as possible) and to combine data from various source (SLD, ID, RWB, user-defined sources) into smart and handy visualisation. Excel macro was just “proof-of-concept” and demonstration of possibilities, not our current work. Hixay has been written from scratch, it’s standalone program (and you use it under Windows, Linux and other mainstream OSes — everywhere Haskell GHC could compile it), and it will produce some interesting reports and even could make changes in XI through API.

Today we can get some graphs, for example see one scenario and big picture . They are built fully automatically from 7.1 and 7.0 systems correspondingly. There are proof-of-concept too, and part of chains aren’t handled correctly yet. Some of errors are here (one communication channel for two systems for example). Also, plain-text script is produced (configuration scenario-based view), but this needs a lot of work before getting stable and good-looking reports of course.

I hope, somebody could find this approach for automating XI and producing documentation interesting a bit.