Freaky Tech Friday - OData of Death

Welcome back Ghouls & Ghost, where you find yourself once again traveling into the strange unknown of Freaky Tech Friday!. Today I have an especially scary tale, for those of you brave enough to dare listen. Be fore warned, as this story goes deep into the unknown, down into the deeps of the ODataQueryService for AX 2012.



That's right, you are now listening to the tale of OData of Death! This tale, like most of its sort, starts off in a typical fashion. A BI or X++ Developer is working on a Query in AX 2012, minding their own business, when they activate there work, go to see the ODataQueryService and .... Nothing!.



What horrible cause could this be? Why will the query not show up as a valid OData feed? OData of Death has struck again, in another AX 2012 instance. In fact there are four deadly sins of query's and the ODataQueryService in general, that control the fate of our much needed hero, known as Personal BI!

These deadly sins I refer to are.:

• OData protocol filters are not supported
• OData protocol create and update operations are not supported
• Each record in an OData query response must have a unique primary key. AOT queries with View data sources are therefore not OData query candidates and will not be displayed in an OData metadata request
• Only queries that support “Value-based paging” are executed. AOT queries whose FetchMode property value is “1:N” will generate an error message in the Windows Event Log

There we have it, and so our Query has fallen to one of these deadly sins. Which one could it be? Since we know that the first are not the cause of the sudden death to our work, it has to be one of the last two. In looking at our query design, we did not use a view.:



Then we must look at the very last sin, the one that can really change how we think about designing queries. That's right it was a FetchMode death! In looking at our query design, we have on the VendCertification data source, a FetchMode of 1:n.



Now to resurrect the dead, and bring forth our query to the living - we need to change the FetchMode from 1:n, back to 1:1. Then Deactivate the query in the Document Data Sources. Make sure to compile, and restore the query so the meta data is refreshed, and further refresh the Dictionary and Elements caches. At this point the ritual for reviving our query is complete. We can activate it once more, and see that our query now appears for use, via the ODataQueryService.



With this however, comes at a price. The data is not exactly as we would like it to be represented. What to do?! There is hope on the horizon, a changing of the wind soon - as it has been foretold that the second coming of AX 2012, in the form or AX 2012 R2 will bring with it redemption for some of these deadly sins.

Further you must think in terms of the desired usage and consumption of your data via OData Query feeds, and use the magic found in the sacred PowerPivot offering to join sources within it's mighty ability.

Well that's all for this weeks Freaky Tech Friday! I hope you avoid the deadly sins, and stay clear of the OData of Death scenario's! Come back next week, if you dare, for another frighten tale on Freaky Tech Friday!Till Next Time!
Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

Report Development Basics in AX 2012 - Debug Query & Code based reports

Today, I wanted to take some time and continue this focus we have around Report Development Basics in AX 2012. This is a continued theme really, building block parts of helping understand how to Create a System of Engagement for AX 2012 customers. Today's focus is around one that I think everyone planning to do any report development, the correct way, in AX 2012 should fully understand. That is Debugging Query & Code based report objects.



First I will say that this post assume you have an understanding of the two types of AX-SSRS report development options. This is either via a Modeled Solution, aka Query Based, or Code based solution, aka Report Data Provider based. You can learn more about these concepts in the following post.: AX 2012 - AX-SSRS Report Design Concepts


Model Driven Solution Diagram

First lets start with how to debug a modeled, or query based report. The idea is that you have a query you have created, and now your not getting the data you think you should. The best way I have found, for debugging such a report data source, is via an X++ job.

The following is an example job that can be used, for doing just that.:

static void TestQuery(Args _args)
{

/*
Example job used to test query results with.:
*/

QueryRun queryRun;
Counter totalRecords;

// Table Variables:

VendTable vend;
VendCertification VendCert;

queryRun = new QueryRun(queryStr(queryVendCertInfo));

if (queryRun.prompt())
{
while (queryRun.next())
{
vend = queryRun.get(tableNum(VendTable));
info(Vend.AccountNum);

VendCert = queryRun.get(tableNum(VendCertification));
info(VendCert.CertificationNumber);

totalRecords++;
}
}

info(strFmt("Total Records : %1", totalRecords));

}

