Power BI from microsoft is one of the most interesting initiatives taken by microsoft within the last year or so. I mean SQL Server Reporting Services were the only way to develop reports if you want to have them out of the box. Also, in order to develop those reports, it is very difficult for a business user to make a report on the fly and share the results with the team.
Power BI has a very vast set of data sources it can connect with, specially the products like Microsoft Excel, Microsoft Access, SQL Server. So, it means that we have an option now to make the reports either in SSRS or in Power BI.
Today, I am going to show you how we can use Power BI to connect with the Dynamics AX 2012 Cubes and make the reports which can give a better understanding of the data as it will be shown as Charts & Graphs. For this, Dynamics AX 2012 Cubes must be deployed and you also need to have a Power BI account on the cloud. You will also need to work with Power BI Desktop for the reporting & the On Premise Data Gateway in order to achieve the connectivity between Power BI and Dynamics AX 2012 Cubes. Here are the steps:
- Download and Install Power BI Desktop from here.
- Download and setup the On Premise Data Gateway. Note that if you want your Power BI reports on the cloud, only then you need to have the On Premise Data Gateway setup.
- Connect the Dynamics AX 2012 Cube based on your need i.e. For Sales and Marketing reports, you can use Sales and Marketing Cube (AX 2012 Cube Documentation). The process is as follows:
Figure 1: Select “Get Data”.
Figure 2: Select “Database > SQL Server Analysis Services” & “Connect”.
Figure 3: Enter your “Serve” & “Database” names. For AX2012, the database name is “Dynamics AX initial” by default.
Figure 4: Select your desired “Cube” for the list & “OK”.
Figure 5: The successful connection will list you the “Measures” & “Dimensions” as shown above.
As we are now successfully connected with the AX 2012 cubes, I’ll show you how you can make a POWER BI Dashboard in my upcoming blog post. Stay Tuned!
