Power BI/Power Query: Load JIRA Issues in Parallel

For nearly a year I haven't done any posting, as I was doing some works not relevant to SQL things, AR/MR with Unity, Machine Learning/Deep Learning, and Flask development. Now I am back to data related works so hopefully I will restart blogging my data experience. Let's see what we can do today.

Due to performance issue, JIRA made the decision to limit amount of records returned from its Rest API to 100 (here). But what if we are working on a large number of issues? like this

So we need to make the API call for more than 200 times. So depends on business requirement, the Power BI report could be just too long to refresh.

An alternative way to handle large amount of issues is we call the API in parallel. To do that, firstly let's create a basic function ufnGetJson to call the API (replace the Organization place holder with correct value):

 (nStartAt) =>  
     let   
       query = Json.Document(Web.Contents("https://{organization}.atlassian.net/rest/api/2/search?maxResults=100&startAt=" & Number.ToText(nStartAt)))  
     in  
       query  

Then based on this ufnGetJson function, we can get total amount of issues and split the stream to multiple batches (5 batches in below example)

 ufnGetRange  
 (n) =>  
     let  
       tmpResult = ufnGetJson(0),  
       totalItem = tmpResult[total],  
       cnt = totalItem / 5,  
       returnRange = {(n-1) * Number.RoundDown(cnt)..n * Number.RoundUp(cnt)}    
     in  
       returnRange  

Code listed above provides us with a boundary of the issue numbers we need to call. Now we just need to create 5 datasets and call the function separately, but keep in mind, we need to provide different n value. e.g. to get the first batch we can have the query like below

 let  
   rng = ufnGetRange(1),  
   startAt = List.Min(rng),  
   boundary = List.Max(rng),  
   pages = {Number.RoundDown(startAt/100)..Number.RoundDown(boundary/100)},  
   pageRange = List.Transform(pages, each _ * 100),  
   paginated = List.Transform(pageRange, each ufnGetJson(_)),  
   #"Converted to Table" = Table.FromList(paginated, Splitter.SplitByNothing(), null, null, ExtraValues.Error)  
 in  
   #"Converted to Table"  

The last step is simply creating a new dataset by combining all 5 datasets into one.

 let  
   combined = Table.Combine({#"Issue Batch 1", #"Issue Batch 2", #"Issue Batch 3", #"Issue Batch 4", #"Issue Batch 5"}),  
      ....  
      ....  
      ....  

At my end this approach improves loading speed a lot, but obviously it depends on connection capacity. Let me know if there is any problem :)

API Response Pagination - SSIS

In my previous posts, I demonstrated how to paginate API response in Power Query. But what if we need to do a pagination via SSIS? As far as I know, at this stage there is no generic Rest connector available in SSIS. Therefore, we have to use script component/task to read response from the Rest API.

To show you the approach in a quick way, let's write some codes to replicate the scenario I demonstrated in this post. Firstly I created below function:

private static string callAPI(HttpClient client, string key, string endPoint)
    {
        string jsonBody;

        var header = new MediaTypeWithQualityHeaderValue("application/json");
        client.DefaultRequestHeaders.Accept.Add(header);

        client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Key", key);

        HttpResponseMessage response = client.GetAsync(endPoint).Result;

        jsonBody = response.Content.ReadAsStringAsync().Result;

        return jsonBody;
    }

Clearly, this function will extract response body from the Rest API (here we assume the API uses HTTP header "Key" for authorization purpose).

So now we have the base method to call the API, what we need to do is creating a recursive routine, so that we can read the next page until nothing returns:

            bool tryNextPage = true;

            using (HttpClient client = new HttpClient())
            {
                while (tryNextPage)
                {
                    string jBody = callAPI(client, key, endPoint);

                    if (jBody.Length > 50)
                    {
                        ResponseBuffer.AddRow();
                        ResponseBuffer.JsonBody.AddBlobData(System.Text.Encoding.UTF8.GetBytes(jBody));

                        int page = int.Parse(endPoint.Substring(endPoint.Length - 1, 1));
                        
                        endPoint = endPoint.Substring(0, endPoint.Length - 1) + (page + incremental).ToString();
                    }
                    else
                    {
                        tryNextPage = false;
                    }
                    
                }

                
            }

As you can see, I defined a Boolean variable to control the While loop. And in each loop, I read length of the Json response to determine if we should read next page.

Simple approach, isn't it? Though it looks like we have to write more lines of code when compare to power query, SSIS does have its own advantages, such as data quality control. Until my next post, enjoy the pagination.

Power BI/Power Query: API Response Pagination Part 3 - Missing Indicator

This is the 3rd post about pagination in Power BI/Power Query.

In part 1 and part 2 we have discussed how to paginate Restful response if the response contains a total item Count or a next page Token. So what if the response only returns us records, without any indicator about the next page?

Let's take a look on Basecamp todo API. If we call the API at the end point /api/v1/projects/{project id}/todos.json?page=1, the begin of the response and the end of the response look like below screenshots:

begin of the response

end of the response

Sorry for the messy screenshots, but the point is, this API doesn't give us a token for next page. We have to query the next page to ensure our API call pulls all records we need. You might have hundreds of ways to do the job, but my first impression is let's create a recursive function. Let's think about the problem:

1. We receive the first page from the response and merge the records into a table "T";
2. Then we move on to the next page
2.1 If the next page contains records then we repeat step 1 (merge records into "T") and step 2
2.2 If there is no records in the next page, then we stop and return the table "T"

It is simply a classic recursive scenario. In Power Query, the recursive function is all about the "@". Let's look at the codes

let
    ufnQuery = (n) =>
        let
            jsonDoc= Json.Document(
                        Web.Contents(
                            "https://basecamp.com/{domain}/api/v1/projects/{project id}/todos.json?page=" & Number.ToText(n), 
                            [Headers=[Authorization="Basic {the key}"]]
                        )
        ),
        tmpTbl = Table.FromList(jsonDoc, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        tmpTbl,

    fnRecursive = (tbl, n) =>
        if Table.RowCount(ufnQuery(n)) > 0 then @fnRecursive(Table.Combine({tbl, ufnQuery(n)}), n+1) else tbl,

    tbl = ufnQuery(1),
    result = fnRecursive(tbl, 2)  
in
   result

As you can see, the key function in codes above is the 2nd function, in the function body, we check amount of records returned from the call, and if the amount is greater than 0, we recursively (@) call the function by passing the merged table and next page number. Simple enough, right?