Power BI with on-premise Dynamics CRM

[Olivia]

How do we connect to on-premise CRM?

Is it possible to have Power BI dashboard for each users of CRM with different security role?

Will Power BI in any way implement the inbuilt security model of CRM? 

[amichalove]

According to this PowerBI feature update note, it is possible to connect PowerBI to CRM On-Premise using "Custom ADFS Authentication Services".

Support for Custom ADFS Authentication Services

With this update, we’re adding support for using custom ADFS authentication endpoints through our Organizational Account credential type. This allows access to data sources that require ADFS authentication such as some on premise instances of Dynamics CRM. After Power BI is registered by your admin, you will be able to approve a custom endpoint when prompted for access. You can also manage the list of already-approved endpoints within the Options dialog, under “Global -> Security”.

http://blogs.msdn.com/b/powerbi/archive/2015/04/22/16-new-updates-to-the-power-bi-designer-preview-a...

What I cannot seem to find is any documentation the process reference in the line "After Power BI is registered by your admin...". I am the Office365 Admin, I don't know where to go to "register" Power BI!

Has anyone else had any luck? any ideas where to go to look?

[amichalove]

FYI I posted a thread with documentation and screenshots of what I have done thus far to connect Dynamics CRM 2015 On-Premise to Power BI.

http://community.powerbi.com/t5/Integrations-with-Files-and/quot-you-have-not-approved-any-authentic...

[KHorseman]

My company uses on-prem CRM. My current solution is to use a SQL Server database connection. I remote into our hosting server where we have installed Office, Power BI Personal Gateway, and Power BI Desktop. It's not ideal but it gets the job done until a dedicated CRM connector is available.

[mickemakela]

Hi! We at Fenix Solutions are proud to announce Dynamics CRM (Online / OnPremise) and Power BI Connector! It's free to download and use! Check it out here: http://www.fenixsolutions.fi/microsoft-dynamics-crm-and-power-bi-connector/ Any feedback is appreciated!

[KHorseman]

That's great news. Could you provide some instructions on how to use this? There doesn't seem to be any documentation and I don't know how or where to install it or what to do with it.

Update: I've figured some of it out but most of it seems not to work. By looking at the xml files in the download I figured out that it's probably meant to be a managed CRM solution and so it was. I installed the solution to our CRM test environment, but it's not doing anything useful.

Almost everything I click on gives an error message, so by process of elimination it looks like in order to do anything, I first need to select a CRM Saved View and then create a new Power BI Dataset. When I try this it first says:

"DataSet named test was created
Table TestView was created succesfully and data loaded"

but then when I hit OK it says:

"Error: Error: Error: 500: Internal Server Error:
Unexpected exception from plug-in (Execute):
FS.PowerBIRestLibrary.PowerBIRestLibrary: System.Net.WebException:
the remote server returned an error: (501) Not Implemented."

Afterward I see nothing under Power BI Datasets, so I guess it just doesn't work. It says it was created successfully but after the error there's nothing there. The only other tab is "Update Power BI Table" and clicking on that gives an error message telling me that I need to select a dataset, so that obviously can't be done.

Also it looks like even if I ever get that part to work, this is limited to whatever CRM Saved Views it recognizes. So we don't do anything in Power Query? All queries have to be created as advanced finds in CRM and then exported through this tool to Power BI? That's going to be a problem for a few reasons. First of all it's only showing me (at a generous estimate) maybe 1% of the saved views I normally have, and none of the views that I would actually use for reporting. So how can I make it show the rest of my saved views? For that matter, how can I get it to show me system views? It seems like this method of exporting a dataset from a saved view will be pretty awkward to use if I ever have to change the contents of a BI report later on, but I honestly can't evaluate that since so far I can't even get any data out of this at all.

[KHorseman]

OK, this morning I had occasion to visit my Power BI dashboards and I noticed that all of those tests that I made have shown up as datasets there, so even though they don't show up in the CRM exporter window where I expected to see them they are apparently being sent to my Power BI account. But they're only there as datasets online. Is there no way to get these into Power BI Desktop? There is no way to create relationships, calculated columns, or measures online.

[mickemakela]

Hi,

