THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Master Data Services Team

Blog for the Microsoft SQL Server Master Data Services (MDS) team. Blog posts are contributed by various team members.

  • Exporting Master Data White Paper Published

    [This post was created by Jim van de Erve of the Content and Community Experience Group, Microsoft.] 

    Microsoft SQL Server 2012 Master Data Services (MDS) enables you to build and manage master data for your operational or analytical needs. But how do you export that master data from MDS into your systems? The "Exporting Master Data from Master Data Services" white paper at http://msdn.microsoft.com/en-us/library/jj218341.aspx shows you how. It describes the subscription views that you can export your master data into, and shows how to use SQL Server Integration Services (SSIS) to import your master data from a subscription view into a SQL Server table. Included is a step-by-step procedure that demonstrates how to set the process up, with sample T-SQL.

    Legal Notice

    © 2012 Microsoft Corporation. All rights reserved. This information is provided “as-is”. Information and views expressed, including URL and other Internet Web site references, may change without notice. You bear the risk of using this information. Examples are provided for illustration only. Provision of this information does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this information for your internal, reference purposes only.

  • How to Install Master Data Services (MDS) Service Pack 1 (for SQL Server 2008 R2)

    [This post was created by Jason Howell, Senior Escalation Engineer in the SQL Server Support team specializing in MDS]

    SQL Server 2008 R2 Service pack 1 was released in July 2011, as well as CU#1 for Service Pack 1 a few days afterwards, and CU #2 just yesterday August 15th, 2011.

    The purpose of this blog is to call out the difference in finding and running the download specifically for Service Pack 1, since it is packaged and extracted differently that Cumulative Updates. Other than the download & extraction steps, it is pretty much the same process as other Cumulative Updates for MDS. Steps 1 and 3 are the main differences here as compared to this this prior blog post.

    Installing the Service Pack 1 for MDS requires manual intervention in this release, and running the typical Service Pack 1 setup for the other parts of SQL Server itself is not enough to patch MDS databases and websites.

    If your server does not have an existing MDS installation, you can install a fresh copy of MDS following these steps in MSDN: Installing and Configuring Master Data Services You can use the SP1 MSI to install a fresh copy of MDS if needed.

    If you already have a copy of MDS up and running, you can patch the existing instance with Service Pack 1 updates.

    Always test the update and upgrade process in a non-production environment before applying an update and upgrading the MDS database in your production environment.

    1. Download SQL Server 2008 R2 Service Pack 1

    The first step is to download the service pack.

    You can use this link to download the English version, or change the language as needed. http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26727

    For example get the file SQLServer2008R2SP1-KB2528583-x64-ENU.exe

    However, wait until you have read Step 3 to run the download.

    A. It’s worth noting that the MDS Service Pack 1 download package looks different than the CU downloads.

    Service Pack 1 does not have a specific MDS download like the CU’s downloads have. Pick the SP1 for x64.

    clip_image001

    Note: The KB for 2008 R2 Service Pack 1 (KB2528583) is located here: http://support.microsoft.com/kb/2528583

    B. What’s in SQL Server 2008 R2 Service Pack 1?

    Service Pack 1 includes fixes for SQL Server 2008 R2 CU1 through CU6 plus a few other fixes that are not in the RTM CU’s. Read the Kb for an exact list of the additional fixes not in CU1-CU6.

    Moving forward, fixes made in the RTM cumulative updates are also ported forward to the next subsequent cumulative update for SP1. However, fixes made to the SP1 cumulative updates will NOT be ported back to the RTM unless there is express need to do so. This makes SP1 updates favorable moving forward to get the most fixes.

    The green cumulative updates for MDS are included in Service Pack 1. The orange items below are carried forward in CU#1 for SP1. To get the latest updates, then use SP1 CU2 instead of SP1 itself. The list grows every 2 months, so refer to this list if in doubt which one is latest.

    • RTM & Updates
      • 10.50.1600.1 Release to Manufacturer – May 10, 2010
      • 10.50.1617.0 RTM Security Patch MS11-049 (2494088) June 14, 2011
      • 10.50.1702.0 RTM CU #1 (981355) May 18, 2010
      • 10.50.1720.0 RTM CU #2 (2072493) June 21, 2010
      • 10.50.1734.0 RTM CU #3 (2261464) Aug 16, 2010
      • 10.50.1746.0 RTM CU #4 (2345451) Oct 18, 2010
      • 10.50.1753.0 RTM CU #5 (2438347) Dec 20, 2010
      • 10.50.1765.0 RTM CU #6 (2489376) Feb 21, 2011
      • 10.50.1777.0 RTM CU #7 (2507770) Apr 18, 2011
      • 10.50.1790.0 RTM CU Security Patch MS11-049 (2494086) June 14, 2011
      • 10.50.1797.0 RTM CU #8 (2534352) June 20, 2011
      • 10.50.1804.0 RTM CU #9 (2567713) Aug 15, 2011
      • etc…
    • SERVICE PACK 1 & Updates
      • 10.50.2500.0 SP1 Release to Web (2528583) July 13, 2011 (contains RTM CU6 + others)
      • 10.50.2769.0 SP1 CU #1 (2544793) July 18, 2011 (contains RTM CU7 - RTM CU8)
      • 10.50.2772.0 SP1 CU #2 (2567714) Aug 15, 2011
      • etc…

    C. Instead of SP1 itself, you can alternately download CU2 (or later) for Service Pack 1

    Instead of SP1 itself, you could run CU1 or CU2 (or later CU) for SP1 if you have business requirements to do so. There is a specific MDS file available for download for each CU, which contains only the piece to patch Master Data Services.

    Since the MDS download contains a *.msi, it is unlike the rest of SQL feature area CU’s which use *.msp patches that require SP1 as a prerequisite to installing. Therefore you could use the SP1 CU2 file for a fresh install of MDS.

    clip_image003

    When you fill out the request page, as shown above the hotfix website will send you an email with the link to download the CU update.

    2. MDS Preparations – stop current activity, backup your MDS Database, check the starting version

    The upgrade process is two-fold: First, Upgrade the binaries. Secondly, upgrade the database schema

    Therefore, before we begin, let’s stop all current activity so that we have a clean and safe copy of the MDS database in a consistent state, and make a backup of the MDS database and transaction log to be sure we have a safe copy just in case.

    A. Stop the IIS web site or application pool.

    This will keep users out of the system while we do the upgrade maintenance. You may want to alert the MDS users about the scheduled maintenance according to your businesses' change control processes.

    Open Internet Information Services (IIS) Manager and stop the Master Data Manager Web application and the MDS Web service (if enabled). You can do this either by stopping the application pool that contains the Web application and service, or you can stop the Web site that contains them. If you stop the application pool, you stop requests for all applications in that application pool. If you stop the Web site, you stop only traffic to the Web site and any applications and services it contains.

    clip_image004OR clip_image005

    B. Stop any query or maintenance operations

    Such as scheduled jobs, reports, or custom applications that interact with the MDS database directly.

    You could stop your SQL Agent service temporarily if unsure about scheduled jobs that might affect MDS data.

    C. Backup the MDS database and the transaction log

    You get to pick the name of your MDS database when you originally set it up with the Configuration Manager, so your database name may vary.

    If you use FULL recovery model on the MDS database, you can back up the transaction LOG also.

    Use a TSQL query or SSMS backup as you please.

    BACKUP DATABASE MDS TO DISK='C:\mybackups\mds_before_sp1.bak'
    BACKUP LOG MDS TO DISK='C:\mybackups\mds_log_before_sp1.trn'
    Object Explorer in SQL Server Management Studio:

    clip_image006

    D. Query mdm.tblSystem in the current MDS database.

    Review value for SchemaVersion and note the value, so you can compare the value after you apply the MDS update to verify that the value has been increased. The SchemaVersion may vary based on the build you are starting with. Some MDS Patches do no schema upgrades, but some do. Depending on which version you start from, Service Pack 1 may or may not upgrade the schema.

    USE MDS
    GO
    SELECT * FROM mdm.tblSystem

    3. Run the Service Pack 1 executable and msi’s

    The SP1 download SQLServer2008R2SP1-KB2528583-x64-ENU.exe is a self-extracting zip. You can run SP1 for all of SQL if you want (see step 3A) or manually extract just the Master Data Services *.msi if needed (see step 3B).

    Double-clicking the .exe extracts SP1 to a temp folder on the root of the drive

    clip_image007

    After extracting, it will prompt you with security UAC to run the service pack 1 setup.

    A. Option 1: Run SP1 for all SQL Server feature areas first, then find the MDS msi.

    If you need to patch all of the features of your SQL Server with Service Pack 1, follow the normal steps in the GUI.

    Running SP1 will extract the MasterDataServices.msi file but it will not run it automatically.

    After you have finished patching SQL Server 2008 R2 with Service Pack 1, you can then find the MSI for MDS in the setup bootstrap folder. It’s kept here for caching purposes:

    C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Update Cache\KB2528583\ServicePack\1033_enu_lp\x64\setup\ masterdataservices.msi

    On my computer it is in this folder, but I imagine the language (1033=English) may vary based on which SQL locale you have used in the installation.

    Within that folder, click the MSI to run the MDS SP1 setup masterdataservices.msi

    B. Option 2: Manually Extract SP1 if you do not need to patch any other parts of SQL.

    If you don’t need to patch the other features of SQL Server, or maybe you need to just patch MDS as a separate step, you can manually extract the Service Pack 1 and get to the MSI. Several 3rd party compression tools let you right click on the .exe and extract it to a folder of your choosing as well.

    From a command line, you can run:

    SQLServer2008R2SP1-KB2528583-x64-ENU.exe /extract

    clip_image008

    clip_image009

    Find the MDS msi in the specified location, in the subfolder:
    <Extracted folder>\1033_enu_lp\x64\setup\masterdataservices.msi

    clip_image010

    4. Run the masterdataservices.msi manually (like any other MDS update)

    clip_image011

    clip_image012

    Run through the installation until it completes

    .clip_image013

    Note: If you start the MDS website at this point and visit it, you may get a version incompatible error. This is expected behavior when the schema version increases because the MDS binaries are upgraded, but the database is not yet upgraded. Users cannot use MDS yet if you see this message.

    Client Version: 10.51.2500.0
    Database Version: 1.0.0.0
    The Client version is incompatible with the Database version. Please ask your administrator to upgrade the Client components or the Database components or both.

    clip_image014

    Note the binaries and scripts are upgraded already to 10.50.2500.0 (the build number of R2 Service Pack 1)

    Binaries are the *.exe or *.dll in the Master Data Services installation folders such as

    C:\Program Files\Microsoft SQL Server\Master Data Services\Configuration\

    C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication\bin

    When you right click on them in Windows Explorer, and view the properties, you can check the file version:

    clip_image015

    5. The Master Data Services Configuration Manager may need to run to complete the MDS database schema upgrade.

    A. Run Configuration Manager

    It will try to run automatically after the setup completes, or you can run it later from the start menu. Complete the additional steps therein to upgrade your MDS Database and your MDS Websites.

    clip_image016

    B. Connect and select your database

    In the MDS Configuration Manager, click the [Select Database…] button.

    Get connected to your MDS database on whichever SQL Server it may reside on. Note if you utilize a named instance, you have to manually type in the servername\instancename because the configuration manager forgets which instance to connect to every time.

    clip_image017

    C. Upgrade your MDS database if needed

    If you see the below red error text "This database requires an upgrade. You cannot change system settings until the database is upgraded" then you need to upgrade your MDS database.

    Sometimes you do not see this text, if you already had the schema upgrades are already in place from a prior cumulative update patch, then no further action is required, skip to step 6.

    If you get the error after you picked the database server and the existing database name, in the MDS Configuration Manager, use the [Upgrade Database…] button to upgrade the database.

    clip_image018

    D. Click through the Upgrade Database Wizard

    When you run the Upgrade, you will see several screens in the upgrade wizard. It will show which schema upgrades need to happen. It’s easy, so I’ll just show the progress bar:

    clip_image019

    E. Now check the database SchemaVersion in your MDS database by running the query to confirm the schema is now upgraded.

    I expect 1.0.7.0 for Service Pack 1.

    USE MDS
    GO
    SELECT * FROM mdm.tblSystem

    clip_image022

    6. Start the MDS website and Application Pool and make sure it works.

    A. Start the web site and application pools

    After the database upgrade is complete, your users can start using the MDS website again. Start the Website and or Application Pools, and remember to enable any SQL Agent jobs or other maintenance you may have disabled for the upgrade timeframe.

    clip_image020clip_image021

    B. Browse through your MDS website to make sure the models are present, and that things seem normal.

    You can get IIS to help you if you forgot the URL:

    clip_image023

    You can confirm the version number in the Help menu (blue question mark on the upper right of on the web page).

    The MDS website shows the version as 10.51.2500.0 for Service Pack 1.

    clip_image024

    clip_image025

  • New Master Data Services Book Available

    A new Master Data Services book is now available on amazon.com.

    Microsoft SQL Server 2008 R2 Master Data Services;  by Tyler Graham and Suzanne Selhorn;  ISBN 007175623X

     

    This book was written by two members of the Microsoft MDS team and is a resource and reference for using the SQL Server 2008 R2 version of Master Data Services. A Kindle version is also available.

    An updated version of the book will soon be available for SQL Server 2012:

    Microsoft SQL Server 2012 Master Data Services 2/E; by Tyler Graham and Suzanne Selhorn; ISBN 0071797858 

  • New Master Data Services Training Available

    [posted by Suzanne Selhorn, Technical Writer on the MDS team]

    Some new self-paced training is now available on the Microsoft Download Center. To take advantage of this training, you should have a working installation of MDS with sample data already loaded.

    01 Introduction
    http://download.microsoft.com/download/5/9/F/59F1639E-EF57-4915-8848-EF1DC2157EBB/01 Introduction.pdf
    This lesson provides an overview of MDS.

    02 MDS Environment
    http://download.microsoft.com/download/5/9/F/59F1639E-EF57-4915-8848-EF1DC2157EBB/02 MDS Environment.pdf
    This lesson provides an overview of MDS terminology and features of the Master Data Manager web application.

    03 Managing Entities
    http://download.microsoft.com/download/5/9/F/59F1639E-EF57-4915-8848-EF1DC2157EBB/03 Managing Entities.pdf
    In this lesson, MDS terminology is explored in more depth. You start working with data and learn how to use the Master Data Manager web application to do common tasks.

    04 Managing Hierarchies
    http://download.microsoft.com/download/5/9/F/59F1639E-EF57-4915-8848-EF1DC2157EBB/04 Managing Hierarchies.pdf
    This lesson provides information about all of the available hierarchy types in MDS. You practice adding, removing, and moving members in hierarchies.

    05 Creating Collections
    http://download.microsoft.com/download/5/9/F/59F1639E-EF57-4915-8848-EF1DC2157EBB/05 Creating Collections.pdf
    This lesson shows you how to create collections, and how to add and remove items from a collection.

    06 Version Management
    http://download.microsoft.com/download/5/9/F/59F1639E-EF57-4915-8848-EF1DC2157EBB/06 Version Management.pdf
    This lesson explains version management and shows you how to view and reverse transactions.

    07 Modeling Guidelines
    http://download.microsoft.com/download/5/9/F/59F1639E-EF57-4915-8848-EF1DC2157EBB/07 Modeling Guidelines.pdf
    This lesson gives you ideas for how to use MDS to model your organization's data. It prepares you for the next lesson, where you will use the knowledge you've gained to start creating your model.

    08 Model Building
    http://download.microsoft.com/download/5/9/F/59F1639E-EF57-4915-8848-EF1DC2157EBB/08 Model Building.pdf
    In this lesson, you will use the knowledge from Lesson 07 to begin creating a model for your data.

  • Reserved Words

    (This was posted by Suzanne Selhorn, Technical Writer on the MDS team) 

    People often ask about which words are reserved—which words they shouldn’t use when creating model objects or words they shouldn't use for members. Here is a list of words you should not use.

    Model object reserved words
    Don't use these for names of objects (models, entities, attributes, version flags, subscription views, etc).

    ID LastChgUserName
    Code LastChgVersionID
    Name LastChgVersionNumber
    EDMWeight MDMMemberStatus
    EDMMemberStatus Status
    EnterDateTime Status_ID
    EnterDTM ValidationStatus
    EnterUserID ValidationStatus_ID
    EnterUserName Version_ID
    EnterVersionNumber       VersionFlag
    LastChgDateTime VersionName
    LastChgDTM VersionNumber
    LastChgUserID

    Member reserved words
    Don't use these words for members.

    Root EDMMemberStatus
    MDMUnused EDMUnused
    MDMMemberStatus EDMWeight
  • Downloading and Installing SQL Server 2008 R2 Master Data Services (MDS) Cumulative Updates

    (This post was contributed by Reagan Templin, Lead Technical Writer for the MDS Team)

    Note: This article is applicable to Microsoft SQL Server 2008 R2 Master Data Services feature. SQL Server 2012 now ships Master Data Services on the DVD with the other SQL Server 2012 features and is patched by the same Cumulative Updates and Service Packs for SQL Server 2012 and the downloads are no longer specific to MDS for separate download .

    Recently, we released the first cumulative update (CU) for Master Data Services (MDS). This blog entry explains how to download and install that CU, as well as subsequent CUs for MDS.

    Each Master Data Services (MDS) cumulative update is released as a stand-alone installer for SQL Server 2008 R2 Master Data Services. An update can be installed on a server without a previous installation of MDS, or it can be applied over an existing RTM installation or previously updated installation of MDS.

    Note MDS is a feature of the versions and editions of SQL Server listed in Setup Requirements (Master Data Services) in SQL Server Books Online (BOL). Any computer where you install MDS must be licensed accordingly. For more information, refer to the End User License Agreement (EULA).

    To download an MDS Update:

    1. At the top of the Knowledge Base page for the download, click View and request hotfix downloads.
    2. Read the terms of the agreement and click I Agree if you agree and want to continue.
    3. On the Hotfix Request page, select the necessary file to obtain this update.
    4. Specify an e-mail address to e-mail this update to, and type the characters in the picture. Then click Request hotfix.
    5. On the Hotfix Confirmation page, review the information and wait for the hotfix e-mail to arrive.
    6. From the hotfix e-mail, open the link and download the .Zip file.
    7. Extract the .Zip file and provide the password included in the hotfix e-mail. MasterDataServices.msi will be extracted to the location you specify.

    Installing MDS cumulative updates on a server without an existing MDS installation

    If your server does not have an existing MDS installation, you can follow the process documented in Installing and Configuring Master Data Services in SQL Server BOL. However, instead of running MasterDataServices.msi from the product media as directed in the documentation, run MasterDataServices.msi from the location where you extracted it in the download process above. Then create and configure the MDS database, Master Data Manager Web application, and MDS Web services as documented in SQL Server BOL.

    Applying MDS cumulative updates to an existing MDS installation

    If you apply an MDS cumulative update to an existing installation of MDS, you must run the installation package and then use Master Data Services Configuration Manager to upgrade the database.

    Important   Always test the update and upgrade process in a non-production environment before applying an update and upgrading the MDS database in your production environment.

    Before you start the upgrade process:

    • Open Internet Information Services (IIS) Manager and stop the Master Data Manager Web application and the MDS Web service (if enabled). You can do this either by stopping the application pool that contains the Web application and service, or you can stop the Web site that contains them. If you stop the application pool, you stop requests for all applications in that application pool. If you stop the Web site, you stop only traffic to the Web site and any applications and services it contains.
    • Stop any operations, such as scheduled jobs, that affect the MDS database.
    • Query mdm.tblSystem in the current MDS database. Review value for SchemaVersion and compare the value after you apply the MDS update to verify that the value has been increased.
    • Take a current backup of the MDS database. If you are using the full recovery model, take a log backup immediately before you upgrade the database. If you are using the simple recovery model, take a full or differential database backup. For more information about backups, see “Backup Overview (SQL Server)” at http://msdn.microsoft.com/library/ms175477(SQL.105).aspx.

    To apply an MDS cumulative update:

    1. On the server where you want to install the update, navigate to the location where you extracted MasterDataServices.msi and double-click it.
    2. The following message appears: A lower version of this product has been detected on your system. Would you like to upgrade your existing installation? Click Yes to proceed with the upgrade process.
    3. The Microsoft SQL Server 2008 R2 Master Data Services installation wizard opens. Follow the instructions in the wizard to configure installation options and then click Install.
    4. After installation completes, Master Data Services Configuration Manager opens so you can upgrade the MDS database.
    5. Click Databases, and then click Select Database. Connect to the database you want to upgrade. Once the database is selected, the following message appears: This database requires an upgrade. You cannot change system settings until the database is upgraded.
    6. Click Upgrade Database. The Upgrade Database Wizard opens. Follow the instructions in the wizard to upgrade the database and then click Finish.
    7. To verify that the database upgrade was successful, connect to the MDS database and query mdm.tblSystem. The value for SchemaVersion should be increased and match the value as specified for the individual cumulative update.

    If the update was unsuccessful, open Control Panel and use Programs and Features to uninstall Microsoft SQL Server 2008 R2 Master Data Services and then re-run the steps in this procedure.

    After you apply the cumulative update and upgrade the database, start the Master Data Manager Web application, MDS Web service (if enabled in your environment), and any operations that you suspended prior to the upgrade process. Verify that your environment works as expected before you allow users to access the environment.

  • New Resource for Master Data Services Information

    (This post was contributed by Reagan Templin, Lead Technical Writer for the MDS Team)

    We’ve launched a new Web page on MSDN to consolidate information about Master Data Services (MDS) from many different sources: http://msdn.microsoft.com/en-us/sqlserver/ff943581.aspx.

    It is a portal to refer to, whether you’re just starting with MDS or are already implementing MDS in an environment. Over time it will grow and develop into an even richer source of information as more content becomes available and the MDS community evolves.

    The following table outlines the kind of information that is available on the portal:

    Section Description
    Getting Started Features links to introduce, learn about, and troubleshoot MDS.
    Highlights Highlights specific resources and contains an RSS feed of those resources that you can follow with an RSS feed reader.
    Resources Contains several tabs of information including Articles and Whitepapers, Books, Documentation, Knowledge Base, Videos, and Samples.
    Related Products and Technologies Contains links to pages about other products and technologies that integrate or are useful to understand when working with MDS.
    Downloads Contains links to pertinent downloads for MDS.
    In the Spotlight Spotlights a useful resource, contributor, or other information.
    Team and Community Blogs Features the top four latest blog entries from a feed of blogs related to SQL Server and MDS. The list of blogs that contribute to the feed are available by clicking the OPML link next to the heading of this section.
    Forums Contains links to the current MDS forum, and to the archived master data management (MDM) forum.

    If you have ideas for updates, you can click the Site Feedback link at the bottom of the portal page and submit your ideas.

  • Creating Web sites and applications in Master Data Services Configuration Manager

    (This post was contributed by Reagan Templin, Lead Technical Writer for the MDS Team)

    Lately, I've been seeing some confusion about creating and configuring the Master Data Manager Web application in Master Data Services (MDS) Configuration Manager. In this blog entry, I'm going to walk through the process in a bit more depth to help everyone understand more about the Web Configuration page with a focus on the Web Application area. Though I'll go over some prerequisites here, I urge everyone to read Installing and Configuring Master Data Services in SQL Server Books Online (BOL) for a workflow of the entire process of installing and configuring MDS in an environment. There are lots of good nuggets of information in that topic that I won't recover in entirety here.

    Prerequisites

    First, remember that Master Data Services (MDS) is 64-bit only, so whether you're evaluating MDS or beginning deployment in a dev, test, or production environment, any computers running MDS must have 64-bit hardware and run a 64-bit edition of the Windows operating system.

    Speaking of operating systems, MDS requires Internet Information Services (IIS) 7.0 or later. For IIS 7.0, you need to have Windows Vista or Windows Server 2008. For IIS 7.5, you need to have Windows 7 or Windows Server 2008 R2. Additionally, the edition of IIS must have the Windows Authentication feature (if a client OS) or role service (if a server OS). The versions and editions of Windows that have Windows Authentication are listed in Setup Requirements (Master Data Services) in BOL.

    You also need .NET Framework 3.5 SP1. If you don't already have .NET Framework 3.5 SP1 on the system, it will be installed before MDS or you can obtain it directly from the Microsoft Download Center at http://www.microsoft.com/downloads/details.aspx?FamilyID=ab99342f-5d1a-413d-8319-81da479ab0d7&DisplayLang=en. To avoid problems, you should install .NET Framework 3.5 SP1 after you install IIS. If you cannot or did not do it in this order, see the following forum post for additional information: http://social.msdn.microsoft.com/Forums/en-US/mdmsetup/thread/37b98d95-d833-4abf-9215-67c538ff2871.

    Finally, you need to make sure that you've installed the necessary Windows features (if a client OS) or role services (if a server OS). These are listed in Web Application Requirements (Master Data Services) in BOL. Most of the features or role services are selected by default when you install IIS, however a couple like Windows Authentication, .NET Framework 3.0 Features, and Windows PowerShell are not default options. If you run into trouble with the Web application after you create and configure it, verify your installation options against the list in the Web Application Requirements (Master Data Services) topic and make sure you've got things configured properly. PowerShell isn't included on Windows Vista, but you can download it from the Microsoft Download Center.

    Web Configuration Page: Web Application Section

    Now that we've got prerequisites covered, let's get into the MDS Configuration Manager and the Web Configuration page. It's helpful to think of this page as a simplified interface for creating the MDS Web application without requiring the use of IIS Manager or other IIS tools. However, the beauty of MDS using IIS is that you can use all of the great features and functionality of IIS for your MDS Web application. So, you'll likely find MDS Configuration Manager to be a good tool for initial setup of the MDS Web application, but can then use IIS tools for more advanced configuration and full access to all the functionality IIS provides.

    Most of the confusion we've seen with the Web Configuration page has to do with the Web Application section of the user interface (UI). The Web Application section is an area where you can:

    • Create MDS Web applications on the local computer.
    • Select MDS Web applications from the local computer to view or configure database and Web service options. When you select an MDS Web application, the details of the database instance and MDS database are displayed, as well as the information about whether the MDS Web application is enabled for Web services.

    It's important to note that when you create a new Web site in MDS Configuration Manager, you're creating a new Web site but also creating the MDS Web application to be the root application in that site. If you aren't familiar with the changes to sites, applications, and virtual directories starting with IIS 7.0 and what all of this terminology means, read my article, Understanding Sites, Applications, and Virtual Directories, on IIS.NET that explains in further depth what is happening here.

    Use the following image to decide what to do in the Web Application section. (Click the image to open a larger copy).

    What to do on the Web Configuration page

    If you choose to create a site or an application, see the following topics for more information about what to specify in the specific dialog boxes: Create Web Site Dialog Box (Master Data Services) and Create Web Application Dialog Box (Master Data Services) in BOL.

    Once you have created the MDS Web application, select a database to connect to and optionally, enable Web services. For more information, see How to: Associate a Master Data Services Database and Web Application and How to: Enable Web Services (Master Data Services) in BOL.

    Important   If you do not at least connect the new MDS Web application to a database, you'll run into problems when you select the site again. It will display the message that the site has no MDS Web applications, even though you created one. You must use IIS Manager or another IIS tool to delete the incomplete MDS Web application. Then add it to MDS Configuration Manager, making sure to connect it to the database this time, and click Apply. If you do not delete the incomplete MDS Web application before trying to add it again, you'll receive the error message "An unexpected error occurred: Object reference not set to an instance of an object."

    Messages and Troubleshooting

    Message Description

    "This Web site has no Master Data Services application. Either click Create Application to create one, or select or create another site."

    You can add an MDS Web application to the selected site. If you do not want to create an MDS Web application in the selected site, select a site that you do want to create the application in or create a new site.

    • Create an MDS Web application in the selected site.
    • Select a different site from the drop-down list if you want to create the MDS Web application in a different site OR you need to select a site with an MDS Web application so that you can configure the database or Web service for that application.
    • Create another site if you decide that you want the MDS Web application to be in an entirely new site.
    "In this tool, you can configure only one Master Data Services Web application in a Web site." You can't have an MDS Web application nested within another MDS Web application. So, once you create a site and the root Web application is the MDS Web application, you cannot select that site and create another MDS Web application in it, otherwise you receive this message. Select a different site.

    If you encounter other error messages or issues when configuring MDS, see Troubleshooting Installation and Configuration Issues (Master Data Services) in BOL. For more information about the MDS Web.config file, see Web Configuration Reference (Master Data Services) in BOL.

  • Recorded MDS Presentations Available at Tech-Ed Online

    We had great interest for our MDS presentations at the 2010 Tech-Ed / BI North America Conferences.  Thanks to all who attended.  If you didn’t get a chance to attend the MDS sessions, you can view the recorded presentations at Tech-Ed Online.

     

    Introducing Microsoft SQL Server 2008 R2 Master Data Services [BIE203]

    This session introduces Master Data Services (MDS) and describes typical solutions. Product demonstrations illustrate how Master Data Services can help synchronize operational systems and build better dimensions in analytic systems. The session includes an overview of MDS features including product demonstrations and shows how to get started implementing MDS.

     

    Microsoft SQL Server Analysis Services Dimension Management with Master Data Services (MDS) [BIE02-INT]

    This session discusses the problems of dimension and hierarchy management for data warehouses and Analysis Services and describes a solution to the data management problem using Microsoft SQL Server 2008 R2 Master Data Services (MDS). MDS provides ownership of the dimension data to the right data stewards in the business while giving IT process control, security, and auditability.

  • Master Data Services first cumulative update is now available!

    We have assembled a new release that addresses a number (30+) of issues that we could not address before the SQL Server 2008 R2 RTM release.  Everyone who is seriously considering Master Data Services or even kicking the tires is encouraged to take this update.  Anyone actively using the product is encouraged to take this release as well.

    Here is the location of the KB Article:

    http://support.microsoft.com/kb/2143880

    Note: This is a full installation msi.  You can install this MSI on a new server or an existing server that runs the RTM version of Master Data Services. If you apply this to an existing server, you will need to update your MDS database by using Master Data Services Configuration Manager after you install the MSI. Open Configuration Manager, and click Upgrade Database to do this.

    Master Data Services is a feature of the versions and editions of SQL Server listed in Setup Requirements (Master Data Services). Any computer where you install Master Data Services must be licensed accordingly. For more information, refer to the End User License Agreement (EULA).

  • New Samples on MSDN Code Gallery

    (This post was contributed by John Burrows, Lead Program Manager for the MDS Team, May 2010)

     

    ***THIS POST APPLIES TO SQL SERVER 2008 R2. FOR SQL SERVER 2012 SAMPLES, SEE http://sqlserversamples.codeplex.com/wikipage?title=SQL%20Server%202012%20Master%20Data%20Services***

     

    A couple of new samples have been posted to the MSDN Code Gallery; two sample models that illustrate recursive and explicit cap hierarchies and a Visual Studio solution that contains an example of calling the Model Deployment API via code.

    Sample Models

    Employees

    The Employee sample model contains the employees of a fictitious Winery “Coho Winery” that has a legal structure in the form of three subsidiaries and an operating structure in the form of four operating groups.

    You can download the model from MSDN Code Gallery here:

    http://code.msdn.microsoft.com/SSMDSEmployeeSample

    Once you unzip the .pkg file you can deploy it to your MDS system using the Model Deployment Wizard. For more information about using the Model Deployment Wizard, see “Deploying Models (Master Data Services)” (http://msdn.microsoft.com/en-us/library/ff486956(SQL.105).aspx) in SQL Server Books Online.

    Recursive derived hierarchies are illustrated in the model in three hierarchies:

    1. “Reports to” hierarchy. This hierarchy is a simple recursive hierarchy built on the Employee entity and Manager domain-based attribute.

    2. “Operating groups” hierarchy. This hierarchy is also a recursive hierarchy that adds the Group domain-based attribute on top of the recursive Employee-Manager relationship.

    3. “Subsidiaries” hierarchy. Again, here is another recursive hierarchy that adds the Subsidiary domain-based attribute on top of the Group domain-based attribute on top of the recursive Employee-Manager relationship.

    For more information about recursive hierarchies, see “Recursive Hierarchies (Master Data Services)” (http://msdn.microsoft.com/en-us/library/ff487006(SQL.105).aspx) in SQL Server Books Online.

    Accounts

    The Accounts sample model contains the employees of a fictitious multi-national company, Contoso Pharmaceuticals, which has general ledger systems located in various locations across the globe.

    You can download the model from MSDN Code Gallery here:

    http://code.msdn.microsoft.com/SSMDSAccountSample

    Once you unzip the .pkg file you can deploy it to your MDS system using the Model Deployment Wizard.

    The local general ledger systems in the company have different charts of accounts. The “Corporate” hierarchy in the sample illustrates how a derived hierarchy with an explicit cap can be used to establish a standard corporate chart of accounts that combines the local differing chart of accounts. The hierarchy uses the derived relationship between the account and subsidiary account entities and the explicit hierarchy “base” to standardize the rollup of the corporate chart of accounts.

    For more information about derived hierarchies with explicit caps, see “Derived Hierarchies with Explicit Caps (Master Data Services)” (http://msdn.microsoft.com/en-us/library/ff486988(SQL.105).aspx) in SQL Server Books Online

    Model Deployment API code sample

    Deploying the sample

    This sample illustrates calling the Model Deployment API via code with a simple console application demonstrating a couple of common methods. You can download the sample here:

    http://code.msdn.microsoft.com/ModelDApiSample

    Once you download the sample and unzip it, you will need to perform a couple of steps to successfully build the Visual Studio project. These steps are also documented in the readme in the .zip file.

    The sample has the following external dependencies:

    • Microsoft.MasterDataServices.Core.dll
    • Microsoft.MasterDataServices.Deployment.dll
    • Microsoft.MasterDataServices.Services.dll

    In order to build the solution, you will need to adjust the project references to point to these binaries in your Master Data Services deployment.

    You will also need to update the ConnectionString in ModelDUtil.config to point to your deployed database. Please be sure not to change the name of the connection -- that should be left as "defaultMdsConnection". Also, ModelDUtil.config must be located in the same folder as ModelDUtil.exe.

    Running the sample

    The console application can be run with the following commands:

    ModelDUtil [mode] [params]

    where [mode] is one of the following:

    ListModels -- returns a list of all the user models in the target system

    ModelDUtil ListModels

    ListVersions -- returns a list of the versions for a given model

    ModelDUtil ListVersions [model name]

    CreatePackage -- create a package file for a given model

    ModelDUtil CreatePackage [output package file name] [model name] [version name]

    DeployClone -- deploys a clone of a model from a given package

    ModelDUtil DeployClone [input package file name]

    DeployNew -- deploys a model from a given package with the new given name

    ModelDUtil DeployNew [input package file name] [new model name]

    DeployUpdate -- deploys an update to a given version of a model from a given package

    ModelDUtil DeployUpdate [input package file name] [version name to update]

    DeleteModel -- deletes a given model

    ModelDUtil DeleteModel [model name]

    Help -- displays the help

    ModelDUtil Help

    Note: names that contain spaces should be wrapped with double quotation marks. For Example: ModelDUtil DeployUpdate mypackage.pkg "Version 1"

    For more information about the API, see the Model Deployment API reference (http://msdn.microsoft.com/en-us/library/microsoft.masterdataservices.deployment(SQL.105).aspx) in SQL Server Books Online.

  • Updated Master Data Services Documentation and Resources

    (This post was contributed by Reagan Templin, Lead Technical Writer for the MDS Team)

    With the release of SQL Server 2008 R2, it’s a great time to check out the updated documentation and resources for the release, and for SQL Server 2008 R2 Master Data Services ("MDS") in particular. As you saw in the last post (New White Papers Available), there are some great white papers available on MSDN to get you going with MDS. Below you’ll find more information about other updated and newly published content.


    SQL Server 2008 R2 Books Online
    The MDS product documentation was first published in SQL Server 2008 R2 Books Online with the November CTP (at the same time as the first public release of MDS in SQL Server 2008 R2). The MDS User Education (UE) team has been working on improving and adding a lot of additional content that was not available for the first release of the content. You can find the MDS portion of Books Online on both MSDN and TechNet at the following URLs:

    http://msdn.microsoft.com/en-us/library/ee633763(SQL.105).aspx
    http://technet.microsoft.com/en-us/library/ee633763(SQL.105).aspx

    With this documentation update, you’ll see the following improvements:

    • Overviews and workflows for getting started with MDS, installing and configuring MDS, and configuring security (in the Getting Started, Deployment, and Security and Protection nodes)
    • Better installation, configuration, and uninstall documentation (in the Deployment node)
    • Conceptual content with artwork tied to related procedural content (in the Operations node)
    • More comprehensive security documentation (in the Security and Protection node)
    • Updates to the technical reference to have more information about the staging tables and error messages (plus examples for importing data into those tables), Web configuration, and PowerShell cmdlets (in the Technical Reference node)
    • Troubleshooting documentation for installation and configuration, staging, model deployment, and notifications (links from the Troubleshooting node out to topics elsewhere in the doc set)

    If you were familiar with the content before, you’ll notice that some things have moved around a bit in the Table of Contents (TOC), such as some topics being moved out of Getting Started into Operations and out of Planning and Architecture to Deployment. We hope that by moving related content closer together, you’ll find things easier to navigate and have a more comprehensive view.


    Introducing Microsoft SQL Server 2008 R2
    Microsoft Press has released a free e-book for download, Introducing Microsoft SQL Server 2008 R2, which has a chapter about MDS, as well as chapters about the other exciting features and enhancements for the release. The URL to download the book is http://blogs.msdn.com/microsoft_press/archive/2010/04/14/free-ebook-introducing-microsoft-sql-server-2008-r2.aspx

    Enjoy the new content, and keep giving us feedback. We really appreciate it!

  • New White Papers Available

    (This post was contributed by Reagan Templin, Lead Technical Writer for the MDS Team) 

    New SQL Server 2008 R2 Master Data Services ("MDS") white papers are now available on MSDN.

    For an application-agnostic overview of master data management, see Organizational Approaches to Master Data Management.

    For the steps needed to configure Master Data Services to work with a SharePoint workflow, see SharePoint Workflow Integration with Master Data Services.

  • Subscription Views - Part 3: Padding Level-Based Views

    (this post was contributed by Val Lovicz, Principal Program Manager on the MDS Team)

    Background

    This post is the third in a series on Subscription Views in SQL Server 2008 R2 Master Data Services (“MDS”). In the first post, I described how to create standard subscription views. In this post I will describe how to fill in the null columns in a level-based view of a ragged hierarchy.

    This post gives SQL examples that assume you have already created the “_ProductHierarchyLevels” view as described in the first post.

    For those new to SQL scripting and editing in SQL Server Management Studio, refer to Books Online for more information.

    The Problem

    Some subscribing systems require a fixed-level representation of a hierarchy even though the source hierarchy is fundamentally ragged or unbalanced and modeled that way in MDS. Consider the ragged hierarchy displayed below. In this hierarchy, the “PL” branch of the hierarchy is 2 levels deep (not counting “ROOT”) while the “AC” branch is 3 levels deep.

    image

    A level-based representation of this hierarchy would look like the following.

    image

    This level-based column format is often best for subscribing systems like a data warehouse dimension table. However, the nulls may present a problem. For example, in the data warehouse you cannot subtotal product sales by “Level 3” in the product hierarchy and achieve any meaningful information. One solution is to fill in these blank spots in the hierarchy with the leaf-level member. The modified format would then look like the following.

    image

    Solution: Creating a Padded View

    The following query uses the view “_ProductHierarchyLevels” as created in the first post on subscription views.

    SELECT [L0_Code]
          ,[L0_Name]
          ,[L1_Code]
          ,[L1_Name]
          ,[L2_Code]
          ,[L2_Name]
          ,[L3_Code]
          ,[L3_Name]
          ,[Leaf_Code]
          ,[Leaf_Name]
    FROM [mdm].[_ProductHierarchyLevels]
    WHERE Hierarchy = 'Product Management'

    The query results below show that some of the members do not have an L2 or L3 hierarchy parent.

    image

    We can replace the occurrences of NULL with the leaf-level member code and name as needed using the ISNULL transact-SQL function. To do this we could create a query as follows. ISNULL will replace the first value with an alternate value whenever the first value (argument) is NULL.

    SELECT [L0_Code]
          ,[L0_Name]
          ,[L1_Code]
          ,[L1_Name]
          ,ISNULL([L2_Code],[Leaf_Code]) AS L2_Code
          ,ISNULL([L2_Name],[Leaf_Name]) AS L2_Name
          ,ISNULL([L3_Code],[Leaf_Code]) AS L3_Code
          ,ISNULL([L3_Name],[Leaf_Name]) AS L3_Name
          ,[Leaf_Code]
          ,[Leaf_Name]
    FROM [mdm].[_ProductHierarchyLevels]
    WHERE Hierarchy = 'Product Management'

     

    Running the new query will produce what we refer to as a “padded-level view” with results as shown below.

    image

    This is just one way to pad the view. Sometimes you may want or need to pad from the top-down. The key point is you can create a simple view on top of the standard subscription view formats to achieve the final format you require.

    Legal Notice

    © 2010 Microsoft Corporation. All rights reserved. This information is provided “as-is”. Information and views expressed, including URL and other Internet Web site references, may change without notice. You bear the risk of using this information. Examples are provided for illustration only. Provision of this information does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this information for your internal, reference purposes only.

  • Subscription Views - Part 2: Combining and Customizing Views

    (this post was contributed by Val Lovicz, Principal Program Manager on the MDS Team)

    Background

    This post is the second in a series on Subscription Views in SQL Server 2008 R2 Master Data Services (“MDS”). In the first post, I described how to create standard subscription views. In this post I will describe how to create your own custom views that build upon the standard views.

    This post gives SQL examples that assume you have already created the “_ProductHierarchyLevels” view and the “_ProductLeafAttributes” view as described in the first post.

    For those new to SQL scripting and editing in SQL Server Management Studio, refer to Books Online for more information.

    Example 1: Filtering Rows and Columns

    The standard subscription views may have more rows and columns than your export process needs. To improve query performance and produce only the subset of data that you need, consider building your own custom view on top of a standard, generated subscription view. For example, suppose you only wanted the level columns from the “Product Management” hierarchy. The following example SELECT statement specifically queries the level columns and filters the rows to the “Product Management” hierarchy only.

    SELECT [Leaf_Code]
          ,[Leaf_Name]
          ,[L3_Code]
          ,[L3_Name]
          ,[L2_Code]
          ,[L2_Name]
          ,[L1_Code]
          ,[L1_Name]
          ,[L0_Code]
          ,[L0_Name]
    FROM [mdm].[_ProductHierarchyLevels]
    WHERE Hierarchy = 'Product Management'

    To further this example, you could save this SELECT statement as your own custom view in the database by adding a CREATE VIEW statement at the beginning:

    CREATE VIEW _ProductsPMHierarchy AS
    SELECT [Leaf_Code]
          ,[Leaf_Name]
          ,[L3_Code]
          ,[L3_Name]
          ,[L2_Code]
          ,[L2_Name]
          ,[L1_Code]
          ,[L1_Name]
          ,[L0_Code]
          ,[L0_Name]
    FROM [mdm].[_ProductHierarchyLevels]
    WHERE Hierarchy = 'Product Management'

    Note: If you need to make changes to the view definition and recreate it, issue a DROP VIEW command as follows.

    DROP VIEW _ProductsPMHierarchy

    Example 2: Joining Multiple Views

    Another common requirement is combining attribute and hierarchy data together. Suppose you need a view combining both the Product Management hierarchy levels and the Product attributes. You can achieve this by creating a simple custom view with a JOIN on two standard subscription views. The following example SQL will create a new view “_ProductsAndPMHierarchy”.

    CREATE VIEW _ProductsAndPMHierarchy AS
    SELECT pa.[Member_ID]
          ,pa.[VersionName]
          ,pa.[VersionNumber]
          ,pa.[VersionFlag]
          ,pa.[Name]
          ,pa.[Code]
          ,hl.[L3_Code]
          ,hl.[L3_Name]
          ,hl.[L2_Code]
          ,hl.[L2_Name]
          ,hl.[L1_Code]
          ,hl.[L1_Name]
          ,hl.[L0_Code]
          ,hl.[L0_Name]
          ,pa.[ChangeTrackingMask]
          ,pa.[ProductSubCategory_Code]
          ,pa.[ProductSubCategory_Name]
          ,pa.[ProductSubCategory_ID]
          ,pa.[Color_Code]
          ,pa.[Color_Name]
          ,pa.[Color_ID]
          ,pa.[Class_Code]
          ,pa.[Class_Name]
          ,pa.[Class_ID]
          ,pa.[Style_Code]
          ,pa.[Style_Name]
          ,pa.[Style_ID]
          ,pa.[Country_Code]
          ,pa.[Country_Name]
          ,pa.[Country_ID]
          ,pa.[StandardCost]
          ,pa.[SafetyStockLevel]
          ,pa.[ReorderPoint]
          ,pa.[MSRP]
          ,pa.[Weight]
          ,pa.[DaysToManufacture]
          ,pa.[DealerCost]
          ,pa.[DocumentationURL]
          ,pa.[SellStartDate]
          ,pa.[SellEndDate]
          ,pa.[SizeUoM_Code]
          ,pa.[SizeUoM_Name]
          ,pa.[SizeUoM_ID]
          ,pa.[WeightUoM_Code]
          ,pa.[WeightUoM_Name]
          ,pa.[WeightUoM_ID]
          ,pa.[InHouseManufacture_Code]
          ,pa.[InHouseManufacture_Name]
          ,pa.[InHouseManufacture_ID]
          ,pa.[FinishedGoodIndicator_Code]
          ,pa.[FinishedGoodIndicator_Name]
          ,pa.[FinishedGoodIndicator_ID]
          ,pa.[DiscontinuedItemInd_Code]
          ,pa.[DiscontinuedItemInd_Name]
          ,pa.[DiscontinuedItemInd_ID]
          ,pa.[DiscontiuedDate]
          ,pa.[ProductLine_Code]
          ,pa.[ProductLine_Name]
          ,pa.[ProductLine_ID]
          ,pa.[DealerCostCurrencyCode_Code]
          ,pa.[DealerCostCurrencyCode_Name]
          ,pa.[DealerCostCurrencyCode_ID]
          ,pa.[MSRPCurrencyCode_Code]
          ,pa.[MSRPCurrencyCode_Name]
          ,pa.[MSRPCurrencyCode_ID]
          ,pa.[Size_Code]
          ,pa.[Size_Name]
          ,pa.[Size_ID]
          ,pa.[EnterDateTime]
          ,pa.[EnterUserName]
          ,pa.[EnterVersionNumber]
          ,pa.[LastChgDateTime]
          ,pa.[LastChgUserName]
          ,pa.[LastChgVersionNumber]
          ,pa.[ValidationStatus]
    FROM [mdm].[_ProductLeafAttributes] pa
    INNER JOIN [mdm].[_ProductHierarchyLevels] hl ON pa.Member_ID=hl.Leaf_ID
    WHERE hl.Hierarchy = 'Product Management'

    In this example, I have aliased the views as “pa” for “product attributes” and “hl” for “hierarchy levels”. Note that the two views have been joined on the common member key value in pa.Member_ID and hl.Leaf_ID. As in the first view, I have limited the hierarchy content to the Product Management hierarchy. When generating this SELECT statement, there is no need to type all these column names by hand; see Scripting a Table for more information on generating an initial SELECT statement.

    Once the view is created, we can select from the new view and see the combined set of columns. As shown below, the view now contains both hierarchy level columns and attribute columns for each product row.

    image

    With a little creativity and SQL scripting, the standard subscription views can be customized to produce nearly any output view format you may need.

    Legal Notice

    © 2010 Microsoft Corporation. All rights reserved. This information is provided “as-is”. Information and views expressed, including URL and other Internet Web site references, may change without notice. You bear the risk of using this information. Examples are provided for illustration only. Provision of this information does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this information for your internal, reference purposes only.

More Posts Next page »
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement