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