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!
Follow Me @:
       

"Visit the Dynamics AX Community Page today!"

From Power View to Cube and back again - A Recap Friday

Well what have we here now? Another Friday has settled upon us. Man what busy week this has been, filled with all kinds of great things. I wanted to take the time, and just post a recap of sorts from this & last weeks post. The goal and focus of these past weeks have been around truly using the BI Semantic Model within the context of Dynamics AX 2012 R2.



Without a doubt for each of the topics covered, there is plenty of room for extended reach & depth of knowledge. We can easily spend hours, upon hours within Power View design concepts and more. Further, we could opine and expound on the many depths that exist within the realm of SSAS cubes. Topics as deep as Tuples, and the underlying structures that contain our aggregated measures & dimension attributes. However, while there might be some value for certain people in such a deep journey of understanding for each of the fore mentioned topics, the point would be lost of what the goal really is.



That goal my friends is the enablement of quick consumption of data assets. This is the real goal of any business intelligence story that is to be crafted for a company. Owning the data, and turning that data into a real asset that goes to work for companies. With this goal in mind then, the following is a recap of the recent post, with the goal of quickly getting you up & running with Power View. Further, showing you how *any* cube can be the target for such personal BI artifacts.

• AX 2012 R2 and PowerView - Part I
• AX 2012 R2 and PowerView - Part II Extending
• The Cube behind the Power View - Part I - Perspective
• The Cube behind the Power View - Part II - Create, Deploy & Process

Now having each of these reviewed, you should see how simple it really is to empower yourself and your company or clients to truly start embracing the full BI Semantic Model. When Microsoft Dynamics AX is your system of record, and acting as your main data mart, then you have a great BI offering built right into the product. This is what we refer to as Tier 1 BI for Dynamics AX customers. Meaning that nothing but the stack of Office+SharePoint+SQL Server (OSS) is used to craft a BI story for a company. It's very feasible, and does not take a deep understanding of the empowering underlying technologies to get started.

Well that's all I wanted to gab about today with you. The end result is less focus around what tool really, and more about the data. To much focus at times is placed in the ecosystem currently around specific tools, and myths of 'removing IT' from the equation. The goal is putting your data to work for you, and then the tool comes into focus for the specific BI artifacts that are needed to get the job done.



I would like to add an interesting note here. This is actually my first full blog post, from my new Surface Pro 128 GB version. I'm typing this from the 'real keyboard' cover type as well as having the use of the Surface mouse and stylus for completion of task. I must say I'm very impressed and really looking to push this to the limits. Til Next Time!
Follow Me @:
       

"Visit the Dynamics AX Community Page today!"

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 R2 and PowerView - Part II Extending

What a great day to be alive! I know for a lot of people, including myself, today has special meaning. It's about faith, and remembering that we are finite and need our Creators grace & mercy. So I must first hope that everyone has a blessed good Friday, and you find yourselves doing well. Further, today I wanted to continue the series I started yesterday around Microsoft Dynamics AX 2012 R2 & PowerView.

For those that are just reading this post, you can go to Part I of this series located here.: AX 2012 R2 and PowerView - Part I. Having the knowledge gained from that post, we can now continue with extending PowerView for AX 2012 R2 with our own custom cube!.



We start this journey by focusing on where the PowerView data connections and artifacts are stored within Enterprise Portal. You can see this location in the above screen shot from the demo image I'm using as my target. This is located in.: 'http://[site]/sites/DynamicsAX/Power View Reports/'.



Having the location open, we can then target creating a new data source that we will want PowerView to consume. As we can see in the above image, we target a new data source by simply adding one to the document library. Further what's important to not, as seen in the below image, is the connection type and the connection string.



You will not that we have a connection type that is very specific to enabling PowerView artifact creation. This type is called.: Microsoft BI Semantic Model for Power View. Further the connection string is very important as well. This happens to be pointing to a custom SSAS Cube Database and further a custom SSAS cube.



These where both created from the BI Project wizard within Dynamics AX 2012 R2, from a perspective representing the Measures & Dimensions desired. The following is an example connection string that is valid, that you can easily adjust for your own needs.

Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Data Source=localhost;Initial Catalog=sunSalesAnalysis initial;Cube=Sales Analysis

Now having this in place, we can test our connection and save the new data source for PowerView usage. This is the default library where the PowerView managed code looks for data sources to make use of. Going back to the information in the first post on this topic, that means that when an action menu item passing in a data source name as the parameter, it is in reference to the data sources living in this specific document library on Enterprise Portal.



