Applications Templates without Programming for Microsoft Power BI

One of the newest ways to extract data from Microsoft Dynamics 365 for Finance and Operation, Enterprise edition is to use a Microsoft Azure database that your template can display without programming in Microsoft PowerBI. At the 2017 Summit for D365UG and AXUG, there was a presentation by Microsoft covering the entire ten-step process. The purpose of this presentation was to answer the question we’re frequently asked by our clients: ‘when can we get our data and when will they make it easy for my power users?’. From the presentation, we learned that the data is available now and it’s easily available.

For those of you who are technical, we are leveraging an Azure database to copy a set data entity and dataset out to a neutral location that we can use as a data source for PowerBI.

For those of you who are not technical, the nickname for this process is “bring your own database” (BYOD) and it is much faster and easier to access than traditional Dynamics 365 for Finance and Operations, Enterprise edition code promotions.

The prerequisite operation for this article is a Dynamics 365 for Finance and Operations, Enterprise edition demo image, a PowerBI account, downloadedPowerBI desktop, and access to your Azure subscriptions. Due to these requirements, you may need an administrator to set it up, but that won’t affect your ability to use it.

Application Templates

We will need to use a template for our demonstration. We will be using the application template that is set up to connect Microsoft Dynamics 365 for Finance and Operations Enterprise edition and PowerBI. You can access all of the application templates available on the Microsoft AppSource site. In this area, you can both test drive and download the application for use. As an added bonus, all of these templates are free. Here is the direct access URL: https://appsource.microsoft.com/en-us/marketplace/apps?product=pbi-solutiontemplates

Here are the thirteen templates that are currently available.

For our purposes, we are going to use the D365 Operations – Credit and Collection Analytics. The other options available use connectors from other applications. The Microsoft team has pledged to create many more of these offerings.

Here we can see page one of the credit and collections solutions.

To download the field service template:

In your demo image, Open Internet Explorer.
Navigate to https://appsource.microsoft.com/en-us/marketplace/apps?product=pbi-solutiontemplates.
In the upper right corner, click login. Use the same AAD credentials that you use for Office 365 and PowerBI.
Click D365 Operations – Credit and Collections Analytics.
Click Get It Now.
Click Continue on the agreement to the terms; this will open the get started with Dynamics 365 Setup screen.

The Setup Process

The primary landing page starts you down the path to getting your item configured. Please read this carefully as the options and requirements may be different depending on your configuration. Key is that if you go back a step, it may end up creating an additional database or check for a new connection, so be careful.

To run through the wizard:

From the getting started, check on any prerequisite information and ensure access.
Click Next.
Your next piece will ask to Connect to Azure. Depending on your configuration you may need to specify if it is a work or school account OR your personal account. Click Connect to Azure.
Log in with your AAD account.
Click Accept.
Click Next.
Click Connect to Dynamics 365.
Log in with your AAD account.
Click Accept.
Click Next.
Click Confirm connection.
Click Next.
Enter the following information:
- Server Name: use a unique name. In example: creditcollections4powerbI (and add the date, company name, or another identifier for post side)
- SQL Username: Set a name for the user. Administrator is overused. Try RSM-112017-Administrator
- Password & Password confirmation. Make sure you choose a password that you can easily remember and that you record all information in a secure location.
- Optionally, you can select Advanced. This is where you can set the initial size
Click Check Availability.
Click Wait for the database to be created.
You now need to go to your Microsoft Dynamics for Finance and Operations and get the URL for your instance. Make sure to follow the template that is provided. Click Validate.
Click Next.
You will not have the option of Azure Analysis Services. This has a cost and can be set up later. Change to No.
Click Next.
It will display your summary screen. Make sure to have it send you an email once configured. Click Run. Do not close your browser.
Click Download Report once prompted.

Display Using PowerBI Desktop

Once the download is complete, you can use the new PBIX file.

To open the file and display the results:

Launch PowerBI Desktop.
Click File and open the CreditAndCollectionsImportMode. The first page will display the final steps for the process.
Select Home
Click Edit Queries.
Select Edit Credentials.
Select Database.
Enter User and Password and database information that was set up earlier.
Click Connect. After a download process, the information will display.

Conclusion

This article explored the process of using the new application template process to download and complete the steps necessary to create a database. Once the data has been created, it can then be displayed and interacted with inside PowerBI. The data originates from your instance of Dynamics 365 for Finance and Operations, Enterprise edition and can be refreshed in order to give insights into your credit and collections. Through the process of creating several different renderings of the data, you can quickly interact with the set of information and create inferences for decision making.

For more information, I would recommend attending our PowerBI, or Dynamics 365 for Finance and Operations, Enterprise edition training in person or self-paced e-learning online to learn more.

Plus, keep an eye out for more articles that dive into the technical aspects of using Excel, X++ code, and PowerBI. Please visit academy.rsmus.com for more information and training materials that will cover this and hundreds of other topics for AX 2012 and Dynamics 365 for Finance and Operations, Enterprise edition.

by Shaun McMikle for RSM