Versioning of Members within a Model


  • I am new to MDS in 2012, but I looking into how well it could work as our PDM/PIM system.  Currently we use excel extensively, and have a combination of excel integration and manual entry from excel into our different systems, such as Quality, Manufacturing, Accounting/Purchasing.  Our goal is to have a single repository for all product data.  We would be comfortable using the Excel add-on so that the couple of data admins could upload/change data.  Everyone else, and our different systems would use the data subscription views. 

    My concern, or lack of understanding relates to versions.  It seems like the model (which I equate to as a database/table holding all product records) is what is versioned, rather than a specific member  (which would be a single product).  Is that the case?  If we have 2 product changes a week, I'd like to only change the version on those two products not on the whole model.

    Is that possible?  Perhaps the better question, Is MDS in 2012 ready to be a PIM/PDM?  I know there are lots of PIM/PDM products out there, most seem to have more functionality, and cost than we need.



    Donnerstag, 21. Februar 2013 13:40

Alle Antworten

  • Model Versioning

    -addresses how the model as a whole is published to the enterprise

    -lets you take periodic snapshots of a whole model

    -enables you to purge historical change data after a while

    MDS also tracks attribute-level history for all entitiy members.  So you change a Product.Description, it keeps a record of who, when and why that change was made.

    For entity member versioning, that's one of the many things you will need to "bake in" to your entity design.  For instance you can add a ProductVersion to track the version and ProductVersionStatus to drive workflow around version changes and wire it together with Business Rules.



    Donnerstag, 21. Februar 2013 13:48
  • Aaron,

    MDS 2012 is, in my opinion, a good technology for a PDM/PIM system. I know MDS is used to build PDM/PIM systems.

    A model is not a database: you can have more than one model in an MDS database but, as far as I know, you cannot create links between models out-of-the-box. The model metadata -i.e., the list of entities, its attributes and business rules are stored on a set of common/system tables. The actual data is stored in tables created by the tool and associated to a model. You can argue that, from a data point of view, a model is collection of tables in the MDS repository/database.

    Because more than one model is supported, models can be used to partition independent sets of data.

    A model version tags both data in a model (but not all data, explanation to follow) and the metadata for that data. Within the same model you can have a 2011 product catalog (closed), a 2012 product catalog (open) and a 2013 product catalog (work-in-progress), with different data and different data structures, MDS should be able to handle this. It does so by having a 2011, a 2012 and 2013 version of the model stored in the database. 

    If you have 2 product changes a week on an open product catalog, you do not need to re-version the whole model, you just change the products that need changing. In this scenario, the 3 questions i would ask the business are:

    a) do we need to keep a historical audit trail for each product changed?

    b) can we overwrite an existing product (and loose its previous details).

    c) does the business need more granular versions, for each individual member?

    How this 3 questions are answered partially dictates the model design.

    With very limited knowledge of your business required, I cannot see a good reason to version each member. But I can see a good reason to keep a historical audit trail of changes to products and a way of closing previous 'variants' of a product that is no longer offer.

    Hope this helps.


    • Bearbeitet M Vega Donnerstag, 21. Februar 2013 14:11
    Donnerstag, 21. Februar 2013 14:09
  • Thanks for the quick replies from both David and M. Vega.

    I'll answer M. Vega's questions first. 

    a) Yes.  This is strickly for change control and auditing purposes. Knowing who did what, when is requried.

    b)No.  The reason being that if we want to look at Quality results from a year ago, we need to make sure that we are also looking at the specifications for that test, and also what the required testing was from a year ago. If the specification has changed, and we only look at the current spec the historic data may appear to be bad when in fact it was okay at that time.  Another example might be if we change the pack quantity of a product over time.  If today our product ships in boxes of 20 pieces, perhaps last year it shipped in boxes of 50.  If I want to run a report based on the number of boxes shipped to show the number of pieces we have shipped over time, I need to take into account the change in Master Data.

    c)The requirement is that we can produce a view of what the member looked like at a point in time.  It doesn't really matter how that requirement is met.


    Do those answers help clarify the requirement?  It might be that the MDS should only hold our "current" version at that historical versions need to be kept seperate.  My concern with that is over time we will add new entities and attributes, and I dont want to have to made coding changes to a SSIS package / ETL to populate the historcal product system.

    In regards to David, If there is no built in Member versioning, we would have to create a entity called "Version" with atrributes of a version number, date created, dateapproved, userid, etc that I would ues to drive a business Rule / workflow.  The workflow perhaps could copy the current version and create a new version of the member. 

    Thanks again for the replies. 


    Donnerstag, 21. Februar 2013 16:04
  • Do those answers help clarify the requirement?  It might be that the MDS should only hold our "current" version at that historical versions need to be kept seperate.  My concern with that is over time we will add new entities and attributes, and I dont want to have to made coding changes to a SSIS package / ETL to populate the historcal product system.

    This is a very common pattern.  MDS handles the current/"work in process" version of each entity member, and a table in SQL Server holds the published version along with historical versions.  This is very common when MDS is managing dimension tables for a data warehouse.  The downstream warehouse maintains the dimension using a Type II SCD pattern, and validated entity members are published to the warehouse.  This also allows you to publish entity members only after they have been validated by all your business rules, or are past some state in their lifecycle (eg InProduction Products, but not Proposed Products).

    I would put the downstream database on the same SQL Instance as MDS, that way you can write and maintain a simple Stored Procedure to pull data from the MDS Subscription Views and use a TSQL Merge statement to upsert the target and maintain the SCD records. 

    You could potentially generate the procedure if your model changes are frequent, but I wouldn't initially.  One of the other benefits of doing this is that you don't have to publish all the attributes to the downstream table.  Your MDS entity definition will have several attributes that are only relevant for data stewardship, like ValidationStatus, ApprovalStatus, ApprovalDate, LastChangeDate, etc.  And you'll want to trim those, and perhaps change some attribute names.  So maintaining that mapping layer gives you some real utility in return for maintaining it as your model designs change.



    Donnerstag, 21. Februar 2013 16:22
  • I agree with David, this is a very common MDS design pattern.

    Whether you need a data warehouse/database to store the history of your products depends, in my opinion, on 2 metrics: how many products you have and how many years of history you need to keep. I could not make a recommendation without more information but, although I have a data warehouse background, my first inclination is always to keep as much information as possible in the MDS repository.

    By products, I mean SKUs and not generic products, if you sell the same generic product as 20 pieces, 50 pieces and 100 pieces, this is really 3 different SKUs - e.g. products, with, I hope, different product codes.

    Something to keep in mind is what happens if you sold a product as 20 pieces per pox and you change it to be 50 pieces per box cancelling the previous product. Do you keep the same SKU or do you generate a new one? Strictly speaking, if the former you really have a new product/sku with a lineage back to the 20 piece per box product (this is ideal from a modelling point of view), and if the later you have the same product/sku changing specification (this is trickier from a modelling point of view).

    The specification is, as you pointed out, very important. If the specification is as simple as pieces per box, grams per box, liters per container, or width-depth-length,  then you can model it as part of a product/sku entity. If your specification is more complex, such a list of ingredients and/or a chemical formula, your model becomes more complex and you will need to model the specification as separate entity or group of entities) and will have to synchronize products and specifications. 

    If you need a custom workflow, I recommend to use attributes in each entity to handle this. In other words, I would have in each entity attributes such as Submitted By, Submitted Date, Approved By, Approved By, Rejected By and Rejected Date, and a Version Number that auto-increments for each member. In addition, you might want an entity to hold the current/latest workflow status. I do not feel comfortable with a Version entity to handle your workflow, it could become too complicated.

    In my opinion, the biggest challenge you have is what happens with your product/sku codes each time a product changes. If a change = new SKU your model is relatively straightforward, of a change = re-using  an SKU (even if this does not happen all the time), you have a complex model in your hands.

    Kind regards,


    • Bearbeitet M Vega Donnerstag, 21. Februar 2013 17:32
    Donnerstag, 21. Februar 2013 17:30
  • I'm very impressed with the quick, and useful responses in the forum.

    If I keep the historical products in the downstream table, and use SP / Package to load the data from the MDS Subscription Views, the IT group will need to be invloved each time a new entity or attribute is added to MDS that we want to keep in the downstream table.  They would have to modify the SP / Package and create the new tables/columns in the downstream tables to hold the new data).  I worry that we would be adding a fair number of entities and attributes over time, as each new product would have different attributes.   One of my goals is to allow the Data team to not be reliant on an IT development request each time they want to add somthing new.


    Donnerstag, 21. Februar 2013 19:28
  • True, if your Data team doesn't do ETL and database work.  But adding an entity or attribute that is visible to downstream systems will require cross-team coordination anyway and probably IT involvement.  You could put the downstream tables and proc/pakages on a monthly or quarterly release cycle and update your model with any number of non-breaking design changes in between.



    Donnerstag, 21. Februar 2013 22:12
  • As I hinted in a previous reply you can keep everything within the data team -  i.e., within the MDS data repository - if you don't have a big product catalog and you don't need to keep many years of history. To quantify, if you have 1,000 products (read SKUs), 5 changes per week (260 changes a year), and you need to keep 5 years history, you can keep everything within the data team -  i.e., in the MDS data repository - and you do not need a downstream database to archive history. However, if you have 20,000 products (read SKUs), 100 changes per week (5,200 changes a year) and you need to keep 10 years of history, you might need to consider having a downstream database to archive history. Please note I said 'might', it is possible to store more than 200,000 members in an MDS database without having too many problems.

    In 3 short sentences:

    • it depends on how much data you need to store
    • we cautious with downstream databases for archival of history
    • don't' solve problems you might not have (i.e., archival of history).


    Freitag, 22. Februar 2013 08:32