Managing column and data conformities

[KS_EDW]

I have the opportunity to rebuild some very large conformed dimensions (mix of column and data conformity in the same dimension). I’ve designed many of these before but have never ask what other methods (than mine) are used to manage column/data conforming metadata.

Most often, I created hierarchies and groups (using columns) to describe data conformity among the contributing systems. This is the same practice used to create row level security groups… and works well for describing data level conformity (“groups”) among different systems contributing to a dimension. BUT… I’m completely open to better/alternate methods…. How do others handle this? Preferably… is there a more dynamic method that can recognize if two contributing systems that started as conforming have become non-conforming (based on data)?

For example,
All of these application are going to contribute to the same columns in a dimension:

Application A and B use the same data/attribute values (standardized among the applications)

Application C wants to conform to A and B so a conversion lookup is maintained and applied during ETL.

Application D is in its own universe… but shares similar business columns as A, B, C, D, E, and F, so will use the conformed dimension.

Application E and F share the same data/attribute values but do not have share data/attribute values with A, B, and C.

The reason I want this information is so I can “alert” users (via the BI) that A, B, and C share attribute values, E and F share a different set of attribute values, and D has its own set of values. Thus, when users are working among and combining the subject areas, they are aware there are same/different attribute codes that possibly mean the same thing.

This has been WONDERFULLY handy in the past! Preventing loads of coding, reporting, and analytical issues.

Are there some other methods or suggestions... references to readings???

Let me know if this does not make sense.