Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Query works in Advance Editor but in a Custom Connector it's returning "This table is empty"

Hi All, I'm building a Custom Connector to pull data from one of our products using a Web API. It calls an XML Web Service which returns an XML response.

The overall Connector works perfectly, but I need to refine the call. It needs to loop through the Web Call until all rows have been returned as the Web Service will only return 5000 rows per response.

When executing the Web Call without Paging (getting max 5000 rows), it works perfectly. There is a lot of manipulation to get the XML response into the correct format (the response is horrendously formatted, but there is zero I can do about it).

As soon as I wrap this call up in the Pagination logic, when connecting in Power BI, it's just returning 'This table is empty'.

I've checked every component and it works fine. We also have a JSON format of the Web Service which works perfectly including wih the Pagination, but I can't use that because it's missing key data in the response.

 

This is the code in the connector (the code is too long to add as code here):

Screenshot 2021-07-20 214727_1.png

Screenshot 2021-07-20 214755_2.png

Screenshot 2021-07-20 214851_3.png

The GridWSCall part on it's own works perfectly and I get the data in Advance Editor and Power BI correctly.

 

 

 

 

 

I'm out of ideas on why I'm getting no data in Power BI:

Screenshot 2021-07-20 214225.png

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I've resolved the issue but created a new problem.

The incoming repsonse format for JSON is different to the XML one, so I had to re-format the XML response slightly into the same format. Now the pagination is working, but leads to my new problem.

As its paginating, firstly it seems to be making the WebService call twice from the outgoing messages I can see in Fiddler. I can see the same request twice.

Secondly, it appears to be re-calling messages it has already requested and in random orders:

eg

Call 1 > Cursor Position 1

Call 2 > Cursor Position 1

Call 3 > Cursor Position 2001

Call 4 > Cursor Position 2001

Call 5 > Cursor Position 4001

Call 6 > Cursor Position 4001

Call 7 > Cursor Position 2001

 

it eventually loaded all the data, but seems to have done a lot more work than it should have, thereby taking longer. My test table only had 16K rows which it grabbed in 2000 row increments, but we have tables with 1m+ records.

Any ideas?

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

I've tested it by removing all of the manipulation of the data and just calling the WebService within the Pagination logic. I hardcoded the known responses (number of rows, IsMore) and it's still returning 'This table is empty'.

XMLWebService = (hostUrl as text, gridCode as text, optional filterField as text,  optional RangeStart as datetime, optional RangeEnd as datetime)  =>

