@David Cornwell‚ I just started digging into a few Power BI scenarios myself and wondering if you had any advice for not exposing the apiKey in the datasources? While I work through my proof of concept I'm using Json.Document(Web.Contents(blah blah blah)) to get the data. Needed to do some noodling to traverse the thousands of rows of data (tasks, projects, issues, & hours are the primary objects I'm working with) in DAX, but have a solid pattern to manage that.
The concern is that the apiKey is embedded in the Web.Contents function call and included in the Published dataset, which is downloadable, which means my apiKey would get hijacked. No bueno!
Assuming you took a similar approach; how'd you handle securing that apiKey?
@David Cornwell‚ I believe you've changed roles but still might be interested in participating in this conversation, so tagging your new profile!
Thanks @Kyna Baker - inactive‚ !
So I figured out how to conceal the apiKey, but doing so causes auto-refresh of the dataset in the Power BI Service to fail. Nothing is ever easy, is it?
For sake of documenting how to conceal the apiKey... Power BI needs the Web.Contents() call to be formatted as follows:
Source = Json.Document(Web.Contents("https://<YOUR INSTANCE NAME>.my.workfront.com/attask",
ApiKeyName = "apiKey"
Then change your Data Source Settings (either Data Sources in current file OR Global permissions) to use the Web API credential type. This will prompt you to enter your apiKey value and will securely store it in your local credentials cache. Works great in Power BI Desktop, and publishing to Power BI Service does work, but all refreshes (from anywhere other than your desktop) will fail because the Web API apikey value is only local. Connecting to a gateway doesn't seem to help either as the Web API authentication method is not available on the gateway.
If anyone has handled the refresh, while protecting the apiKey value I'd appreciate some guidance.
Hi @Rick MacDuffie‚ . This is something I'll have to revisit as I haven't been working with Power BI recently. One option/workaround is to use the workspace settings to prevent your users from being able to download the PBIX file (that's what I did before). Not ideal, but it's an option.
I'll do a bit more research soon on this topic. One option is to look at using a service like Azure Data Factory to manage the data extraction from Workfront, then load the data to Data Lake or SQL then you can connect Power Query to that. I expect that Data Factory will be a good option that lets you securely store the API credentials away from your users, and then you could let them download the datasets if need be. I've certainly used Data Factory and DataLake as a source for PowerBI before...just not Data Factory and Workfront as it data source yet.
Keep me posted on what you find out also!