Where's AX form information gone?

In the new AX, detailed form information such as the query statement, datasource and field name at first glance seems to be missing when you right click on a form control. Microsoft has deliberately hidden this feature, as it is more targeted towards consultants than end users. To get it back add the following to the end of your URL path:

&debug=completepersonalization

Steps

1. Right click on a form click Personalise: <FormControlName>
   
   
2. On the Personalise form, click Form information
   
   
   
3. The form information dialog will appear on the right hand side of the screen
   
   

Copy roles from another user

Awesome new feature in the new AX allows Sys Admins to copy the roles of another user as part of role assignment.

Lifecycle Services’ Issue Search making life easier

I’ve been playing with Lifecycle Services (LCS) for Microsoft Dynamics AX that was release in June of this year and have become a big fan of the issue search feature. LCS is a cloud-based portal for customers and their partners to manage projects, from pre-sales through implementation and operation.

To use the issue search feature, you need to create a project, from which you can then click on the Issue Search tile to begin searching.

From the search page, enter you search criteria. The KB article number if you know it,

or a search term.

Once you've found what you're looking for, click on the article link to get more information about the issue.

And here's what I love most about this new feature. From this page I can download the hotfix and evenb view the changes that have been made to the AX objects and code.

Code that is highlighted red is the code in the original version that ha changed, code that is highlighted green, is the new code provided as part of the hotfix. You can see in the example above that the change to this method is the deletion of 4 lines of code.

So why am I a big fan?

Well I think it is more to do with how poorly Microsoft has provided this feature previously. Searching for issues through Partner/Customer Source has been painful to say the least. Even armed with the KB article number often yields no results. Recently I did manage to find a KB article on Partner Source of which the content proved to be extremely helpful. The following day I could not find that same article. I had to trawl through my internet history to find it. Why didn’t I just use LCS then you ask? Well, here lies the gotcha with early adoption, while I did find the KB article easily on LCS, Partner Source had much more information. So obviously not all the KB article information has been transferred to LCS at the time of the writing of this article.

Other than just a seemingly powerful search engine for finding information about logged issues, I especially like that I can review the objects and code affected by the hotfix, without having to install it first. In my experience, it is rare to find a hotfix that only includes code for your specific issue and on the specific version of AX you require. Code analysis is usually required at some point, and to be able to do this upfront without a deployment, is a great advantage.

Still ironing out some issues with issue search

Even though my experience to date has been mostly positive, I have stumbled across the odd issue with this feature. For example, clicking on the title link of some of the issues gives an error. Also, not all KB articles provide the ability to view object and code changes.

More information please

It would be great if Microsoft provided information about the specific build the hotfix was intended for. At the moment the only information you get is major release versions, e.g. Dynamics AX 2012 or Dynamics AX 2012 R2. I deployed a hotfix on AX 2012 RTM only to find out by trial and error that it depended on CU1.

Further still, some narrative about the actual issue reported and any configuration requirements or new features introduced to address the issue would be helpful. I guess we can expect to see more of this with time.

Conclusion

The new issue search feature in LCS, though not perfect, is a vast improvement on what the Dynamics AX community has had before. Further enhancements to this, should only help to improve the often time consuming, costly and tedious process of issue resolution.

Database Engine Tuning Advisor (DTA) gotcha with AX and indexes

Performance tuning AX SQL queries seems to be the norm these days since the release of AX 2012 and today I was bitten by my efforts to reduce a query's execution time.

I often use Microsoft SQL Server Profiler to collect information about database activity, in order to detect performance bottlenecks. Sometimes I discover something blatantly obvious that only takes a quick tweak of the query or new index to make right. Other times when the answer is less obvious or there are far too many results to analyse manually, I opt to see what recommendations the Database Engine Tuning Advisor (DTA) will come up with. To date, this has served me well and I haven't had any issues, until today.

At some point after finishing my analysis on a test environment under load, a full database synchronisation was performed as part of a deployment, and it failed with the following error message.

SQL error description: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot drop the index 'CUSTTABLE._dta_index_CUSTTABLE_6_1915921947__K94_K93_K83_K96_K152_K1_K149_2_3_4_5_6_7_8_9_10_11_12', because it does not exist or you do not have permission.

SQL statement: DROP INDEX CUSTTABLE._dta_index_CUSTTABLE_6_1915921947__K94_K93_K83_K96_K152_K1_K149_2_3_4_5_6_7_8_9_10_11_12

Here's the screenshot.

So from a little research, it seems that if the DTA fails it does not clean up hypothetical indexes, and leaves them in the sys.indexes table of the database you are tuning.

As the code that generates the errors is in the AX kernel, I'm not sure exactly why it detects these indexes however I hypothesise that AX is doing a look up on the SQL system table indexes to get a list of indexes, that are then compared to those on the AOT. AX will then attempt to generate DROP INDEX SQL statements to clean them up. So why then wouldn't it just drop the indexes and be done. It looks to me like there is a size limit on the number of characters for either the SQL statement that AX generates or part there of. As such, the complete index name is truncated and the index that AX is trying to drop cannot be dropped as it truly does not exist.

As it turns out, this is a known 'feature' of the DTA and the deletion these also documented by Microsoft.

To fix the issue, I wrote the following SQL query to generate my DROP INDEX statements:

SELECT 'DROP INDEX ' + i.name + ' on [' + o.name + ']'
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE i.is_hypothetical = 1
  AND o.type = 'u'

You may find also that the DTA has also left behind some hypothetical statistics that need cleaning up. Here is the SQL query to generate the DROP STATISTICS statements:

SELECT 'DROP STATISTICS [' + object_name(i.[object_id]) 
        + '].['+ i.[name] + ']'   
FROM sys.stats i 
WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1 
  AND i.[name] LIKE '_dta%' 
  AND user_created = 0

Run these queries in SQL Server Management Studio (SSMS) against your AX database, copy the output into a query window in SSMS and execute. You may wish to review these before actually executing, that is why I prefer to generate the code first and then run, rather than run a script that runs dynamically generated code.

Now run the database synchronize in AX and you should no longer get errors.