Now to break this down a bit. We have a QueryRun object that points to our desired query. In this case, it's one I created called queryVendCertInfo. This query references the VendTable, and further has a relation to the VendCertification table.

I'm able to execute the query, and the prompt call even brings forth the SysQueryForm for Dynamic Filters can be applied. Having this, you can now successfully test in query, as well as any query that is a bound data source for an AX-SSRS report dataset.


Code Driven Solution Diagram

Moving right along, the next area of focus for report debugging is a code driven AX-SSRS element. This is a report object, that as it's bound data source is a Report Data Provider class. There is a nice How to, posted by Microsoft you can find here.: How to: Configure the Debugger to Debug a Report Data Provider Class [AX 2012]

Let me state here that this assumes your working in the development mode, where everything is contained within a single server role. Meaning that for the developer, you have your own AOS, SSRS, SQL Server, as well as Visual Studio 2010, AX client, etc. With that assumption aside, you can then easily debug a report, that is based on a Report Data Provider class.

Since there is a nice how to, I have no need to re-hash those steps. I will say that you need to make sure that the AOS Service account is in the Microsoft Dynamics AX Debugging Users group.



After doing this, and making sure that you have an AOS configuration that enables global break points, the next steps are placing a Breakpoint; keyword in the processReport() method and then having the MorphX IDE Debugger open.



After doing this, you can then debug a code based report, either when executed from within the AX 2012 work space, or from within Visual Studio 2010. One thing that you will notice, when debugging from within VS2010 is after the preview action runs through the debug process once, it stops from running through the debug process again.

There is a process however that you can employ, that will allow you to reload the VS2010 project, and therefore allow multiple debug runs, without having to close out of VS2010 each time.



As the above screen shot suggests you need to unload the VS2010 project. In doing that, the next step would be to reload the VS2010 project that contains the AX-SSRS report elements.



With the above information you should have what you need to help debugging most AX-SSRS reporting development scenarios. That's all for this post, check back soon as more to come, including the continue Freaky Tech Friday series tomorrow! Till Next Time!
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!"

AX 2012 - Excel Add-in Revisited

With the release of Microsoft Dynamics AX 2012, came some great new ways of achieving business requirements, and user experiences. One of those is the Excel Add-in for Microsoft Dynamics AX 2012. I first wrote about this topic, back in June, in which you can find here: Microsoft Dynamics AX 2012 Excel Add-in. This has been a hot post actually, with a lot of comments, and a lot of visits.

It's rightfully so that it should be, being that it's new, and there is some confusion on how to do some of the more complex data sets, within AX, that go beyond just a simple table, which that first post covered for us. With that in mind, I thought I would help clear the air a little bit, in how the Excel Add-in is actually very flexible, and something to watch out for in December from Microsoft on this topic.

First, lets look at the starting point, in which we can start to work with more advanced data sets in AX. This concept, is enabled through the use of document services. This is the way, in which complex data sets in AX are exposed internally and externally of AX 2012. This is true, even for the Excel Add-in.

From the excel point of view, what we are talking about is the Add Data ribbon button, and when the drop down appears clicking on the Add Data, instead of Add Tables.



When clicking on this, for the first time, if you've not setup any Document Data Sources within AX 2012, your selection options will be blank. Now we have talked a little bit about this new concept of Document Data Sources, in the following post.: AX 2012, PowerPivot and a Dash of OData Feeds

In that above post, I covered with you how to enable Excel 2010 PowerPivot to work with OData Feeds from AX 2012. This was enabled through having Query Type of Document Data Sources enabled through the Organization Administration module, Setup, Document Management and finally Document Data Sources.



Here we can also add a document data source, of type service. These services must be document services and extend from the AIF Document Services types. These can not be custom services that do not extend as part of the AIF document services.

With that in mind, we can add a couple of document services, to our list, from within that form, as seen below.



