Clean up Dynamics AX modelstore without lossing ID reference in model

9. July 2015

Working with AX2012 has many times left you with a model store that looked like a mess. Various elements was accidently placed in the wrong layers and models. In AX2009 the only way to cleanup, this was to Export -> Delete -> Import the elements.

This is partly the same story in AX2012, although moving between models in the same layer is only part of metadata manipulation.

The biggest problem on an existing application is that the element id is changed executing the Export -> Delete -> Import routine. In AX2009 you could export this “With ID”, and then import with same ID, but this feature does not exist anymore.

In the AXUTIL tool, you can find a feature to keep ID, but I found this very difficult to use for cleaning up. However, I found another way that can move elements between layers without losing the ID on elements that I want to share here.

! Remember that this is not supported by Microsoft and is executed on your own risk

There are three main scenarios, you want to consider when you clean up Dynamics AX modelstore without losing ID, moving elements between layers

Security elements

For reasons unknown the security elements “Security Roles” in Dynamics AX is treaded different from other elements in the application. The root element is part of the Model store as any other elements but the SUB roles, duties etc. is handled separately hence the solutions is slightly more complex

AOT Elements

Standard AOT elements is defined as classes, forms, tables etc. When cleaning up these elements the main concern is if these has been changed in more layers at the same time, or only exist in one layer

ISV Layer elements

When you purchase an ISV solution, you will potentially get this in the ISV layer where you have no access. In case the ISV Partner closes the business, you can be stocked with elements in the ISV layer you cannot change. This can be a big problem for you. In addition, you could meet someone that delivers a solution in the same layer that you are doing you development in and you want avoid any conflict.

Security Elements

In this example i have created a new Dynamics AX Role called MyNewSecurityRole. I have added Duty, priviligies and a subrole to this role

Also i have added a Table as well to the Role

The important thing to remember here is that the Model Database contains many tables with all these metadata informations. Below find a list of tables in the model DB that you should consider. Based on what security that has been added to the element more or less of these elements must be updated likewise

 

 

  • ModelSecPolRuntimeEX
  • ModelSecurityCommon
  • ModelSecurityPolicy
  • ModelSecurityPolicyConstraint
  • ModelSecurityPolicyTable
  • ModelSecurityRole
  • ModelSecuritySubRole

All of these tables includes reference to the layer and to the Element handle and must be updated accordingly.

Listed here is the procedure used to move this element from USR to VAR

Select * from ModelElement where name = ‘MyNewSecurityRole’

This gave me the Element Handle needed to find all the related elements: 979505

Select Modelid,* from ModelElementData where ElementHandle = 979505

This resulted me to find the ModelID and the LayerId of my Element. Remember that this element was created in the USR layer and not inherited from any other layer. We now have the Layer=14 and Model=15

Select * from layer

Returns a list of layers and ID and i find that i want to move this to “VAR” Layer ID = 10

Select * from ModelManifest

Returns a list of Models and their ID, and I find that I want to move this to 11 = VAR Model

The below script eas executed on my element and this script succesfully moved the USR element to the VAR layer.- You have to restart the AOS server for the application to show the correct layer in the AOT

DECLARE @handle AS BIGINT
DECLARE @ToLayer AS INT
DECLARE @ToModel AS INT
SET @handle = 979505
SET @ToLayer = 10
SET @ToModel = 11
ALTER TABLE ModelElementData NOCHECK CONSTRAINT ALL
ALTER TABLE [ModelSecurityPermission] NOCHECK CONSTRAINT ALL
ALTER TABLE [ModelSecuritySubRole] NOCHECK CONSTRAINT ALL
ALTER TABLE [ModelSecurityRole] NOCHECK CONSTRAINT ALL
ALTER TABLE ModelElementData NOCHECK CONSTRAINT ALL
UPDATE [ModelSecurityPermission] SET OWNERLAYER = @ToLayer where OWNERHANDLE = @handle
UPDATE [ModelSecuritySubRole] SET ROLELAYER = @ToLayer where ROLEHANDLE = @handle
UPDATE [ModelSecurityRole] SET ROLELAYER = @ToLayer where ROLEHANDLE = @handle
UPDATE ModelElementData SET LayerId=@ToLayer, ModelId=@ToModel where ElementHandle = @handle
ALTER TABLE ModelElementData CHECK CONSTRAINT ALL
ALTER TABLE [ModelSecurityPermission] CHECK CONSTRAINT ALL
ALTER TABLE [ModelSecuritySubRole] CHECK CONSTRAINT ALL
ALTER TABLE [ModelSecurityRole] CHECK CONSTRAINT ALL
ALTER TABLE ModelElementData CHECK CONSTRAINT ALL

