Database Design Patterns/Considerations when leveraging Master Data Services

Question

I'm trying to find a good source of information on the database and/or table design considerations/patterns to be aware of when designing a data warehouse and leveraging MDS/DQS components...things like adding "current flag", "created date", "end date", "last updated date" for slowly changing dimension tables...but MDS-specific.

In a few articles/blogs on MDS, there's been a comment or two about "once your MDS model is in production periodic updates should be scheduled that apply any changes to the master data made in MDS to the production tables in your DW".

For example, as a developer, are there any additional columns I would want to include in my dimension tables like a flag indicating that the data in this record was updated via MDS-update-process? Or perhaps a separate database schema to track MDS-related changes with FK-contraints to the production tables?

Also, I don't have enough rep to create a new tag, but it might be helpful to have a [Master-Data-Services] or [MDS] tab

Answer 1

at one of my project we used MDS as a central repository for enities that required data steward attention. At each data warehouse refresh ETL pumps pulled data from source systems and MDS as well. MDS historical data was kept in data warehouse by utilizing slowly changing dimension process.

In MDS' subscription view there is a time stamp for last record change time so you should be able to recognize changed members easily.

Hope this helps.