Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Adjacency Matrix in MDS with a recursive derived hierarchy? Anyone tried this? Expand / Collapse
Posted Wednesday, April 30, 2014 9:09 AM


Group: General Forum Members
Last Login: Thursday, March 12, 2015 2:42 PM
Points: 199, Visits: 310
I'm working through a problem in MDS and I'm curious to see if anyone else has run into this.

If you've ever worked with CI (UPC barcodes), certain commercially distributed products can be "packaged" in certain ways as defined by suppliers. For example, buying a six-pack of Coca-Cola, each one of the cans have a barcode UPC that indicates that the particular can is to only be distributed as part of a "pack" - they can't be distributed or sold individually.

Certain products can be distributed and sold in more than one way. For example, that same six pack can be distributed and sold as a case bundle, or it can be distributed as a single six pack. Each packaging method has a certain hierarchy.

The upshot of this is, you can't represent the CI model using anything less than an adjacency matrix.

Now, this can be represented in SQL pretty easily - there's tons of examples online that go through the basics of how to do it and how to write the recursive CTE function to represent and return data in this format. My problem is with MDS.

In Master Data Services (MDS) you have several hierarchy types to choose from. The hierarchy type that makes the most sense to me is to use the recursive derived hierarchy. It makes sense because I want to define an entity that contains all the "acceptable" adjacencies for particular items, such that when a user looks at the data in MDS or tries to update its value, it will have certain domain-based enforcement (and thusly lookups).

I think that I've crossed a bridge too far with MDS - but, perhaps one of you knows something I don't know. It looks like any hierarchy in MDS is only based in a parent - child relationship, so that's not quite what the doctor ordered here. I want to avoid explicit hierarchy here (for reasons that should be obvious but if not, ask and I can explain).

Any ideas?

Post #1566450
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse