Tuesday, April 30, 2013

Mixing Dynamic and Static Queries with System Services in AX 2012

In the "old" blog post about using WPF connected to the query service in AX, we talked about consuming a static query and displaying the data set in a WPF grid. The goal there was to be able to whip this up very quickly (10 minutes?!) and I think that worked pretty well.
In this post I'd like to dig a bit deeper. I've received some emails and messages asking for examples on how to use the dynamic queries. Well, I will do you one better and show you how to use both interchangeably. We will get a static query from the AOT, change a few things on it, and then execute it. All with the standard system services!

So, we will first use the metadata service to retrieve the AOT query from AX. In this example, I will be using Visual Studio 2012, but you should be able to run through this using Visual Studio 2010 just fine. We start by creating a new Console Application project. Again, we'll be focused on using the system services here, but feel free to use a WPF app instead of a console app (you can merge with my previous article for example). I'm in Visual Studio 2012 so I'm using .NET 4.5, but you can use .NET 4.0 or 3.5 as well.



Next, right-click on the References node in your solution explorer and select Add Service Reference.



This will bring up a dialog where you can enter the URL of your Meta Data Service. Enter the URL and press the GO button. This will connect and grab the WSDL for the metadata service. Enter a namespace for the service reference proxies (I named it 'AX' - don't add "MetaData" in the name, you'll soon find out why). I also like to go into advanced and change the Collection Type to List. I love Linq (which also works on Arrays though) and lists are just nicer to work with I find. Click OK on the advanced dialog and OK on the service reference dialog to create the reference and proxies.




Ok, now we are ready to code! We'll get metadata for a query (we'll use the AOT query "CustTransOpen" as an example) and print the list of datasources in this query, and print how many field ranges each datasource has. This is just to make sure our code is working.

static AX.QueryMetadata GetQuery(string name)
{
    AX.AxMetadataServiceClient metaDataClient = new AX.AxMetadataServiceClient();

    List queryNames = new List();
    queryNames.Add(name);

    var queryMetaData = metaDataClient.GetQueryMetadataByName(queryNames);

    if (queryMetaData != null && queryMetaData.Count() > 0)
        return queryMetaData[0];

    return null;
}

Very simple code, we create an instance of the AxMetadataServiceClient and call the GetQueryMetadataByName operation on it. Note that we have to convert our query's name string into a list of strings because we can fetch metadata for multiple queries at once. Similarly, we have to convert the results returned from a list back into 1 query metadata object (assuming we got one). We'll return null if we didn't get anything back. If you left the service reference Collection Type to Array, either change this code to create an array of strings for the query names instead of a List, or you can actually right-click the service reference, select "Configure Service Reference" and change the Collection Type to List at this point.
We'll make a recursive method to traverse the datasources and their children, and print out the ranges each datasource has, like so:
static void PrintDatasourceRanges(AX.QueryDataSourceMetadata datasource)
{
    Console.WriteLine(string.Format("{0} has {1} ranges", datasource.Name, datasource.Ranges.Count()));
    foreach (var childDatasource in datasource.DataSources)
    {
        PrintDatasourceRanges(childDatasource);
    }
}

I'm using a console application so I'm using Console.WriteLine, and I have a Main method for the rest of my code. If you're doing a WPF app, you may want to consider outputting to a textbox, and adding the following code somewhere it's relevant to you, for example under the clicked event of a button. Here we call our GetQuery method, and then call the PrintDatasourceRanges for each datasource.

static void Main(string[] args)
{
    AX.QueryMetadata query = GetQuery("CustTransOpen");

    if (query != null)
    {
        foreach (var datasource in query.DataSources)
        {
            PrintDatasourceRanges(datasource);
        }
    }

    Console.ReadLine();
}

Note that we have a Console.ReadLine at the end, which will prevent the Console app to close until I press the ENTER key. When we run this project, here's the output:



Ok, so we're getting the query's metadata. Note that the classes used here (QueryMetadata, QueryMetadataRange etc) are the exact same classes the query service accepts. However, if we add a new service reference for the query service, AX will ask for a new namespace and not re-use the objects already created for the metadata service. If we give it a new namespace we can't pass the query object received from the metadata back into the query service. Of course I wouldn't bring this up if there wasn't a solution!
In your solution explorer, right-click on your project and select "Open Folder in File Explorer".



In the explorer window, there will be a folder called "Service References". Inside you'll find a sub-folder that has the name of the namespace you gave your service reference. In my case "AX". The folder contains XML schemas (xsd), datasource files, the C# files with the proxy code, etc. One particular file is of interest to us: Reference.svcmap. This file contains the URL for the service, the advanced settings for the proxy generation, etc (you can open with notepad, it's an XML file). But the node called MetadataSources contains only one subnode, with the service URL. If we add a second node with a reference to our second URL, we can regenerate the proxies for both URLs within the same service reference, effectively forcing Visual Studio to reuse the proxies across the two URLs. So, let's change the XML file as follows. Note that XML is case sensitive, and obviously the tags must match so make sure you have no typos. Also make sure to increment the SourceId attribute.

