OData Query Utility for Dynamics AX 2012

Well I hope everyone is doing well, and finding yourself very productive in this Exciting Week for Microsoft Dynamics. Of course, I'm talking in reference to the now released Microsoft Dynamics AX 2012 R2.

While that for sure deserve's a lot of attention, there is something I wanted to give to the community at large, as my Christmas present if you will to everyone. What I'm talking about is an Open Source Project I created called.: AX-OData Queries



As the above image implies, it's the gift that keeps on giving! That is, this open source project has ready to use, a Utility Wizard, and context action that makes understanding which Queries in a Dynamics AX 2012 instance, are valid candidates to become PowerPivot OData Feed sources!



Further as part of this project, there is a Library component that I started, in which I have placed a customer aging query, in the standard folder location. You can read more, by visiting the project's home page.

Further, important, is the documentation, so the following is a direct link to that.: AX-OData Queries Project Documentation. Make sure to read the Read Me, and then following along from there. This includes from Install, through to How To Use Guide.



The above image is the Dynamics AX 2012 project that currently comprises as v1.0.0 of the OData Query Utility scope. This again, includes a Wizard as well as an Context menu, that lives under the Add-in's section.



Like all projects, this needs revision and refinement. This is an Open Source project, and so I do encourage you to get involved as you can. Help make this better! Further, the changes and impacts of AX 2012 R2 has not be tested against this. So there is plenty of chances to make this a much better set of tools, and queries for common data sets.



As-is, you can download the Utility as an XPO file, and import it into a target Dynamics AX 2012 RTM or Feature Pack instance. Then you can either run the Wizard, or you can execute the Context Menu for a specific single query element. The goal, is to help enable the continued theme of creating a System of Engagement with Microsoft Dynamics AX. This specific will help on the Personal BI landscape, in helping faster enable use of PowerPivot, via OData Feeds.

Well that's all for now! (Early) Merry Christmas! As updates happen, of course I will take the time and let everyone know. Please visit the project, download, test, enjoy, contribute! Till Next Time!

Update: There was a slight feature added this afternoon. One that I thought was important to make the first grid, in which you filter the searching of queries by layers. We have added a new field called "Number of Queries".



Now I admit the speed in which the utilElements table can be read, which is a key to the majority of this, is not at the level I like. This is one of the area's that will be focused on next.
Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

Using XDS to Secure data via the Organization Model (Part 1) - AX 2012

Recently, I posted an article that covered the resources for helping one understand the options, and how best to model a companies organization within Microsoft Dynamics AX 2012. You can find that article here.: A question around Organization Modeling for AX 2012

As promised, this post kicks off that two part series, in where I will dive into the use of XDS for securing data within AX 2012.



Before we dive right into securing data, based on the organizational model within AX 2012, first it's important to understand the parts that will make up our solution to this. Keep in mind, that the focus for Microsoft Dynamics AX 2012, is always to model more and code less!

With that in mind, and referencing the above image the are we are talking about focusing on is the Data Security area. This is where deny takes place, where as above it in the authorization section, in which we do role based modeling of security, grant takes focus.

For the sake of this post, and it's focus around data security, lets assume then that you already have your role based modeling of security petty much complete. With that assumption aside, our focus for this effort is around the Data Security Policies. This is where we will live, and what objects we created will have context.

As mentioned in previous post, Query Elements are meant to be a re-usable API in AX 2012. They are considered modeling elements, vs. custom code elements.

The idea, is that we create a modeling query used to represent the policy, in what we want to deny. In doing this we then, as stated above, start with a query. For example, if we wanted to model a query that limited prospects, by inbound user id, then that would be the target of our query.

Lets then look at the start of this example query. Since we want to constrain the Prospects from the Sales & Marketing module, we need to first target the smmBusRelTable. Since we want to constrain these for the inbound user id, lets assume responsibilities have been modeled and we have a "Sales Rep" responsibility tied to prospects.



With having this, then we need to add to our query, the smmResponsibilitiesEmplTable table. This is joined to the smmBusRelTable by RefTableId & RefRecId as a 1:n join type in the query design. Further, since we are targeting the "Sales Rep", we add to our ranges, the ResponsibilityId field, and add for the value: Sales Rep.



The next trick we need to focus on, is then having the query take us from the Worker associated with the responsibility for the the prospect, to the user id. This assumes, then that User's are associated with employee's of type workers via user relations. In that assumption, we can make the connection from smmResponsibilitiesEmplTable -> HcmWorker -> DirPerson -> DirPersonUser.



