This tip comes directly from my esteemed colleague Noah Teal. Thanks for sharing, Noah!
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.
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.
Data backup-restore of data db after Model delete and XPO import in target layer.
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.
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.
AxUtil Model import with conflict push to another layer.
Export custom Model. Import same Model with conflict push to target layer. Delete custom Model from original layer.
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.)
Create a Job that compares Model Store Table and Field Id’s to existing SQLDictionary values and update differences.
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.
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!