tables Archives - DAXRunBase

Picking list registration performance

We recently have implemented a new Picking application in our warehouse. Our slow performing statement e-mail has immediately highlighted that with our data volume the WMSOrder and SalesTable records were underperforming, taking 5+ seconds each time when the form was opened (close to 20). We needed to fix the Picking list registration performance.

WMSOrder

Apparently it is a standard AX form and table, but the link which they have been using is incorrectly defined, resulting in an InnerJoin for SalesTable unnecessarily.

Picking list registration performance

Also the field used for the join has no index, you should create one for WMSOrder.InventTransRefId to gain more performance.

After the adjustment it looks much better and performs really fast:

WMSOrder

DAXRunBase / 2018-02-27 / AX 2012 / 0 Comments

Fix Table and Field ID synchronization conflicts in AX

It is a very common issue when we change metadata in a source/build environment incorrectly that the Production environment’s AX database field identifiers are getting out of synch from our Application Object Tree (AOT). This can typically happen during a code upgrade scenario (after applying a hotfix or a new ISV solution), or if a field/table has been deleted (because for some reason we did not want to rely on the SysDeletedObjects6X configuration key: https://technet.microsoft.com/en-us/library/dn793968.aspx). In this post I would like to share my latest code to Fix Table and Field ID synchronization conflicts in AX.

The kernel is storing your current data DB’s object identifiers from your AOT (your ModelStore in the _model DB) in a table called SQLDictionary. When you do metadata object changes and release the ModelStore to your other environments, AX allocates new Object Identifiers sequentially per TreeNode type. For example the Tables are from the same range of IDs, but the Fields subnode of the Tables are starting from the same sequence for each table. You can verify this in SQLDictionary by checking TabId and FieldId values for them.

Before you attempt to Synchronize your Data Dictionary after the ModelStore release, the first step should be to run an ID conflict fixing tool. Once the IDs in the SQL Dictionary are matched with your AOT, it is safe to start synchronization. You get a visual feedback on the ID fixing tool’s progress as per below, and it finishes relatively fast:

SQLDictionary Fix Table and Field ID synchronizaton conflicts in AX

You may read up about the issue in a bit more on the following link and have a look at how others were approaching the problem.

http://daxdude.blogspot.com/2012/12/table-ids-out-of-sync-remediesprevention.html

The Fix Table and Field ID synchronization conflicts in AX job and the class behind is available on the DAXRunBase GitHub:

https://github.com/DAXRunBase/AX-2012-R3/tree/master/Fix%20Table%20and%20Field%20ID

DAXRunBase / 2017-06-26 / AX 2012 / 0 Comments

Do not update AX database tables from SQL

This requirement keeps popping up on the Community Forums by people who are not familiar of how Microsoft Dynamics AX works, typically for third-party integration, data warehousing and reporting scenarios. In this post I would like to give some insights on why is it not recommended to update AX database tables from SQL.

Record identifiers

Each row in Microsoft Dynamics AX database within Microsoft SQL Server has a unique identifier which is by default the primary/surrogate key as well for the records. The field is called RecId, and it is maintained as an incremental, int64-based positive or negative number. The highest number I was able to allocate is 3 below the maximum value of a 64-bit integer, which was 9,223,372,036,854,775,805 and you would get an error for the next value:

RecId_Maximum_error

If you would ever reach the maximum value, the manual fix is to set the next value to be -9,223,372,036,854,775,807.

RecId_Minimum_Maximum

(more…)

DAXRunBase / 2017-06-13 / AX 2012 / 0 Comments

How to find AOT objects in AX

At some point all developers have the requirement to be able to locate a particular piece of AX object or code. AX does provide some out-of-the-box tools that you could use in order to find Application Object Tree (AOT) entries. Here are the tools and techniques available on how to find AOT objects in AX.

1. The Developer tools

In the AX Developer client under the menu Tools > Model management > Model elements you get a Form-based representation of the top-level Application Object Tree entries, which you could filter by a specific layer, model, type or element name. It is displaying the element from a system view called SysModelElement.

(more…)

DAXRunBase / 2017-01-04 / AX 2012 / 0 Comments

Find SQL tables and views not part of the AOT

The other day I had a requirement to identify and delete any objects in the AX 2012 SQL database which are custom made by other means than AX DB synchronize. I am excluding all tables and views which exists in the ModelStore (if you have your model DB separate, you might want to change ModelElement to [YourAXDB_model].[dbo].[ModelElement] to get it working). Also we had to exclude a bunch of kernel-level tables and views. Here is a T-SQL statement which could find SQL tables and views not part of the AOT in AX:

SELECT obj.type, obj.name, obj.create_date, sum(au.total_pages)*8 AS [Space_in_KB] FROM sys.objects AS obj
JOIN sys.partitions AS p ON p.object_id = obj.object_id
JOIN sys.allocation_units AS au ON au.container_id = p.hobt_id
WHERE is_ms_shipped = 0 AND obj.type IN ('U', 'V')
AND NOT EXISTS (
	SELECT *
		FROM ModelElement WITH (NOLOCK)
	WHERE ModelElement.Name COLLATE Latin1_General_CI_AS = obj.name COLLATE Latin1_General_CI_AS
		AND ModelElement.ElementType = 44
	)
AND obj.name NOT LIKE 'Model%'
AND (
	(obj.type = 'U'
		AND obj.name NOT LIKE 'AxId%'
		AND obj.name NOT LIKE 'DATA%'
		AND obj.name NOT LIKE 'Layer%'
		AND obj.name NOT LIKE 'PrintJob%'
		AND obj.name NOT LIKE 'SECURITY%'
		AND obj.name NOT LIKE 'Source%'
		AND obj.name NOT LIKE 'SQL%'
		AND obj.name NOT LIKE 'SYS%'
		AND obj.name NOT LIKE 'TIMEZONE%'
		AND obj.name NOT LIKE 'USER%'
		AND obj.name NOT IN ('DEL_ACCESSRIGHTSLIST',
			'DEL_COMPANYDOMAINLIST',
			'DEL_DOMAININFO',
			'DEL_USERGROUPINFO',
			'DEL_USERGROUPLIST',
			'ElementTypes',
			'existingPaths',
			'GlobalFieldIdPool',
			'myModel',
			'myParents',
			'newPaths',
			'PLAN_',
			'SchemaVersion',
			'TABLECOLLECTIONLIST',
			'VIRTUALDATAAREALIST',
			'VSASSEMBLY'
			)
	)
	OR (obj.type = 'V'
		AND obj.name NOT LIKE 'Secur%'
		AND obj.name NOT LIKE 'SysModel%'
		AND obj.name NOT LIKE 'UTIL%'
		AND obj.name NOT IN ('ConfigurationKeys', 'LicenseCodes', 'Origins')
		)
	)
group by obj.type, obj.name, obj.create_date
ORDER BY obj.type, obj.name

SELECT obj.type, obj.name, obj.create_date, sum(au.total_pages)*8 AS [Space_in_KB] FROM sys.objects AS obj

JOIN sys.partitions AS p ON p.object_id = obj.object_id

JOIN sys.allocation_units AS au ON au.container_id = p.hobt_id

WHERE is_ms_shipped = 0 AND obj.type IN ('U', 'V')

AND NOT EXISTS (

SELECT *

FROM ModelElement WITH (NOLOCK)

WHERE ModelElement.Name COLLATE Latin1_General_CI_AS = obj.name COLLATE Latin1_General_CI_AS

AND ModelElement.ElementType = 44

)

AND obj.name NOT LIKE 'Model%'

AND (

(obj.type = 'U'

AND obj.name NOT LIKE 'AxId%'

AND obj.name NOT LIKE 'DATA%'

AND obj.name NOT LIKE 'Layer%'

AND obj.name NOT LIKE 'PrintJob%'

AND obj.name NOT LIKE 'SECURITY%'

AND obj.name NOT LIKE 'Source%'

AND obj.name NOT LIKE 'SQL%'

AND obj.name NOT LIKE 'SYS%'

AND obj.name NOT LIKE 'TIMEZONE%'

AND obj.name NOT LIKE 'USER%'

AND obj.name NOT IN ('DEL_ACCESSRIGHTSLIST',

'DEL_COMPANYDOMAINLIST',

'DEL_DOMAININFO',

'DEL_USERGROUPINFO',

'DEL_USERGROUPLIST',

'ElementTypes',

'existingPaths',

'GlobalFieldIdPool',

'myModel',

'myParents',

'newPaths',

'PLAN_',

'SchemaVersion',

'TABLECOLLECTIONLIST',

'VIRTUALDATAAREALIST',

'VSASSEMBLY'

)

OR (obj.type = 'V'

AND obj.name NOT LIKE 'Secur%'

AND obj.name NOT LIKE 'SysModel%'

AND obj.name NOT LIKE 'UTIL%'

AND obj.name NOT IN ('ConfigurationKeys', 'LicenseCodes', 'Origins')

)

group by obj.type, obj.name, obj.create_date

ORDER BY obj.type, obj.name

The result shows all custom created objects with their creation date and size occupied in the DB, which are good candidates for removal. Here are my entries after running the job for SQL tables and views not part of the AOT. I can see that 4 tables have been duplicated, and a SQL database compression estimation script was also storing data – they can all be dropped:

SQL tables and views not part of the AOT

DAXRunBase / 2016-08-22 / AX 2012 / 1 Comment