Query SAP Business ByDesign Web Services via Power Query

In this post I would like to share my experience about work with web services using Power Query.

Unfortunately, some very important information is not available in data sources, e.g. Accounts Relationships and Pricing Conditions (price lists, customer specific discount lists etc.). Hope someday we will get this in reports.

However, currently, we can get this info only from

UI manually, almost impossible to do if you have X tenants and XXX customers with specific price lists

Web services – Jedi path, require knowledge and skills, or trustable tools, that can be checked

To our luck, with every new release we get more and more web services.

Basic idea of web service usage

Make POST http request with XML
Parse XML response

Firstly, we can make queries and test everything in SOAP UI. Just because it is easier to understand how web services work in SOAP UI examples.

Following two posts help to get basic knowledge

Assume that you know how to create XML query in SOAP UI.

Now, step by step about PQ part.

I’ve started my journey from nice post of Chris Webb

http://blog.crossjoin.co.uk/2014/04/19/web-services-and-post-requests-in-power-query/

Got basics of http POST request in M code (Power Query programming language)

WS_query = Web.Contents(

URL_ADDRESS,

[Content=Text.ToBinary(PostContents)]),

In our case with ByD, PostContents is equal to XML request.

Another good example from Curt Hagenlocher

http://stackoverflow.com/questions/28361628/how-to-post-a-multipart-form-data-using-power-querys-web-contents

Let

actualUrl = “http://some.url“,

record = [__rdxml=”some data”],

body = Text.ToBinary(Uri.BuildQueryString(record)),

options = [Headers =[#”Content-type”=”application/x-www-form-urlencoded”], Content=body],

result = Web.Contents(actualUrl, options)

result

This code sample shows, that second parameter of Web.Contents can be complex enough.

In official documentation on MSDN you may full list of options for second parameter

For our web service request to ByD we need Headers, Content, and Timeout (in some cases, but be careful).

Consider web service Query Accounts

After we make basic query in SOAP UI we have

Headers

XML query

I’m not a specialist in web technologies, so method of trial and errors revealed that important for request only following headers

Authorization
#”Content-Type” = “text/xml;charset=UTF-8”,

Rest

SOAPAction – query works without it, but I’m not sure that it will work for other queries. So probably we will need it.
#”Connection” = “Keep-Alive” – suppose in some cases it is useful. Maybe someone can advise when use it and when omit?

Others – question for me. I decided exclude them from my queries.

Authorization looks not convenient for reading by humans. After playing around I found that it is in Base64 encoding and can be received in PQ by following code

Authorization = “Basic ” & Binary.ToText(Text.ToBinary(Login & “:” & Password))

Typical content of query

“<?xml version=””1.0″” encoding=””UTF-8″”?>

<soapenv:Envelope xmlns:soapenv=””http://schemas.xmlsoap.org/soap/envelope/”” xmlns:glob=””http://sap.com/xi/SAPGlobal20/Global””>

<soapenv:Header/>

<soapenv:Body>

<glob:CustomerByIdentificationQuery_sync>

</SelectionByInternalID>

</CustomerSelectionByIdentification>

</ProcessingConditions>

</glob:CustomerByIdentificationQuery_sync>

</soapenv:Body>

</soapenv:Envelope>”

Looks pretty simple for one account. To get several accounts we have to repeat highlighted block with corresponding Account ID.

So we need a list of

“<SelectionByInternalID><InclusionExclusionCode>I</InclusionExclusionCode><IntervalBoundaryTypeCode>1</IntervalBoundaryTypeCode><LowerBoundaryInternalID>” & AccountID & “</LowerBoundaryInternalID></SelectionByInternalID>”)

Not a problem for PQ. Start from table ACCOUNTS, that contains list of Account IDs which data we want to get from web service.

Couple lines of code

Accounts = Table.ToList(Excel.CurrentWorkbook(){[Name=”ACCOUNTS”]}[Content]),

Query_List = List.Transform(Accounts, each “<SelectionByInternalID><InclusionExclusionCode>I</InclusionExclusionCode><IntervalBoundaryTypeCode>1</IntervalBoundaryTypeCode><LowerBoundaryInternalID>” & _ & “</LowerBoundaryInternalID></SelectionByInternalID>”)

Give us

Our resulting XML request is a string, long, long string. So list should be converted to text, to be able to join it to header and footer of XML.

Query_Text = Text.Combine(Query_List)

gives

Text without delimiters. For XML it is doesn’t matter. Less characters – less size.

OK, we are ready to make full text of request in PQ:

Request_body = “<?xml version=””1.0″” encoding=””UTF-8″”?>

<soapenv:Envelope xmlns:soapenv=””http://schemas.xmlsoap.org/soap/envelope/”” xmlns:glob=””http://sap.com/xi/SAPGlobal20/Global””>

<soapenv:Header/>

<soapenv:Body>

<glob:CustomerByIdentificationQuery_sync>

<CustomerSelectionByIdentification>”

Query_Text

“</CustomerSelectionByIdentification>

<ProcessingConditions>

<QueryHitsUnlimitedIndicator>true</QueryHitsUnlimitedIndicator>

</ProcessingConditions>

</glob:CustomerByIdentificationQuery_sync>

</soapenv:Body>

</soapenv:Envelope>”

And finally, we can make a web request

WebService_query = Binary.Buffer(Web.Contents(“https://my” & TenantId & “.sapbydesign.com/sap/bc/srt/scs/sap/querycustomerin1?sap-vhost=my” & TenantId & “.sapbydesign.com HTTP/1.1”,

[Headers=[#”Content-Type” = “text/xml;charset=UTF-8”,

#”Authorization” = Authorization ],

Content=Text.ToBinary( request_body )])),

URL we copy from SOAP UI, it is provided by WSDL file.

I use Binary.Buffer to load result in memory to

avoid occasional additional queries of web service. Don’t know if it is possible, but just in case
Make easier further work with data

We know that response of web service is XML. In PQ it is very easy to parse XML, simply expanding nodes. Each XML subtree is a table object in PQ

response

Open as XML

We got simple table

We need Body of response, so click on Table in Body row

No choice – click on Table

Again no choice – click on Table

Where is my data? Click on Table

Good, I bet, that data in Customer’s row.

Before drilldown to table we can select cell with Table and check data in preview area

We have two options here

Idea from post of Chris Webb

Table3{[Name=”Customer”]}[Table]
Filter + Remove column

#”Filtered Rows2″ = Table.SelectRows(Table3, each ([Name] = “Customer”)),

#”Removed Columns1″ = Table.RemoveColumns(#”Filtered Rows2″,{“Name”})

In 1st option Table auto-expands with full list of fields in it, that most probably not what we want.

I went with 2nd option – left necessary table and then expand it manually, choosing fields that I need.

Last steps in code view

#”Imported XML” = Xml.Tables(WebService_query),

Table = #”Imported XML”{1}[Table],

Table1 = Table{0}[Table],

Table2 = Table1{0}[Table],

Table3 = Table2{0}[Table],

#”Filtered Rows2″ = Table.SelectRows(Table3, each ([Name] = “Customer”)),

#”Removed Columns1″ = Table.RemoveColumns(#”Filtered Rows2″,{“Name”})

What I like in Power Query, is that I don’t have to program all of this transformations. Development is much faster than with VBA or other programming language. PQ is desinged for work with data.

OK, Next step is to expand table, we can choose fields from menu easily.

Relationship is a sub-table itself

One more expand

Where

CRMH02-2 – Is Ship-To Party Of

BUR001-1 – Has Contact Person

Great, we got relationships, that are not available in data sources for reports!

Final code

let

GetListOfAccountsGeneralData = (Accounts as list) =>

let

TenantId = Number.ToText(Excel.CurrentWorkbook(){[Name=”TENANT_ID”]}[Content]{0}[Column1]),

Login = Excel.CurrentWorkbook(){[Name=”USERNAME”]}[Content]{0}[Column1],

Password = Excel.CurrentWorkbook(){[Name=”PASSWORD”]}[Content]{0}[Column1],

Query_Text = Text.Combine(Query_List),

request_body = “<?xml version=””1.0″” encoding=””UTF-8″”?>

<soapenv:Envelope xmlns:soapenv=””http://schemas.xmlsoap.org/soap/envelope/”” xmlns:glob=””http://sap.com/xi/SAPGlobal20/Global””>

<soapenv:Header/>

<soapenv:Body>

<glob:CustomerByIdentificationQuery_sync>

<CustomerSelectionByIdentification>”

Query_Text

“</CustomerSelectionByIdentification>

<ProcessingConditions>

<QueryHitsUnlimitedIndicator>true</QueryHitsUnlimitedIndicator>

</ProcessingConditions>

</glob:CustomerByIdentificationQuery_sync>

</soapenv:Body>

</soapenv:Envelope>”,

Authorization = “Basic ” & Binary.ToText(Text.ToBinary(Login & “:” & Password)),

WebService_query = Binary.Buffer(Web.Contents(“https://my” & TenantId & “.sapbydesign.com/sap/bc/srt/scs/sap/querycustomerin1?sap-vhost=my” & TenantId & “.sapbydesign.com HTTP/1.1”,

[Headers=[#”Content-Type” = “text/xml;charset=UTF-8”,

// #”SOAPAction” = “http://sap.com/xi/A1S/Global/QueryCustomerIn/FindByIdentificationRequest“,

// #”Connection” = “Keep-Alive”,

#”Authorization” = Authorization ],

Content=Text.ToBinary( request_body )])),

#”Imported XML” = Xml.Tables(WebService_query),

Table = #”Imported XML”{1}[Table],

Table1 = Table{0}[Table],

Table2 = Table1{0}[Table],

Table3 = Table2{0}[Table],

#”Filtered Rows2″ = Table.SelectRows(Table3, each ([Name] = “Customer”)),

#”Removed Columns1″ = Table.RemoveColumns(#”Filtered Rows2″,{“Name”})

#”Removed Columns1″,

result = GetListOfAccountsGeneralData(Table.ToList(Excel.CurrentWorkbook(){[Name=”ACCOUNTS”]}[Content])),

#”Expand Table” = Table.ExpandTableColumn(result, “Table”, {“InternalID”, “Relationship”}, {“InternalID”, “Relationship”}),

#”Expanded Relationship” = Table.ExpandTableColumn(#”Expand Table”, “Relationship”, {“RelationshipBusinessPartnerInternalID”, “RoleCode”}, {“RelationshipBusinessPartnerInternalID”, “RoleCode”})

#”Expanded Relationship”

Reasonable question, what happen when we input 20k accounts?

Of course, web services have limitation. They should not be used for reporting purpose. However, when data not available from reports – we have no choice.

For large list of account we must use paging.

There are two options

Paging engine that is provided by web services
“Manual Paging”, or paging on client side

First option is not easy to implement. Therefore, I skip it.

In my sample workbook you will find 2nd option.

I called it “paging on client side”. General idea is to divide initial requested list of objects on list of small lists, that contain default quantity of objects. Quantity, that be surely digested by web service, e.g. 500 accounts.

When I came to this task Chris Webb released small but great post, which helped me to understand magic of List.Generate. Comments to this post are very useful as well.

I tried to use Table.AddColumn before, passing parameters of query in tables rows, but Power Query makes request for all rows in one moment, therefore web service going crazy and gives same result for different rows. I couldn’t find a way to make delay between rows when use Table.AddColumn.

So, I came to requirement to make loop. In PQ this job is done by List.Generate

Code, that will help you to make query for 20k accounts

let

Accounts = Table.ToList(Excel.CurrentWorkbook(){[Name=”ACCOUNTS”]}[Content]),

Count = List.Count(Accounts),

Step = 500,

Steps = Number.RoundUp(Count / Step),

Delay = 1, // seconds

r = List.Buffer(

List.Skip(List.Generate(

() => [

i = 0,

Page = null

each [i] <= Steps,

each let

Accounts_List_To_Query = List.Range(Accounts, Step * [i], Step),

GetPage = (Accs as list) => GetListOfAccountsGeneralData(Accs)

in [

i = [i] + 1,

Page = Function.InvokeAfter(()=>GetPage(Accounts_List_To_Query), #duration(0,0,0, Delay))

each [Page]

)

)),

#”Table from List” = Table.FromList(r, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#”Expand Column1″ = Table.ExpandTableColumn(#”Table from List”, “Column1”, {“Table”}, {“Table”}),

#”Expand Table” = Table.ExpandTableColumn(#”Expand Column1″, “Table”, {“InternalID”, “Relationship”}, {“InternalID”, “Relationship”}),

#”Expanded Relationship”

Conclusion

We can make a simple workbook that pulls data from web service for provided list of accounts.

Three parts of such workbook

II.

III.

Result

I’m sure, mentioned queries can be optimized, universalized for different web services (up to Expand part).

Downloads

You may download sample workbook from this link.

Excel City

Query SAP Business ByDesign Web Services via Power Query

3 thoughts on “Query SAP Business ByDesign Web Services via Power Query”

Leave a Reply Cancel reply