Analyzing Dynamics AX Sales Data with Power BI in 10 minutes

Views (1440)

Community Member

Like(0)

Report

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 a 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 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.