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:

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.

  • 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
  • Click “Connect

  • You will get the message “Successfully validated
  • Click “Next

  • 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
  • Click “Next

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”

  • Almost there….
  • 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!

2017-01-10_20-43-21

 

  • This is the tab Daily

2017-01-10_20-45-40

  • Tab Sales

2017-01-10_20-46-27

  • Tab Pipeline

2017-01-10_20-48-04

  • Tab Owner

2017-01-10_20-48-21

  • Tab Product

2017-01-10_20-48-41

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:


ERRORS:

These are some of the errors I got before I could make it work!

Error No1.

  • An error has occurred, see additional details to learn more
  • Failed authentication
  • Allow access this module: Microsoft-CrmGetOrgs
  • Solved by getting CRM Admin permissions.

Special thanks to Kasper Birch Olsen


crm-error1

Error No2.

  • 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

crm-error2