AOT Elements

VARTable

When working with “normal” AOT elements it is simpler, the only consideration is if the element has been change on multiple layers. I have created an element in the VAR layer and modified it in the USR layer as shown below

 

I will now demonstrate how to collapse this into the VAR layer.

We need to find the Element Handle:
Select ElementHandle from ModelElement where name = ‘MyTableinVAR’

Then we need to find all related Elements:
Select * from ModelElement where RootHandle = 979506

 

RelatedElements

As expected this shows the element we created

We are interested in the elements that are in the USR layer on this rootelement, for this we execute the following SQL:

SELECT dbo.ModelElement.ElementType, dbo.ModelElement.RootHandle, dbo.ModelElement.ParentHandle, dbo.ModelElement.ElementHandle, dbo.ModelElement.Name, dbo.ElementTypes.ElementTypeName,

dbo.ModelElementData.ModelId, dbo.ModelElementData.ElementVersion, dbo.ModelElementData.SAVECOUNT, dbo.ModelElementData.VERSION, dbo.ModelElementData.BASEVERSION,

dbo.ModelElementData.LayerId

FROM dbo.ModelElement INNER JOIN

dbo.ModelElementData ON dbo.ModelElement.ElementHandle = dbo.ModelElementData.ElementHandle LEFT OUTER JOIN

dbo.ElementTypes ON dbo.ModelElement.ElementType = dbo.ElementTypes.ElementType

WHERE (dbo.ModelElement.RootHandle = 979506) AND (dbo.ModelElementData.LayerId = 14)

ElementUSR

This give us elementhandle 979509 / 10

Executing these SQL
Select Modelid,* from ModelElementData where ElementHandle = 979509
Select Modelid,* from ModelElementData where ElementHandle = 979510

And we find that 09 has 2 records (one for VAR and one for USR), whereas 10 only have the USR layer

ElementUSR1

The trick !

When you change anything from a lower layer in a higher layer, in this case VAR and USR, the full record is copied from VAR to USR. As the Unique clustered index is LayerId, ElementHandle it is not possible to just update the USR element with the LayerID for VAR.

But you can delete the VAR Element an then make the USR element you primary element, and that element will contain everything

So executing the following three SQL the result is that my Element now is full in VAR

DELETE ModelElementData where ElementHandle = 979509 and LayerId = 10

UPDATE ModelElementData SET LayerID =10, ModelId=11 where ElementHandle = 979509

UPDATE ModelElementData SET LayerID =10, ModelId=11 where ElementHandle = 979510

Remember you will not see this in the application updated before you have restarted the AOS Server

As you will notice from the below image, all elements is now placed in the VAR layer.

OnlyInVar

ISV Cleanup

The same procedures goes if you want to move elements up from ISV layer to VAR layer in order to be able to make changes to an ISV solution, that is no longer supported.

You will realize that the Model Store does not really care where the Elements are placed. The application will always pick the TOP 1 based on the Layer, Handle key.

It is actually possible to move elements in and out of any layer from the backend.

It is however, not recommended to execute this operation on any layer that is supported either by Microsoft or any ISV vendor. However, if you are repairing on a customized application with many elements sometimes it just might be a good idea to move the whole existing codebase into the VAR layer, and start from a fresh in the CUS layer. This way you can always track what you did compared to the original application.

Leave a Reply

Axvice