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!"

Dynamics AX 2012 R2 - OData Improvements

I hope everyone has had a productive Dynamics filled week! Today I wanted to spend time, talking about a topic that I've covered a good bit in the past 12 months. Specifically, that is the improvements that have came with the release of Dynamics AX 2012 R2 around the topic of OData.



If your just now reading about this topic for the first time, OData is an underlying technology protocol that helps enable Personal BI concepts for use with Microsoft Dynamics AX. With the release of Dynamics AX 2012 R2, we do have some improvements to speak towards in this area. Specifically these improvements revolve around Document Data Sources. You can find this, under Organization Administration > Setup > Document Management > Document Data Sources.



As seen in the above screen shot, the document data sources form has changed a bit with this new release. Specifically what is pointed out, is the option for creating custom query elements. Also, the edit button, which happens to be disabled until after selecting and registering a query for target of custom filtering.

That is actually the biggest improvement with this release, in regard to the topic of OData. Someone that is an Administrator, not necessarily a developer, can filter queries beyond what they have in their specific designs.



What we see in the image above is the screen that pops up after selecting a custom query option, and having a target query element to create customer filtering options for. We are able, similar to the query options on most grids and forms, select and give specific values for query execution.



Having this set, we are then able to see the Edit Query button enabled for such custom query elements within the Document Data Sources form. Further, once we have our query filtering set, we can rename such elements in the form, that better reflect the nature of the filter being applied. This can be seen in the following image.



Having this, we can now consume the ODataQueryService that comes as part of every AOS for creating Personal BI artifacts through tools like PowerPivot & PowerView. That is what we see in the following image, our valid OData query elements within the design space of PowerPivot.



Now I will point out, the majority of the design requirements for query elements to be valid OData feeds has not changed. You can find out more specific information on that topic, from the following CodePlex Open Source project.: AX-OData Project.

Well that's all for this week. I plan on getting back into a more 2-3 post routine, per week very soon. There is a lot going on in the Dynamics space, and specifically with my new Role as Director of Business Intelligence for Sunrise Technologies, Inc. I will be posting a lot about Dynamics AX and BI in the coming weeks and months, further launching something brand new with MyDynamicsBI.com More on that soon. Make sure to check back next week! Till Next Time!

Follow Me @:
       

"Visit the Dynamics AX Community Page today!"

AX 2012 Personal BI - By Example - Customer Aging II

Yesterday, we started a series together on using PowerPivot with Microsoft Dynamics AX 2012. In the post, AX 2012 Personal BI - By Example - Customer Aging I, we went over a custom query named "CustomerAging" that we were able to connect to via OData feeds from AX 2012. In doing so we left off, with filling our PowerPivot window, with data from AX 2012.



With this we now have the ability to create a new PivotTable within our Excel 2010 workbook and use our PowerPivot data set to power the Pivot Table for reporting. We do this, by closing our PowerPivot window, and then clicking on the new PivotTable button from the PowerPivot Ribbon, as shown below.



Doing this, we should see a screen similar to the following screen shot, that has our field selections for our PivotTable to the right, as well as the working PivotTable area, within our workbook to the left.



Now that we have our PivotTable options to start working with, lets look at some data that we have, which we imported during the first part of this series. Since this is a customer aging report, we should start with the values section. This means we will want to get the Open Balance value. If I just highlight and click the 'CustAgingLine_1_OpenBalanceMst', then you will see that this automatically sums this value for me, and places it within the Pivot Table, values area as shown below.



Having this, and looking at the value, does not look to pretty really. Lets do a little face lift work on the value label, and change that. We can do this by right clicking on the Sum Value field, and the left clicking on Edit Meassure.



Doing this, you should see a form similar to the below. This is where you can set the value for the name of the Meassure as well as other options. For now, we just care about changing the name. I'm going to change it to Open Balance.



Doing this, gives us a nicely formated name for our Value that we care to report on. This could also be changed in the PowerPivot window, but for now we will work within the PivotTable for such changes.

Moving forward from here, we need some rows, and columns to report on. So first, lets get some rows to report on. Since, again, this is a Customer Aging report, let us focus on the customer account & name values. This is made up of 'CustTable_1_AccountNum' and 'DirOrganization_1_Name' from our field list. Lets place these two fields, by draging and dropping them into the row columns as seen in the below image.



Now if we look to our Excel workbook, we can see that the PivotTable itself has been updated to reflect our new Row Label fields. In looking at this, and like the change we made for Open Balance, this is just not quite what we are looking for. In order to help make this report look a little better, lets create a computed column in our PowerPivot Window, and call it Customer. We do this by going back into the PowerPivot Window, and then scrolling to the far right and click to add a column.



