Analyze Cube Data with Excel

In some cases you might want to view cube data without having to create either a KPI or a report to display on a Role Center. You can quickly analyze data by using Microsoft Office Excel to connect to a Microsoft Dynamics AX analysis cube. For example, you might want to view the current balance of several different general ledger accounts.

To analyze the General Ledger cube data through a pivot table, you must import
the cube data from your Analysis Services database into Microsoft Office Excel.

Follow these steps to analyze cube data in a Microsoft Office Excel pivot table:

1. Open Microsoft Office Excel. Click Data > From Other Sources > From Analysis Services. The Data Connection Wizard opens.

2. On the Connect to Database Server page, enter "localhost" for the server and use Windows Authentication. Then click Next.

3. On the Select Database and Table page, select the Dynamics AX database, select the General ledger cube, and then click Next.

4. On the Save Data Connection File and Finish page, enter a file name and friendly name, and then click Finish.

NOTE: After you have set up a data connection to a cube, that connection can be reused to connect to the cube in the future.

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

6. In the Pivot Table Field List pane, select General ledger amount - accounting currency which is located under the Ledger transactions node. This adds the measure to the data region area in the pivot table.

7. Select Account type and Number located under the Chart of accounts node.

NOTE: The available dimensions will vary depending on your Analysis Services project.

8. Browse the data in the pivot table. Use the column filter to select specific accounts to view. 

  

Regards,
Hossein Karimi