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

转载：http://blogs.msdn.com/saveenr/archive/2008/11/02/dynamics-ax-2009-ad-hoc-analysis-using-excel-2007-to-get-data-from-an-ax-olap-cube.aspx

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

 

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

 

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