Walkthrough: Analyzing Cube Data in Excel

Dynamics AX 2009

You can analyze data by using Microsoft Office Excel to connect to a Microsoft Dynamics AX OLAP cube.

To complete this walkthrough, you will need:

  • Microsoft Dynamics AX with sample data

  • The SalesAnalysis cube created in Walkthrough: Creating a Cube.

  • Microsoft SQL Server 2005

  • SQL Server Business Intelligence Development Studio

  • Analysis Services extensions for Microsoft Dynamics AX

  • Microsoft Office Excel 2007

To analyze the SalesAnalysis cube data through a pivot table you must import the cube data from Microsoft Dynamics AX into Microsoft Office Excel.

To analyze cube data in a pivot table

  1. Open Microsoft Office Excel.

  2. From the Data menu, click Get External Data, click From Other Sources, and then click From Analysis Services. Data Connection Wizard displays.

  3. On the Connect to Database Server page, enter the name of the server that contains the Analysis Services database for the cube created in the previous procedure, enter credentials used to access the server, and then click Next.

  4. On the Select Database and Table page, select the SalesAnalysis database, and then select the SalesAnalysis cube.

  5. On the Save Data Connection File and Finish page, enter Sales Analysis.odc as the file name, enter Sales Analysis as the friendly name, and then click Finish.

  6. In the Import Data dialog box, select PivotTable Report, specify the location for the report within the spreadsheet, and then click OK.

  7. In the Pivot Table Field List pane, mark the checkbox for Invoice amount located under the ∑ Customer invoice node. This adds the measure to the data region area in the pivot table.

  8. Under the Customers - Invoice account node, mark the checkbox for Customers - Invoice account.Customer groups - Customers. This adds the dimension to the row area in the pivot table.

  9. Under the Time node, select the checkbox for Year - Quarter - Month - Days.

  10. Browse the data in the pivot table. You can expand and collapse rows and columns in the pivot table.

Community Additions