cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RayBrosius Frequent Visitor
Frequent Visitor

Cant Refresh Dataset

I am attempting to get some data from our Jira Software cloud instance.  I found the code listed here on teh Atlassian site and it works great on teh Desktop PowerBI, but when I publish the dataset to the Service and try to schedule a refresh I get an error shown below.

 

Something went wrong
There was an error when processing the data in the dataset.
Please try again later or contact support. If you contact support, please provide these details.
Data source error: Unable to refresh the model (id=2201454) because it references an unsupported data source.
Cluster URI: WABI-WEST-EUROPE-redirect.analysis.windows.net
Activity ID: 8985f632-6baf-46a8-925f-e0577539cf36
Request ID: e8b00ccf-5ec4-17a0-27fd-3f959d805e53
Time: 2018-11-05 17:51:51Z

 

 

 

How can we have this query work as a scheduled refresh?

 

let

 yourJiraInstance = "https://yourinstance.jira.com/rest/api/2/",
 
 jql = "&jql=status+not+in+(Cancelled,+Closed,+Done)",
 
 qryBase = yourJiraInstance & "search?maxResults=100" & jql & "&startAt=",

 Source = Json.Document(Web.Contents( qryBase & "0" )),
 numIssues = Source[total],

 startAtList = List.Generate(()=>0, each _ < numIssues, each _ +100),
 urlList     = List.Transform(startAtList, each qryBase & Text.From(_) ),
 data        = List.Transform(urlList, each Json.Document(Web.Contents(_))),

 iLL = List.Generate(
     () => [i=-1, iL={} ],
     each [i] < List.Count(data),
     each [
         i = [i]+1,
         iL = data{i}[issues]
     ],
     each [iL]
 ),

 issues = List.Combine(iLL),

 #"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"key", "fields"}, {"issue", "fields"}),
 #"Expanded fields" = Table.ExpandRecordColumn(#"Expanded Column1", "fields", {"assignee", "created", "creator", "description", "issuetype", "parent", "priority", "project", "reporter", "resolution", "resolutiondate", "status", "summary", "updated"}, {"assigneeF", "created", "creatorF", "description", "issuetypeF", "parentF", "priorityF", "projectF", "reporterF", "resolutionF", "resolutiondate", "statusF", "summary", "updated"}),
 #"Expanded assignee" = Table.ExpandRecordColumn(#"Expanded fields", "assigneeF", {"key"}, {"assignee"}),
 #"Expanded creator" = Table.ExpandRecordColumn(#"Expanded assignee", "creatorF", {"key"}, {"creator"}),
 #"Expanded issuetype" = Table.ExpandRecordColumn(#"Expanded creator", "issuetypeF", {"name"}, {"issuetype"}),
 #"Expanded priority" = Table.ExpandRecordColumn(#"Expanded issuetype", "priorityF", {"name"}, {"priority"}),
 #"Expanded project" = Table.ExpandRecordColumn(#"Expanded priority", "projectF", {"key"}, {"project"}),
 #"Expanded reporter" = Table.ExpandRecordColumn(#"Expanded project", "reporterF", {"key"}, {"reporter"}),
 #"Expanded resolution" = Table.ExpandRecordColumn(#"Expanded reporter", "resolutionF", {"name"}, {"resolution"}),
 #"Expanded status" = Table.ExpandRecordColumn(#"Expanded resolution", "statusF", {"name"}, {"status"}),
 #"Changed Type" = Table.TransformColumnTypes(#"Expanded status",{{"created", type datetimezone}, {"resolutiondate", type datetimezone}, {"updated", type datetimezone}}),
 #"Expanded parentF" = Table.ExpandRecordColumn(#"Changed Type", "parentF", {"key"}, {"parent"})
in
 #"Expanded parentF"
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Cant Refresh Dataset

Hi @RayBrosius,

 

As the error message shows, the data source isn't supported. I would suggest you refer to dynamic-web-contents-and-power-bi-refresh-errors/ and using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/ and makr some changes. 

 

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Community Support Team
Community Support Team

