9 people following this project (follow)

Project Description
A set of useful add-ons for SQL Server Master Data Services, including utilities for generating an MDS model, loading the model, writing a custom UI for model data and integrating the model with SharePoint BCS.

This project is a collection of utilities I created over the course of working with customers to implement SQL Server Master Data Services. They are intended to speed up MDS implementations and help out with some common requests we've gotten from customers. I am not part of the Master Data Services product team and this code is provided as-is for your evaluation.

These utilities help with the following scenarios:

1) Importing an existing relational model into Master Data Services

2) Loading data into an MDS model without having to "pivot" the attributes into seperate rows. This enables you to load MDS data from a simple TSQL script or SSIS package.

3) Viewing and Editing model data from custom .NET code or from SharePoint using the Business Connectivity Services.

4) Viewing and Editing model data from a custom ASP.NET UI with full data binding support. This enables you to build custom UI's for MDS with little or no coding.

5) Understanding the MDS Web Services interface by providing sample code for how to interact with various parts of the API.

There are four utilities included:

MDSDataSource Control
CRUD Web Service Generator
Mds_staging database
MDSModelImport

Here are some more details about the utilities:

MDSDataSourceControl for data binding ASP.NET UI to MDS Model Data, supporting sorting, paging CRUD eg:
      <asp:GridView ID="gvMembers" runat="server" AllowPaging="True" 
        AllowSorting="True" AutoGenerateDeleteButton="True" 
        AutoGenerateEditButton="True" AutoGenerateSelectButton="True" CellPadding="4" 
        DataKeyNames="Code" DataSourceID="MDS" EnablePersistedSelection="True" />
      
 
      <mds:MdsDataSource 
          ID="MDS" 
          runat="server"  
          ModelName="ProductionManagement" 
          EntityName="Equipment" 
          SearchTerm="DepartmentCode<> 'UNK' AND AssetCategory  ='CAPITAL'"
          AttributeList="Code, Name"          
          />


CRUD SOAP Web Service Generator
Code and a web page for generating WCF SOAP service wrappers on top of the MDS Web Service. The CRUD web services are suitable for integrating with SharePoint BCS.
So for an entity like Equipment, it generates a web service with operations like:

  [OperationContract]
  void Equipment_Add(Equipment entity);
 
  [OperationContract]
  void Equipment_Update(Equipment entity);
 
  [OperationContract]
  void Equipment_UpdateAttribute(string code, string attributeName, string attributeValue);
 
  [OperationContract]
  string Equipment_GetAttribute(string code, string attributeName);
 
  [OperationContract]
  void Equipment_Delete(string code);
 
  [OperationContract]
  Equipment Equipment_GetByCode(String code);
 
  [OperationContract]
  System.Collections.Generic.List<Equipment> Equipment_GetByAttributeValue(String attributeName, String attributeValue);
 
  [OperationContract]
  System.Collections.Generic.List<Equipment> Equipment_GetAll(int skip, int top, string orderBy);


The CRUD Web Service generator (DataServiceGenerator.cs) is in the MDSCustomAppUtilities.dll, and there's a sample admin portal page in the MDS Data Sample web site that you can use as an admin UI to generate the web services.

Mds_staging database
Enables entity-based staging with database with a stored procedure to generate SQL Views in front of the MDS Staging tables.

For each entity a view is created that PIVOT’s the staged data, and puts an INSTEAD OF INSERT trigger on each view so that data loaded against the views is UNPIVOTED and loaded into the staging tables. To set this up, run the DDL script available in downloads on the SQL Server instance that hosts your MDS database. It includes some Synonyms that point to objects in the MDS database, so if your MDS database isn't called "mds", then you will need to edit the script.

These artifacts are generated by a stored procedure in the database called GenerateStagingObjects. So initially, or after you make a structural change to your model, just connect to SQL Server and run:

  use mds_staging;
  exec dbo.GenerateStagingObjects 'MyModelName';


MDSModelImport
A command-line utility to import model metadata from a SQL Server database. For customers who do their data modeling in ERWin or somesuch to get jump-started on their MDS modeling.

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
-----------------------------------
Examples
MDSModelImport.exe -server (local) -database AdventureWorks -schema HumanResources -model HumanResourcesModel -MDSEndpoint http://localhost/mds/Service/Service.svc



This is an initial release of sample code, so if you want to use this, be prepared to provide feedback, especially if you run into trouble. Use the Discussions and Issue Tracker to log any problems.

I'll be posting a series of walkthroughs about these utilities on my BLOG:

http://blogs.msdn.com/b/dbrowne/archive/2010/12/17/implementing-an-master-data-management-solution-with-sql-server-master-data-services-and-the-mds-utilities-part-1-creating-the-model.aspx.
http://blogs.msdn.com/b/dbrowne/archive/2010/12/20/implementing-an-master-data-management-solution-with-sql-server-master-data-services-and-the-mds-utilities-part-2-loading-the-model.aspx.
David

dbrowneatmicrosoft

Last edited Dec 21 2010 at 3:02 PM by dbrowne, version 12