Connecting Power BI with SQL database

[Anonymous]

We are currently using Dynamic CRM online and power bi as reporting and dashboards.  Due to the limiation of CRM online and slow autorefresh.  i'm thinking to get a SQL database on-premises.  is this approach fessible? what's the benifit can you see? or any other better suggestion?   thanks!

[Anonymous]

Hi, 

Have you looked at the Dynamics 365 'Data Export' solution?

It replicates the data from the CRM to an Azure SQL Database and keeps it synchronised. 

We have been using this for various analytics scenarios on Dynamics 365 data. 

Regards, 

Matt

[Anonymous]

@Anonymous I just recently got the DES, haven't really tried yet.  do you know how's the speed compare to DES to Power BI and Dynamic CRM to Power BI?  

[Anonymous]

Hi @Anonymous,

In my experience the initial replication of the CRM can take some time, depending of course of the amount of data in there, but once it's up and running you can play around with the different service levels of the Azure SQL Database - the P1 service tier is the minimum recommended to avoid any issues with throttling during the replication and synchronisation, and you can always increase it depending on what you are doing. 

Having said that, I have seen issues whereby we have increased significantly the service tier of the database, but a refresh in Power BI still takes a while and doesn't use up all the DTUs (i.e. not maxing out the performance of the service tier).

Main advantage is that you can query away without impacting the Production environment, as @Anonymous mentions.

Regards, 

Matt

[Anonymous]

I've made quite a few reports inside our business and this approach works for me in many cases.  The main advantage is that i don't affect the performance of the main users of the system.

The clear disadvantage to me is how up to date the data is.  If your report consumers only need the data to be at the interval of the replication, then you'll be totally fine.