locked
MDS Excel transactions RRS feed

  • Question

  • Hi

    I have an Excel sheet connected to an MDS data store that the user utilizes to pull down data and enrich the data with values then save it back to the MDS data store. 

    As part of the MDS application, I need to capture the user input (i.e. transactions) in order to:
    - save the user enrichments for audit purposes so I can see which users added which enrichments
    - archive the user enrichments so that it can be re-instated in the event that the user enrichment data is lost
    - capture when a user make a change to drive the application data e.g. a user sets a flag to 'close' a record such that it does not re-appear on the MDS data the next time the user pulls data from MDS.

    The user enrichments are stored in the following tables:

    [MasterDataServices].[mdm].[tblTransaction]
    [MasterDataServices].[mdm].[tblAttribute]
    [MasterDataServices].[mdm].[tblUser]

    I wrote the following very basic SQL query to pull the data.

    SELECT u.UserName, attr.DisplayName, trx.*
      FROM [MasterDataServices].[mdm].[tblTransaction] trx
      INNER JOIN [mdm].[tblAttribute] attr ON trx.Attribute_ID = attr.ID
      INNER JOIN [mdm].[tblUser] u ON trx.LastChgUserID = u.ID
      WHERE trx.LastChgDTM > GETDATE()-5

    My question is; is this the correct approach?  Is there something out there that can do this in a better way?

    Thanks in advance.



    Friday, December 4, 2015 7:29 PM

Answers

  • Although query on the underlying table is not officially documented and supported, but your approach is correct and is the only option in SQL 2012/2014, since we didn't have a way to export transaction log in the SQL 2012/2014.

    In the SQL 2016, We added slow changing dimension type 2 support. So what you need is export a "Leaf Members SCD Type 2" subscription view. Currently, you can download a SQL 2016 CTP3.1 build to try it out.

    https://msdn.microsoft.com/en-us/library/hh231101.aspx


    Monday, December 7, 2015 7:39 PM