“New AX” (previously known as Microsoft Dynamics AX 7 or just AX7) includes an impressive suite of reporting options, and of these options, Microsoft Power BI is one of the most promising. In this article we’ll walk through how to connect to AX from the Power BI report designer and create a simple example report.
Power BI is a reporting framework developed by Microsoft to present primarily visual, aggregated data. If you’ve heard the term “Key Performance Indicator” (KPI), you can think of a typical Power BI report as a collection of KPIs. Where SSRS and Excel provide primarily row-based output, Power BI is more about presenting visual information.
So why does Power BI matter in the AX world? It matters because Power BI is purpose-built for visual data, where AX has historically repurposed other technologies (such as SSRS) to present visual and aggregate data. It is also more flexible and accessible than some of the other reporting technologies available to AX users (you don’t have to be a developer to build a Power BI report). For more information on what Power BI can do for your business, read this great article: https://www.sikich.com/insight/power-bi-new-dynamics-ax/.
How do I get Power BI?
The basic web-based Power BI client is currently free. You can sign up and sign in to the web-based designer at https://powerbi.microsoft.com/en-us/get-started/. Alternatively, there is a desktop client available for download from Microsoft at https://powerbi.microsoft.com/en-us/downloads/. For the exercise below, you will need the desktop client.
How do I connect Power BI to AX?
Before we connect to AX, we need a goal to work toward. For this exercise, we’ll produce a chart which displays the relative count of sales orders per customer in an AX system. This is not necessarily something a report user would ask for, but it’s a good example for this tutorial.
Note: Due to limitations in the data source options of the web-based Power BI client, it is advised to use the desktop Power BI client when connecting to Dynamics AX.
- When you first open the Power BI Desktop client, you will be presented with a screen that, among other things, asks you to sign in. Do so now.
2. Once you’ve signed in, the splash screen disappears and you’re dropped into the report design canvas.
3. Right now we don’t have any data sources selected, as is evidenced by the blank ‘Fields’ section to the right of the canvas. Our first order of business will be to connect to AX so we can load that customer/sales data we’re trying to display. Click the ‘Get Data’ button to open the list of data sources available to Power BI.
4. Wow, look at all those data sources! For connecting to AX, we’re looking for ‘OData Feed’, which we can find using the search box, or by manually locating it in the ‘Other’ group.
5. When you find the ‘Odata Feed’ option, select the ‘Connect’ button to start the connection wizard.
6. The first screen will prompt you for the URL of the target OData feed. This is normally the URL of your AX client postfixed with ‘/data’. So for example, if your AX client URL was ‘https://contosowidgets.cloudax.dynamics.com‘, then by default the OData feed is exposed at ‘https://contosowidgets.cloudax.dynamics.com/data‘. For demonstration purposes that URL has been included in the image below, but keep in mind you will need to use your own organization’s URL.
7. When Power BI succeeds in connecting to your AX system’s OData feed, the next thing it will ask for is credentials. By default, Power BI highlights the ‘Anonymous’ authentication option, but AX authentication requires an ‘Organizational account’, so highlight that tab and Sign in with your AX user credentials.
8. After successful sign-in, click ‘Connect’ to start loading the list of data accessible from the AX OData feed (this will take a moment).
9. Once the AX data has loaded, the ‘Navigator’ screen will open. This screen displays all of the data you have access to within AX. The data is exposed via customizable “data entities” in AX, so if the data you’re looking for isn’t represented in the list, you can create a new data entity in AX to expose it. In our case, we’re looking for sales order information, and a quick search for ‘sales’ reveals a dataset that exposes what we want: ‘SalesOrderHeaders’. Select the ‘SalesOrderHeaders’ entry in the result list to view a preview of that entity’s available data.
10. Once the AX data has loaded, the ‘Navigator’ screen will open. This screen displays all of the data you have access to within AX. The data is exposed via customizable “data entities” in AX, so if the data you’re looking for isn’t represented in the list, you can create a new data entity in AX to expose it. In our case, we’re looking for sales order information, and a quick search for ‘sales’ reveals a dataset that exposes what we want: ‘SalesOrderHeaders’. Select the ‘SalesOrderHeaders’ entry in the result list to view a preview of that entity’s available data.
11. The ‘Fields’ list next to the canvas is now populated with our selected data source, as well as the list of fields available from that data source. We want to display the count of sales orders per customer name, so we’ll need the customer’s name, and we’ll also need the unique identifier for each customer (their account number). So let’s select ‘InvoiceCustomerAccountNumber’ and ‘SalesOrderName’ from the field list. Note that Power BI has automatically generated a display artifact for the fields we selected – cool!
12. Our goal is to display a count of sales orders per customer, so let’s add the count now. In the ‘Values’ pane, click the dropdown next to the ‘InvoiceCustomerAccountNumber’ field and select ‘Count’. Voila! Power BI now displays a count of sales orders per customer.
13. The current ‘table’ format Power BI gave us by default is good for building and troubleshooting the report output, but it’s not the quick visual representation we’re looking for in a finished report. Let’s switch the table artifact to display using one of the built-in chart formats. Select the table in the canvas if it isn’t highlighted already, then choose the ‘Clustered Column Chart’ format from the visualizations pane. (note the chart in this screenshot has been resized to improve visibility).
14. Looking good! Now you can save the report, and (optionally) publish to your organization for other users to view. Congratulations – you’ve built a Power BI report with AX data.