Back to Blog Index

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 bSSRS Reporte 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 Power BI get dataalso 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.

Power BI get data

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 1SQL Server Database Power BI

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.Power BI connection screen

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.Power BI get data

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.

Power BI clean dashboard

 

Let’s drag N drop this field to the worksheet and select a ‘slicer’ visualization type for it.

Power BI calculated column

 

Let’s enhance our dashboard with several more visuals.

Power BI slicer visualization

 

 

 

 

 

Written by: Viacheslav Nefedov • Developer • Arbela Technologies