Walkthrough: Creating a PowerPivot Data Mash-up
Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2
You can use Microsoft Dynamics AX 2012 R2 and Microsoft Office Excel with PowerPivot to combine data from multiple sources for analysis. This topic demonstrates how to create a data mash-up that combines information extracted from a list page in Microsoft Dynamics AX and an OData feed that surfaces data from a Microsoft Dynamics AX query. To create the mash-up, perform the following steps:
Extract data from a list page into Excel.
Extract data from Microsoft Dynamics AX into PowerPivot by using an OData feed.
Create a pivot table with data from both sources.
Microsoft Dynamics AX 2012 R2
Microsoft Office SharePoint Server 2010 or above (Enterprise edition) with PowerPivot services and Power View services
Microsoft Office Excel 2010 or above with PowerPivot add-in
For information about PowerPivot capabilities in Excel 2013, see What’s new in PowerPivot in Excel 2013.
Extracting Data from a List Page into Excel
The first set of data for the data mash-up is from the All customers list page in Microsoft Dynamics AX. You can add additional fields to the list page and then extract the data by using the Export to Excel button.
To add columns to the list page
Click Accounts receivable > Common > Customers > All customers.
Right-click the column header of the grid and then click Personalize.
Click Add fields.
Add the following fields from the Customers table:
Method of Payment
Customer classification group
Close the Add fields window and refresh the form.
To extract data from the list page
- After the All customers list page is refreshed, click Export to Excel List.
Importing Data into PowerPivot by Using an OData Feed
The other set of data for the mash-up is from an OData feed. You can create an OData feed that exposes information from an existing Microsoft Dynamics AX query or from a custom Microsoft Dynamics AX query. After the OData query is set up, you can import the data into PowerPivot. In the following procedures, you will create an OData feed to expose data from the CustTransList query, and then import the data into PowerPivot.
To create an OData feed
Click Organization administration > Setup > Document management > Document data sources.
Enter the following values:
Data source type
Data source name
Select the Activated check box.
Enter a description of the feed.
Close the Document data sources form. You can test the OData feed by navigating to the following URL:
You may need to adjust the port number in the URL. 8101 is the default port number.
To import content using the OData feed
In the same Excel workbook you used earlier in this walkthrough, click the PowerPivot tab.
Click PowerPivot window.
Click From Data Feeds.
In Data Feed Url, enter http://<ServerName>:8101/DynamicsAX/Services/ODataQueryservice/.
Select CustTransList, and then click Finish.
Creating a Pivot Table
Now that you have data from the list page and from the OData feed, you can link the data and then view it as a pivot table.
To create a linked table
In Excel, click the PowerPivot tab.
Click Create Linked Table.
Right-click the sheet title, and then click Rename. Enter Customers to rename the sheet from Table to Customers.
To create a pivot table
In PowerPivot, click PivotTable.
Click OK to create a pivot table in a new sheet.
Notice that the pivot table contains data from both sources of data: the Customers data you exported from the list page and the data from the CustTransList query that is exposed by the OData feed.
Expand CustTransList and then select CustTrans_AmountMST. The total sales value is displayed in the pivot table.
Expand Customers and then select Segment. The segment value is displayed in the pivot table.
Click Create on the warning that says Relationship may be needed. Click Close.
After you complete this walkthrough, you can upload a PowerPivot model (your Excel sheet) into SharePoint library, and use it to create Power View reports. For more information about creating Power View reports, see Create a report by using Power View to connect to a cube and Walkthrough: Creating an Analyze Data Button on a List Page.