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.
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.'
- 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.
- 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!