Master Data Services (MDS) is designed to solve inconsistent data issues by providing an authoritative data source...
for customers who use multiple database types. But what happens if an organization has several different databases that contain customer contact information and that information changes?
For instance, a customer may change his or her phone number. If the various databases are truly disparate, then the phone number might get updated in one database but not in another, which results in inconsistent data.
MDS is intended to address this problem by allowing solution providers to create a repository or hub for master data. That way, applications can be configured to pull data from a single authoritative source rather than from a dedicated database that may not be up to date.
But that doesn’t mean an application would no longer be able to use a dedicated database. MDS is typically used only by multiple applications for static data. Any data that is application-specific or highly transactional should continue to reside in its present location.
Getting your customers started with the MDS can be a big job for solution providers. First you need to work with customers to determine which data should be included in the MDS. You can guide your customers by explaining that they should include data that is used by multiple applications. But it will ultimately be up to them to make the final decision on selecting the data and to deal with any existing contradictory data.
Setting up MDS in a lab environment
Before implementing MDS for your customers, spend some time setting up the MDS in a lab environment so that you can get used to the process.
Implementing MDS involves building a model and then importing data into that model. Building a model is easy because it’s similar to creating a database from scratch. But the process for importing data into the newly created model takes some getting used to.
When you are finally ready to implement the MDS, don’t be overly ambitious with your customer. Start out by creating a small model, and make sure that both you and your customer are comfortable with the MDS before you implement it on a larger scale.
Building a Model
To create a model, launch the MDS console and then click on the System Administration option. Next, select the Models option from the Manage menu and then click on the plus icon to add a new model.
When you create a model, you will be prompted to provide a model name. There are also three check boxes that should normally be selected. These check boxes include:
- Create entity with same name as model
- Create explicit hierarchy with same name as model
- Include all leaf members in mandatory hierarchy
After creating a model, you can begin creating entities. Each entity will have two attributes that are created automatically. The attributes are Name, which is the name of the entity, and Code, which acts as a primary key for the entity. Use the Add Leaf Attribute option to create additional attributes for an entity.
Now that the model is in place, it’s time to move your customer’s master data into the model. There are three ways to accomplish this:
- Enter the data manually with the MDS interface. This option is makes sense only if you are starting from scratch or if you have a very small customer data set.
- Perform the data integration programmatically, which requires you to write a small program that reads data from an existing database and inserts it into the newly created model. Depending on your development skills, this may be the easiest of the three options.
- Create a series of staging tables, and then use a batch import process to import the data.
Using subscription views to link apps to MDS
The last step in the process is to re-configure your customer’s applications to use master data wherever possible. This requires a bit of programming, and the process involves more than just linking the application to MDS tables. In fact, you should avoid linking an application directly to MDS tables because future versions of the MDS will likely use a different schema, which could cause applications making direct-table queries to break.
The preferred method for linking an application to the MDS is to use subscription views, which provides an easy way to access master data through the Integration Services or through SQL scripts. Subscription views are appealing because they use recognizable column names, and that make the data much easier to work with.
When implementing the MDS, keep in mind that SQL Server treats master data differently from other types of data, so you will need to train your customers on how to maintain master data. You may also find it beneficial to create a series of rules that are designed to check for unwanted data before it is inserted into the master data.
About the expert
Brien M. Posey, MCSE, is a six-time recipient of Microsoft's Most Valuable Professional award for his work with Exchange Server, Windows Server, Internet Information Services, file systems and storage. Posey has served as CIO for a nationwide chain of hospitals and was once responsible for the Department of Information Management at Fort Knox. As a freelance technical writer, he has written for TechTarget, Microsoft, MSD2D, Relevant Technologies and other technology companies. You can visit his website at www.brienposey.com.