Connecting Power BI to Dynamics 365 CRM – Solution Template (Download now)
Today I would like to show you an amazing solution template for Dynamics 365. It definitely has one of the most beautiful layouts I have ever seen in Power BI. – Great job Dynamics team! J
Just to make it easier for you:
Enterprise ready sales management analytics
The Dynamics 365 solution template contains everything needed for a secure, scalable and extensible Sales Management dashboard analytics. In just a few hours stand up an end-to-end solution including Scribe’s cloud-based ETL platform, a cloud or on-premise database, and polished Power BI reports (including a security model derived from your own Dynamics 365 instance).
Sales management for Dynamics 365:
Get started quickly with pre-built data models for Sales Management analytic dashboards
Use an intuitive wizard based UI to setup and customize data integration
Gain enterprise grade performance by processing large data volumes from Dynamics 365
Optimize data integration with incremental data extraction capability
Everything is freely available on GitHub and completely documented so you can spend your time customizing the end results instead of designing and coding from scratch.
So, let’s get started:
Access the Microsoft Power BI Solution Templates:
Click on “Microsoft Dynamics 365 – Sales Management for Dynamics 365“
Get powerful insights from your Dynamics 365 customers in Power BI with a scalable, extensible and secure solution. Accommodate your own CRM customizations, add data from other applications, such as actual revenue from your ERP system, and reuse features of the existing security model.
You can also access directly here: https://powerbi.microsoft.com/en-us/solution-templates/dynamics-crm/
In the “Sales Management for Dynamics 365“
Click in “Install now“
Now you are in the “Get started with Sales Management for Dynamics 365” or “Step 1 – Get started“
Look how cool it is this step-by-step! Wellllll donnneeee!
You can now select what Dynamics instance you would like to use
I’m using the “Dynamics 365 Online“, which I highly recommend.
You can also see all the requirements to get this template created below
ATTENTION: In the end of the post I’m showing all the errors I got until I got to work!
“Step 2 – Dynamics 365 Login“
Click on “Connect“
Select the account you would like to use.
Select the “Dynamics 365 Organization“
Select the “Azure Subscription“
Check the box where you agree with the estimated cost for the Azure SQL Database and Azure Key Vault. 75$/month
You change that later for a cheaper service.
I’m using a trial/demo for this post, but I also did the same steps for our production environment and it worked perfectly.
“Step 3 – Login to the Key Vault“
You will get the message “Successfully validated“
“Step 4 – Target“
Select “New Azure SQL Instance“
Type a “Server Name” : dynamics365crm
Type a “SQL Username” sqladmin
Type a “Password” : MyPasswordIs365xMoreComplex
Click “Check Availability“
ATTENTION: remember/copy these info, because you will need it afterwards.
You can subscribe for bla bla bla…
Yeh! I did…
“Step 5 – Progress“
Wait until all the 7 tasks are done and completed
I have got another error here: check in the end of the post.
You see this, doesn’t mean it’s completed… sorry…
Wait 15min… if nothing happens, press F5 to refresh.
Buuuuuuut, if you see this, you did it!!! J
Now click on “Download PBIX” to get you Power BI Desktop file.
If you are like me, and just wanted to see the file, in the end of the post I will share the PBIX for download without all these steps!
After downloading the Power BI Desktop file, PBIX, you will need to configure it to use your Azure Database.
But it’s pretty simple…
Click on “Edit Queries“
Connect to your data You have now successfully set up your Sales Management solution template. In order to start exploring your data please select ‘Edit Queries’ in the top ribbon, under the Home tab. You should see an Edit Credentials message in the yellow bar. Select it and make sure you are in the Database tab (as opposed to the Windows tab). Input your SQL credentials (the ones you specified during setup) and press connect. Data should now start coming into your Power BI file!
Select the “STSqlServer“
Type your Azure SQL Server name: dynamics365crm.database.windows.net
Select the “STSqlDatabase“
Type the of your database: asdf%&2246D&¤
In the Home tab, click on “Close and Apply”
Now type you SQL Server Username: sqladmin
Type your Password: MyPasswordIs365xMoreComplex
And yesssssss!!! It’s alive…
Super cooool Power BI Desktop report for you to enjoy!
This is the tab Daily
Hope you like it… because it’s a beautiful report and I love it do make it alive! J
To view and try the live report click here:
Download my report in .PBIX format:
Link you can send in emailhttps://app.powerbi.com/view?r=eyJrIjoiNjRlYmRjN2YtY2UwOC00NDZiLTljMmUtZmJjNWM3OTdkZjk0IiwidCI6IjQyNDc0M2I3LTU3ZTctNGRiNi1iYmRkLTFiNDliMzcyZmNiYiIsImMiOjh9
<\iframe”>Html you can paste into your blog or websitehttps://app.powerbi.com/view?r=eyJrIjoiNjRlYmRjN2YtY2UwOC00NDZiLTljMmUtZmJjNWM3OTdkZjk0IiwidCI6IjQyNDc0M2I3LTU3ZTctNGRiNi1iYmRkLTFiNDliMzcyZmNiYiIsImMiOjh9
These are some of the errors I got before I could make it work!
An error has occurred, see additional details to learn more
Allow access this module: Microsoft-CrmGetOrgs
Solved by getting CRM Admin permissions.
Special thanks to Kasper Birch Olsen
Could not create replication profile.
Solved by the CRM/Power BI guru’s:
“It’s was real problem with CRM. We’ve escalated to our friends over there. We’re told they will have a fix shortly – today hopefully.“
Special thanks to Richard Tkachuk and the entire team: PBISolnTemplates@microsoft.com