Re: Cant Refresh Dataset

Hi @RayBrosius,

 

As the error message shows, the data source isn't supported. I would suggest you refer to dynamic-web-contents-and-power-bi-refresh-errors/ and using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/ and makr some changes. 

 

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
RayBrosius Frequent Visitor
Frequent Visitor

Re: Cant Refresh Dataset

I was able to use these articles to successfully change the "query" in to Jira in a manner that allowed me to schedule refreshes on the PowerBI Service.

 

Took some tweaking of the actual JQL into Jira and the authentication methods but after a couple of trials and errors got it working.

 

Thanks!!!

gidster99 Frequent Visitor
Frequent Visitor

Re: Cant Refresh Dataset

Hi Ray - I have the same issues as you decribe... Would you please post your modifed code that allowed you to refresh your JIRA Data via the Power BI Service?

Many thanks

RayBrosius Frequent Visitor
Frequent Visitor

Re: Cant Refresh Dataset

Below is what I got to.  I don't recall exactly the person that I got this from.. I cut down on a lot of the extra coments and tried to make is simple.  but basically it was from a post that showed how to seperate the "query" / post call into what is teh "WEbsite" from the parameters.  This allows Power BI Service to recognize in essence that you are logging int the same server.

 

Where I put in "YOUR" are the main things you will need to change...

 

You can generate any JQL statement that you need to pull teh list of issues from Jira... Suggest that you find a good logical way to organize the query so you are not pulling 10's of thousands of rows back from Jira in one query.  YOu can use PowerBI to execute multiple queries in parallel and then merge back into one table later in the ETL..

 

I suggest that you start with hardcoding the numIssues variable with a small number, so as to allow the query to run quicker and you can work through problems faster..

Good luck and I hope this helps..

***************************************************************************************************************************

let
//  Get the Number of Issues to retrieve
//  apikey is YOUR own web token api key from Atlassian
Source = Json.Document(
            Web.Contents("https://YOURHOSTNAME.jira.com/",
            [
                RelativePath="rest/api/2/search",
                Query=
                [
                  maxResults="100",
                  jql="project=YOUR PROJECT",
                  startAt="0",
                  apikey="YOUR WEBKEY"
                ]
            ]
)),

//  Number of issues to process
numIssues = Source[total],

 

// Now it is time to build a list of startAt values, starting on 0, incrementing 100 per item
startAtList = List.Generate(()=>0, each _ < numIssues, each _ +100),

 

