Implementing an Master Data Management Solution With SQL Server Master Data Services and the MDS Utilities (Part 1 Creating the Model)
I’ve been working with a number of customers to implement SQL Server Master Data Services, and found some common requests from customers that require some custom code to implement. Over the course of those engagements I wrote a set of utilities for MDS that are now published on CodePlex https://mdsutilities.codeplex.com. The questions I kept getting were:
1) How do I import an entity model from SQL Server (or ERWin) into Master Data Services?
2) How do I load the entity model from a SQL table or an SSIS package?
3) How do I build a custom web UI for business users to edit master data?
4) How do I integrate master data into SharePoint Business Connectivity Services (BCS)?
The answers these questions were:
1) Manually
2) Write code to pivot your data to fit the Entity/Attribute/Value form of the MDS staging tables.
3) Write an ASP.NET web application using the MDS web service
4) Write simple wrapper web services in front of the MDS web service that BCS can consume.
Which led to me writing samples for all these scenarios and those samples are the CodePlex MDS Utilities project. The components of the MDS Utilities project are the new answers to these questions:
1) Use the MdsModelImport.exe command line utility to import a SQL Server database schema.
2) Use the mds_staging database to generate entity-based staging views that front-end the MDS staging tables.
3) Write an ASP.NET web app using MdsDataSource control for no-code and low-code data binding to MDS model data.
4) Use the DataServiceGenerator or the DataServiceAdministration web page to generate CRUD web services for your model, and plug those in to SharePoint BCS.
I am going to walk through all four of these scenarios in a series of posts. In this post we’ll just create a simple MDS model from the AdventureWorks sample database.
First download MdsModelImport.exe from the current project release https://mdsutilities.codeplex.com/releases, or download the source and built it. It’s a .NET 4.0 project, so you’ll need .NET 4 to build and run it. (Remember you don’t need Visual Studio 2010 to build a .NET project, you can just run C:\windows\Microsoft.NET\Framework\v4.0.30319\msbuild.exe.)
You can see the available command line arguments with -?
C:\projects\Codeplex\MDSUtilities\Output>MDSModelImport.exe -?
Usage: MDSModelImport
Qualifiers:
[-?] Print this help guide.
-Server:STRING Source SQL Server
-Database:STRING Source Database Name
-Model:STRING Name of the MDS Model to create. It must not exist.
[-Schema:STRING] Schema to use for import. Default is all schemas.
[-MDSEndpoint:STRING] URL to MDS Web Service. Default is in MDSModelImport.exe.config
So we want to create a model from AdventureWorks. We’ll use just the HumanResources schema, and create a model called HumanResources. Also we’ll specify the MDS web service endpoint address on the command line. If you omit the MDSEndpoint argument, you need to configure the WCF connection information in the MDSModelImport.exe.config file.
If the model name is in use you may need to delete the existing mode (MdsModelImport won’t delete it for you). You delete a model by navigating to the System Administration/Manage/Models in the MDS UI, or jump to it here https://localhost/MDS/Admin/dimension.aspx?ACTION=LIST (assuming you are working on the MDS server and the web site is using the default name of ‘MDS’). Just click on the model you want to delete to select it and hit the red X.
Then run MdsModelImport.exe from a command prompt, running as an account with administrative access to MDS and read access to the target database.
C:\projects\Codeplex\MDSUtilities\Output>MDSModelImport.exe -server (local) -database AdventureWorks -schema HumanResources -model HumanResources -MDSEndpoint https://localhost/mds/Service/Service.svc
-------------------------------------
Source Server: (local)
Source Database: AdventureWorks
MDS Web Service Endpoint Url: https://localhost/mds/Service/Service.svc
Importing HumanResources schema.
-------------------------------------
Model Created
After creating the model you’ll need to hit the ‘refresh’ link on the MDS home page for it to show up:
You can then explore the model in MDS, or delete the MDS model, make changes to the SQL schema and rerun the import tool. The mapping from tables to entities is simple: For each table an entity is created with the same name. If you have prefixes or CAPS_AND_UNDERSCORES naming conventions in your database, you may want to create a database version with more readable names before importing. Every entity in MDS is uniquely identified by an attribute called Code, and every entity also has a Name attribute, so the key structure on the tables doesn’t matter. The import tool will simply create a scalar attribute for each column in your table, including key columns, but excluding columns that are part of a Foreign Key. For each Foreign Key on your table a Domain Attribute is created.
The resulting model is not a finished product and the MdsModelImport tool is not intended to keep your model in sync with a SQL Server database. Rather it’s a “jump start” to get an initial draft of the MDS model design in place quickly, and save you from having to manually enter all the entities and attributes. From that starting point you can add and remove entities and attributes, write business rules, implement security, etc.
In the next post I’ll show you how to easilly load data into this new model using a straight SQL INSERT, or a _simple_ SSIS package using the mds_staging database from the MDS Utilities.
David
dbrowne_at_microsoft
Comments
Anonymous
November 07, 2011
When I run the MDSModelImport tool, I get this message: The HTTP request is unauthorized with client authentication scheme 'Anonymous'. The authentication header received from the server was 'Negotiate,NTLM'. In the IIS manager, I can see where the site has the Negotiate and NTLM providers enabled for Windows Authentication. Those setttings seem to be required for the MDS website to work correctly. If i also add Anonymous Authentication (valid?), i get a different message: There was no endpoint listening at <server address>... Being a lot closer to SQLServer than web services, I'm not sure where to adjust authentication parameters to make this work correctly. Thanks, RobAnonymous
April 24, 2013
Hi, Thanks for this, it's very interesting. If you were implementing SQL MDS 2012, would you use a custom web service or the one provided? Thanks, JustinAnonymous
March 21, 2014
I got the following error using SQL Server MDS 2012 And using the following sentence C:UsersAdministratorMDSModelImport.exe -server (local) -database Sales -schema dbo -model Sales -MDSEndpoint http://localhost/mds/Service/Service.svc The underlying provider failed on Open. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) at System.Data.EntityClient.EntityConnection.Open() at MDSModelImport.SQLCatalogModel.CatalogEntities.OnContextCreated() at MDSModelImport.SQLCatalogModel.CatalogEntities..ctor(SqlConnection connection) at MDSModelImport.SQLCatalogModel.DataContextExtensions.GetCatalog(SqlConnection con) at MDSModelImport.Program.Run(String serverName, String databaseName, String schema, String modelName, String endpointUrl) at MDSModelImport.Program.Main() Any Idea??Anonymous
August 24, 2015
Hi. After Importing using MDSModelImport, I Browsed imported entity from my mds site. But imported leaf attributes of entities not appeared. Let me Know. Please