(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.
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.