Reply
Super Contributor
Posts: 949
Registered: ‎12-29-2015

Re: Power BI with on-premise Dynamics CRM

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.

Frequent Visitor
Posts: 4
Registered: ‎01-05-2016

Re: Power BI with on-premise Dynamics CRM

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!
Super Contributor
Posts: 949
Registered: ‎12-29-2015

Re: Power BI with on-premise Dynamics CRM

[ Edited ]

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.

Super Contributor
Posts: 949
Registered: ‎12-29-2015

Re: Power BI with on-premise Dynamics CRM

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.

Frequent Visitor
Posts: 8
Registered: ‎01-14-2016

Re: Power BI with on-premise Dynamics CRM

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?
Frequent Visitor
Posts: 8
Registered: ‎01-14-2016

Re: Power BI with on-premise Dynamics CRM

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...

Frequent Visitor
Posts: 8
Registered: ‎01-14-2016

Re: Power BI with on-premise Dynamics CRM

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...

Frequent Visitor
Posts: 4
Registered: ‎01-05-2016

Re: Power BI with on-premise Dynamics CRM

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ä

Frequent Visitor
Posts: 4
Registered: ‎01-05-2016

Re: Power BI with on-premise Dynamics CRM

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