AX 2012 R2 - Till Partition Do We Cubes Part
Well I hope everyone finds themselves doing well this fine Tuesday. There for sure is a lot of great things going on within the Microsoft Dynamics ecosystem. Spring is finally here for most of us, and everyone is well into this years projects. With this, I wanted to focus a bit on a new concept that came as part of Microsoft Dynamics AX 2012 R2. This concept is around partitioning within AX 2012 R2 and how that relates to the cubes that are built.
Before we get to far into this topic of 'Till Partition Do We Cubes Part', it's very important to understand the concept of the new partitioning that has taking place. To help with this, we can look at the following resources.:
- Data partitioning architecture [AX 2012]
- Partitions, Companies, and Data Isolation in Microsoft Dynamics AX [AX 2012]
Having the above resources, one can start to get an idea - at least technically - the what & how behind the concept of data partitioning in Dynamics AX 2012 R2. Now that we have at least a baseline level of knowledge in which to work from, lets move to the divorce seen in our tragedy.
Now sometimes in my writings I set a bit of flare to it, and this is no different. This tale is less of a tragedy really, but a concept that should be understood in how Microsoft see's Analysis Services working within the context of data isolation. You see, a lot of times it does not makes sense to have everything, including the kitchen sink, in a single cube. There are a lot of reason's, that separate, specifically design cubes would exist for a companies BI story. This happens to help address one of those, especially as you get into larger companies that need isolated data & security elements.
Here is where the value of such a separate plays into the crafting of said BI story for a company. Hence why we see in the examples that Microsoft provides for us. Keep in mind, that the cubes that come with Microsoft Dynamics AX are very generalized, and meant to act as a starting point for OLAP data. They are never meant to address a companies full, specific needs around analytic's.
So, in following with the image we see above partition key plays a role in the number of cube databases that an instance of Dynamics AX 2012 R2 is meant to have. The same cube structure however is found in both databases in the provided example. This implies that you can have similar analytic's that are rendered, but with security & data isolation needs addressed. Finally, if you so desired, you can easily create a cube structure that crossed partitions, or reporting elements that cross partitions. See the below, from so titled 'Partitions, Companies, and Data Isolation in Microsoft Dynamics AX [AX 2012.]'
"-- T-SQL to run in the Dynamics AX database directly in SQL Server: SELECT da1.Partition, pt2.PartitionKey, pt2.Name, da1.ID as [Company-ID] FROM DataArea as da1, Partitions as pt2 WHERE da1.Partition = pt2.RecId ORDER BY pt2.PartitionKey, da1.ID;"
Now do keep in mind what the following is prefaced with, in that: 'If you have sufficient authorization' This means to cross partitions and legal entities. Hence, this is not a technical issue, as much of a functional modeling, data & security isolation need. As you scale up and out with Dynamics AX, and specifically when it's only a spoke in the total solution, such needs exist. Further such possibilities exist, with Dynamics AX 2012 R2, out-of-the-box no other software required!
Well that is all for this post. There is a lot for us to continue to talk about, specifically and we continue down our path of further and further creating Systems of Engagement for Microsoft Dynamics AX customers. Til Next Time!