AX 2012 - From PowerPivot to Tabular Model

Today I wanted to continue the focus around some of the great options that now come as part of the stack offering for Microsoft Dynamics AX customers. This builds on the BI Semantic Model, going from the perspective of personal BI through to needed scale offered via organizational BI concepts.



What I'm speaking about specifically to you today, is the ability to target a PowerPivot BI Artifact and use it as the basis for creating a scalable, tabular model for SQL Server 2012. This knowledge, builds on that which I've covered in depth for Personal BI. This recently includes my last post Dynamics AX 2012 R2 - OData Improvements. Further, this is a prime example that I explained at a high level, with the post about Understanding the natural flow of the BI Semantic Model.



What we see above is the final outcome of a focus around PowerPivot, OData and a Dynamics AX Query Element. This query element brings together Sales data, for a specific set of customer groups. You can see in the following screen shot, the targeted query element from the AOT.



What would prompt you to take this PowerPivot artifact and move it along from the Personal, Team and into the Organizational BI area is the need to scale. That is the focus when we talk about organizational BI artifacts. These are BI needs that need to scale beyond the capability of what Personal & Team BI aspects can offer us. Having created this personal BI artifact in PowerPivot, we do not have to start from scratch. In fact, we can actually use the work completed in the design space of the PowerPivot workbook, to become the basis of an Analysis Service Tabular Model. This starts with SQL Server Data Tools, the replacement of BIDS for SQL Server 2012.



Having launched SSDT, we can navigate to creating a new project type of Analysis Services > Import from PowerPivot. This is what we see in the below screen shot.



In doing this action, we are then taking to a screen in which we must set a target work space database instance. For the sake of time, I'm doing all of this on an AX 2012 R2 demo image that you can download from Microsoft PartnerSource. This is why, as seen in the screen shot below, I'm targeting a localhost\Tabular instance of SSAS.



Having set our target work space server, we can then move forward with selecting a PowerPivot workbook. At this point there are some very specific security design considerations you need to keep in mind. Specifically when the target is a remote server vs. a local server. To find out more about this topic, please review the following resource.: Impersonation (SSAS Tabular). The goal of this article is to show off the concepts, in which such details will be discussed at length, in a later post.



What we see in the above screen shot, is the selection of our PowerPivot personal BI artifact that we created in earlier post. Having this selected, our tabular workspace server is able to read in the meta-data as well as data contained within the workbook. We can see this reflected in the following image.



Now that we have our tabular model created from the effort put forth in the creation of the personal BI artifact, we can begin modifying and creating needed elements to complete our scaled out BI needs. What important to keep in mind, is that any computing measures that where created in the PowerPivot artifact, are now in our tabular model. Further, the connection via an OData feed to our Query Element is in tact. You can see this from the screen shot below.



Having this understood, and not worrying about security design concerns at this point, we can proceed with deploying our tabular model to our workspace server for Analysis Services. That is what we see, in the outcome below.



With the above example, we can see how quickly the BI Semantic Model can easily take the efforts of a Personal BI design and turn that into a truly scalable state as a tabular model. This does require SQL Server 2012 as well as Dynamics AX 2012. With this however, we can now process our OData feed from within the Tabular model, and work off it for scalable artifacts that can help complete a companies BI story. Further this takes advantage of Dynamics AX, specifically with the use of Query Elements & Document Data Sources.

That's all for this post, but check back soon as I continue to dive further into taking full advantage of Dynamics AX, in helping put your companies data to work for you! I will continue to build on the concepts that I have been writing about, plus more including coverage of the upcoming Convergence 2013! Till Next Time!
Follow Me @:
       

"Visit the Dynamics AX Community Page today!"