Power BI Web Site Issues
For those that want to make use of Dynamics NAV 2016 and the web based Power BI NAV Content Pack be aware that there are a couple of gotchas that aren’t spelled out by Microsoft very well.
The first big issue (and very close to a no-go issue – IMHO) is how you must configure NAV 2016 for it to authenticate from the Power BI web site. The only way for the Power BI web site to connect to your published OData pages/queries is by configuring an NST Instance to use “NavUserPassword” authentication. Thereby forcing you to create additional user id’s and passwords for all users that require access to NAV 2016 data via the Power BI web site.
The second big issue is how you must configure the Power BI web site to be able to authenticate properly to NAV 2016. The ONLY way that it is officially supported and works is by using BASIC authentication! That is – your credentials to your ERP system are being sent over the wire as a simple BASE64 encoded text string and being cached in memory by your browser. The only saving grace is the Power BI site is SSL protected so outside snoopers should not be able to easily grab your credentials.
The third big issue with the Power BI website is a bug in its URL decoder. If you follow any of the Microsoft walk-through’s to get setup and you take their advice for getting the correct URL:
“An easy way to obtain this URL is in Dynamics NAV to go to Web Services, find the powerbifinance web service and copy the OData URL, but leaving out the “/powerbifinance” from the URL string.“
If you happen to have a comma (and maybe other characters as well) in your company name, the properly encoded URL from NAV will NOT work! Commas when encoded will show as “%2C”, you need to actually replace that with an actual comma. Also, oddly enough, you can also replace the “%20” with their original spaces and that will also work.
Power BI Desktop – to the Rescue, almost
So what are you to do if the above is more of a PITA than it is worth? Simple – use the Power BI Desktop application. The Power BI Desktop works properly with Windows authentication, and knows how to properly decode the URL you get from the NAV web services page. You can create your reports with the Power BI Desktop and then publish them to the Power BI Web Site. There are a couple downsides, (1) Microsoft hasn’t created a NAV content pack for the desktop so you will have to manually create everything and (2) the published reports will be static since the web site won’t be able to authenticate back with the Windows credentials used by Power BI Desktop.
Power BI Desktop + Enterprise Gateway – Now We’re Talking!
So now that you’re frustrated ;), how can you fix this mess into something that is actually usable and manageable? Use Power BI Desktop in conjunction with a Power BI Enterprise Gateway. A Power BI Enterprise Gateway allows you to create one connection to your Dynamics NAV 2016 OData source that can be shared by all datasets/reports/dashboards that you create. That way, you can create an NST Instance with “NavUserPassword” but only have to create one “gateway” user that can be used for the Enterprise Gateway and your Power BI Desktop users can continue with their Windows user authentication. There is of course a downside, when a user publishes a report/dataset, they will have to edit the dataset to change it from trying to use the original Windows credentials to use the Enterprise Gateway instead. Thankfully, the switch to Enterprise Gateway is retained even if the dataset is subsequently replaced by a future publishing of the dataset. Once you have your reports built with Power BI Desktop, published to the web and re-pointed to an Enterprise Gateway connection you can then share your creations with users and they will be able to refresh the data at will. And if you are a Power BI Pro subscriber, you will also be able to utilize scheduled dataset refreshes as well.
Unfortunately, until Microsoft changes the Power BI web site and/or Dynamics NAV, we’re stuck with employing a couple of extra layers in order to fully utilize Power BI. However with the deployment of one extra NST Instance and an Enterprise Gateway you can begin enjoying the benefits that Power BI connected to Dynamics NAV 2016 has to offer.