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

 snap1452

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

 snap1453 snap1454

The Data Connection Wizard launches

 snap1455

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

 snap1473

For Log on credentials, select Use Windows Authentication

 snap1474

Click Next

 snap1475

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

  snap1481

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

Just click Finish

 snap1482

The wizard will close

An Import Data dialog will appear

click OK

 snap1483

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

 snap1484

On the right side, look at the PivotTable Field List

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

 snap1485

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

 snap1487

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

 snap1490

 

Under Bank transactions, check Master Amount - Bank

snap1492

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

 snap1493