Dynamic domain-based entity creation and relation to base entity

    General discussion

  • Hi everyone -

    My first post here...  I have a base entity (PROJECT) with an attribute called RecordType (a domain-based attribute).  Depending on the value of RecordType, the business wants to use that result set as input for another reference entity, and then relate them together in a possible {one|many}-to-many relationship.  For example:

    If RecordType = Strategy, then there is a need to create a separate entity called STRATEGY with the results of that filter.  The PROJECT entity and STRATEGY now need to be related together in a PROJECT_STRATEGY relationship table.

    There seem to be a couple of options, but none are elegant, and some simply don't work:

    Option #1 (doesn't work): I create the relationship table (PROJECT_STRATEGY) with a domain-based attribute of the PROJECT entity and a second domain-based attribute of the PROJECT entity (but called Strategy), and then try to create a business rule that limits the selection of the second domain-based attribute by RecordType.  MDS tells me I can't use a domain-based attribute in the business rule.

    Option #2: I create a SQL Server view called STRATEGY (which is a query based on the PROJECT entity - "where RecordType = 'Strategy') and use that as the reference set.  However, there is now no way to utilize that result set inside MDS.  In other words, I cannot use it as a domain-based attribute in the PROJECT_STRATEGY relationship table.  This means that an external system/UI would need to enable the relationship selection and write back into the PROJECT_STRATEGY relationship table inside MDS.

    Option #3: I create a STRATEGY entity that is auto-updated (via SQL Server job) every 10 minutes or so from the PROJECT entity (can't figure out how to load a reference entity based on a result set from an existing entity).  This allows me to create two domain-based attributes in the PROJECT_STRATEGY table (as described in Option #1), but is also what I call a non-standard customization, so I have been reticent to go this route.

    So, my questions/comments are:

    Is there any way to make Option #1 work (using business rules)?

    Option #2: Our development team doesn't want to create a separate system/UI to relate the list of PROJECTs to the list of STRATEGYs and then write them back into the PROJECT_STRATEGY relationship table.  This also limits the ability to maintain the PROJECT_STRATEGY table inside MDS since the STRATEGY domain-based attribute would become a free-form attribute at that point.  Is there any way to register a view as a reference entity?

    Should I explore Option #3?  Non-standard, non-native functionality introduced into MDS (any system, really) is something I wanted to avoid, but only from a purity and possible upgrade standpoint.  Should I get over this?

    I have looked at derived and explicit hierarchies for the PROJECT entity, but they do not seem to apply in the scenario above.

    Has anyone tried to create {one|many}-to-many relationships between a base entity and a subset of that base entity without resorting to non-standard customizations within MDS?  Is this as bizarre as it sounds?

    Any help would be greatly appreciated.


    Wednesday, August 13, 2014 3:08 PM