Hello people, we are back once again with another exciting blog related to Microsoft Power BI. As we all know, Power BI offers you to connect with what matters to you. It does not matter which type of data you have or where the data exists, with Power BI you can connect with hundreds of data sources. Today we are going to see how one can configure and get data from Dynamics 365 on-premises into Power BI. So please follow the steps below:
1) Firstly , you should have a Power BI account to login and it MUST be the same as your D365 login.
Go to http://www.powerbi.com. Click on ‘Get Started Free’ button. Go ahead with the instructions
2) Register Your Application.
Login to your Power BI account you have created in Step 1 and then go to http://dev.powerbi.com/apps
1) Server side Web app as App Type
2) In the redirect URL type: https://usnconeboxax1aos.cloud.onebox.dynamics.com/oauth
3) The Home Page URL can be any bogus URL.
4) Select all the check boxes under ‘Choose APIs to access’ heading.
Once you click Register App button , you will get Client ID and Client Secret. Note this down as it is very important ahead.
3) Install the Local Power BI Gateway.
Login to Power BI account and download the gateway from upper right corner.
When installing the gateway, choose the “on premises data gateway” option. Follow the instructions. You will have to give name your gateway and create a gateway recovery key.
4) Configure Dynamics 365
Login to your Dynamics 365 account. Click on the hamburger on the upper left side and click on System Administration and then Power BI.
Change the Azure AD tenant to your tenant eg: somecompany.onmicrosoft.com. The Client ID is the client ID you got in Step 2, and the application key is the client secret.
You are now ready to go.
5) Test Connections
Login to your Power BI account, then click on gear icon and then ‘Manage Gateways’
Click on the “Test all connections” button. If the gateway computer is turned on the screen should display the “Online: you are good to go” message.
6) Adding Data Source to the Gateway
Click on ‘Add data sources to use the gateway link’.
As mentioned, use the AOSUser/AOSWebSite@123 as username/password combination. Now this is all about Configuration. Now you can get data from Sql Server into the Power BI desktop.