Creating a PowerPivot data source for Power View

You can create Power View reports directly off the data within Dynamics AX by building your own personal data sources through Excel and saving them to a PowerPivot gallery in SharePoint. This gives you the flexibility of accessing data that may not be in the existing Power View cubes, including custom tables and fields that you have added, without having to become a DBA and tweak the reporting cubes themselves.

In the following recipe we will work through a simple example of how you can use the PowerPivot add-on for Excel to access inventory information from Dynamics AX, and then save it to a PowerPivot gallery in SharePoint so that we can report off it.

Getting ready

Before you are able to create a PowerPivot data source, you need to make sure that you have the PowerPivot add-on configured in Excel. If you have it installed, then when you open up Excel, there should be a PowerPivot tab in the ribbon bar:

If you don't have it installed, then follow...