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.

Publishing Master Data with Subscription Views - Part 1: The Basics

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

Background

This post is the first in a series on Subscription Views in SQL Server 2008 R2 Master Data Services (“MDS”). In this first post I will describe this feature and then provide step by step instructions for creating example views using the sample Product model delivered with MDS.

Subscription views provide a simple way to extract and publish master data from MDS using Integration Services, SQL scripts or any other tools and techniques that access a standard SQL Server view. MDS administrators may generate subscription views as needed from the Integration Management - Export Page of the Master Data Manager Web User Interface (“UI”) or the ExportViewCreate operation of the Web services API.

Subscription views are helpful because they denormalize the master data and apply recognizable names to columns. This makes them much easier to work with than the underlying tables which are normalized for performance and data integrity and have key-based names.

Note: Direct queries against the MDS tables should be avoided because future product releases may not have the same schema objects.

Once generated, subscription views appear as ordinary views within the “mdm” schema of the MDS SQL Server database. To access the views, you need to have the db_datareader role on the MDS database or be assigned a role with Select permission on the views.

Note: Permission to access database views should only be granted to trusted individuals who may read all MDS data. Views do not enforce MDS application security permissions.

Subscription views are best suited to bulk, batch-oriented exports of master data and may not be best for all integration scenarios. For event-based integration, use the change tracking notification feature. For programmatic access that enforces user security and application logic, use the Web services API.

Managing the Subscription Views

To browse and manage existing subscription views or generate new views, use the Export page in the UI.

1. Go to the Master Data Manager URL in your web browser.

2. Click Integration Management on the home page.

3. Click Export on the menu bar.

After following the above steps, you will see the list of Subscription Views, which is initially empty in a new MDS instance. Here you may add new subscription views, edit configurations of existing views, delete existing views or regenerate views as needed when the underlying model changes.

All objects on the Export page are described in Books Online, so I will not describe the individual page elements here.

Creating an Attribute View

Let’s create a view of all products and their attributes using the sample Product model. To create this view, perform the following steps.

1. Click the Add Subscription View button. The Create Subscription View Definition panel will appear.

2. Enter a subscription view name like “_ProductLeafAttributes”. I added the underscore so that it will sort to the top of the list.

3. Select the “Product” model.

4. Select the “VERSION_1” version.

5. Select the “Product” Entity.

6. Select the “Leaf attributes” format.

7. Click Save. The definition panel will close and the new view will now appear in the list.

If you now connect to the MDS database using SQL Server Management Studio, you will see the newly created view in the database. Of course, you will need to have sufficient permissions directly in the database to connect and see the views.

image

Note that you may need to refresh the list of views if you already had the list open in Management Studio when you generated the new subscription view in MDS.

Usefulness of an Attribute View

The “Leaf Attributes” view format lists all the leaf entity members and includes columns for all attributes. In our product example, domain-based attributes are denormalized into the Code and Name of the referenced members in other entities rather than providing meaningless key values. Other information, such as the Version Name and Version Flag is also provided. Each column is named based on the user-defined model object names for easy recognition.

image

Creating an Explicit Hierarchy View

Let’s create a level-based view of explicit hierarchies applied to products using the sample Product model. To create this view, perform the following steps.

1. Click the Add Subscription View button. The Create Subscription View Definition panel will appear.

2. Enter a subscription view name like “_ProductHierarchyLevels”.

3. Select the “Product” model.

4. Select the “VERSION_1” version.

5. Select the “Product” Entity.

6. Select the “Explicit levels” format.

7. Change Level to 4 so that we expose up to four level columns.

8. Click Save. The definition panel will close and the new view will now appear in the list.

Usefulness of a Hierarchy View

Hierarchies are often represented in relational tables and views as level-based columns (i.e. each column represents a level) or as parent-child relationships (e.g. each row represents a relationship of one child to one parent). Subscribing systems will impose preferred data formats. For example, a relational data warehouse would typically require a level-based view.

image

Selecting Versions

Subscription views filter versions based on a version or a version flag. Use version flags as the recommended approach when you expect the published version to change periodically. Version flags allow you to point at the currently desired version for export by moving the version flag from version to version; this avoids the need to regenerate the view with each new version.

Model Changes; Regenerating a View

Over time, you may need to expand or otherwise revise your master data model. If you already have subscription views, those views may become out of sync with the underlying model. For example, there may be a new attribute that is not included in the view. Because subscription view changes can destabilize your processes that consume those views, MDS lets administrators electively regenerate views (and sometimes requires deletion of views prior to a model change). As a guide, the Changed column in the list of views will show True for any views where the model has changed since view generation. To regenerate a view against the latest model: select the view, click Edit then click Save.

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.

Published Wednesday, March 03, 2010 2:45 PM by mattande
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Master Data Services Team said:

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

March 3, 2010 4:21 PM
 

Master Data Services Team said:

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

March 4, 2010 9:46 AM
 

lauga said:

Hi and thanx for a great blog

You say:

"For event-based integration, use the change tracking notification feature. For programmatic access that enforces user security and application logic, use the Web services API."

I would like to look into the change tracking notification feature, do you have any examples showing how to use it and how it works? Is that supported in the CTP version?

March 30, 2010 8:59 AM
 

mattande said:

Hi Lauga,

Please see the blog post http://sqlblog.com/blogs/mds_team/archive/2010/02/15/enabling-human-workflow-part-1-changing-your-model.aspx, specifically step 4, which gives an example of using change tracking.  Unfortunately, change tracking is not available in the CTPs, but will be part of an upcoming release.

Thanks,

Brian

March 31, 2010 11:23 AM
 

bernadette bernabe said:

Hi,

I have a problem in deleting an entity from my model in MDS. It says "The entity cannot be deleted because it is referenced by a domain-based attribute". I have already deleted the domain-based attribute from the entity. Still I am unable to delete it. Do we have a work around on this?

September 12, 2011 2:54 AM
 

Sonia said:

Hello,

I see these views on your screenshot "viw_SYSTEM_1_1_CHILDATTRIBUTES".

Would you mind letting me know, what these views are? can I use them to export my entity data?

April 15, 2014 4:12 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement