To get all inserted or updated members from a SQL Server MDS database, subscription views are used. Deleted members can’t be found in the views, so another technique must be used.
Before a fact table is loaded, a common pattern is to do a lookup in SSIS before the loading. The lookup can be some key from a dimension for instance, or maybe a repository of primary keys in a MDM solution. But what if the lookup doesn’t return a matching value? Sometimes when this happens, we want to insert new values to the lookup table (to get the key) before the loading of the fact table starts. Here I’ll show one way to handle this scenario.
Some types of data are shared across systems. One of the best examples of data that is shared across systems is customers. They can appear in a lot of different systems, perhaps a CRM system and a sales system. This means there is a risk of conflicting information. If the CRM system has one customer address, and the sales system another, which one is the correct?
In master data management discussions the phrase “single version of the truth” is often used. What it means is that there must be one reliable source for information regarding, for instance, customers.
Not all types of information are suitable for a MDM solution. Borrowing terminology from data warehouse concepts, dimensions can be managed in a MDM solution. Facts are not suitable in a MDM solution and are better managed by the traditional OLTP systems.
A MDM solution is also a good place to centralize data quality improving activities.
A MDM solution has to be architected so that some systems publish information to the MDM solution, and other systems subscribe to the information. Some systems can be both publisher and subscriber.
Note that normally in a MDS solution, each source system has its own copy of the data. There are solutions where the master data is removed from all source systems and they instead have to look up that information from a centralized master data hub. However, those solutions require rather large changes in the source systems that may not be practical.
In this blog post I’ll briefly describe Microsoft’s solution for MDM – SQL Server 2012 Master Data Services, or MDS. I’ll explain the central concepts in MDS, but I won’t show the steps to actually use MDS. That may be another blog post.