locked
MDS view transactions UI does not correctly display history when Code change RRS feed

  • Question

  • The view transactions window on both the excel add-in and web UI do not display transaction history correctly when code change. Is this a bug?


    How to reproduce:

    1. Enter a new member with Code A and some other attributes. Click on the view transactions (in Excel add-in or web UI) and we see the transaction history.
    2. Change the Code to B on the same member. The view transactions window now shows empty. History of additional changes made to other attributes will show up fine but not the history captured when Code was A.
    3. Execute the mdm.viw_system_transactions view in the database on the member and it correctly shows all the transaction history including the code change from A to B.

    I believe that this is caused by the UI using Code as a filter for displaying transaction history. It should use the internal member id because it does not change.


    When we permanently delete the member through staging (ImportType 6) and later add a new member with either Code A or B, the previous history of A or B shows up on web UI / excel add-in with a new create member transaction after the previous member history. But if you execute the mdm.viw_system_transactions view in the database, it again correctly shows the transaction history on the new member with a new internal member_id.


    Please help because the UI is not correctly displaying transaction history.

    • Edited by May Chen Friday, April 12, 2013 12:49 PM formatted to read better
    Thursday, April 11, 2013 2:05 AM

Answers

  • As far as my understanding goes, the "Code" column is the identifier column (analogus to a businesskey/primary key) of the data you're trying to maintain using MDS. If you change the Code value from A to B, that becomes a new key in itself and hence gets treated as a brand new record. Think of it as a PK/FK analogy. If you enforce the PK of a row to be changed to something else in a lookup table (ofcourse after you disable FK relationships), you'll have no way to find a successful match from the referencing table (which had the FK column in it) using the older value.

    I'd highly recommend that you discourage/prevent Code values from being updated by the user. If they need to input a new code value, they should do so by adding a new row using the excel add-in and then publish that change. You can then soft-delete the older row via your ETL that syncs MDS updates back to your datawarehouse.

    Hope this helps!

    Cheers!

    Muqadder.

    Monday, April 15, 2013 8:04 PM
  • The following is what we found:

        Microsoft MDS uses an internal member id as PK/FK, saving us the trouble to create them. This internal member id is also returned via subscription views. Using member id to do joins with other entity views is faster than using Code.

        We are using business codes rather than the MDS auto generated codes because the latter creates lots of complexity for the business users who use excel add-in to bulk load data (many of which are domain-based attributes).

        Business codes do change due to mistakes in data entry and must be corrected. However, that does not mean to create a new record. In fact, the mdm.viw_system_transactions view correctly shows a change to the existing record with the same member_id. When the entity is used as domain-based attribute by other entities, members of the other entities also correctly show the new changed code B (because member id is used as PK/FK, not code).

        It is only the UI that does not display transactions correctly.  

    Microsoft,

         Could you please correct the discrepancy in the UI? The UI should display transaction history that is consistent with the mdm.viw_system_transactions view.  

    Thank You in Advance



    Thursday, April 18, 2013 12:13 AM

All replies

  • As far as my understanding goes, the "Code" column is the identifier column (analogus to a businesskey/primary key) of the data you're trying to maintain using MDS. If you change the Code value from A to B, that becomes a new key in itself and hence gets treated as a brand new record. Think of it as a PK/FK analogy. If you enforce the PK of a row to be changed to something else in a lookup table (ofcourse after you disable FK relationships), you'll have no way to find a successful match from the referencing table (which had the FK column in it) using the older value.

    I'd highly recommend that you discourage/prevent Code values from being updated by the user. If they need to input a new code value, they should do so by adding a new row using the excel add-in and then publish that change. You can then soft-delete the older row via your ETL that syncs MDS updates back to your datawarehouse.

    Hope this helps!

    Cheers!

    Muqadder.

    Monday, April 15, 2013 8:04 PM
  • The following is what we found:

        Microsoft MDS uses an internal member id as PK/FK, saving us the trouble to create them. This internal member id is also returned via subscription views. Using member id to do joins with other entity views is faster than using Code.

        We are using business codes rather than the MDS auto generated codes because the latter creates lots of complexity for the business users who use excel add-in to bulk load data (many of which are domain-based attributes).

        Business codes do change due to mistakes in data entry and must be corrected. However, that does not mean to create a new record. In fact, the mdm.viw_system_transactions view correctly shows a change to the existing record with the same member_id. When the entity is used as domain-based attribute by other entities, members of the other entities also correctly show the new changed code B (because member id is used as PK/FK, not code).

        It is only the UI that does not display transactions correctly.  

    Microsoft,

         Could you please correct the discrepancy in the UI? The UI should display transaction history that is consistent with the mdm.viw_system_transactions view.  

    Thank You in Advance



    Thursday, April 18, 2013 12:13 AM