Microsoft Dynamics AX 2012 and the importance of PowerPivot

As my continued series on Microsoft's next release of AX, Microsoft Dynamics AX 2012, I wanted to take and focus around Microsoft Office PowerPivot.



I first wrote about this topic back in May of 2009, which you can find here.: The Future of Microsoft BI: Project 'Gemini'

In that article, I attempted to explain the benefits and purpose of what then project Gemini, and now that we know it as PoverPivot, will have for Dynamics AX.

Today, with Microsoft Dynamics AX 2009 and the Office 2010 system PowerPivot is already being used in some forms or fashion, however as I pointed out in a previous AX 2012 post, through the use of OData feeds, it will have a much bigger role. Microsoft Dynamics AX 2012, OData and EDM

Moving beyond this, lets think about what PowerPivot means for a customer. The point of PowerPivot is to enable true self service BI within a company. To allow IT depts to control, through security, access to certain datasets, published feeds, etc. that can then be handed off to power users, who can consume and work with those feeds, published cubes, etc. to perform self service BI with that given data and it's context.

PowerPivot takes the need of having every little cube type, out of the hands of a OLAP developer, and places its ability to get at the same data within the power users that need to define the model and reporting results desired.

Looking at this from Microsoft Dynamics AX 2012 point of view, we will see PowerPivot users consuming AX data, in really three ways.:

• Through published Data Services from AX Query Objects as OData feeds


• Through consuming developed OLAP cubes, that are built from AX data.


• Finally through consuming SSRS Atom feeds. (Whats new in SQL Server 2008 R2 - Section: Rendering Reports to Data Feeds)

This is very powerful stuff, because all of these ways of exposing datasets that come from Dynamics AX 2012, all interact and get their respective data through the Application layer.

Very important to understand this point, and so it's very powerful indeed. What you will have then is a Developer, or Architect create a Cube from a perspective within AX, also maybe a couple of Query Object based OData feeds, as well as some AX SSRS reports. These can then be consumed by a PowerPivot user, with proper security context and rights to perform self service BI needs.

There is a great article on Microsoft TechNet about consuming SSRS 2008 R2 Atom Data Feeds for PowerPivot.

"With the release of SSRS 2008 R2, we now have a new rendering extension to render Atom data feeds from SSRS reports. You use this extension to generate Atom-compliant data feeds that are readable and exchangeable with applications that can consume data feeds generated from reports. For example, you can use the Atom rendering extension to generate data feeds that you can then use in the SQL Server 2008 R2 PowerPivot client."

Now will these replace the need for dashboards, No! In fact these will help enable more AX mashups, that mix and match technology, that enable secure access to company data, while enabling the users tools they need to get at and work with that data.

That's all for now, but check back soon as more to come!

Till next time!

"Visit the Dynamics AX Community Page today!"