Original:



New:



Again, I can't stress enough, don't make typos, and make sure you use upper and lower case correctly as shown. Now, save the Reference.svcmap file and close it. Back in Visual Studio, right-click your original service reference, and click "Update Service Reference".



FYI, if you select "Configure Service Reference" you'll notice that compared to when we opened this from the Advanced button upon adding the reference, there is now a new field at the top that says "Address: Multiple addresses (editable in .svcmap file)").

If you made no typos, your proxies will be updated and you are now the proud owner of a service reference for metadata service and query service, sharing the same proxies (basically, one service reference with two URLs). First, let's create a method to execute a query.
static System.Data.DataSet ExecuteQuery(AX.QueryMetadata query)
{
    AX.QueryServiceClient queryClient = new AX.QueryServiceClient();

    AX.Paging paging = new AX.PositionBasedPaging() { StartingPosition = 1, NumberOfRecordsToFetch = 5 };

    return queryClient.ExecuteQuery(query, ref paging);
}

Note that I use PositionBasedPaging to only fetch the first 5 records. You can play around with the paging, there are different types of paging you can apply. So now for the point of this whole article. We will change our Main method to fetch the query from the AOT, then execute it. For good measure, we'll check if there is already a range on the AccountNum field on CustTable, and if so set it. Here I'm doing a little Linq trickery: I select the first (or default, meaning it returns null if it can't find it) range with name "AccountNum". If a range is found, I set its value to "2014" (a customer ID in my demo data set). Finally I execute the query and output the returned dataset's XML to the console.
static void Main(string[] args)
{
    AX.QueryMetadata query = GetQuery("CustTransOpen");

    if (query != null)
    {
        var range = (from r in query.DataSources[0].Ranges where r.Name == "AccountNum" select r).FirstOrDefault();
        if (range != null)
        {
            range.Value = "2014";
        }

        System.Data.DataSet dataSet = ExecuteQuery(query);
        Console.WriteLine(dataSet.GetXml());
    }

    Console.ReadLine();
}

And there you have it. We retrieved a query from the AOT, modified the query by setting one of its range values, then executed that query. Anything goes here, the metadata you retrieve can be manipulated like you would a Query object in X++. You can add more datasources, remove datasources, etc.
For example, before executing the query, we can remove all datasource except "CustTable". Also make sure to clear order by fields since they may be referencing the other datasources. Again using some Linq trickery to achieve that goal.
// Delete child datasource of our first datasource (custtable)
query.DataSources[0].DataSources.Clear();
// Remove all order by fields that are not for the CustTable datasource
query.OrderByFields.RemoveAll(f => f.DataSource != "CustTable");

Thursday, April 25, 2013

Dynamics AX 2012 Compile Times

As I'm sure most of you are aware, compile times in Dynamics AX 2012 are a concern with more functionality being added. Especially on our build environments, which are (non-optimized) virtual machines, we are looking at around 3 hours for AX 2012 RTM/FPK and around 5 hours for R2. There have been discussions on the official Microsoft Dynamics AX Forums about this very topic, and there seem to be huge differences in experiences of compile times. After a lot of discussion with other people on the forums, and consequent chats with Microsoft people that are "in the know", I think it's pretty clear which areas one needs to focus on to optimize compile times.

1) The AX compiler was originally built when there was no talk about multi-core. So, as a result, you've probably noticed a compile only uses one thread. With today's trend of more cores but at lower clock speeds, an "older" machine (CPU type) may possibly perform better than a new one, or a desktop machine may perform better than a high-end server.
2) The communication between AX and SQL is critical. The communication with the model store is critical (AOS gets the source code from the model store, compiles it, puts the binaries back in the model store).
3) The model store is in SQL so SQL has to perform optimally.

To this end, I set out to build one of our customer's code bases (AX 2012 RTM CU3, no feature pack) on an "experimental" build machine. This code base has been taking an average compile time of 3 to 3.2 hours every time on our virtual AOS connected with physical SQL.


The new setup? A Dell Latitude E6520 laptop:

* Core i7-2760QM CPU @ 2.4GHz, 4 Cores, 8 Logical Processors
* 8 GB memory
* High performance SSD (Samsung 840 Pro), 256GB
* Windows Server 2012, SQL 2012, AX 2012 RTM CU4

Besides this hardware (2.4ghz clock speed - number of cores doesn't matter, SSD to maximize SQL throughput), the key elements of our setup are putting the AOS and the SQL server both on this same machine, and disabling TCP/IP in the SQL server protocols so that it uses shared memory instead. This is the least overhead you can possibly get between the AOS and SQL.

The difference in compile time is staggering. I actually ran it multiple times because I thought I had done something wrong. However, since this is an automated build using TFS, I know the steps and code and everything else is EXACTLY the same by definition. So.... drumroll!
(Note I didn't list some extra steps being done in the automated build explicitly so that's why it may not seem to add up...)


Old Build Server

New Build Server
Remove old models
00:00:27

00:00:03
Start AOS
00:01:26

00:00:25
Synchronize (remove old artifacts from DB)
00:06:52

00:05:57
Import XPOs from TFS
00:13:17

00:03:55
Import VS Projects
00:00:29

00:00:11
Import Labels
00:00:22

00:00:08
Synchronize (with new data model)
00:05:42

00:01:55
X++ Compile
02:29:36

00:41:28
CIL Generation
00:13:41

00:05:29
Stop AOS
00:00:10

00:00:03
Export Built Model
00:00:42

00:00:12
Total Build Time
03:14:43

01:00:59

So yes, the compile time got down to 41 minutes! We've actually switched using this machine somewhat permanently for a few customers, we'll be switching more. Now I need another machine for R2 compiles :-) I will post the compile times for R2 when I get to those.

Happy optimizing! :-)

Monday, April 15, 2013

Exception Handling in Dynamics AX

Exception handling in Dynamics AX is a topic that is not discussed too often. I figured I would provide a quick musing about some of my favorite exception handling topics.

Database Transactions
Exception handling while working with database transactions is different. Unfortunately, not a lot of people realize this. Most exceptions cannot be caught within a transaction scope. If you have a try-catch block within the ttsBegin/ttsCommit scope, your catch will not be used for most types of exceptions thrown. What does happen is that AX will automatically cause a ttsAbort() call and then look for a catch block outside of the transaction scope and execute that if there is one. There are however two exception types you CAN catch inside of a transaction scope, namely Update Conflict and Duplicate Key (so don't believe what this MSDN article says). The reason is that it allows you to fix the data issue and retry the operation. You see this pattern in AX every now and then, you have a maximum retry number for these exceptions, after which you throw the "Not Recovered" version of the exception. The job below shows a generic X++ script that loops through each exception type (defined by the Exception enumeration), throws it, and tries to catch it inside a transaction scope. The output shows if the exception is caught inside or outside the transaction scope.

static void ExceptionTest(Args _args)
{
    Exception exception;
    DictEnum dictEnum;
    int enumIndex;

    dictEnum = new DictEnum(enumNum(Exception));
    for (enumIndex=0; enumIndex < dictEnum.values(); enumIndex++)
    {
        exception = dictEnum.index2Value(enumIndex);
        try
        {
            ttsBegin;

            try
            {
                throw exception;
            }
            catch
            {
                info(strFmt("%1: Inside", exception));
            }

            ttsCommit;
        }
        catch
        {
            info(strFmt("%1: Outside", exception));
        }
    }
}


Fall Through
Sometimes you just want to catch the exception but not do anything. However, an empty catch block will result in a compiler warning (which of course we all strive to avoid!). No worries, you can put the following statement inside your catch block:

Global::exceptionTextFallThrough()

Of course, you're assuming the exception that was thrown already provided an infolog message of some sort. Nothing worse than an error without an error message.


.NET Interop Exceptions
When a .NET exception is thrown, they are typically "raw" exceptions compared to our typical 'throw error("message here")' informative exceptions. I've seen quite a lot of interop code that does not even try to catch .NET call exceptions, let alone handle them. The following examples show different tactics to show the actual .NET exception message. Note that not catching the error (trying to parse "ABCD" into an integer number) does not result in ANY error, meaning a user wouldn't even know any error happened at all.

Strategy 1: Get the inner-most exception and show its message:
static void InteropException(Args _args)
{
    System.Exception interopException;
    
    try
    {
        System.Int16::Parse("abcd");
    }
    catch(Exception::CLRError)
    {
        interopException = CLRInterop::getLastException();
        while (!CLRInterop::isNull(interopException.get_InnerException()))
        {
            interopException = interopException.get_InnerException();
        }
        
        error(CLRInterop::getAnyTypeForObject(interopException.get_Message()));
    }
}


Strategy 2: Use ToString() on the exception which will show the full stack trace and inner exception messages:
static void InteropException(Args _args)
{
    System.Exception interopException;
    
    try
    {
        System.Int16::Parse("abcd");
    }
    catch(Exception::CLRError)
    {
        interopException = CLRInterop::getLastException();
        
        error(CLRInterop::getAnyTypeForObject(interopException.ToString()));
    }
}


Strategy 3: Get all fancy and catch on the type of .NET exception (in this case I get the inner-most exception as we previously have done). Honestly I've never used this, but it could be useful I guess...
static void InteropException(Args _args)
{
    System.Exception interopException;
    System.Type exceptionType;
    
    try
    {
        System.Int16::Parse("abcd");
    }
    catch(Exception::CLRError)
    {
        interopException = CLRInterop::getLastException();
        while (!CLRInterop::isNull(interopException.get_InnerException()))
        {
            interopException = interopException.get_InnerException();
        }
        
        exceptionType = interopException.GetType();
        switch(CLRInterop::getAnyTypeForObject(exceptionType.get_FullName()))
        {
            case 'System.FormatException':
                error("bad format");
                break;
            default:
                error("some other error");
                break;
        }
    }
}


throw Exception::Timeout;