Thank you for trying out the connector! I'm sad to hear that You have encountered some problems starting at the installation. In our site we tell that it's a managed solution component, but we will sure write more detailed instructions! Reading your messages it's seems that the connector has worked correctly and You were able to transfer Your CRM saved views to the Power BI Online: Great! 

I will investigate the problem about the tool not showing up all the CRM saved views. Currently we are querying userquery entity, but I don't see why we could't make union to the system views also! I will keep that on my mind. Lastly, the error message you seen in response is from the Power BI API and why it appears I don't have an answer yet. Did all of Your test ended up with that same error message? I haven't encounter that error message before. Did You get the data loaded there as well? About the datasets and Power BI desktop: to my knowledge there's no way to export them from the site to the desktop application but I will try to search for an answer!

Regards,

Micke Mäkelä

[mickemakela]

With Our Power BI Connector You can now autorefresh Your CRM Saved views to the Power BI Online! Get it here:  http://bit.ly/1RxvgBF

[greenchevette]

Lehigh Valley Health Network in Allentown PA is also extremely interested in utilizing PowerBI with our On Prem CRM system. Please bump the priority on this integration. Dan Bortz, MBA RCA Lead Information Services 1245 Cedar Crest Blvd – Suite 100 Allentown, PA 18103 Office - 610.402.1974

[tklooster]

PEAK 15 Systems is also very interested in using Power BI with on premise. 

[mickemakela]

Hi! After reading the posts I'm more than happy to tell You that we at Fenix Solutions are releasing hopefully by the end of this week Dynamics CRM (OnPremise / Online) Power BI Connector! With it You can import your CRM advanced finds to Power BI along with the data! You can also use the tool to refresh the Power BI table with CRM advanced find after You have imported it to the Power BI. More info coming soon! -Micke Mäkelä (Fenix Solutions Oy)

[Greg_Deckler]

Use OData, follow this post:

https://www.linkedin.com/pulse/building-ultimate-microsoft-crm-dashboard-under-hour-greg-deckler?trk...

[paulhasell]

Hi, sadly the referenced link doesn't work for us. It refers to CRM Online which works perfectly, however our IFD on-premise won't connect as it can't get the authorization URL from ADFS

Power BI is supposed to be compatible, not sure if it's a problem in the ADFS setup but I can't find any useful tips anywhere.

[amichalove]

As best I can tell, there are two steps to Odata connectivity with Power BI....First you provide your Odata endpoint (from the Dynamcis CRM Developer menu). When you connect it in PowerBI, you will indeed get the message:

The WWW-Authentication header doesn't contain a valid authorization URI. Header value: ",Negotiate, NTML'.

That is because you need to change the authentication method on the PowerBI cofnig screen to "Anonymous". When you do that, PowerBI will move to the next step of authentication, which is to talk to your ADFS server (you do have a Dynamics CRM instance that is IFD (Internet Facing Deployment) don't you? It is the only way this will work).

When PowerBI talked to the ADFS proxy, it is there that you want to use Organization Credentials. Sadly, it is also there where I cannot seem to move beyond.

See this other thread for my screenshots: http://community.powerbi.com/t5/Integrations-with-Files-and/quot-you-have-not-approved-any-authentic...

[curth]

Does Windows authentication work for you? Those headers suggest it might.

[paulhasell]

I tried that first but get an error in the Query Editor

It seems to be trying to process an ADFS url as the OData target rather than the CRM Odata Url that's passed.

[topry]

Paul - we have the same issue. I've never been able to get Excel/PowerQuery to work with on-premise CRM using the default OData connector.

According to the following article, it is a known issue. Hopefully, MS provides a connector/method to resolve this issue.

http://blog.cobalt.net/blog/excel-odata-feed-from-dynamics-crm-on-premise-or-ifd

This method does work for us, but does not help with PowerBI (directly) as the OData connector in the desktop tool has the same issue / error. Currently, we use the method above to export to Excel - which means we have to manually update the files.

[paulhasell]

Hi, it was indeed a known fault, one that MS was supposed to have fixed with the latest release of Power BI and Power Query, sadly it appears that it's not. My suspicion is that the on-prem IFD needs to be configured the same as Office 365 to use OAuth2 rather than WIA but I can't find any useful documentation on how to do that.

It's interesting that the mobile client and SDK connections work fine but then they're not OData endpoints,