Now that we have our new column we need to add Dynamic Analysis eXpression (DAX) formula that will concantenate our Account Number & Customer Name, into one field for us. We can achieve doing that with the following such formula.

"=CONCATENATE(CustomerAging[CustTable_1_AccountNum],CONCATENATE("-",CustomerAging[DirOrganization_1_Name]))"

Doing this, allows us and saving we should then see our new computed column, that combined "AccountNum - Name" in our new column called customer, as seen in the following screen shot.



Now that we have our new computed column, lets close our PowerPivot window. We should then be able to remove the current row labels, scroll to the bottom of our field list, and see our new Customer field. Lets drag and drop that now into our Row Labels as seen below.



Now that we have our new computed column place, we should see this reflected in the the workbook where our PivotTable is being built out. You should see something similar to the screen shot we have listed below here.



Now we are off to a really good start, with our personal BI journey that we are taking with PowerPivot and AX 2012. It's worhty to note that, in accessing data through the application layer of AX 2012 through the use of OData feeds, representing query elements we are able to inherit all the great security and features of Dynamics AX.

Well that's all for this evenings post. I hope you all have a wonderful night, and chekc back soon as we continue our journey on personal BI tomorrow. We will continue with adding Column labels, as well as Vertical & Horitizontal Slicers! Till Then!
Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

AX 2012 - Excel Add-in Revisited

With the release of Microsoft Dynamics AX 2012, came some great new ways of achieving business requirements, and user experiences. One of those is the Excel Add-in for Microsoft Dynamics AX 2012. I first wrote about this topic, back in June, in which you can find here: Microsoft Dynamics AX 2012 Excel Add-in. This has been a hot post actually, with a lot of comments, and a lot of visits.

It's rightfully so that it should be, being that it's new, and there is some confusion on how to do some of the more complex data sets, within AX, that go beyond just a simple table, which that first post covered for us. With that in mind, I thought I would help clear the air a little bit, in how the Excel Add-in is actually very flexible, and something to watch out for in December from Microsoft on this topic.

First, lets look at the starting point, in which we can start to work with more advanced data sets in AX. This concept, is enabled through the use of document services. This is the way, in which complex data sets in AX are exposed internally and externally of AX 2012. This is true, even for the Excel Add-in.

From the excel point of view, what we are talking about is the Add Data ribbon button, and when the drop down appears clicking on the Add Data, instead of Add Tables.



When clicking on this, for the first time, if you've not setup any Document Data Sources within AX 2012, your selection options will be blank. Now we have talked a little bit about this new concept of Document Data Sources, in the following post.: AX 2012, PowerPivot and a Dash of OData Feeds

In that above post, I covered with you how to enable Excel 2010 PowerPivot to work with OData Feeds from AX 2012. This was enabled through having Query Type of Document Data Sources enabled through the Organization Administration module, Setup, Document Management and finally Document Data Sources.



Here we can also add a document data source, of type service. These services must be document services and extend from the AIF Document Services types. These can not be custom services that do not extend as part of the AIF document services.

With that in mind, we can add a couple of document services, to our list, from within that form, as seen below.



Now that we have these services as document data sources within AX 2012, we can now go back to Excel 2010, within the Excel Add-in and click on our add data, and see our two new services we can make use of.



With this, now we can work with more complex data sets within AX 2012. This also means, that Office 2010, very much is a major part of the user experience in regard to AX 2012. This also means, that some very complex data set needs, can be accomplished through the use of Query Objects, and Document Services that wrap those query objects to expose them internally and externally of AX 2012.

With this said, there are some issues, and area's to watch out for. Basically, there are some super normalized data sets, like customers, vendors, etc. To help address those needs, Microsoft will be releasing in December a resource kit, that will contain artifacts that help enable the use of the Excel Add-in, for the master data, that has some super normalization aspects to it, that make it a little harder to work with from Excel. The basic concept will still be used that you see above here, that will be enabled with staging tables that the process will still use document services to enable.

I hope this helps further explain the value of the Microsoft Dynamics AX 2012 Excel Add-in and how it can be used right now with even more complex data sets, than just simple tables. You enable this through the use of Document Services. Also, it's very important to understand that Microsoft will be releasing that resource kit to help out with some of the super normalized master data elements. Finally, you can create your own document services, that wrap your own custom data sets, or query objects, that in turn can be consumed and made use of in a bi-directional nature through Excel. This is a huge benefit to customers of AX 2012, and something that needs to be considered when thinking about designing and developing scope for customer.

That's all for now, check back soon as more to come. Till next time!

"Visit the Dynamics AX Community Page today!"