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
Navigate to the Data tab in the Ribbon
Click on From Other Sources in the ribbon and select From Analysis Services
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
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
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
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