Getting TEMPO Worklogs in Power Bi

[Anonymous]

Hi Team,

I am currently working on a project to extract Worklogs from Atlassian's TEMPO Timesheets using Power BI. Over the past few weeks, i have done enormous research on TEMPO Rest API's and extracting the Tempo worklogs in Power BI from Web data source. The team manages around 48 projects in Tempo and i need to get Worklogs from all the projects. I am able to extract Worklogs for individual Projects using Project Key as API Parameter. However, if am not specifying the Project Key, a 504 gateway timeout error is thrown by Power BI editor as the records are very large. As my organization is moving from Project Server to Tempo, this would be a great win if i could achieve this in Power BI without doing any ETL. I am using below query to fetch data from Tempo.

let
Source = Json.Document(Web.Contents("https://api.tempo.io", [Headers=[Authorization="Bearer xxxxxxxxxxxxx"],RelativePath="rest-legacy/tempo-timesheets/3/worklogs", Query=[projectKey = "HSI"]]))

in

Source

I would expect the query to return something like [projectKey in "HSI","JOG","MOG"]. Any suggestions would be heavily appreciated as this would turn out to be a robust solution for creating Timesheet Reporting.

Many Thanks,

Arjun

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Please change your current query to a function:

let
    parameterSource = (Project as text)=>


let
Source = Json.Document(Web.Contents("https://api.tempo.io", [Headers=[Authorization="Bearer xxxxxxxxxxxxx"],RelativePath="rest-legacy/tempo-timesheets/3/worklogs", Query=[projectKey = ""&Project&""]]))

in

Source

in

   parameterSource

Then create the following table and invoke the above function in the new table.


Regards,
Lydia

[Anonymous]

Thanks a lot guys!. its working for me

[Anonymous]

This is working fine for me but going further, we might create a staging database to store all the worklogs of all users and analyse the timesheet for different teams within the organization. 

We might need a more robust solution to extract worklogs. Is it possible to extract all the worklogs from REST API using SSIS. If yes, can you please share few tips in achieving this?.

Thanks,

Arjun