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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

powerbi load JIRA worklog via RESTAPI so slow

hi,  guys

Basically, probem I met is the same as https://community.powerbi.com/t5/Desktop/Get-data-from-Jira-Rest-API/td-p/516130, but can not solved by its solution OR maybe I do not understand fully?

My code:

 

 

 

 

 

let
    Source = Json.Document(Web.Contents( #"my company JIRA",
        [
            RelativePath="/rest/api/2/search",
            Query=[
                maxResults="1000", 
                jql=#"JQL EPICS",
                fields="key",
                startAt="0"
            ]
        ])),
    total1 = Number.Round(Source[total]/1000 + 0.5-1, 0, RoundingMode.Up),
    List = List.Transform({0..total1}, each _ * 1000),
    #"Converted to Table" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Data", each GetJIRADCPage(#"JQL EPICS", "key", [Column1])),
    #"Expanded Data" = Table.ExpandTableColumn(#"Invoked Custom Function", "Data", {"Column1.key"}, {"Column1.key"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Data", {"Column1.key"}),
    parent = Table.RenameColumns(#"Removed Duplicates",{{"Column1.key", "ParentKey"}}),
    nbr = List.Count(parent[ParentKey])-1,
    total = Number.Round(nbr/#"EPICS COUNT PER PAGE" + 0.5 - 1, 0,  RoundingMode.Up), //Using #"EPICS COUNT PER PAGE"(currently 30) in case url too long 
    List99 = List.Transform({0..total}, each _ * #"EPICS COUNT PER PAGE"),
    #"Converted to Table99" = Table.FromList(List99, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type99" = Table.TransformColumnTypes(#"Converted to Table99",{{"Column1", type number}}),
    #"Invoked Custom Function2" = Table.AddColumn(#"Changed Type99", "Data", each GetJIRADCChildren(Text.Combine(List.Range(parent[ParentKey], [Column1], #"EPICS COUNT PER PAGE"), ","), #"FIELDS TASK")),
    #"Removed Columns2" = Table.RemoveColumns(#"Invoked Custom Function2",{"Column1"}),
    #"Expanded Data99" = Table.ExpandTableColumn(#"Removed Columns2", "Data", {"Column1"}, {"Column1"}),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Expanded Data99", "Column1", {"key", "fields"}, {"key", "fields"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Column1",{{"key", "Issue key"}}),
    #"Removed Duplicates99" = Table.Distinct(#"Renamed Columns2", {"Issue key"}),
    #"Expanded fields" = Table.ExpandRecordColumn(#"Removed Duplicates99", "fields", {"summary", "customfield_12790", "status"}, {"summary", "customfield_12790", "status"}),
    #"Expanded status" = Table.ExpandRecordColumn(#"Expanded fields", "status", {"name"}, {"status"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded status",{{"customfield_12790", "ParentKey"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1),
    //#"Invoked Custom Function1" = Table.AddColumn(#"Added Index", "worklog", each GetWorklog([Issue key]))
    #"Add worklog url" = Table.AddColumn(#"Added Index", "worklog url", each #"my company JIRA"&"/rest/api/latest/issue/"&[Issue key]&"/worklog"),
    #"Invoked Custom Function1" = Table.AddColumn(#"Add worklog url", "worklog", each Json.Document(Web.Contents([worklog url])))
in
    #"Invoked Custom Function1"

 

 

 

 

bottleneck is #"Invoked Custom Function1".

Power query preview(max 1000 rows) quick(about 3-5 seconds), but loading afte clicking apply change is very slow, almost (1-10) by (1-10) rows per second. 

Appriciate help!

5 REPLIES 5
v-kelly-msft
Community Support
Community Support

Hi @Anonymous ,

 

In your query, this one

 

#"Add worklog url" = Table.AddColumn(#"Added Index", "worklog url", each #"my company JIRA"&"/rest/api/latest/issue/"&[Issue key]&"/worklog"),
    #"Invoked Custom Function1" = Table.AddColumn(#"Add worklog url", "worklog", each Json.Document(Web.Contents([worklog url])))

 

has queried again by the API in each rows, which means if you have 1000 rows in a page, the 1000 rows with be re-queried after executing all the queries above,which will increase the calling numbers of API,so you can first remove the last query to check whether the loading has been sped up.If it is the issue,will remove the query be a best solution for you?

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Anonymous
Not applicable

hi, @v-kelly-msft 

I had the same feeling about performance bottleneck you mention above, which is API call per row,  And it is testified by the fact  data will be loaded fast if  #"Invoked Custom Function1" line is removed.

 

What I am curious is that there is the same API call per row in https://community.powerbi.com/t5/Desktop/Get-data-from-Jira-Rest-API/td-p/516130, but seems fixed by changing from "each Json.Document(Web.Contents(#"AddIndex"{[Index]}[Custom]))" to "each Json.Document(Web.Contents([Custom]))".  That give me hope that maybe the poor performence is caused by some rule mentioned at this link, which is "avoiding loading table in power query"?

If the poor performance is caused by API call per row 100 percentage, then I don't know how to get worklog from JIRA quickly.  there are 3 potential approaches to get worklog data what I can find by now:

  1. get whole worklog since date via https://jiradc.int.net.mycompany.com/rest/api/latest/worklog/updated?since=sometimestamp
  2. get worklog one by one via "GET /rest/api/2/issue/{issueIdOrKey}/worklog/{id}"
  3. get worklog for multi issues via eg. https://jiradc.int.net.mycompany.com/rest/api/latest/search?jql=issue%20in%20(ISSUE1, ISSUE2)&fields...

BUT all above are not satisfied for me since

for 1: I don't want to hunt my interested data among whole company, it is too huge.

for 2:there are 4000 issues by now, one issue one api cal takes too much time.(the current problem we mentioned here)

for 3: was thought excellent, but sadly, the max count of worklog return per issue is 20, but of course there are plenty of issues whose count of worklogs excced.

I am tortured by this for 3 days!

 

Hi @Anonymous ,

 

Go to query editor,at the bottom of column profile, choose"entire dataset",as shown below:

Annotation 2020-05-18 175951.png

Then remove "    #"Invoked Custom Function1" = Table.AddColumn(#"Add worklog url", "worklog", each Json.Document(Web.Contents([worklog url])))" and go to the column "#"Add worklog url",check the number of rows and distinct rows to see whether they are close to: (using groupby functionality)

Annotation 2020-05-18 180315.png

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
 
Anonymous
Not applicable

@v-kelly-msft,

 

#"Invoked Custom Function1" and "#"Add worklog url" have the same number of rows

(distinct  row), about 4000 rows. But to be honestly, I don't know the point to check rows count after we both realize 4000 rows lead 4000 API calls maybe cause  time-cost. what I am  curious is how poor performance gone at https://community.powerbi.com/t5/Desktop/Get-data-from-Jira-Rest-API/td-p/516130, am  I missing  something?

Hi @Anonymous ,

 

Then you'd better use another kind of API which can process multiple queries...

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors