locked
Is there a way to see how data changed from one version to another? RRS feed

  • Question

  • Hello,

    I need to see how data changes in different versions of the same model, e.g. in VERSION_1 the attribute value was '123', in VERSION_2 - '321' and so on. Is there a way to do so?

    Any help would be appreciated!

    Thanks in advance,

    Vitaly.

    Thursday, October 27, 2011 4:32 PM

Answers

  • We need to define what it is you're trying to do -

    In Master Data Services - "Attributes" means "Fields" or "Columns"

    In MAster Data Services - "Entity" means "Table"

    Records seem to still be "Records.

    The tough part is finding which records have changes, and Binary_Checksum works very well as illistrated above, when only the fields of interest are included in the calculation.

    THEN - take the records that are output and screen for the record attribute that changed.

    If you want to go direct - use the metadata to identify the table for the Entity, then use the tblVersions to identify the Version_ID and compare row by row based on the field position, but reflecting back to what the various attributes and domain IDs mean is problematic at best - take a peek and you'll understand.

    One of the benefits of using the subscription approach, which simply creates views of the data, is that to shift from one version set to another on the same entity is as simple as administering the subscription and re-running the query.

     

    Tony

     


    Richard A. "Tony" Eckel Rochester, NY
    • Marked as answer by irgendwo Friday, October 28, 2011 8:02 AM
    Thursday, October 27, 2011 7:21 PM

All replies

  • Vitaly,

     

    Ralative to "Data" (not records)

     

    Create two subscriptions - one called "Old" the Other "New" (or what ever you want...)

    set Old to Version_1, New to Version_2

    Open SSIS and create a query agains the "new" subscription using the Script as Select... against the Subscription View - into a new query window.

    Edit out all the non-Data fields ( the ID, the time stamps, the user referances - you can figure it out...)

    Using that as the base, create a query like:

     

    Select N.*, O.*

    from dBase.Schema.NewSubscription as N join dBase.Schema.OldSubscription as O on N.Code = O.Code

    where Binary_Checksum(N.Field1, N.Filed2, N.Field3, ... ,n.Fieldn) <> Binary_Checksum(O.Field1, O.Filed2, O.Field3, ... ,n.Fieldn)

     

    Caveat - Binary Checksum has a few Problems with short length fields and can return the same value for multiple letter sequences. However - without actually creating a sequence of ((N.field1 <> O.field1) or (N.field21 <> O.field2) or ... (N.fieldn <> O.fieldn) this is the easist way to go...

     

    Tony


    Richard A. "Tony" Eckel Rochester, NY
    Thursday, October 27, 2011 6:06 PM
  • Sorry, looks like  I wasn't specific enough in my previous post. By saying "Data" I meant exactly "Records", e.g. the attibute values.

    I guess creating subscriptions for different versions and comparing attribute values instead of binary checksums could be a workaround. Though, I'd like to know if there is any other way to perform comparison without creating subscriptions? It's likely that I'll need to compare the whole historical array of records for specific entity and creating subscriptions for each and every version, even for those which are not used anymore, is not the best idea in this case.

    I think keeping track of changes between different versions is a common task and would like to know if it could be done in a more straightforward way.

    Anyway, thanks a lot for your reply and idea!

    Thursday, October 27, 2011 6:39 PM
  • We need to define what it is you're trying to do -

    In Master Data Services - "Attributes" means "Fields" or "Columns"

    In MAster Data Services - "Entity" means "Table"

    Records seem to still be "Records.

    The tough part is finding which records have changes, and Binary_Checksum works very well as illistrated above, when only the fields of interest are included in the calculation.

    THEN - take the records that are output and screen for the record attribute that changed.

    If you want to go direct - use the metadata to identify the table for the Entity, then use the tblVersions to identify the Version_ID and compare row by row based on the field position, but reflecting back to what the various attributes and domain IDs mean is problematic at best - take a peek and you'll understand.

    One of the benefits of using the subscription approach, which simply creates views of the data, is that to shift from one version set to another on the same entity is as simple as administering the subscription and re-running the query.

     

    Tony

     


    Richard A. "Tony" Eckel Rochester, NY
    • Marked as answer by irgendwo Friday, October 28, 2011 8:02 AM
    Thursday, October 27, 2011 7:21 PM
  • Indeed, looks like subscription approach is preferable, at least for now. 

    Direct approach seems to be too complicated at first glance, maybe I'll try to look into it later. As far as I understand there is no manual or article on how to use this method for this or related tasks? At least I wasn't able to find anything similar.

    Thanks a lot for your help!

    Thursday, October 27, 2011 7:54 PM