The Cube behind the Power View - Part I - Perspective

Recently I started a series on making the most use of Power View for Microsoft Dynamics AX 2012 R2. This is a great new tool that helps further complete the personal BI story for Dynamics AX customers. It's enabled within a framework delivered as part of the current version from Microsoft, and part of that framework is the consumption of SQL Server Analysis Services Cubes.



Before we get into the 'cube behind the Power View', lets recap with the following resources in how we arrived here. The following is the list of post that should be read, before reading this post.

• AX 2012 R2 and PowerView - Part I (Understanding)
• AX 2012 R2 and PowerView - Part II Extending

With the above list, and what was covered in mind, now we can look into the cube that powered this concept. The whole point that I was trying to get across with the above to post is that it's easy to extend the Microsoft Dynamics AX 2012 R2 Power View Framework. Further to this point, not only can you extend from the base cubes that exists within Dynamics AX, but also any cube database you create and cube contained within.



The above image is the final representation of a cube created from a perspective with the AOT. Unlike perspectives within cubes themselves, these are elements within the AOT that allow the modeling of a SQL Server Analysis Services Cube. We can see in the following image, that is the starting point for our custom cube.



Perspectives are made up of Table & View elements from within the AOT as well. You must think in terms of facts that you want to measure, and dimensions to slice said measures via. This is the point of tables, and views to provide such facts and dimensions & attributes. We can see in the following image, the tables & views that make up our targeted perspective. It happens to be the InventTable, as well as, the SalesLineCube & CustTableCube view elements.



After having set our perspective targets from the tables & views we can make use of, our next focus is around the properties of each. What we see in the following series of images, is the properties of the InventTable, SalesLineCube & CustTableCube as they sit within the perspective. What we make a choice for each, is how we are modeling our to-be cube outcome.


InventTable Element Properties.




SalesLineCube Element Properties.




CustTableCube Element Properties.

You will note that the SalesLineCube provides both measures as well as dimensions. Further it is the targeted transaction table, in which the cube will build measures from. This is why we have this property marked as such. Further you will note that the other two elements are left as auto and only have dimension label established for them. This then allows Dynamics AX to understand we want each of these elements to be dimension elements against our "fact table" represented in the SalesLineCube element.

Moving along from this point, our next focus is to pick which elements we want to become measures, and then finally what we want as dimension attributes. Keep in mind, that we will make use of the SalesLineCube view as both a fact table containing measures as well as dimension attribute values to slice via. Lets start with the measures on the SalesLineCube. I'm going to show the following image, that is an example of each what this should look like. Then have a list of each measure we care about to finalize our needs.



What we see in the above image is the targeted SalesQty field, from the SalesLineCube view element in the perspective. We see the properties for said field, in how we label it, type of being a measure, and further the default total type of sum. Having this, now we can use the following list to create our final measures.:

• SalesQty - 'Sales Qty' - Measure - Sum
• SalesPrice - 'Sales Price' - Measure - Sum
• SalesStatus - 'Order Status' -Attribute - (blank)
• ShippingDateConfirmed - 'Ship Date' - Attribute - (blank)
• CreatedDateTime1 - 'Created Date' - Attribute - (blank)

Having the above established for the SalesLineCube element in the perspective, next we can do similar setup for both the InventTable, as well as the CustTableCube. Keep in mind, that the InventTable, when modeled as a cube will be 'Product Information'. Further CustTableCube, will show up as 'Customer Information.'
InventTable

• ItemId - 'Item Id' - Attribute
• ABCRevenue - 'ABC Rev' - Attribute

Moving forward still we will need to complete our dimensions from the CustTableCube view element. Doing this is very similar to what we just did, selecting fields that are meaningful in which our dimension attributes should target. These will be used, to slice & dice our measures with. The following is the list for the 'Customer Information' dimension set.
CustTableCube

• AccountNum - 'Account Id' - Attribute
• GroupName - 'Group' - Attribute
• Name - 'Customer Name' - Attribute
• CountryRegionId - 'Country' - Attribute

Now having the above set for the perspective, we have now modeled what we want our new 'Sales Data' cube to be made of. Having these specific measures, as well as dimension attributes specified will allow Dynamics AX 2012 R2 to build out our cube as desired. That is where this part I of 'The Cube behind the Power View' ends. We will pick this back up in my next post, for proceeding with creating a new Analysis Service project, deploying, processing and finally consuming. Til 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!"

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

Happy New Year - The Force is Strong with Dynamics AX

Happy New Year Everyone! Wow, what a year 2012 was and already it's come and gone. It was a year filled with all kinds of important releases for the Microsoft Dynamics Ecosystem, including closing out the year with the release of Microsoft Dynamics AX 2012 R2!



Now the above is just great, Hal Howard, Fred Studer & Jedi Master Mike Ehrenberg. "The Force is strong with Microsoft Dynamics AX".

I thought having this highlight as the kick off post for 2013 would be just perfect. It's time to close out the old, complex ways of the dark side. It's time to focus on powerfully simple software - It's time for Microsoft Dynamics AX!

Check back here, as I continue my specific focuses around Dynamics AX, including a heavy focus on true BI, and more ways to battle the forces of evil! Happy New Year --- and Till Next Time!
Follow Me @:
       

"Visit the Dynamics AX Community Page today!"