Troubleshooting Data Import Export Framework

As promised in my previous blog post, I would write two blog posts with a summary from my presentation on the AXUG European Congress. In this post I will provide some tips related to troubleshooting Data Import Export Framework issues.

(meer…)

Tips about Data Import Export Framework performance

Last week I was a speaker on the first AXUG European Congress. This event took place on May 9 and 1o in Stuttgart. My session was about the Data Import Export Framework in Microsoft Dynamics AX 2012. The subject was related to performance and troubleshooting. I decided to write a summary of the presentation in two blog posts. In this post I will recap the performance thoughts. (meer…)

My session on the AXUG European Congress 2016

I have not written a blog for some time. Not that I did not have anything to share, but I was burdened with a neck hernia. Now it seems like I’m recovering, I can start blogging again! For this time I will keep the content very light weighted. (meer…)

Consume JSON and azure bus the easy way

As we all know, communication these days is still changing. We might think that the whole world is Microsoft and .Net. Well last month I have seen again that this is not the case. We had a customer who had a website running on Linux and programmed in Java, which we had to integrate with AX. This meant that WCF inbound messages could have been a solution, since they already had XSD available on the java website. Generating XSLT and AIF in and outbound could be a suitable option in this case.  But suddenly I started thinking… why do we always map external XSD on Microsoft Dynamics AX XSD? Why not consume their XSD straightaway?

This is quite possible, but more items need to be taken into consideration.For example security: why should the customer have to open their firewall at company site to provide access to AX from the external website’s point of view?  One might ask why not use the azure bus adaptor from AX? In my opinion, there are too many external obstacles to get this one up and running quickly. One has to understand additional topics like Microsoft ADFS and Claim based authentication. All these points make you think that we are on the wrong track.

After quite some experiments, we came with the following solutions.

• Windows Azure Bus, acts like a windows message queue, only it is outside my company and can be accessed by all parties that have the secret key – https://azure.microsoft.com/en-us/pricing/details/service-bus/
• Start using JSON.

Azure Service bus

The next diagram describes the global architecture. The Service Bus acts like a message queue somewhere out there in the world. Dynamics AX is just an App that can push and pull messages to this service bus. If all those apps also use the same data format structure, life becomes easy. That is why we decided to use JSON.

JSON

I will outline how JSON works. Basically this is similar to XML, being just a format style of data

Above screenshot taken from http://www.service-architecture.com/articles/web-services/javascript_object_notation_json.html

The next screenshot is a visual studio project where I included three special libraries (see the red box). The rest of the project are my contracts and a JSON class. This class is used in my X++ code. The libraries are easy to integrate in visual studio. For more details one can have a look at http://docs.nuget.org/consume/installing-nuget

The contract classes are not very different from the SysOperation classes we create in X++. One creates classes that define the contract. In the next example I created three classes. The first class is named JsonEnvelop and it has 2 related classes JsonHeader and JsonBody (Note the JsonBody is similar to the JsonHeader class)

After that I created two methods for sending and receiving the message. If you look closely, you can see that the trick is the DataContractJsonSerializer class. This class wraps my classes to the content that we call JSON.

Now all we have to do is the actual send and receive method, which also needs some configuration stuff. This is added in the next part.

Example of x++ code (pseudo code)

Conclusion:

• YES it works!
• Because we use a special library like JSON and azure bus, you cannot hook in the visual studio project directly in the AOT. At compilation time, Dynamics AX cannot resolve it. So you have to do it old school and copy all DLL files to the server Bin map of their AOS.

