Many-to-Many Relationships in Derived Hierarchies (Master Data Services)

I was looking what's new in Master Data Services 2016. I come across that MDS will support many to many relationship in Derived Hierarchies after seeing CTP 2.3. Initially I thought it is a cool feature.

I work for a prestigious bank in Europe and they always have tough requirement regarding hierarchy management. I thought this feature can address the need of bank and also make the user interface intuitive. I am referring to https://msdn.microsoft.com/en-us/library/mt345527.aspx , but my assumption was wrong. I tried exploring this feature in Azure environment with CTP 2.3 and found that you are not allowed to move the members of hierarchy from Hierarchy Editor. To move the members , you have to use the Entity Explorer and that is the reason this is not a nice feature.

Actually to address the same kind of issue in SQL 2012. We came up with an idea of Bridge entity in SQL 2012 which you call a class (( the new type of level) in SQL 2016 CTP 2.3.

Let me give you an overview of what we require. We have a requirement that we need a multi level hierarchy for different systems/applications. The product is mapped to hierarchy. So we have a model called Product and it can be used as a whole or subset as needed by the systems/applications. We map the product to hierarchy at the lowest level.

Assume you have a hierarchy with 3 levels than we have Level1, Level2 and Level3 defined as a separate entities. Level1 is the top level, so it is defined as a separate entity. Level2 is the second level and we define a Domain based attribute (DBA) for it which points to Level1. Level3 is the lowest level where we define 2 domain based attributes, first one is referring to Level2 and the second one is referring to Product. You can argue that we can use the recursive entities but it has its own limitations. You can add product to any level (it should be at lowest level only) using recursive or parent child entity. MDS doesnt support in a simple way to create a business rule which should allow user to add the product at only lowest level, workflow can work here but I dont want to use it. Also the way it works and UI look will confuse the business users.

The idea of implementing this kind of solution is that to keep the Hierarchy key static even though you have shuffled the product. This hierarchy key will be referred to Fact tables. Whenever product is shuffled, there is a no need to reload the historical data for fact. Just process the shared dimension of product and the changed measures will appear in the report. Please take a note that here we have a typical requirement. Whenever product is shuffled within the hierarchy , the measure associated with it should be shown to the new place it belongs to now. Business don't want to see it as Dim Type 2. I can say it is typical Type 1 and not simple. If you want to keep it simple than you have to waste lots of resources and time.

So I see that Class ( the new type of level) in SQL 2016 is trying to address the our solution built in SQL 2012 but it is not flexible.

The problems are:

1.Microsoft is not exposing the new type of level and it seems to be implemented implicit.

2. In our solution still you can move around the products at lowest level directly from Hierarchy editor but you can't do it in SQL 2016.

3. The solution we made in SQL 2012 will break in SQL 2016 and it is really weird.

I believe it is a good initiative by Microsoft but I believe this has not been thought well before designing and implementing. It may be due to lack of real scenarios.

I urge Microsoft to address above problems and redesign this feature as it should not create issue for the solution you built in previous version (SQL 2012). I am happy to help Microsoft in doing so. You can contact me at manishkr.cv@gmail.com if you need more information on the same. I can share my mobile number as well if you need it. Looking forward to hear positively from Microsoft.

September 11th, 2015 10:16am

1. The mapping entity (the entity that has 2+ DBA. In your case, it is level 3.) is not exposed in the m2m relationship.

2. Currently, the m2m level is readonly. We may enable update and delete in the near future.

3. Yes. The existing solution shouldn't break. Can we have more details on it?

Free Windows Admin Tool Kit Click here and download it now
September 11th, 2015 2:14pm

Let me give you more detail. The mapping entity is is not exposed in the m2m relationship and this is creating problem in SQL 2016. In my case Level 3 which has 2 + DBA is exposed in SQL 2012, so we can move the Products mapped to Level 3 using hierarchy editor. I think it is not clear. Let me explain in details. 

Level 1 member 1 --> Business

Level 1 member 2 --> Personal

Level 2 member 1 --> Loan and parent is (Level 1 member 1 --> Business)

Level 2 member 2 --> Deposit and parent is (Level 1 member 1 --> Business)

Product member 1 --> 001

Product member 2 --> 002

Product member 3 --> 003

Product member 4 --> 004

Product member 1 and 2 are mapped to (Level 2 member 1) using Level 3 as a bridge. So 001 and 002 are mapped to Loan under BusinessProduct member 3 and 4 are mapped to (Level 2 member 2) using Level 3 as a bridge. So 003 and 004 are mapped to Deposit under Business

Now if I want to move 001 from Loan under Business to Deposit under Business, I can just drag and drop it using hierarchy editor. This I can do in SQL 2012. If I imply the same solution in SQL 2016 than I can't just drag and drop 001 from Loan under Business to Deposit under Business using hierarchy editor. To do the same function in SQL 2016 I have to use Entity Editor. This is what I called as weird.

The things I can do without mapping entity (m2m relationship) in SQL 2012, I will unable to do that in newer version i.e. in SQL 2016. You are providing mapping entity concept as a new functionality but I see a problem because you are not giving a full control of it. I can do the things without using mapping entity in SQL 2012 as per my Bridge (Level 3) approach. And it seems to be better and flexible approach than what you offer in SQL 2016.

So if I want to choose to migrate to MDS 2016 from 2012 than preferably I will not do that even though we have very fascinating features like performance and row level security enhancement.


September 11th, 2015 5:51pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics