Publishing Dynamics AX Data To Power BI On Office 365 Using The Data Management Gateway

 If you ask me, Power BI is the best thing since sliced bread because it gives you a great set of reporting tools. I didn’t think it could get any better until it was released out on Office 365 giving you a hosted solution without the hassle of having to configure your own SharePoint site to host your dashboards on, and also included cool tools like the natural language Q&A feature. It couldn’t possibly get any better than that. But it can, because you can also use the Data Management Gateway to link your on premise data to Office365 making the data available to users that are not even logged in to Dynamics AX through Excel.

Don’t chain up your data within your local system – set it free so that it can help everyone.

How To Do It…

1. There is a little bit of setup required though in order to get this working. First you need to create a gateway connection between Power BI and your local Dynamics AX database. To do this, connect to Office 365 and open up the Power BI Admin Center. From there, select the Gateways page and click on the New Gateway link.
   
2. When the New Gateway setup starts, give your Gateway a Name and a Description. Also check the Enable Cloud Credential Store option before clicking on the Create button.
   
3. This will take you to the Install & Register page which will have a unique Gateway Key for you. Don’t close the page just yet.
   
4. If this is the first time that you are doing this then you will need to install the Data Management Gateway client on the local Dynamics AX server (or really any server that is able to access the data). To find the install kit, just click on the Download button.
   
5. This will take you directly to the Data Management Gateway download page.
   
6. Download the install kit and run through the installation wizard.
   
7. After it is installed, a Configuration Manager dialog will be displayed for you to connect your local database to the Office 365 gateway.
   
8. Paste the Gateway Key from the Office 365 Gateway page into the Gateway Key field and then click the Register.
   
9. Then when the Specify Certificate For Credentials page is displayed, select the Use Power BI Generated Certificate and then click on the Next button.
   
10. When the Export Certificate dialog is displayed, type in the recovery password and then click the Next button.
    
11. This will create a certificate for you that you will want to save away.
    
12. Next select your connection method (Normal, or secure) and then click on the Next button.
    
13. And then click on the Finish button to finish setting up the Data Management Gateway.
    
14. If everything is working correctly and the service is started on the server, then you will see the Service Status form and you can close the Configuration Manager form.
    
15. Now return back to the New Gateway setup on Office 365 and click the Finish button.
    
16. This will take you to the Gateways page and you will see your new Gateway is up and running and communicating with the server.
    
17. Now we want to publish some Data Sources through the Gateway to make them available for reporting off. To do this, click on the Data Sources link to open up the Data Sources maintenance page.
    
18. Then click on the New Data Source button and select the SQL Server option.
    
19. When the Data Source Usage page is displayed, check both the Enable Cloud Access and the Enable OData Feed options and click on the Next button.
    
20. Within the Connection Info page, give your Data Source a Name and also a Description.
    
21. Then select the Gateway that you just configured.
    
22. Then enter in the Server Name that has the local MSSQL instance running on it and also the Database that you want to publish out.
    
23. Click on the Set Credentials button.
    
24. If this is the first time that you are doing this then you may be asked to install the Data Source Manager application on the server.
    
25. This will open up the Data Source Settings dialog box. First select the Credentials Type.
    
26. Then enter in the Username and Password that you will be using for the connection.
    
27. Click the Test Connection button to validate the credentials and then click on the OK button.
    
28. When you return back to the Connection Info page, click on the Next button.
    
29. Now something really cool will happen – Office 365 will connect back to the local system and get all of the tables and views… it may take a few minutes though…
    
30. When the tables are returned back and listed, just select the data that you want to publish by checking the table or view.
    
31. After you are done, click the Next button.
    
32. Finally, you are given the option to specify which users are able to access this data. After you have configured that, click the Finish button.
    
33. Office 365 will now create the Data Source for you.
    
34. And after a few minutes it will be available for consumption.
    
35. One last task that you may want to do before you tell everyone that this is available is to make the Data Source a little friendlier for the users. To do this, open up the Power BI admin page and select the Data Sources tab. Change the Show option to All Data Sources and you will see your new Data Source that you just created.
    
36. The name is a little generic, so click on the … to the right of the name and click on the Manage link.
    
37. This will open up the Data Source details and you can change the Display Name and Description to something as little better.
    
38. Now we are done – we can start using the data source within Excel reports.
    
39. The easiest way to find the Data Source is through Power Query’s
    Online Search option. Before clicking on this button though select the POWER QUERY ribbon bar and then click on the Sign In menu button within the Organization group.
    
40. This will allow you to log sign into Office 365 within Excel.
    
41. Once you are signed in, click on the Online Search button within the POWER QUERY ribbon bar.
    
42. This will open up an Online Search panel to the right of the worksheet.
    
43. Just type in a key word from the data that you published, and the data sources will miraculously appear in the list.
    
44. If you click on any of the Data Sources then it will connect to Office 365 for the data, and then back down to the local database – regardless of if you are connected to the network or not.
    
45. And then Power Query will add the data to the worksheet.
    
46. From this data we can create PowerView reports…
    
47. We can publish them back up to Power BI on Office 365…
    
48. And if we really want to be clever, add the report to Q&A by clicking on the Add to Q&A menu item within the reports pop up menu.
    
49. Once it’s been added, just click on the Ask with Power BI Q&A link in the top right hand corner of the page.
    
50. That will open up the Q&A dashboard.
    
51. Now just type in your question and see what Power BI discovers.
    
52. The more details you ask, the more elaborate the response will be.
    
53. You can even create the nudge Q&A a little by suggesting the visualization.
    
54. If you take a little bit more time with the data source and rename the fields and remove all the data that you don’t want to display, then you also end up with a better data set…
    
55. And you can ask more English-like questions.
    
56. Since this Data Source is connected through the Data Management Gateway then there is one final feature that we can now take advantage of and that is the ability to refresh the data automatically from Office 365. To do this, just select the Dashboard, click on the … menu item and select the Schedule Data Refresh menu item.
    
57. This will open up the Refresh Schedule options page.
    
58. To configure the automatic refresh, just switch the button to ON set the refresh schedule details, and save the settings.
    
59. If you look in the History tab, you will be able to see all of the refreshed that have been performed, and if they were successful or not.

Now you have love data that is available to everyone through Office 365. How cool is that!

To See the original post and to download the walkthrough click here:
http://www.dynamicsaxcompanions.com/Tips-And-Tricks/Tips-Tricks-Archive/Publishing-Dynamics-AX-Data-To-Power-BI-On-Office-365-Using-The-Data-Management-Gateway