Subscribe to our newsletter and get our latest blogs straight to your inbox and if you have any questions, don`t hesitate to contact us.

Clean model store upgrade to CU 10

Clean model store upgrade to CU 10

We had a request from one of our customers running AX 2012 R3 to upgrade to CU10. There were already a few Knowledge Base articles (more than 50) installed in their current system. So what we did is to hook in on MS Dynamics Lifecycle Services and select all KBs that form part of CU10. This resulted in over 4500 KB models in the model store. This became more troublesome since when compiling, we were even getting compilation errors. This is clearly a situation you don’t want to have at all.

How can we resolve this problem?
Download the slipstream installation of CU10 from LCS. When extracting this download, one will see the two models below.

Question now is: how can we get these two models in our customer environment without losing any data and replace the 50 KB articles already installed. The following is a small trick for cleaning up this mess. Import the above model with axutil, with the option /conflict:overwrite. This will result in moving the code from the existing 50 KB models to the new SYP model. So now the already installed 50 KB articles become empty shell models. All that remains to be done is to remove these empty shells. For this one can use the axutil list > lists.txt command. This command will give a text file with containing the installed models.

– Copy this file to excel
– Remove all lines that you don’t want to remove (the lines with Hotfix and SYP will stay)
– Next remove all information except the ID column and add the content of column A (axutil delete /model: ) before it followed by column C (/noPrompt) as shown in the screenshot below.

– Finally, copy and paste the content back to the list.txt file

All that remains to be done is to execute this in the cmd prompt again. This is quite a slow process, but in the end, model by model gets removed. When it is finished it is recommend to import the two models from CU10 one more time. From this point on one can start the code upgrade as normal. I personally tested this scenario and there was no data loss when synchronizing the database. Of course, there are no guarantees. Always test before using!

Microsoft Dynamics AX Client – One for all

The ‘new’ Microsoft Dynamics AX is now a web based application. There is now a “One for all” client. When you look at previous versions like Microsoft Dynamics AX 2012, there are two features which were also web based. This blog post will inform you about the Enterprise portal and Warehouse Mobile Devices Portal features in the new version.

(meer…)

Tips on AX 2012 Security Development Tool – Part 8

Last year I posted some tips on the Microsoft Dynamics AX Security Development Tool. Recently my colleague Boye Walters also wrote an episode and named it Tips on AX 2012 Security Development Tool – Part 7. Well… he fanned the fire on his topic and here is another edition: Tips on AX 2012 Security Development Tool – Part 8; focusing on the view type Duties and Privileges.

(meer…)

SQL Trace Flag for Dynamics Ax: Do we need it?

What is a SQL Trace Flag?

A SQL Trace Flag is an indicator that changes the way the SQL engine reacts. Why would we want to that? Well because the DBA always knows best, uh well actually because the standard behavior of the SQL engine will not always give the best possible performance for a Dynamics Ax ERP. Which SQL Trace Flag could be of interest for a Dynamics Ax Database? Well I found 5 that work great for some situations. (-T1117 -T1224 -T2371 -T4136 -T4199). Yes I said “some situations” because which SQL Trace Flag can be enabled depends on lots of things, and should be decided carefully. Let me inform you about these 5 SQL Trace Flags.

SQL Trace Flag T1117: TempDB in multiple files

When you divided the TempDB in multiple data files to match the number of CPU cores as recommended by Microsoft for Dynamics AX, SQL Trace Flag –T1117 comes in handy. It will change the standard behavior of auto growing database files in a file group. The standard behavior is to auto grow one of the files at a time. With this SQL Trace Flag enabled all files in a file group will grow simultaneously.

For example:

1 db divided in 4 data files with an initial size of 100MB and an auto grow of 20MB each. When your reaches the 400MB and all 4 files are full, one of them will auto grow to 120MB. But the next data will be written in the file with the freest space. Guess which one that will be…. The one that grew off course.   Now the same example with SQL Trace Flag –T1117 globally enabled: When your reaches the 400MB and all 4 files are full, all of them will auto grow to 120MB. The next data will be written in the file with the freest space. Guess which one that will be…. All of them.

Should we enable this SQL Trace Flag this minute?

No, there is a drawback. This SQL Trace Flag is great for the TempDB but it affects all data files of all databases in the instance. They will simultaneously grow if they’re in the same file group. So think before you act, if you don’t want the files to grow simultaneously put them in different file groups, or don’t enable this SQL Trace Flag.

More information on this SQL Trace Flag: Microsoft Dynamics Ax Performance team

SQL Trace Flag T1224: Running big operation during the day.

When running big operations (5000+ rows) during the day, like master planning, they could be blocking your users from doing their daily business. This is due to lock escalation. SQL locks a page to make sure only one process effects that page at a time. When a certain number of pages of a table are locked by the same process SQL can decide to lock the table if this is less costly on memory. This will make all requests for pages in that table wait till the first process is done. SQL Trace Flag -T1224 Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation.

Should we enable this SQL Trace Flag this minute?

No, if you don’t need to run big operations simultaneously or with users in the system, you’re probably free of blocking by lock escalation. And lock escalation will have a positive effect on the performance of your SQL. So please plan large operations carefully outside office hours where possible.

SQL Trace Flag T2371: Keeping statistics up-to-date on large tables

The SQL query optimizer uses statistics to create query plans that improve query performance. Performance of Dynamics AX depends largely on up-to-date statistics. That’s why it is recommended to set “Auto update statistics” on. But when will the statistics be updated? Well when 20% of the rows are updated/inserted. This SQL Trace Flag is only available on SQL Server 2008 R2 SP1 and all later versions.  There was a small update to this SQL Trace Flag in SQL Server 2008 R2 SP2 and SQL Server 2012 SP1 that makes it more precise.

For example:

On a table containing 1,000 rows the statistics are updated when 200 rows are updated. And the same table after a while containing 1,000,000 rows the statistics are updated when 200,000 rows are updated. Did you read that right? Yes you need to update 200,000 rows till the statistics are updated. Imagine even larger tables. We all know that in an ERP like Dynamics AX these large tables are pretty common. Microsoft also recognized that and released SQL Trace Flag –T2371. This SQL Trace Flag will make the 20% rule a dynamic percentage rule that kicks in at 25,000 rows.

For example with SQL Trace Flag –T2317 globally enabled:

SQL Server will update statistics on a table containing 1,000 rows when 200 rows are updated. And on a table containing 1,000,000 rows the statistics are updated when 32,000 rows are updated. In a chart the dynamic percentage rule looks like this:

Should we enable this SQL Trace Flag this minute?

No, first examine if your database has a lot of large tables (above 100.000 rows is a good rule of thumb). Without large tables this SQL Trace Flag is useless.

SQL Trace Flag T4136: Parameter sniffing optimisation

Dynamics AX uses parameterized SQL statements if we don’t use the ‘Forceliterals’ parameter in AX. I personally have never seen a developer who fully understood the working of ‘Forceliterals’ let alone use it in code. It even is best practice not to use it.

Why? Well when using parameterized SQL statements the query plans are cached and reused. SQL Server will use one plan for all similar queries instead of compiling each query.  This can significantly reduce CPU overhead and improve throughput. That sounds like a good thing and it is.

But there is a drawback, if the queries are complex and/or the data distribution on the columns against which the parameter is compared vary, the cost of different plan choices can change.  A plan that is optimal for one parameter value may perform poorly for another value.  The query optimizer still needs to estimate the selectivity and cardinality of predicates using a parameter value. In Dynamics AX especially from version 2012 on the queries are almost always complex.

One way to avoid parameter sniffing is to put the OPTIMIZE FOR UNKNOWN option to all SQL statements leaving AX. Another way is to enable SQL Trace Flag -T4136. This SQL Trace Flag is only available on SQL Server 2008 R2 Cumulative Update 2, SQL Server 2008 SP1 Cumulative Update 7 and SQL Server 2005 SP3 Cumulative Update 9 and later.

For example:

If we have 2 companies in our Dynamics Ax Application and one only 2 customers and the other a couple thousand. The following could happen: if the first query we send to loop through customer is done in the company with a couple thousand customers , it could look like:

SELECT * FROM CUSTTABLE WHERE  DATAAREAID = ‘@P2’

And SQL Server Engine will solve it by doing a full table scan because almost all records are needed.

Next time we do the same loop in the other company due to the parameter on dataareaid (and partition in AX2012) the query is identical. That is why SQL Server will solve it in the same manner, with a table scan, where a page scan could be more appropriate. If only SQL could store a queryplan per dataareaid….

Should we enable this SQL Trace Flag this minute?

No, because Microsoft provided a way to use parameters but not for dataareaid (and partition in AX2012). This makes SQL server store a queryplan per dataareaid, making SQL Trace Flag T4136 completely unnecessary.

AX2009 SP1: KB 3000700

AX2012 RTM: KB 2920058

AX2012 R2: KB 2969229

You can find more information on the blog of Dynamics in the field.

More information on this SQL Trace Flag: Microsoft Support on SQL Trace Flag T4136

SQL Trace Flag T4199: Query execution plan updates

Microsoft turned off All query execution plan updates/hotfixes by default to make sure that an update will not affect the way your query executes. Several of these hotfixes will positively boost performance for Dynamics AX. Microsoft controls all future query processor fixes that are scheduled to be ‘On-By-Default’ in a later release with trace flag t4199. This SQL Trace Flag is only available on Cumulative update package 6 for SQL Server 2005 Service Pack 3, Cumulative update package 7 for SQL Server 2008, and Cumulative update package 7 for SQL Server 2008 Service Pack 1 and all later versions.

Should we enable this SQL Trace Flag this minute?

No, there is are 2 drawbacks. 1: Your DBA will send you off to an insane asylum because of the next drawback. 2: If you enable this SQL Trace Flag you enable all future hotfixes/updates to the query execution plans, because we all know you’re going to forget about enabling this SQL Trace Flag.

More information on this SQL Trace Flag: Microsoft Support on SQL Trace Flag T4199

Security: Past, current and future

When you read the title of this blog post you might think this post is about changes during the lifecycle of Microsoft Dynamics AX 2012. Or maybe it is related to the security framework in older versions (AX 2009), the current version (AX 2012) or the new Microsoft Dynamics AX (aka ‘AX 7’). Well, it’s not. This post will tell you about a standard feature to restrict access on past, current and future records for tables which supports date effective data. It is called “Date effective policies”.

(meer…)

SQL Window Functions in Ax? Yes we can

Are SQL Window Functions new?

Since 2003 in the SQL ANSI/ISO standard and extended in 2008. Microsoft implemented the first window functions in their SQL Server 2005 version (known as OVER clause) and extended them in the following versions.

Window Functions in all SQL Versions

So to be short no they are not new for the ANSI/ISO standard and for Microsoft SQL Server window functions exist for a few version now. Within in Dynamics Ax we use X++ which is translated by the Dynamics Ax Kernel to T-SQL to make the database return a result set. This makes us depended of the implementation of X++, which in Dynamics Ax 2012 doesn’t support window functions for select statements etc. But I can show you a way on how to use them in AX 2012 and up.

What are SQL Window Functions?

Let me show you with an example. As this is a blogpost for Dynamics Ax minded people let us use the SalesLine table.

If you want to select a few fields of this table, you would write something like the following (in SQL and X++):

 SELECT SALESID,  ITEMID, SALESCATEGORY, CUSTACCOUNT, LINEAMOUNT FROM SALESLINE;

If you would like to see the total amount of the salesorder in the same result set you would need to do a “GROUP BY”:

SELECT SALESLINE.SALESID,  ITEMID, SALESCATEGORY, CUSTACCOUNT, LINEAMOUNT FROM SALESLINE

JOIN (SELECT SUM(LINEAMOUNT) AS Total, SALESID FROM SALESLINE GROUP BY SALESID) AS A ON A.SALESID = SALESLINE.SALESID;

in X++ you would do something like this:

While Select SalesId,  ItemId, SalesCategory, CustAccount, LineAmount from SalesLine
 {
     select sum(LineAmount) from salesLine2 where salesLine2.SalesId == salesLine.SalesId;
 }

But it will send a statement for each salesline to the database.

TIP

A better way to do this in Ax would actual be:

1. Create a Query object for the ‘select sum(LineAmount), SalesId from salesLine’ bit.
2. Put that Query in a View.
3. Now create a new Query with in the datasource the SalesLine and join it with the View.

Now let’s make it even more interesting. I would like a statement to get a resultset with the following columns:

1. the SalesId,  ItemId, SalesCategory, CustAccount, LineAmount.
2. the total amount of the salesorder.
3. the avg LineAmount of the customer in the same category.
4. the LineAmount of the last time this customer ordered this item.
5. running total of this customer.

SELECT SALESID, 
ITEMID, 
SALESCATEGORY, 
CUSTACCOUNT, 
LINEAMOUNT,
SUM(LINEAMOUNT) OVER (PARTITION BY SALESID) AS [Total amount salesorder],
AVG(LINEAMOUNT) OVER (PARTITION BY CUSTACCOUNT, SALESCATEGORY) AS [Avg LineAmount customer category],
LAG(LINEAMOUNT,1) OVER (PARTITION BY CUSTACCOUNT,ITEMID ORDER BY SALESID) AS [Last time ordered for],
SUM(LINEAMOUNT) OVER (PARTITION BY CUSTACCOUNT ORDER BY SALESID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Running Total];

And here we are, our first window functions. They are called window functions because they put a window (subset) over the rows you get out of your FROM clause.

The <window function> part can be: Ranking functions, Aggregate functions or Analytic functions.

After the OVER keyword you can limit the size of the window:

• OVER() isn’t limiting at all so all rows are considered.
• OVER (PARTITION BY X) means limit the window to al rows that have matching values in Column X in current Row.
• OVER (ORDER BY) changes the sort order in the window. (Can be nice if you use RANK or ROW_NUMBER window functions)
• OVER( ROWS BETWEEN … AND …) limits the window to the rows between the given criteria seen from current row.

How to use these powerful window functions in Dynamics AX?

As I mentioned before X++ isn’t a real options. But in the View objects in the AOT there is a little gem that we can polish for our purpose.

1. Create a Query for the SELECT SALESID, ITEMID, SALESCATEGORY, CUSTACCOUNT, LINEAMOUNT FROM SALESLINE part.
2. Use this Query in a new View.
3. Create a method on the View with the following code:

   public static server str windowFunction()
   {
       //make sure you use this Dictview because the names of the tables are changed in the SQL statement send from Ax to SQL.
       //This makes sure the right names are used
       DictView dv = new DictView(tableNum(<<name of view>>));
   
       //This is the real magic 
       return 'SUM('+dv.computedColumnString('SalesLine', 'LineAmount') +') OVER ( PARTITION BY '
               + dv.computedColumnString('SalesLine', 'SalesId');
   }
   

4. Now right click on the ‘fields’ branch of the view choose NewReal computed column.
5. Give the new field a nicer name and set the name of the method in the ‘viewmethod’ property.
6. Save and synchronise the view.

Now what did we just do?

We changed the create statement of the view to include ‘SUM(LINEAMOUNT) OVER (PARTITION BY SALESID)’ as a column. By synchronizing we created a view in SQL Server that contains the window functions we want to.

Could we use this for other things then window functions?

Yes you can use all possibilities of the T-SQL language here. But remember you are changing the Select clause of the view here. Putting a select statement in here (or a stored procedure) will for sure make the view run a long time as every row will trigger a separate statement. But a scalar value function can be very valuable sometimes (like ISNULL()).
Interesting links:

A 20 minute training about window functions in SQL 2012.

All code samples, walkthroughs and other instructions are provided as-is. Use any and all information provided in this blog at your own risk. It is never advised to import or write code in a production environment without rigorous testing in a test or development environment.
All opinions expressed in this blog are solely my own and do not necessarily reflect the opinions of my employer.
The names of actual companies and products mentioned in this blog may be the trademarks of their respective owners.
Microsoft and Dynamics AX are registered trademarks of Microsoft Corporation in the United States and other countries.