Power BI and Dynamics AX: Part 2: Extract and Transform
This is part two continuing from Power BI and Dynamics AX: Part 1: Introduction.
This post will focus on the steps involved in extracting data from Dynamics AX into PowerQuery and transforming that data ready to be exposed in your visualisations, or used in PowerQ&A.
Extracting Data via OData
The first few steps of this process would typically be driven by an IT Professional within the business. It requires some knowledge of the structure of data within Dynamics AX to identify the correct query.
To extract data from Dynamics AX, we will be using OData (Open Data Protocol) services. OData allows us to expose a specific query from Dynamics AX for use in external applications. Which queries are available through OData is managed from within AX in Document Data Sources.
Organisational Administration \ Setup \ Document Management \ Document Data Sources
A detailed guide to setting up your Data source can be found here.
Once you have setup your data source, you can view all your published feeds from this URL (Replace <Server Name> with your AOS Server):
https://<Server Name>:8101/DynamicsAx/Services/ODataQueryService/
Note: some queries due to the way they have been developed or certain joins do not expose through OData Properly. The best way to test is a quick connection from Excel after publishing the OData service to see if the query has worked properly.
Some things to keep in mind when selecting your query:
- Invest the time to find the right query, there are a huge number of Queries available from standard AX which you should review before trying to extract multiple tables and joining them externally.
- OData Protocol filters are not supported, so if you require a filtered dataset to be published, you need to apply the filter from within the AX Query or from within PowerQuery. To do this in AX you can do this through the AOT, or by selecting the "Custom Query" option on the Document Data Sources form when creating a new data source.
- Each record in the response must have a unique primary key. AOT View objects which don't have a unique key will not be presented when you try and query the OData Service.
- If you try and access the URL from a web browser and you receive a "Internal Server Error" you may have published a 'bad' query, try setting them to inactive and reactivating them one by one to find the problem query.
Once you have your OData service ready to go, we are ready to connect to the data from PowerQuery. PowerQuery will have two main functions when working with data; Extraction – from Dynamics AX as well as other data sources, and Transformation – to remove unwanted columns, rename columns and tidy up our data to make it as user friendly as possible.
PowerQuery is accessed through a ribbon within Microsoft Excel. If you don't have PowerQuery installed, you can get it here.
A detailed guide of how to connect to your OData source from PowerQuery can be found here.
Important Note: If you plan to use the scheduled refresh functionality within Power BI, you need to ensure the correct case has been used on the OData URL when entered into PowerQuery. At the time of writing the authentication process for Power BI refresh lookups credentials for the OData service with the following case:
https://<Server Name>:8101/DynamicsAx/Services/ODataQueryService/
If you have any characters upper/lower case different to the above – the authentication will fail on refresh.
Transform your Data
After you've connected to your OData source and pulled it into PowerQuery, you can now leverage the tools within PowerQuery to transform your data ready for end users and visualisations.
The data exposed from Dynamics will come out with technical names and often unwanted data, below is an example of the ProjTransPostingCube Query from Dynamics AX R3 CU8.
A detailed guide of how to perform transformations can be found here.
The key transformations to implement when working with Dynamics AX data:
- Remove unwanted columns.
- Rename Column Names to user friendly names
- Example "ProjTable_Name" to "Project Name"
- This step is key to PowerQ&A to support natural language queries.
- Change DateTime formatted fields to Data type "Date"
- Example "10/02/2015 00:00:00:00" to "10/02/2015"
- Merge with other Dynamics AX or Internal Data sources to provide a combined dataset to end users.
- More details on a merge example can be found here.
- Insert Year, Quarter and Month Columns to be used in Visualisations.
- If you select a date field in the query you can add these columns by using the Date option on the "Add Column" ribbon.
- Once added – ensure to change the Data Type to "Text" otherwise when you include it in visualisations it will try and total Years as real number values.
Once transformed a data source is not only easier to work with when designing visualisations, it is also allows PowerQ&A to work with the data in natural language queries. Below is the same ProjTransPostingCube query after transformation.
Enhancing your data with measures
Using PowerPivot within Excel, you can start to add calculated values and KPIs to your data set to use within your visualisations. This functionality is accessed from the PowerPivot tab within Excel, to open up the PowerPivot Data Model, click Manage.
Using the calculation grid at the bottom of the pane you can create calculated measures which will then be available in your visualisations. In the example below we have a new measure for "Actual Cost" which is based on Project Transactions, filtered on "Project – Cost" transactions. A detailed guide of how to create measures can be found here.
Once you've created your measures and saved the data model, they will be available in the field list for PowerView and can be added to visualisations like in the example below.
If you would like to align your terminology and calculations to the Standard Dynamics AX cubes review Cube and KPI reference for Microsoft Dynamics AX [AX 2012] for a breakdown of the measures available in the standard cube and the basis of the calculation.
Merging with Data from Azure Marketplace
One of the most powerful features of PowerQuery is leveraging data from other data sources, including the Azure Marketplace. The Marketplace has a collection of data from population statistics, business and revenue information and reference data to help build your visualisations. One of the most helpful is a data source for Date information. While this may sound basic, it's really helpful in presenting visualisations without having to reinvent the wheel in your query.
A great example and one I have used is DateStream (https://datamarket.azure.com/dataset/boyanpenev/datestream) it is a free data source which contains a reference of the Month name, Day name, Quarter, etc for dates.
To use a data source from Azure, you first need to sign up on Azure Marketplace with your Microsoft account (Live) https://datamarket.azure.com/home. Once you've signed up and found the data source you would like to use, you subscribe to the data source through the data market. Now when we log in through Excel, it will be available for us.
In Excel, the process is similar to if we are connecting to OData. From the PowerQuery tab select "From Azure" > "From Microsoft Azure Marketplace". You will then be prompted to enter your credentials (using your Microsoft account you used at the Azure Marketplace). After signing in you will be presented with a list of data sources you have subscribed to online.
Once the data is loaded into your data model, you follow the same merge process we described earlier to merge the new date fields with your data source. The result is now the additional columns in your primary query. In the example of the date reference query, we now have Year, Quarter and the Month name to use in visualisations.
Sharing your Transformed Query with others
After you've invested the time to transform your query into a nice end user ready data source, you can save it to your Office 365 Data Catalogue. This will allow other users in your organisation to benefit from the time you've invested in transformation and work with the end result in Excel and in their visualisations. You can access the Data Catalog from the PowerQuery ribbon in excel, you'll need your Office 365 Credentials to log in.
A detailed guide to saving and sharing queries can be found here.
Now you should have a clean and friendly data source available within Excel, the next post will talk about creating and publishing visualisations.
Thanks,
Clay.
Comments
Anonymous
December 20, 2015
Can Power BI be used with AX 2009Anonymous
December 20, 2015
Hi Ankur - you can, but not using the same methods as described in this post. Unfortunately you need to spend more time in accessing the data. At the moment you can use an SSAS Tabular model to expose your data to PowerBI (this is described in the last post of this series). Also, new functionality is about to come out in preview to support Multidimensional SSAS Cubes as a data source for PowerBI - this would be your easiest way to leverage the Dynamics AX cubes to build Power BI reports. I hope this helps. Thanks, Clay.