Clean up Dynamics AX modelstore without lossing ID reference in model
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
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.
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
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,
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.