Dynamics AX 2009: Ad hoc Analysis using Excel 2007 to get data from an AX OLAP Cube

a lot of time in Dynamics AX 2009 integrating AX with Microsoft’s BI Stack. One of the benefits of this work is that you can use familiar tools to access BI data in AX.

In this post, I’ll demonstrate how to use Excel 2007 to connect to an AX Cube. Keep in mind that there’s nothing special about these steps with regard to AX, these are the same steps one would use to connect to any AX Cube.

This will be a simple “Hello World” example – the simplest one available to demonstrate the things are working.


First, launch Microsoft Excel 2007

  snap1450 snap1451

Navigate to the Data tab in the Ribbon


Click on From Other Sources in the ribbon and select From Analysis Services

 snap1453 snap1454

The Data Connection Wizard launches


For Server name, enter the name of the machine where the cube is on (in this example “isotopex”)


For Log on credentials, select Use Windows Authentication


Click Next


Now you’ll be at the Select Database and Table page of the wizard

Under Select the database that contains the data you want, select “Dynamics AX”

Select the General Ledger cube

Click Next


Now you’ll be at the Save Data Connection File and Finish page of the wizard.

Just click Finish


The wizard will close

An Import Data dialog will appear

click OK


Now you’ll see a block PivotTable attached to the “General Ledger Cube”


On the right side, look at the PivotTable Field List

Under Bank account descriptions, check By Bank Account ID – Name


Once you check it you’ll see the bank accounts on the left.


Expand some of the bank accounts, you’ll see there is a hierarchy



Under Bank transactions, check Master Amount - Bank


And now you’ll see the data sliced by bank account


Comments (0)

Skip to main content