(this post was contributed by Val Lovicz, Principal Program Manager on the MDS Team)
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.
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.
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.
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.
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.
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.
2. Enter a subscription view name like “_ProductHierarchyLevels”.
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.
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.
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.
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.
© 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.