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.