//  User this to get a list of lists for 100 issues at a time.
data        = List.Transform(startAtList, each Json.Document(Web.Contents("https://YOURHOSTNAME.jira.com/",
            [
            RelativePath="rest/api/2/search",
            Query=
                [
                  maxResults="100",
                 jql="project=YOUR PROJECT",
                 startAt=Text.From(_),
                  apikey="YOUR WEBKEY"
                ]
            ]))),

 // ===== Consolidate records into a single list ======
 // so we have all the records in data, but it is in a bunch of lists each 100 records long. 
 //
 // In essence we need extract the separate lists of issues in each data{i}[issues] for 0<=i<#"total"
 // and concatenate those into single list of issues .. from which then we can analyse
 //

 // so first create a single list that has as its members each sub-list of the issues,
 // 100 in each except for the last one that will have just the residual list.
 // So iLL is a List of Lists (of issues):

 

 iLL = List.Generate(
     () => [i=-1, iL={} ],
     each [i] < List.Count(data),
     each [
         i = [i]+1,
         iL = data{i}[issues]
     ],
     each [iL]
 ),
 // and finally, collapse that list of lists into just a single list (of issues)
 issues = List.Combine(iLL),

 // =============================================================
 // so now we've got the long list of issues back from JIRA
 // ... now to do something with this - extract the bits we want
 //
 // at this point you have two options
 // 1. just keep all the code below in place
 //    .. and you'll see the results of this straight away. Do that first.
 // 2. after you have done that option 1., I recommend:
 //     - re-edit this query in Advanced Editor
 //     - delete all the code below. yes, DELETE it all
 //     - close/save this query, and then
 //     - to figure out how all this works,
 //     - follow the guidance from this post:
 //       https://www.mssqltips.com/sqlservertip/4621/using-power-bi-with-json-data-sources-and-files/
 //       ... starting half way down with para that begins "Drilling from the top level of the record ..."
 //       Those details there made all the difference to me figuring this out

 // righto .. so here is the code that is one way to split out the details into some fields that might be interesting to you


 #"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"key", "fields"}, {"issue", "fields"}),
 #"Expanded fields" = Table.ExpandRecordColumn(#"Expanded Column1", "fields", {"assignee", "created", "creator", "description", "issuetype", "parent", "priority", "project", "reporter", "resolution", "resolutiondate", "status", "summary", "updated"}, {"assigneeF", "created", "creatorF", "description", "issuetypeF", "parentF", "priorityF", "projectF", "reporterF", "resolutionF", "resolutiondate", "statusF", "summary", "updated"}),
 #"Expanded assignee" = Table.ExpandRecordColumn(#"Expanded fields", "assigneeF", {"key"}, {"assignee"}),
 #"Expanded creator" = Table.ExpandRecordColumn(#"Expanded assignee", "creatorF", {"key"}, {"creator"}),
 #"Expanded issuetype" = Table.ExpandRecordColumn(#"Expanded creator", "issuetypeF", {"name"}, {"issuetype"}),
 #"Expanded priority" = Table.ExpandRecordColumn(#"Expanded issuetype", "priorityF", {"name"}, {"priority"}),
 #"Expanded project" = Table.ExpandRecordColumn(#"Expanded priority", "projectF", {"key"}, {"project"}),
 #"Expanded reporter" = Table.ExpandRecordColumn(#"Expanded project", "reporterF", {"key"}, {"reporter"}),
 #"Expanded resolution" = Table.ExpandRecordColumn(#"Expanded reporter", "resolutionF", {"name"}, {"resolution"}),
 #"Expanded status" = Table.ExpandRecordColumn(#"Expanded resolution", "statusF", {"name"}, {"status"}),
 #"Changed Type" = Table.TransformColumnTypes(#"Expanded status",{{"created", type datetimezone}, {"resolutiondate", type datetimezone}, {"updated", type datetimezone}}),
 #"Expanded parentF" = Table.ExpandRecordColumn(#"Changed Type", "parentF", {"key"}, {"parent"})
in
 #"Expanded parentF"

*****************************************************************************************

gidster99 Frequent Visitor
Frequent Visitor

Re: Cant Refresh Dataset

Thanks Ray - this is looking very hopeful (at least, Data is currently being processed).

 

At the moment we have around 60k JIRA Tickets that this is trying to Retrieve... It currently takes about an hour... I am intrigued by your idea to break the Issues down into smaller chunks and then merge them to get better performance.

 

If you get a moment to elaborate further that would be appreciated... but if not I will try some experimenting tomorrow!

 

Once I publish my PBIX to the Power BI Service I will test that it can now schedule a refresh which will be fantastic.

 

Thanks again!

RayBrosius Frequent Visitor
Frequent Visitor

Re: Cant Refresh Dataset

So the idea is to basically setup multiple queries... If you have multiple projects in Jira or different ticket types, create JQL queries to break down the pull into chunks.  you will have then multiple tables.  Be sure to generate the same columns and  you can use the Merge Query function in Power Query to pull the items back into one Big table. 

 

Of course you can "experiment" with the Power BI Service.  If you are refreshing once a day then an hour long refresh may suffice.  Although a lot can go wrong in an hour...

 

I have not looked into any kind of incremental refresh strategy, which could really help to cut down on the query time. 

RayBrosius Frequent Visitor
Frequent Visitor

Re: Cant Refresh Dataset

BTW.. This is where I got the 'code' to be able to schedule the refresh..

 

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...