Workfront and Tableau

I'm actually using Microsoft Power BI with Workfront data. Like Tableau, there is no out-of-the-box Workfront connector for Power BI, however, much of the principles are the same. Most of the early reporting I have done has been based on manually exporting data to Excel from Workfront reports, then using these sheets as data sources in Power BI. However, I've progressed onto using the Workfront API as a source from which to load data into the data model in PowerBI. As everyone else has said, the majority of the complexity is in working out what data you need, getting it out of Workfront and into your data model. From there on, producing some cool visualisations is the fun and easy part! Keen to hear if there are any other Power BI users out there. I also spent a fair amount of time reviewing Tableau. Whilst the visualisations are a little more slick in Tableau, I found PowerBI much more flexible/powerful when working with data. Things that were really hard in Tableau and required extensive/complex hacks (LoD calculations in Tableau) seemed to just work in PowerBI. There are also loads of good add-on visualisations in the Power BI store for low or no cost. It also is a fair bit more cost-effective. So far, so good anways.... Cheers David Cornwell

Hi David, I'm doing *exactly* the same r.e. taking Excel exports of reports I've built in Workfront and putting them in to Power BI. I'll start turning into a grumpy analyst soon haha. How have you used the API to put data into Power BI? Do you dump it into a database and then connect that to Power BI or? :) Jamie Hill JLL

Hi Jamie, So far, I've set it up so that Power BI Desktop connects directly to Workfront's API as a web data source for each Workfront object type I want, using API queries I've written to give me the various fields I want. For each object type (projects, tasks, assignments etc) it drags down the records 2000 at a time and stores these temporarily as tables while the queries execute. At the end, it combines the tables together into one table per object type, which I can then use for my visualisations. As PowerBI doesn't support incremental load, this solution will only give you one snapshot of data as at the time you refresh the data. If trending is required you would need to implement some other solution to execute the queries and store it in a data warehouse which you would then connect PowerBI to as a data source. We will likely do this at some point. So far for trending, I have not used the API and instead save exported workfront report outputs in Excel format in a folder each day/week and then use that folder as a data source, and use a date from the file name as the snapshot date for doing trend reporting. I'm really keen to hear of your experiences with whatever you do with PowerBI, so please let me know. Cheers, David Cornwell

Hi @David Cornwell , I'm also connecting to Power BI with Workfront's API and pulling projects based on the criteria I need. It works fine, but I haven't found a way to pull custom data - are you doing that? I have a main query that pulls all the tasks from one project (based on project ID). I turned the project ID into a parameter, parameterized my URL from the main query, and then I created a custom function that references my parameter. So, now I have a list of all projects that I want and by using the custom function, I'm able to run my main query for each project in the table by just clicking on that line. It's great because I have a list of all the projects and I can navigate to the list of each project's tasks depending on which I'm interested in. However, much of the data I want to report on is sitting in custom fields on those projects/tasks. Wondering if you've had any success or tried pulling custom data, or if you stick to Workfront standard items. Thanks! Kirsten Heikkinen Trek Bicycle Corporation

Hi @Kirsten Heikkinen - yes, you can definitely access custom fields on any object type. Just enter it in the following format: DE:InsertYourFieldName Cheers David Cornwell

@David Cornwell How does that look in the URL? Right now I've added &fields=* to return all the fields for a task. Thanks for the help!! Kirsten Heikkinen Trek Bicycle Corporation

Hi @Kirsten Heikkinen , You could use the following if you wanted to add a single custom field to your existing query: &fields=*,DE:InsertYourFieldName Obviously you are using * to return all the standard fields. There is an equivalent which will return all custom fields, called parameterValues. So if you wanted all standard and custom fields on an object you can use: &fields=*,parameterValues A word of caution though....I prefer to specify the actual fields that I'm interested in. This makes your queries/refreshes faster, and will also make your data model smaller (and therefore faster / more efficient) in PowerBI. By all means, you can use the options to get all fields while you're working out what you need, but then it's a good idea to change over to specifying the fields you need. Hope this helps! p.s. I was out riding my Trek on the weekend! Regards, David Cornwell

Thanks, @David Cornwell . This is super helpful! Happy to hear you were out riding! Any weather is biking weather :) Kirsten Heikkinen Trek Bicycle Corporation