Part 2: Power BI Dashboards for Dynamics 365 Finance & Operations via the Common Data Service

[Greg_Deckler]

Introduction

Part 1 of this series introduced at a high-level how the BYOD pattern could be implemented in the new world of Dynamics 365, providing a cloud first spin on a common on-premises pattern for reporting on Dynamics data. Part 2 of this series details the first three steps of connecting Dynamics 365 to Power BI via the CDS, namely:

• Creating the CDS Environment
• Creating the Connection
• Customizing the Entity (OPTIONAL)

Pre-requisites

Before we get started with the detailed walkthrough there are few prerequisites that must be addressed. These prerequisites include:

1. Dynamics 365 Finance & Operations Environment - While it may go without saying, we are going to go ahead and state it anyway, you will need a functioning Dynamics 365 Finance & Operations environment. This can be a sandbox or production Dynamics 365 Finance & Opertions tenant or, what we tend to use internally for training and demo purposes, an all-in-one Dynamics 365 virtual machine deployed via LifeCycle Services (LCS). Creating an all-in-one Dynamics 365 Finance & Operations virtual machine is a blog post in and of itself. Luckily, there are numerous existing blog articles out there on how to do it, including this one. 
2. Office 365 Identity - You will need an Office 365 login
3. PowerApps - You will need a license for PowerApps in order to create the CDS environment
4. Power BI - You will need a Power BI Pro license

Creating the CDS Environment

The first step in connecting your Dynamics 365 environment to Power BI is creating the CDS environment that will serve as the bridge between Dynamics 365 and Power BI. To create this environment, follow these steps:

1. Open a web browser, go to https://admin.powerplatform.microsoft.com and logon with your Office 365 account. You should see a screen like the one below:

2. In the left-hand navigation pane, click on Environments. The following page is displayed, showing any existing CDS environments:

3. In the ribbon area, click + New. This opens a pane on the right-hand side of the page as shown below. In this pane, enter a Name for the environment, choose a Type (either Production or Trial) and choose a Region. Additionally, slide or check the box to Create a database for this environment and then choose the Next button at the bottom of the pane.

4. The right-hand pane changes after clicking the Next button as shown below. Now choose a Language and Currency. In addition, slide or check the box to Enable Dynamics 365 apps. This is important and why we create the CDS environment in powerplatform versus PowerApps. The PowerApps admin portal does not provide this option when creating a CDS environment. This option allows one to deploy the Dynamics 365 Finance and Operations entities into the CDS. Now click the dropdown for Automatically deploy these apps and check all the boxes. Finally, click the Save button at the bottom of the pane.

5. After clicking save, a banner will be displayed reading New environment "xyz" is getting prepared. It can be used once active as shown in the screenshot below. Your new environment should be listed along with a State of PreparingInstance. Wait for the instance to become Ready. You may have to manually Refresh the list/page.

6. Once the environment is Ready, click on the name of the environment to see the details. A page similar to the following screenshot is displayed. Be sure to copy the Environment URL as we will need this later.

Creating the Connection

OK, so now we can create a data connection to our Dynamics 365 environment in our CDS environmet. 

1. Navigate to https://powerapps.com

2. Make sure to switch to the correct environment in the upper-right of the top bar where thre is a drop down for Environment.

3. In the left-hand navigation, expand Data and click Connections. Click on the Create a connection link.

4. Search for dynamics and then choose Dynamics 365 for Fin & Ops.

5. Authenticate and a new connection should be created and show up in the list of connections within the environment.

Creating/Customizing the Entity

Creating a connection to Dynamics 365 Fin & Ops creates numerous entities specific to Dynamics 365 Fin & Ops. We can use the Entities as is or we can customize these entities. We can also create entirely new entities. To create an entity, follow these steps:

1. In the left-hand navigation, navigate to Date and then Entities. Note the list of entities displayed. Scroll through the entities and note that many of these are specific to Dynamics 365 Fin & Ops versus CRM.

2. To create a new entity, click New Entity. The New Entity panel is displayed. Note that the entity is prefixed by the id of the CDS environment. Complete the information and then click then Create button.

3. The new entity should now be displayed in your list.

4. To add a field to our entity, click the Add field button. Fill in the information in the panel that is displayed and then click the Done button.

5. Your new field will be added and note that it is prefixed by the ID of the CDS environment. Click the Save Entity button in the lower right of the screen.

6. Your new entity or modified entity has now been created/updated.

[Cloud_Martha]

Thanks for a great article and inspiration. I have a little question:

I would like to build a datawarehouse with the entities.

The entity you are showing us at the end "lives" in an azure db somewhere (the one you created).

So we would build the datawarehouse in the same azure server.

My question is, if you can build and export the entity from the Dynamics 365FO, why would you do it with powerapps and cds?

Greetings.

/The Martha