The Cube behind the Power View - Part II - Create, Deploy & Process

I hope everyone is having a blessed, Dynamics filled week! Hard to believe that we are well into April of 2013. I do love when the days get longer, the sun is closer, and there is plenty of vitamin D to be absorbed. With this, I wanted to continue the series that was started yesterday, focusing on 'The Cube behind the Power View'. In yesterday's post, Part I - Perspective, we covered the in's and out's of modeling a cube from a perspective.



Having gone through the previous post, you should have a perspective that is ready for targeting a brand new Analysis Services project. After generating the analysis services project in the AOT, we will further create, deploy & process our newly generated cube. Lets get's started then!

A - Create
Our next step is to make use of the 'SQL Server Analysis Services Project Wizard.' This is located within a development work space for AX 2012 R2. You then click on the Tools menu option, Business Intelligence (BI) Tools & finally launch the wizard. Shown in the below image is that exact path.



In launching the Analysis Services Wizard, you should see a screen shot similar to what we see in the below image.



Clicking on the next button will take you to the first prompt where we start to configure this session of the Analysis Services Wizard.



You will notice in the above screen shot, that I have selected the create option. Further I've supplied what the project should be called. The goal is to create a separate SSAS database, and cube within. This leaves in tact the out-of-the-box cube database and cube elements. Further you will notice the name I've given this new project. It has a prefix, which is recommended being this will live in the AOT. Since I work for Sunrise Technologies, I use the prefix of "sun".

Clicking next after filling in the prompt for the create option, will lead us to the below image.



You will notice in the above screen shot, that we have selected the Sales Data perspective. This is listed by label, and what the cube will be named. Moving right along from here, clicking next will bring us to the next image, shown below.



The above shows dimension possibilities from the Shared Dimensions perspective. This is not something we care about in our current targeted cube, and therefore we will leave out for now. Clicking next on this screen, with leaving everything left blank, should take you to the following.



What you see with the above, is the date dimensions in which we can select and work with. These are generated and created within Dynamics AX. We can modify these, and further create different one's, like a fiscal date set. Keep in mind that based on any dimension we selected which has a Date or UtcDateTime data type, will be crossed with these date dimensions. Dynamics AX will automatically generate this for yu via the modeling of the perspective that you created earlier. Selecting date above, and clicking next, should take you to the following screen.



The above image shows the language selection for the cube that is being targeted. Based on the languages you have setup within Dynamics AX, such can be taking advantage of here. Translations exist within cubes as they do within Dynamics AX. For our current target we are selecting just U.S. based English. Doing so, and clicking next will take you to the following.



You will see in the above image we are now generating our project. This basically ends the create portion of this post, as the Analysis Service project is now generated and living within the AOT.

B - Deploy
Now that we have created our analysis service project, we will continue using the wizard to finish things out. With that, we should see a screen shot similar to the below.



Clicking on next from the progress update, should take us to the following screen.



You will notice I'm saving the actual project to the AOT, under the name sunSalesAnalysis. This allows for again a separate project not related to the out-of-the-box cubes. Clicking next, after filling this prompt out as shown above, will take me to the following.



Here is where we actually select which cube databases we want to deploy. Since the concept of partitions came into being with R2, cubes take this into consideration. With the demo data set we have two partitions which are initial and ps. Since we only care about the inital partition and the legal entities contained therein, that is the target for deployment.

C - Process
I eject the process here, as it's an option we have selected int he above screen shot. Doing this means once the cube database is deployed it will process as well. Allowing us to remain within Dynamics AX the entire time, without having to jump into anything else to achieve this. Having made our above selections and clicking next, will take us through a series of progress updates.



Having reached the end of the deployment and processing, we should see an image similar to the one below.



In clicking next, that is it! We have created, deployed & finally processed our new cube. If there where multiple cubes targeted for this specific database, then those would have processed as well. Just to show that this processing took place, I quickly consumed the Sales Data cube from within SQL Server Data Tools. You can see this data, processed in the cube in the image below.



Now obvious there are plenty of things that can be done from this point. More advanced topics that could and should be covered. However there is only so much one should write at a given time, specifically for the type of medium you find yourself reading right now. I hope this helps you out, and further helps show how easy it is to create a cube. Finally, keep in mind the focus of extending power view within Dynamics AX. The combined knowledge should give you exactly what you need to really start taking advantage of the bull BI Semantic Model. Make sure to check back soon & often! 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!"

ODataQueryService Paging Setting for AX 2012

Today I wanted to spend a little time, in the trenches if you will, with the Microsoft Dynamics AX 2012 ODataQueryService. I've talked a good bit about this, and it's value over the past 12 months for enabling Personal BI conepts through the use of PowerPivot.



Today I wanted to spend time, talking through an issue that you might encounter when actively using OData feeds from AX 2012. Lets first look at the desired need for reporting, specifically targeting the InventTable or Item Master within Dynamics AX.



What you see in the above image, is a query that joins several tables, in order to get a full result for Item data, as an OData Feed. As you can see, we have Invent, Purch & Sales Setup data, Item Group, Model group and the InventTable itself. When trying to execute such a query as an OData feed within Dynamics AX 2012 with default paging settings for the ODataQueryService, you might see your TempDB database grow in an uncontrolled fashion. This was seen with an Dynamics AX 2012 FP CU3 instance, with SQL Server 2012 database.

In looking at this further, it seemed to take place anytime the InventTable was involved within a Query. EcoResProduct data works fine, as well as tables with a lot more records. The behavior that was seen, specifically related to PowerPivot not being able to complete the request for data, and the TempDB taking as much space and it could on the drive that hosted it's .MDF file.

If however, we change the paging file setting in the AOS(es) AX32Serv.exe.config files, key of ODataQueryPageSize, value from 1000 to 5000, then you will see such a problem go away.

       <add key="ODataQueryPageSize" value="1000" />

change to:

       <add key="ODataQueryPageSize" value="5000" />

.

Even with this in mind, when need to keep the goal of what PowerPivot is meant to deliver front and center. Meaning it's better to design queries, that would represent one or two tables for a dataset. These separate queries, then become valid targets for fact tables and dimension tables within the PowerPivot design space. The front & center concept here, is the fact that we are building a cube when working with PowerPivot. So though you can increase the paging size for the ODataQueryService, but keep in mind that the best route might be to break up a larger query with multiple tables, into multiple queries, with less tables per each query.

This setting change was first reported by Microsoft, with the following post.: OData Query Service. Further keep in mind, that you can join the Open Source project I have going, focusing around OData Queries and Utilities for Microsoft Dynamics AX.: AX-OData Queries on CodePlex

That is all for this post, but check back soon as a lot more to come. Till Next time!
Follow Me @:
       

"Visit the Dynamics AX Community Page today!"