Is it possible to do a bulk insert from the Workfront API into SQL Server using fusion?

[RyannaBe]

My goal is to store Workfront task details in SQL to use to build reports in Power BI. Any help is greatly appreciated. Thank you!

[darin_patterson]

Hi Ryanna,

Unfortunately, Workfront Fusion isn't designed really to support "reporting use cases" (e.g. I want to report on all of the data in Workfront in some other tool). Workfront Fusion is designed to support transactional integrations (e.g. synchronizing a subset of data in two different systems like Workfront and Salesforce) or automating Workfront processes based upon simple or complex criteria.

[Doug_Den_Hoed__AtAppStore]

Hi Ryanna,

I invite you to consider our Workfront Snapshot solution, with which you can back up your own Workfront data in a self serve and downloadable fashion, then use results for conversion, archival, or advanced analytic reporting purposes...such as using a SQL Server DTS Package to import the data (which comes in a Microsoft Access .mdb format) so you can then analyze it using Power BI reports.

Regards,

Doug

[Rick_MacDuffie]

Could it, probably. I've used fusion for a few bulk export/import things.

That said, you don't have to put Fusion or SQL in the middle. I have a Power BI model that pulls in 5 years worth of projects, tasks, issues, etc direct from Workfront's API using incremental refresh. Took a little fiddling, but if your comfortable in Power Query you can traverse all the rows of tasks you have in a single query, and build visuals against that.. All it takes is a Web connector in Power BI and some clever M to traverse all the pages in increments of 2000 rows to pull in all the tasks.

[Rick_MacDuffie]

Had to look through my youtube history to recall the video that I found most helpful.

https://www.youtube.com/watch?v=vhr4w5G8bRA

Feeling industrious....? Below are two M queries that handle the heavy lifting in Power BI.

My GetTasks function:

-------------------------------------------------------------

(index as number)=>

let

Source = Json.Document(Web.Contents("https://<YOUR INSTANCE>.my.workfront.com/attask",

[

RelativePath="/api/v12.0/task/search",

Query =

[

apiKey=<YOUR WORKFRONT API KEY>,

entryDate=Text.From(<YOUR DATE RANGE START>),

entryDate_Range=Text.From(<YOUR DATE RANGE END>),

entryDate_Mod="between",

#"$$FIRST"=Text.From(index),

#"$$LIMIT"="2000",

fields="<COMMA SEPARATED LIST OF FIELDS YOU WANT TO WORK WITH"

]

]))

in

Source

-------------------------------------------------------------

The important looping pages portion of the Tasks query:

-------------------------------------------------------------

let

Source = List.Generate(

()=> [Result = try GetTasks(0) otherwise null, Offset = 0],

each List.IsEmpty([Result][data]) <> true,

each [Result = try GetTasks([Offset]+cPageSize) otherwise null, Offset = [Offset]+cPageSize],

each [Result]

in

Source

-------------------------------------------------------------

Enjoy!

[Doug_Den_Hoed__AtAppStore]

Inspired, Rick. Thanks for sharing!

Regards,

Doug

[Rick_MacDuffie]

Happy to share! That List.Generate function is a weird one. This isn't what I started with, but I believe it is the optimized state.

In the event you can't get that to work, you could use another function to get the page requirements that calls the API to learn how many rows of the entity there are (eg. /api/v12.0/task/count). Then do some M work to create a list of index/offsets by dividing the count by 2000 and rounding the result up to the nearest whole number and adding a custom column that is a list ( ={0..[Rounded Value]} ), expanding that list and multiplying the value by 2000 in another custom column to give you the offset (eg 0, 2000, 4000, 6000, etc). Finally, add yet another custom column, this time invoking that same GetTasks function with the offset value for that row.

It sounds more complicated than it is. This approach does end up using more lines of M code, but you don't have to write any of it. Just use the built in functions found in the header of the Power Query editor. I feel it is easier to explain than the List.Generate solution, but harder to read. Results are results.

[RyannaBe]

Thank you very much!