Having done a successful save of the data source, you should see something very similar to the above screen shot. This means that we are ready to move back into Dynamics AX 2012 R2 for completing our extending of PowerView. Like all things in Dynamics AX, this extension should remain within an AOT project.



What we see in the above screen shot is that project, including having highlighted the custom action menu item that will launch our PowerView design process. You will notice that I'm targeting the name given to the data source that lives in the document library on EP. That is the parameter value that will launch PowerView to target the specific custom cube.



Having created the menu item, and launching it, we see in the above image a field list from the custom cube I created from the above mentioned perspective.



This literally took under two hours to complete, from cube to launching into the design space. To further bring home the point that this is a separate cube database, and cube, please reference the below image.



You can see that I have a new cube catalog, or database as well as a custom cube called Sales Analysis. Further you can see in the above image the dimension structure in which we can slice our measure via. Again, this was 100% generated from a perspective within the AOT. Having completed this, we can now create compelling visualizations with PowerView based on the custom cube.



Further having completing such visualizations, saving these to the 'Power View Reports' document library we can surface these and execute them as web parts, or full screen mode.



The above knowledge, mixed with the first post of this series, should enable you to start easily taking full advantage of Power View, well beyond the out-of-the-box cubes. Having these powerful visualizations that allow power users to help complete a BI story for a customer is the icing on-top of the BI cake represented by the Microsoft Stack. This will truly help you to engage your user base and help you down the continue journey of Creating a System of Engagement!

That's all I have for this post. Feel free to comment or leave me any questions you might have. Further, I will back into the cube creation process behind this in a new post, so that there is a complete story from end-to-end for you. Keep in mind, this would work for *any* cube. This does not have to be just the Dynamics AX cubes. Further you can start to see how this can be placed throughout the Dynamics AX forms and user experience. Til Next Time!
Follow Me @:
       

"Visit the Dynamics AX Community Page today!"

SQL Server 2012 - SSAS Tabular Modeling

In my last post about SQL Server 2012 & the Future of BI with Dynamics AX, I mentioned that we would start with the introduction of concepts and continue to dive deeper. With that in mind, lets dive a little further into Tabular Modeling with SQL Server 2012 - SQL Server Analysis Services.



I have referenced several times, the above image, and what it shows is the current AX 2012 Analysis Services Architecture and how OLAP Cubes are consumed and made use of within an AX instance.

It's very important to understand how the out-of-the-box AX 2012 OLAP Cubes are created, and consumed, to help understand how the new offerings coming with SQL Server 2012 might be enabled by Microsoft. Keep in mind the current target for the use of SQL Server 2012 with Dynamics AX is 2013 with the release of Microsoft Dynamics AX 2012 RC2.

The purpose of the following post, was to point out how BI Analytics for AX 2012 currently works and can be used: AX 2012 - Diving into BI Analytics



With this understanding, we can move forward with looking at what's going to be new for SQL Server 2012, specifically relating to Analysis Services - Tabular Modeling. To help us understand these concepts a little better, we have a few resources already out there. First off, lets get connected to the Microsoft Published resources for this topic.

The current resource we have from Microsoft, which we can use with the download of SQL Server 2012 RC0, is the following: Tabular Modeling (Adventure Works Tutorial).

From the Tutorial:
"To better support the data analysis needs of sales and marketing teams and of senior management, you are tasked with creating a tabular model for users to analyze internet sales data in the AdventureWorksDWDenali sample database."

Taking this, there are a good bit of key concepts that you can start to understand for the use of Tabular Modeling, with a realistic scenario. The big picture parts that should have special attention applied during this exercise is around the parts that make up a Tabular Modeling Project.

Applying this understanding of SQL Server 2012 Tabular Modeling Projects, along with how OLAP Cubes are created and consumed in AX 2012 today, we can piece together how Microsoft might take advantage of this technology with Microsoft Dynamics AX 2012 RC2. Further we also can get a glimpse into PowerPivotV2 which plays a critical part in the consumption of Tabular Models from SQL Server 2012.

Finally, I will point out on this topic a book that was recommend to me, and one that I personally ordered yesterday: Applied Microsoft SQL Server 2012 Analysis Services: Tabular Modeling



The Author of this book, Teo Lachev, by far, has the most exposure and expertise on this topic right now that I can find. I expect this book to get high ratings for myself personally once I'm done with it.

Well that's all for this post, but continue to check back as we look into the future of BI with Dynamics AX, along with many, many other topics. Till Next Time!

Follow Me @:
    

"Visit the Dynamics AX Community Page today!"