MVP Summit - A world of value in a weeks time

Well the MVP Summit 2013 has come and gone. This year marked the 20th anniversary of MVP's, and the first year that there was any tracks related to Microsoft Dynamics AX. I can't share details with you, being under NDA, however I can say this. Without-a-doubt, the level of involvement by the Microsoft Executives and Team members with MVP's was outstanding.



Above, pictured from left to right is: ME (Brandon George), Dick Wenning, Joris de Gruyter, Joel Leichty & Antonio Gilabert. These where the Dynamics AX MVPs that attended this year's summit. There where several, several meetings all adding value. This was not just for us MVP's either, but also for Microsoft as well with the feedback & perspective we as a group of MVP's where able to provide.

Another aspect to this summit was the involvement and sessions of the other MVP tracks, that helped share knowledge. This knowledge is very applicable to Dynamics AX, as it sits atop the Microsoft Stack. For those Dynamics AX MVP's that could not attend, make it a priority to do so next year. This is one event that you will want to attend without-a-doubt.

Well that's all for this week. I realize that I did not get a chance to post, but that is honestly a testament to how busy I was during this weeks events & sessions. There are some very, very good things to talk about in the coming week, specifically focused on true Business Intelligence. Till Next Time!

Follow Me @:
       

"Visit the Dynamics AX Community Page today!"

AX 2012 - PowerPivot Date Dimension Query

Today I wanted to spend time building on the topics that I've been covering of late. That is the value of PowerPivot and how Dynamics AX customers to create true Personal BI artifacts with it. A key topic that is important to understand when looking at creating such BI artifacts is around the use of a Date Dimension Table. Find out more about this topic, specific to PowerPivot from the following resource.: TechNet: Dates in PowerPivot



With this in mind there are several ways that this can be achieved. Specifically what we care about is relating some date value at a transaction level data, in order to slice & dice such data by. We could make use of fiscal calendar's, outside date table, or we can create one from a working calendar inside Dynamics AX 2012. The focus of this How-To is exactly around that approach. Making use of the working calendar concepts in Dynamics AX, to have a specific one that generates date values, and that we make use of for our PowerPivot Date Table.



What you see in the above image is the building out of a specific Work Calendar that I'm targeting for use as my Date Table within PowerPivot. After having that built, the next focus is around the project that will contain the elements that help complete this scope. That is what you see with the following image.



Looking at the project, you can see there we have a table, query & job elements. These are the Dynamics AX parts that help us create and expose a Date Table for use with our PowerPivot artifacts. Like most scope, we need to start with a targeted table that will house our date data. That is what we can see in the following image.



From this point, we will then need a Query that will become the basis for an OData feed. This query element, simply exposes the new custom table and it's fields. That is shown in the following image.



After having our custom table targeted, next we can move towards creating X++ code that will fill this table. This X++ code, in this current write up, it housed and executed from within a Job element. This could easily be adapted to have parameters and execute as a batch job, or fired from a menu item within an instance of Dynamics AX. This is the tie between the working calendar & date data we created, and the Query element that will expose the data it generates.

With that, lets look at the X++ code. We have two sections of this job really. The first section is pictured below, and it shows the declaration section of the job variables, as well as a local method. For those that don't understand local methods for Dynamics AX, please review the following resource.: MSDN: Local Functions. Even though that is stated for Dynamics AX 2009, this still is a valid concept for AX 2012.



What we see in the local method, or function, is the creation reading of a date value and based on that date value, we are returning the calendar year quarter that the date belongs to. Having this, we can now move to the second half of the job. That is seen in the following image.



The above, we see the majority of the business logic behind the creation of our target date table data. This job will clear the custom table, and then proceed to fill it with information based on the working calendar data we created. You can see several calls that will give us specific information like: DayName, Week, MonthName as well as our local function Qtr().

Having complete this, and executing the job the majority of what we need to do within Dynamics AX is completed. Since we are targeting this for use as an OData feed, the next step for us is to target the query element within the project and publish it as a Document Data Source. That is what we see highlighted in the below screen shot.



Once we have done this, we are now able to launch into a PowerPivot design space to add the new query as a date table for our Personal BI needs. As we can see in the following image, our new query appears as a valid OData feed.



I'm adding this as a new data source to an existing PowerPivot artifact that is focused on looking as Sales Data. After adding the new data source to the PowerPivot design space, we can see the two query elements from the AOT, as shown below.



Next step is to join our two query elements within the design space of PowerPivot. What we are doing here is stating that the sunSalesLineData query is the fact table, and the sunDateTimeTable query is a dimension table, linked to the fact table. We are linking these two tables via the sunSalesLineData.SalesLine_ConfirmedDlv = sunDateTimeTable.sunDateDimTable_TransDate. Creating this relation allows us to make use of the fields within the targeted Date Table, to slice & dice our sales data with. We can see this relation in the following image.



Now that we have our relationship established and our PowerPivot workbook updated, lets take a look at the current Month value. I derived the "month" value used in the current workbook by making use of a DAX expression within PowerPivot. It's a computed column, with the following expression.: '=MONTH([SalesLine_ConfirmedDlv])' This gives us a numerical value that represents the month from the SalesLine_ConfirmedDlv date value. You can see this as a horizontal slicer in the following screen shot.



This might be fine for some reports, but the goal of a Date Table within PowerPivot is to add more flexibility and friendliness to the date values which we filter and slice by. Now that we have the date table we can target as dimensions for slicing our facts from the sales data with, we can replace the integer month value, with friendly month names. We can see that with the following image.



Because we built out our date table within Dynamics AX and expose it as a Query element, we have flexibility for control of the dates. As well as we could make use of targeted languages, labels and more. As you can see in the following image, we have the ability to make use of any of the fields now, to slice and filter by.







With the above we have a powerful, yet simple example of enabling a date table built from Dynamics AX. Further, by example, how with little effort such scope can start to add value and use for our personal BI development needs. Further I will add, we could then make the choice to expose any number of fiscal or working calendar data in such fashion. Building from the above knowledge, you can see where this Date query can be added as part of the design as a dimension table for many, many PowerPivot artifacts.

There are plenty of detail specifics that could be considered in the design of this, but I hope that you see the value and ease we have with empowering the creation of useful and value added BI artifacts with Dynamics AX & PowrPivot. That is all I have for this post. I will be back next week to continue focusing on the helping put your companies data to work for you! If your interested in what else can be done with BI Semantic Model as well as BI in general, make sure to contact my team at Sunrise, to see how we can help you today! Sunrise Technologies Site. Till Next Time!

P.S.: I will be at the MVP Summit all next week at Microsoft HQ. I do plan however to get at least two post up next week. Make sure to check back soon and often!

Follow Me @:
       

"Visit the Dynamics AX Community Page today!"

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