Tips and tricks: Moving customizations without losing element IDs

This tip comes directly from my esteemed colleague Noah Teal. Thanks for sharing, Noah!

THE PROBLEM:LostId

How do I move objects from VAR layer to CUS layer WITHOUT losing Element Id’s?

THE INTERIM UPDATE:

I tried several things, mainly involving exporting all mods in one layer as an XPO, importing the XPO in to a higher layer (USR = high; SYS = low), then deleting the mods from the original layer. Remember that Models are specific to layers, so they can’t be imported in to a different layer. The result of each of the various scenarios were the same: didn’t work. The CUS layer never contained the full object.

Scenarios: Moving VAR mods to CUS layer, then delete VAR mods.

1)      Export VAR, import CUS directly without modification. (No difference in VAR and CUS.)

2)      Export, modify VAR objects, import directly. (VAR difference in CUS.)

3)      Modify VAR objects, export, remove mods from VAR objects, import CUS.(CUS difference in VAR.)

AX smartly determines that CUS layer is the same as VAR and does not import/modify CUS objects. In Scenario #3, the CUS only showed the new object elements (class method, table field); therefore, when the actual object was deleted from VAR, CUS only had an “unknown object reference” left.

My next step is to see what happens when I modify the layer specification of the Model and Elements in the Model Store of the db. Danger! Danger! Update to follow.

THE SOLUTIONS:

Here are the results of testing the three scenarios offered:

1) Model XPO export-import with data backup-restore;

2) AxUtil export-import with conflict push to new layer;

3) Use a Job with that calls Class\ReleaseUpdateDB to update SQLDictionary. An example of this was found at http://dev.goshoom.net/en/2011/11/id-change/

 

I ran the scenarios with minimal number of objects, so more testing should be done with a full set of customizations.

Note: Only the Table Id’s matter when Element Id’s change since AX tracks and synchs SQL table definitions by Table Id and Field Id. Changing those properties will cause data loss when AX recreates the tables in SQL Server. Note: Stopping/starting the AOS for Model deletion is assumed in the option procedures.

Option 1:

Data backup-restore of data db after Model delete and XPO import in target layer.

Procedure:

Export XPO of Model with all customizations. Backup the data db. Delete Model. Import XPO in to the target layer, new Model. Restore the data db.

Results:

Inconclusive with a small data set, but won’t work based on what I know, now. There are several methods for exporting and importing SQLDictionary data, but the result is still the same. Restoring the old data db – or just SQLDictionary table – after the custom objects are moved to different layer restores SQLDictionary to its old values, which don’t match what is now in AX. The AOS manages creation of new Element Id’s/Table Id’s and stores in Model Store. SQLDictionary only stores table metadata based on what is defined in the Model Store. AX looks up the Table Id and Field Id in the Model Store, checks SQLDictionary if those table id’s and field id’s exist, then creates them in the business data db if they don’t exist. This is why SQL Server throws an error, “Table XXX” already exists”, when the Table Id’s don’t match, but there is an existing table with the same name. The condition is that the Model Store and SQLDictionary are out of synch.

Option 2:

AxUtil Model import with conflict push to another layer.

Procedure:

Export custom Model. Import same Model with conflict push to target layer. Delete custom Model from original layer.

Results:

Doesn’t work. AxUtil sees the import Model matches an existing Model name, id, layer, version, etc.. Therefore, the user is prompted to replace the existing Model in the source layer, which is an overwrite. No conflict Model is created. (I tried changing Model manifest so AxUtil sees them as diff Models, but could not change the details.)

Option 3:

Create a Job that compares Model Store Table and Field Id’s to existing SQLDictionary values and update differences.

Procedure:

Export custom Model as XPO. Stop AOS. Delete Model. Start AOS. Import the XPO in to the target layer and Model. Run the Job to update SQLDictionary to match the new Model Store Table and Field Id’s.

Results:

Worked! Since at no time was a data dictionary synch executed nor the AOS stopped, SQLDictionary was never updated and no data lost. Essentially, this process synch’s SQLDictionary to the Model Store without recreating the tables in SQL Server.

Janet here: Happy DAXing!

Advertisements

About janeteblake

Dynamics AX developer
This entry was posted in AX2012, Dynamics AX. Bookmark the permalink.

3 Responses to Tips and tricks: Moving customizations without losing element IDs

  1. Hi Janet, thank you for this informative post. I have one brief comment to add on your option 3, in case it is not obvious to any of your readers: Option 3 is basically what I do in this scenario as well, but bear in mind that this does not keep your original element IDs. Because you have removed and re-created the elements, if the element did not already exist in a lower layer, it will get a newly assigned element ID as it is re-created by the XPO import. Option 3 does of course prevent the fact that they have likely changed from causing problems during database synchronization. The other caveat to be aware of is that there are some table fields which contain element IDs, so if you have changed the corresponding ID, you now have a data consistency problem and need to replace those old element IDs with their new equivalents (one example of this is security role assignments, another is new financial dimensions based on existing tables, i.e. not just a dimension with custom values). Fortunately there are not many places where this happens, but you have to keep your eyes open for it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s