Analyzing Dynamics AX sales data with Power BI in 10 minutes
Power BI is a lightweight and simple BI application that can be used to analyze data from many kinds of databases — it has been issued in two editions – professional and free.
The main features of it are the number of supported data sources and the ease of the data mashup process. Calculated columns and calculated measures can be used to create different KPI’s and a lot of visualization options provide the ability to see the result on graphs, charts, and maps.
Below I’ll show how to use it the simplest way, connecting to AX database directly, and how to spend only 10 minutes to create a sales dashboard. If you need a more mature solution with prebuilt data warehouse, robust ETL processes with change tracking and incremental update, prebuilt reports and so on – please contact Arbela and we can provide you with a ready to use solution.
To create a Power BI dashboard we’ll need the direct access to an AX 2012 SQL Database from your workplace and Power BI Desktop application.
Currently, there are two ways to use Power BI – from the web, using Power BI service and from a desktop using Power BI desktop application. This is also an option to create a dashboard with Power BI desktop and to upload it then to a Power BI service. Go to www.powerbi.com to download the latest version of Power BI desktop for free. Scroll down the page to find the download link at the very bottom of the page. No registration is needed – just download and install the application.
Start the application and you’ll see the welcome screen with links to documentation and videos; along with a ‘Get data’ link that we use to connect our Power BI file to Dynamics AX data. Click the link:
Now you can see that even the free version of Power BI desktop supports a number of data source types where we need the SQL server option.
Now you can connect to a database and select certain tables or views in it, or query the database with a custom select statement. I’ll use a simple select statement here to get the information from CustInvoiceTrans table that holds customer invoices data.
SELECT
CUSTINVOICETRANS.InventQty,
CUSTINVOICETRANS.LineAmountMST,
CUSTINVOICETRANS.ItemId,
CUSTINVOICETRANS.NAME as ItemName,
CUSTINVOICETRANS.InvoiceId,
CUSTINVOICETRANS.InvoiceDate,
CUSTOMER.State,
CUSTOMER.CustName
FROM CUSTINVOICETRANS
JOIN CUSTINVOICEJOUR ON
CUSTINVOICEJOUR.partition = CUSTINVOICETRANS.partition AND
CUSTINVOICEJOUR.DataAreaId = CUSTINVOICETRANS.DataAreaId AND
CUSTINVOICEJOUR.InvoiceId = CUSTINVOICETRANS.InvoiceId AND
CUSTINVOICEJOUR.InvoiceDate = CUSTINVOICETRANS.InvoiceDate AND
CUSTINVOICEJOUR.DATAAREAID = ‘USMF’
JOIN CUSTTABLE ON
CUSTTABLE.partition = CUSTINVOICEJOUR.partition AND
CUSTTABLE.DataAreaId = CUSTINVOICEJOUR.DataAreaId AND
CUSTTABLE.AccountNum = CUSTINVOICEJOUR.OrderAccount
OUTER APPLY
(
SELECT TOP 1
LOGISTICSPOSTALADDRESS.STATE,
DIRPARTYTABLE.NAME AS CUSTNAME
FROM
DIRPARTYTABLE,
DIRPARTYLOCATION,
LOGISTICSPOSTALADDRESS
WHERE
CUSTTABLE.PARTY = DIRPARTYTABLE.RECID AND
DIRPARTYTABLE.RECID = DIRPARTYLOCATION.PARTY AND
DIRPARTYLOCATION.LOCATION = LOGISTICSPOSTALADDRESS.LOCATION
) CUSTOMER
Here you can see how the connection screen looks in Power BI.
You’ll be prompted for login credentials and connection type – ‘direct query’ or ‘import’. Import mode means that all the data will be loaded into your computer operating memory whether Direct query means that your application will query database ad hoc only for the data needed to display currently selected visuals (graphs and charts) and this mode is somewhat limited in possible calculation functions — as a result, choose the import mode.
Now you can see the empty worksheet with a list of imported fields and you can drag N drop them to create a fine looking dashboard.
I’ll create a new calculated column to show the year by right clicking the query name (query1) on the right of the page and selecting ‘New column’ from a context menu. The formula will be pretty easy.
Let’s drag N drop this field to the worksheet and select a ‘slicer’ visualization type for it.
Let’s enhance our dashboard with several more visuals.