Clean up Dynamics AX modelstore without lossing ID reference in model

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

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

 

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)

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

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.

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.