(this post was contributed by Val Lovicz, Principal Program Manager on the MDS Team)
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.
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.
A level-based representation of this hierarchy would look like the following.
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.
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.
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.
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.
© 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.
Very interesting. I'm curious since I have a similar situation that' biting me in MDS right now. I have a need to create and maintain a recursive derived hierarchy in an adjacency matrix. An adjacency matrix item can have multiple "adjacent" unique values. Typically in SQL this is achieved by using a "Left Boundary" and "Right Boundary" on the entity definition.
Can we modify your view to do something similar as defined by the data?
I know MDS won't visualize this (although I wish it would). This is commonly an issue when dealing with packaging codes as a result of CI / UPC definition. The same item can be packaged and sold in different ways - so you want that to be inherited by the things that are "adjacent" so the rules validate correctly...