Now that we have these services as document data sources within AX 2012, we can now go back to Excel 2010, within the Excel Add-in and click on our add data, and see our two new services we can make use of.



With this, now we can work with more complex data sets within AX 2012. This also means, that Office 2010, very much is a major part of the user experience in regard to AX 2012. This also means, that some very complex data set needs, can be accomplished through the use of Query Objects, and Document Services that wrap those query objects to expose them internally and externally of AX 2012.

With this said, there are some issues, and area's to watch out for. Basically, there are some super normalized data sets, like customers, vendors, etc. To help address those needs, Microsoft will be releasing in December a resource kit, that will contain artifacts that help enable the use of the Excel Add-in, for the master data, that has some super normalization aspects to it, that make it a little harder to work with from Excel. The basic concept will still be used that you see above here, that will be enabled with staging tables that the process will still use document services to enable.

I hope this helps further explain the value of the Microsoft Dynamics AX 2012 Excel Add-in and how it can be used right now with even more complex data sets, than just simple tables. You enable this through the use of Document Services. Also, it's very important to understand that Microsoft will be releasing that resource kit to help out with some of the super normalized master data elements. Finally, you can create your own document services, that wrap your own custom data sets, or query objects, that in turn can be consumed and made use of in a bi-directional nature through Excel. This is a huge benefit to customers of AX 2012, and something that needs to be considered when thinking about designing and developing scope for customer.

That's all for now, check back soon as more to come. Till next time!

"Visit the Dynamics AX Community Page today!"

Microsoft Dynamics AX 2012 - MorphX Drag and Drop, but no fields?!

Alright, so one of the cool things that I wrote about recently, for Microsoft Dynamics AX 2012, was around the new Type Hierarchies, and the Type Hierarchy Broswer and it's use.

In looking at this, however, I was questioned about the ability to be able to say, create a new Query object, and being able to drag fields over from the table, into the new Datasource. To help bring this topic home, lets look together and one of the new datasets in Microsoft Dynamics AX 2012, the EcoRes* tables.

If we go to the EcoResProduct table, right click and go to the type hierarchy browser for this table, we can see, that it extends from common, and from it extends the EcoResDistinctProduct table object.



Now, lets take and open an AOT window, and browse to the EcoResDistinctProduct table object, so that we can see what kind of fields we have to work with, directly from the object within the AOT tree itself.



As you can see from above, we have no fields actually, within the AOT object, that represents the EcoResDistinctProduct table, to actually reference directly or work with directly. So what can be done then, for dragging and dropping fields, on datasources for example?

To show this off, we will need a new custom Query object. This will be the same, however, for any datasource for a form, report, etc. etc. So lets take and create our new custom Query object. Once we have that, take and drag and drop the EcoResDistinctProduct table from the AOT window, into the datasources of the new custom Query as shown below.



In doing this, we see that we now have the EcoResDistinctProduct table as our datasource, and again no fields, when expanding the fields section under the Datasource node. Now here, you could manually add a new field, but that's no fun, now is it? Enter the power of AX 2012, and the continued use of MorphX!

Simply go back to the AOT window, and expand the fields of the EcoResProduct table, and drag over, say the DisplayProductNumber field. Drop it into the fields section of the EcoResDistinctProduct datasource, and bang! We now have the DisplayProductNumber field, for the EcoResDistinctProduct datasource and table, and not the EcoResProduct table.



Looking at the properties, below, you will see this is actually the case, as Morphx and the power of AX 2012 still handles this for us, and understands that we want that field, for the Distinct products, and not the products itself.



That's all for now, but check back soon as a whole lot more to come! Till next time!



"Visit the Dynamics AX Community Page today!"

Pros and Cons of using SSRS for Dynamics AX 2009 Reporting

I wrote the following blog entry, in order to attempt to give an idea of what all reporting possibilities exist for getting at a company's data, inside Dyanamics AX 2009.

Dynamics AX 2009 - Reporting Possibilites

Since then I have also wrote the following, detailed technical how to blog entries about calling a custom SSRS report from X++ code, and passing in parameters, and also for getting at X++ source code, from within a SSRS report.

