Forum Discussion
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
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,
KellyDid I answer your question? Mark my post as a solution!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:- get whole worklog since date via https://jiradc.int.net.mycompany.com/rest/api/latest/worklog/updated?since=sometimestamp
- get worklog one by one via "GET /rest/api/2/issue/{issueIdOrKey}/worklog/{id}"
- get worklog for multi issues via eg. https://jiradc.int.net.mycompany.com/rest/api/latest/search?jql=issue%20in%20(ISSUE1, ISSUE2)&fields=worklog
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:
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)
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