Now that we have achieved this, we next need to focus on our final range for the DirPersonUser table. That being of the user field. For the value that we supply for the range, it is a function: (currentUserid())



Now that we have this supplied, our query is ready to be set for the basis of a Security Policy. That is the next element of focus, in modeling security with XDS, a security policy. Doing this, we create a security policy, set our constraint properties, and finally enable the policy.



The above we see the correct security policy, and that it is further constraining the SalesQuotationTable as well as the SalesQuotationLine table. Further, the following is the properties of the above listed security policy object.



With the above enabled, and not having anything supplied for the ContextType, this policy is not enabled for anyone, and will deny access to the Prospects & Quotation tables based on the query we modeled. Meaning anyone that does not have a Sales Rep responsibility association for a prospect will not see that prospect when access AX 2012 from the Rich Client, EP, Reporting or service integration. Only users with System Administration role can see everything.

This would mean, that most likely you would want to at least apply this by Security Role, say Sales Rep that you had modeled. Then this policy would only deny access by the modeled query, for the specific user id's, when having the Sales Rep Security role applied to their user id.

Another point about security policies is that the intersection of multiple policies is what is shown. Where as in the grant focused authorization of role based security modeling, that will take the sum of all granted rights, for all applied security roles. Security policies, all that are enabled, must fully be satisfied. This means you could enable overlapping security policies that have adverse affects on what is trying to be achieved. Therefore, like all things, use caution.

Having the above base knowledge, and walk through for using data security policies to deny access to specific data, we can then move forward with part II of this focus, with using the organization model relating to released products, to model security against.

That's all for today's post, I hope you find it useful. Check back soon as more to come, including part II of this post, more on BI and the System of Engagement, as well as upcoming AXUG Summit coverage. Till Next Time!
Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

AX 2012 Personal BI - By Example - Customer Aging II

Yesterday, we started a series together on using PowerPivot with Microsoft Dynamics AX 2012. In the post, AX 2012 Personal BI - By Example - Customer Aging I, we went over a custom query named "CustomerAging" that we were able to connect to via OData feeds from AX 2012. In doing so we left off, with filling our PowerPivot window, with data from AX 2012.



With this we now have the ability to create a new PivotTable within our Excel 2010 workbook and use our PowerPivot data set to power the Pivot Table for reporting. We do this, by closing our PowerPivot window, and then clicking on the new PivotTable button from the PowerPivot Ribbon, as shown below.



Doing this, we should see a screen similar to the following screen shot, that has our field selections for our PivotTable to the right, as well as the working PivotTable area, within our workbook to the left.



Now that we have our PivotTable options to start working with, lets look at some data that we have, which we imported during the first part of this series. Since this is a customer aging report, we should start with the values section. This means we will want to get the Open Balance value. If I just highlight and click the 'CustAgingLine_1_OpenBalanceMst', then you will see that this automatically sums this value for me, and places it within the Pivot Table, values area as shown below.



Having this, and looking at the value, does not look to pretty really. Lets do a little face lift work on the value label, and change that. We can do this by right clicking on the Sum Value field, and the left clicking on Edit Meassure.



Doing this, you should see a form similar to the below. This is where you can set the value for the name of the Meassure as well as other options. For now, we just care about changing the name. I'm going to change it to Open Balance.



Doing this, gives us a nicely formated name for our Value that we care to report on. This could also be changed in the PowerPivot window, but for now we will work within the PivotTable for such changes.

Moving forward from here, we need some rows, and columns to report on. So first, lets get some rows to report on. Since, again, this is a Customer Aging report, let us focus on the customer account & name values. This is made up of 'CustTable_1_AccountNum' and 'DirOrganization_1_Name' from our field list. Lets place these two fields, by draging and dropping them into the row columns as seen in the below image.



Now if we look to our Excel workbook, we can see that the PivotTable itself has been updated to reflect our new Row Label fields. In looking at this, and like the change we made for Open Balance, this is just not quite what we are looking for. In order to help make this report look a little better, lets create a computed column in our PowerPivot Window, and call it Customer. We do this by going back into the PowerPivot Window, and then scrolling to the far right and click to add a column.



Now that we have our new column we need to add Dynamic Analysis eXpression (DAX) formula that will concantenate our Account Number & Customer Name, into one field for us. We can achieve doing that with the following such formula.

"=CONCATENATE(CustomerAging[CustTable_1_AccountNum],CONCATENATE("-",CustomerAging[DirOrganization_1_Name]))"

Doing this, allows us and saving we should then see our new computed column, that combined "AccountNum - Name" in our new column called customer, as seen in the following screen shot.



Now that we have our new computed column, lets close our PowerPivot window. We should then be able to remove the current row labels, scroll to the bottom of our field list, and see our new Customer field. Lets drag and drop that now into our Row Labels as seen below.



Now that we have our new computed column place, we should see this reflected in the the workbook where our PivotTable is being built out. You should see something similar to the screen shot we have listed below here.



Now we are off to a really good start, with our personal BI journey that we are taking with PowerPivot and AX 2012. It's worhty to note that, in accessing data through the application layer of AX 2012 through the use of OData feeds, representing query elements we are able to inherit all the great security and features of Dynamics AX.

Well that's all for this evenings post. I hope you all have a wonderful night, and chekc back soon as we continue our journey on personal BI tomorrow. We will continue with adding Column labels, as well as Vertical & Horitizontal Slicers! Till Then!
Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

AX 2012 - Query Elements, a reusable API

I hope everyone is doing well, as we launch full on into our Dynamics Spring! I love it when the time changes here in America, and we are able to enjoy longer hours in the sun. I'm a spring-summer-fall guy, which really means I love being out in the sun. With that said, I wanted to talk with you today, about Query Element Objects, within the AOT for Microsoft Dynamics AX 2012.



Query node objects, in AX 2012 has received some major overhauls, with giving us the ability to now offer having use, for example, and more complex outer joins, as well as non-exist joins. These kinds of improvements, along with others helps lend themselves to the use of Query Elements from the AOT for a multiple use scenario's. This is where the concept of the Query Element, being slated as a reusable API comes into play. Before we continue forward with that concept, there are some really nice resources from MSDN on this topic I think it's important to share. Mostly, how to create and make use of query elements.: How to: Create Queries by Using the AOT [AX 2012].

With that noted, lets take a look at where all queries are used within AX 2012 and see for ourselves what makes a query element, truly a reusable API. First up, we have the obvious, in which I've talked to in the past on this blog. Specifically I'm making reference to using a query element as the source of a dataset for modeled approach to an AX-SSRS report.



In talking in terms of reporting & BI, having the query as the basis for a reports dataset is the desired, "modeled solution" for report design. In this, as I point out in the past post, the query is the source for the data of the report, and the control of the report is the ranges that one setups from the query.

Moving right along with this concept, we also have seen - in past articles, where query objects can be used as the source of OData feeds for PowerPivot based reports.



In this, we can create a query element in the AOT, that we reference and make use of from the Document Data Sources form that we find under Document Management for an instance of AX 2012. In doing this, we are then able to enable, and publish this query as an OData feed, through the out-of-the-box ODataQueryService. Doing this, we are able to apply and make use of security from within the context of AX, yet offer the flexibility and power, that a tool like Microsoft PowerPivot brings to users and it's nature for creating ad-hoc reports. I've seen in the past, partners training users, even on AX 2012 - to create views that go directly to the database. There is no reason for this, and you should never do such. Instead, make the correct choice with the use of query elements and consume those as your OData feeds for PowerPivot needs.



As we continue to move on with this discover process of what really makes a query element a reusable API, lets look at something else we have talked to in the past on this blog. That is the creation and use of Document Services for AX 2012.



As shown in the above image, referenced from the above post, the use of a query as the basis and start of a document service is critical to understand. There are some key point specific's around certain settings for query elements that are used as the basis for document services, however it does start with the design or use of a query element from within the AOT. The image above is showing the context menu, when you right click on a query element, and what kicks off the AIF Document Service Wizard

The above are just a few examples, in which we can easily speak to and say that query elements truly are a reusable API for AX 2012. Some other area's include: Workflow design, Basis for an RDP class (ie: CustAgingReport), View datasources that themselves are sources for perspectives and cubes, modeling security with XDS, ListPage designs, Cue's, and more!

With the power of Query elements in AX 2012, when you start to look at what all they are the basis of, you should really ask yourself when doing in design work - Can a Query Element be used here? If so, does one already exist, or should I create a new query element? How can it be used throughout my instance of AX 2012? It's a true statement, in that Query Elements are reusable API's for AX 2012, and understanding there value, use cases, and design aspects will help you get the most out of your AX 2012 investment.

That's all for this post, I hope you have a great productive wed, and check back soon as more to come! Till Next Time!
Follow Me @:
    

"Visit the Dynamics AX Community Page today!"