- Calling a Custom Dynamics AX 2009 SSRS report and passing parameters - From X++

- Dynamics AX 2009 - Calling X++ logic from SSRS and C#

Reporting in Dynamics AX 2009 is a heavy focus, as more and more companies are wanting to understand how to get the right data to the right people. That was the whole thinking behind creating role centers in Dynamics AX 2009. To target specific information worker profiles, and to give the ability for companies to target specific information workers in custom ways that fit to the company needs.

Since writing the above entries, I have had a full request now, to talk about the pro's and con's of using SSRS reports for getting at data that lives inside a Dynamics AX 2009 instance.

So looking at this, the pro's are quickly seen. The report can be delivered via a Role Center, inside Dynamics AX, and over the web. The processing of these reports can be scaled out to other servers, for performance needs. SSRS offers the ability to work quickly with SSAS / OLAP data, KPI's, etc. and add this to the report design. The creation of these reports happens in Visual Studio 2008, and therefore VS benefits exists. There exist an ad-hoc tool, that gives users a good reach into the data, though not all parts can be done via the Ad-Hoc right now.

There are more, but the above is the highlighted. And the final pro, I would add to this would be the fact that Microsoft is moving it's Dynamics product line to make use of SSRS as the reporting tool. So moving there now, prepares for the future road map of tomorrow.

Now, with all the great things about SSRS and Dynamics AX 2009, there are of course limits or con's.

One of the first things is, it's not the native report engine and designer inside Dynamics AX. Morphx reports are. So all the standard reports and reporting are done and have been done in X++ reports.

This is I would say, the biggest con about SSRS and Dynamics AX 2009. And because of this fact, and handful of other cons come with this fact.

Like for example, since this is the case, then you have to go through the .Net BC to get at X++ business logic / code from an SSRS report. Everyone would much prefer to have more native access to this.

The interaction with with reports from X++ code is different than standard reports, and requires more attention. There is lack of information about limits, issues, etc. because SSRS is not native.

The Query window that comes with most reports, which allows quick adding of filtering fields, etc. does not exists with SSRS reports. The developer of the report can add parameters to the report, still it's not as flexible as the Query Window that exists for X++ reports.

Now with that said, you can develop a class that makes use of a Query dialog, that captures filtering information, and if your good enough in X++ and think far enough in your SSRS report design, you can offer this ability. (if your interested in that, feel free to contact me for a quote.)

I think that is good enough for now. I will leave on this, SSRS should be a major focus to anyone wanting to do reporting in DAX 2009 and beyond. The limits / cons will continue to move away, as SSRS becomes more and more native to Dynamics AX.

Also, on PartnerSource, the Reporting & BI Courseware was just released eariler this month. So if you have access to PartnerSource I suggest you check this out.

Check back soon, and see you next time!




"Visit the Dynamics AX Community Page today!"

QueryRun and Query Objects - Binding operation failed to allocate buffer space

So if you ever work with the QueryRun and Query objects within X++, and I mean work with them a lot. You might come across an issue.

If for some reason, your start to see the following in thrown errors or in the event viewer, Application event log on the AOS, then you have an issue most likely with the QueryRun and Query Objects.:

Binding operation failed to allocate buffer space

The issue that your facing is the query that your creating. The query your creating, to make use with your QueryRun object is causing the AOS to hit a buffer limit. You can increase the buffer limit at the AOS. But most likely what you will need to do is force literals for the query your building in X++.

It's simple to do, after your create an instance of the Query object simply do a: Query.literal(1); call.

This will 'force the hand' of the AOS to accept the buffer allocation requirement, and allow your advanced query to run. Therefore allowing your QueryRun operation to finish as desired.

Well that's all for now. Ran into this, and thought it would be worth putting out here. If anyone has ran into this before, and wants to share feel free to leave a comment. Also if you have more details about literals and the use as described above, feel free to share that as well.

Check back soon!




"Visit the Dynamics AX Community Page today!"