let
    hostUrl = hostUrl,
    gridCodeString = """" & gridCode & """",
    filterFieldString = """" & filterField & """",
    filterFrom = if RangeStart = null then """1900-01-01""" else """" & RangeStart & """",
    filterTo = if RangeEnd = null then """""" else """" & RangeEnd & """",
    XMLUserName = Extension.CurrentCredential()[Username],
    XMLUserPassword = Extension.CurrentCredential()[Password],
    Pagination = List.Skip(List.Generate( () => [IsMore = null, Last_Key = 0, Counter = 0], // Start Value
                    each [IsMore] <> false,// Whilst this is true, keep going
                    each [GridWSCall = Xml.Tables(Web.Contents(hostUrl ,
                                    [Headers = [#"Content-Type" = "text/plain"],
                                        Content = Text.ToBinary(Text.Combine({ "<?xml version=""1.0"" encoding=""utf-8""?>",
                                               "<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"">
                                                 <soapenv:Header xmlns:wsa=""http://www.w3.org/2005/08/addressing"">
                                                <wsse:Security xmlns:wsse=""http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"" soapenv:mustUnderstand=""1"">
                                                    <wsse:UsernameToken xmlns:wsu=""http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"" wsu:Id=""UsernameToken-1"">",
                                                        "<wsse:Username>",XMLUserName ,"</wsse:Username>",
                                                        "<wsse:Password Type=""http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText"">",XMLUserPassword,"</wsse:Password>",
                                                    "</wsse:UsernameToken>",
                                                "</wsse:Security>",
                                                "<wsa:To>", hostUrl , "</wsa:To>",
                                                "<wsa:MessageID>urn:uuid:20210225001</wsa:MessageID>",
                                                "<wsa:Action>urn:processMessage</wsa:Action>",
                                            "</soapenv:Header>
                                            <soapenv:Body>
                                                <ABC123_GetDataOnly_001 xmlns=""http://schemas.datastream.net/MP_functions/ABC123_GetDataOnly_001"" xmlns:xs=""http://www.w3.org/2001/XMLSchema"">
                                                    <FUNCTION_REQUEST_INFO REQUEST_TYPE=""LIST.HEAD_DATA.STORED"">",
                                                        "<GRID GRID_NAME=", gridCodeString 
                                                        , " USER_FUNCTION_NAME=" ,gridCodeString 
                                                        , " NUMBER_OF_ROWS_FIRST_RETURNED=", """2000""" 
                                                        , " CURSOR_POSITION=",  """1"""  
                                                        , " TERSERESPONSE=""true""", " RESULT_IN_SAXORDER=""true"""
                                                        , " LOCALIZE_RESULT=""false""/>",
                                                        "<GRID_TYPE TYPE=""LIST""/>",
                                                        "<MULTIADDON_FILTERS>",
                                                          
                                                       "</MULTIADDON_FILTERS>",
                                                        "<DATASPY DATASPY_ID=""""/>",
                                                    "</FUNCTION_REQUEST_INFO>",
                                                "</ABC123_GetDataOnly_001>",
                                           " </soapenv:Body>",
                                       " </soapenv:Envelope>" } ))])),// retrieve results per call

                                                             
  
    Last_Key = 12,
    IsMore = if 13 < 1 then null else false,
    Counter = 13+1,

    splitList = GridWSCall
  
    ]
    ,each [splitList] // selector
) ,1)
    
    , Custom = Table.Combine(Pagination)

    in Custom ;
v-jingzhang
Community Support
Community Support

Hi @Anonymous 

 

I think the problem may come from the start and end part of Pagination codes. Maybe the loop logic has some mistake. Try using different start values and some definite loop times (e.g. once, twice) to test it. And also test whether the end loop is correct.

 

Regards,
Community Support Team _ Jing

Anonymous
Not applicable

I've resolved the issue but created a new problem.

The incoming repsonse format for JSON is different to the XML one, so I had to re-format the XML response slightly into the same format. Now the pagination is working, but leads to my new problem.

As its paginating, firstly it seems to be making the WebService call twice from the outgoing messages I can see in Fiddler. I can see the same request twice.

Secondly, it appears to be re-calling messages it has already requested and in random orders:

eg

Call 1 > Cursor Position 1

Call 2 > Cursor Position 1

Call 3 > Cursor Position 2001

Call 4 > Cursor Position 2001

Call 5 > Cursor Position 4001

Call 6 > Cursor Position 4001

Call 7 > Cursor Position 2001

 

it eventually loaded all the data, but seems to have done a lot more work than it should have, thereby taking longer. My test table only had 16K rows which it grabbed in 2000 row increments, but we have tables with 1m+ records.

Any ideas?

 

Hi @Anonymous 

 

It's difficult to find the cause at my side... Is it possible to grab all the requests sent to the API and check the generated parameter values? And do the JSON and XML reponses always return the same number of rows? 

 

Jing

Anonymous
Not applicable

Thanks. From what I have read, it sounds like it is just the behaviour of the Web Service when the Authorisation credentials are part of the body, which is the case for the XML Web Service i'm using. Our JSON one, the Authorisation is in the header and works perfectly and much more efficiently. I guess this is just the way it will be.

Anonymous
Not applicable

Thanks Jing. This connector will either process the message as JSON or XML, depending on the connection parameter.

The JSON call is absolutley identical to the XML call (copy and paste) with only the the actual Web call part changing. However, the JSON with pagination works absolutley perfectly. 

The two messages are identical apart from the GridWSCall, so I'm really confused as to why JSON works and XML doesnt.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors