Office Manager Budget View

vRecently in a project for an Insurance Company, we had the opportunity to implement an MDS solution to prevent the administration and integration of nearly 80 Excel spreadsheets.

Our MDS solution has 4 models, the General, Accounting, Life and Non-Life models. The general is used for support and mapping purposes and it rarely changes over time, whereas the other models are constantly changing, either by updating or inserting new values.

Since the requirements are a living organism, today we are loading into the Data warehouse the last open versions of the aforementioned models. One of the requirements that still endures from day one is the manageability of the models and versions, and our client isn’t very fond of the UI provided by the MDS system.

In order to ease his pain, we developed a Stored Procedure that by executing the MDS’s SP and sending the name of the model and the new version’s name to our SP it creates a new empty version. This last step is very import in our system, because as you might notice, when you copy a version into a new one, the old values come attached. Next I will try to explain the procedure:

As I said before a living organism, in the first version of this code, after all the changes and deletes and copies, we should lock the previous version in order to prevent further updates to the entities, value 2 (locked).

As you might notice I am using the staging process to delete the records from Master Data Services that is why I am truncating all staging leaf tables, for each entity in the model and inserting into those table the records I want to be hard deleted. Every entity has its own leaf table and its own stg.udp_Leaf procedure, in this case used for ‘Hard Delete’. You can find out more about this the MDS staging process here: https://msdn.microsoft.com/en-us/library/ff487040.aspx.