Power BI – JohanPersson.nu

One of the cool features in Dynamics 365 for Finance and Operation are Analytical workspaces which enables Dynamics to display PowerBI workspaces inside of the Dynamics UI which also means that you are able to use the included limited PowerBI license that comes with D365fO

To get this working you can either deploy the includes report packages which are downloadable from LCS or you can create your own and upload them to LCS. There is however some infrastructure required to get this up and running because the report get their data from aggregetad measurements, which you are required to create.

The measurements are syncronized to the AxDW database and the report uses Direct Query to read the data. Since the report reads its data from the AxDW database it does not have the ferformance implications that OData has.

The process for setting this up in a Tier-2 and up environment is pretty straight forward since these all include the specific service for hosting the PowerBI report but in Tier-1 (OneBox) this service is not present and this means that we will have to do a slight workaround.

First we have to create an Azure Service for hosting the report, you will find the detils in this link

The next step is to copy the AxDW database from the environment to Azure SQL. You find the information here and here.

And the last part is to reconfigure the OneBox environment to use the Azure SQL hosted entity store instead of the internal

I only had one minor issue diring the setup… the AxDW admin user was not able to log in to the AxDW database in Azure SQL resulting in this message:

I resolved it by deleting and recreating the user :

CREATE LOGIN [axdwadmin] 
WITH PASSWORD = 'axdwpassword'

CREATE USER [axdwadmin] 
FOR LOGIN [axdwadmin] 
WITH DEFAULT_SCHEMA = dbo; 

ALTER ROLE db_datareader ADD MEMBER [axdwadmin]; 
ALTER ROLE db_datawriter ADD MEMBER [axdwadmin]; 
ALTER ROLE db_owner ADD MEMBER [axdwadmin];

Links:

https://blogs.msdn.microsoft.com/dynamicsaxbi/2017/07/29/deploy-power-bi-embedded-service-for-dynamics-365-for-operations/

https://blogs.msdn.microsoft.com/dynamicsaxbi/2017/07/29/deploy-azure-sql-server-for-hosting-entity-store-database/
https://blogs.msdn.microsoft.com/dynamicsaxbi/2017/07/29/publish-the-entity-store-database-on-azure-sql-server/

https://blogs.msdn.microsoft.com/dynamicsaxbi/2017/07/29/configuring-1box-to-enable-analytical-workspaces-and-reports/

Today I helped two of my colleagues setting integrating Power BI for Dynamics 365 for operations. There are a couple of guides online which I have used to create this post, but I thought I would create one from my experience.

There are a couple of main steps we need to go through:

Create a Azure AD Application
Configure Dynamics 365 for operations for Power BI
Configure Power BI
Configure your workspace

Create a Azure AD Application

There are two ways of doing this… you can do it using the Power BI Developer registration tool or you can do it directly from the Azure Portal. We did it from the Azure Portal like this:

Log into the Azure potal… You can use both the old and the new one
Goto Azure Aztive Directory and select your Azure Active Directory Instance
Go to Applications/App Registrations
Create a new application with these settings:
1. Name: ChooseYourName
2. Application Type: Web app/API
3. Sign-on URL: https://theadressforyourdynamics365foroperationsinstance/oauth
4. App ID URI: ~~https://theadressforyourdynamics365foroperationsinstance~~ Anything EXEPT https://theadressforyourdynamics365foroperationsinstance . You can for instance use https://DynamicsBI, it only has to be unique for each Azure AD Application
  Edited 27/1 2017
5. Reply URL: https://theadressforyourdynamics365foroperationsinstance/oauth
When this is created you will get a Client ID/Application ID and you will create a key. You need to save these because you will need the later. Note that the keys are only visible when they are created, Once you leave the page and go back you cannot see them again. If this happens and you have not saved it you can simply create an new one.Old portal
New Portal:
Once you have set up the application in Azure ID you will need to give it some permissions. This is where we got stuck because in one of the guides there was a screenshot which showed to few permissions and it simply did not work. You add the application Power BI Services and give it the correct permissions. We used the following permissions:
1. View all Groups
2. View All Reports
3. Create Content
4. View content Properties
5. Read and Write all datasets
6. View All Datasets
7. View All Dashboards
8. Add Data to User’s Dataset

Configure Dynamics 365 for operations for Power BI

In Dynamics 365 for Operations, go to System Adminstration – Power BI
You need to Enable the configuration and then you change these values
1. Azure AD Tennant (this you will find in the Azure Portal)
2. Client ID (you saved it above)
3. Application key(you saved it above)
4. Redirect URL: ps://theadressforyourdynamics365foroperationsinstance/oauth (This one is incorrect and will not work… ever… I do not understand how there can be an incorrect default value)The other values are correct

Configure Power Bi

Every user that is going to use Power BI (more or less every user that is usign AX) will need at least a Power BI free License. As the name implies it is actually free and you addit in the Office 365 Admin Portal. Once you have added the wou will have to do this for every user:
Log into Power BI (https://powerbi.microsoft.com)
Click Get Data in the lower left corner
Click Services – Get
Click Get it now on each of the three (for now) AX Apps
On each of the Apps enter the URL for your AX instance (https://theadressforyourdynamics365foroperationsinstance ) and flick next. If you get prompted for authetication select OAuth2

Configure your workspace

Log into Dynamics 365 for operations
Go to the Workspace called Cost Administration
Scroll to the right and click on the Power BI pane
If this is your first time you need to authorize Power BI. Click the link, log in using your credentials and approve the permissions.
Return to the Dynamics Tab and click close
Select the Power BI tiles you want from the Tile catalog and click OK to add them to your workspace