cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aliia Frequent Visitor
Frequent Visitor

Connect MS SQL Server 2016 with Dynamics

Hello!
Previously I've got a problem with slow operation of Power BI during application of changes. My Power BI is connected to Dynamics 365. I found similar question here http://community.powerbi.com/t5/Desktop/Slow-download-from-CRM-Online/m-p/303953#M134371 It was advised to use Database inbetween for direct queries from Power BI.

 

1. As first variant I want to use MS SQL Server 2016 for this. I found out SSIS Integration Toolkit for Microsoft Dynamics 365, however I can't find any info about actions on side of MS SQL Server. Can Microsoft provide a clear instruction how connect their two products with each other in order to solve issue with third one?

 

2. Plan B is MS Access. I found this https://support.office.com/en-us/article/Link-to-or-import-data-from-Dynamics-365-636079c1-9fc3-4fca...I need an example of URL. None of what I used actually worked out. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Connect MS SQL Server 2016 with Dynamics

@Aliia First, I will mention that in my interactions with Dynamics the #1 biggest impact to performance was pulling in related values from another entity. If you are doing this, stop, remove all the related fields and only pull in the actual values from the entity in Dynamics. This most likely requires re-building the relationship and certain fields in Power BI, but it is worth it. My queries went from hours to minutes by doing this.

 

If you go down the routes discussed. You need SQL Servier Integration Services. The 3rd party tools plug into SSIS, and you connect to the Dynamics environment via the tool and extract the data and load it into a SQL Database. From there, you can connect Power BI to the SQL database. 

 

MS Access... I don't have experiance here, but it doesn't seem like a route I would go.

Near SE WI? Join our PUG Milwaukee Brew City PUG
2 REPLIES 2
Super User
Super User

Re: Connect MS SQL Server 2016 with Dynamics

@Aliia First, I will mention that in my interactions with Dynamics the #1 biggest impact to performance was pulling in related values from another entity. If you are doing this, stop, remove all the related fields and only pull in the actual values from the entity in Dynamics. This most likely requires re-building the relationship and certain fields in Power BI, but it is worth it. My queries went from hours to minutes by doing this.

 

If you go down the routes discussed. You need SQL Servier Integration Services. The 3rd party tools plug into SSIS, and you connect to the Dynamics environment via the tool and extract the data and load it into a SQL Database. From there, you can connect Power BI to the SQL database. 

 

MS Access... I don't have experiance here, but it doesn't seem like a route I would go.

Near SE WI? Join our PUG Milwaukee Brew City PUG
Aliia Frequent Visitor
Frequent Visitor

Re: Connect MS SQL Server 2016 with Dynamics

Thanks for reply! I didn't have chance to try it yet. I am waiting the license for SSIS Integration Toolkit.