{"id":9,"date":"2007-05-25T12:02:18","date_gmt":"2007-05-25T12:02:18","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?p=9"},"modified":"2023-01-16T06:08:11","modified_gmt":"2023-01-16T06:08:11","slug":"who-needs-group-populator-when-you-have-multivalue-tables","status":"publish","type":"post","link":"https:\/\/www.wapshere.com\/missmiis\/who-needs-group-populator-when-you-have-multivalue-tables","title":{"rendered":"Who needs Group Populator when you have Multivalue tables?"},"content":{"rendered":"

Getting members into groups seems to be an early hurdle for many people. Microsoft provide the Group Populator<\/a> – an obtusely round-about method involving SQL tables, Select queries, input text files and code that must be run outside of MIIS. Once I got the hang of the multivalue aspect of the SQL MA, I couldn’t figure out why anyone would use Group Populator at all!<\/p>\n

One thing I will concede about Multivalue SQL MAs is they can be monstrously slow for large data sets. Perhaps Group Populator is quicker? Even if it is though, I find the Multivalue method so simple and flexible. Group Populator restricts you to creating groups with rules (like everyone with an office address that contains “Building A” goes into the “Building A” group). A multivalue table gives you complete freedom over who goes into what group, and <\/em>it can be used for other multivalue attributes as well!<\/p>\n

So enough waffle – lets get down to how it works.<\/p>\n

A Multivalue SQL MA needs two tables:<\/p>\n

    \n
  1. Table One lists all the objects with the minimum information required to join them to their metaverse counterpart. Probably all you will need here is an object type and an identifying attribute.<\/li>\n
  2. Table Two links the objects to values, with multiple values possible for one object. It must include an Attribute Name column – but you can include different Attributes in the one table.<\/li>\n<\/ol>\n

    Take an AD\/Exchange example – configuring Distribution Groups. Not only must these groups have members, but they are also restricted on who can email them. The attributes are member<\/strong> for the membership list and (somewhat obscurely) dlMemSubmitPerms<\/strong> for the restricted list.<\/p>\n

    My first table I will call ADMV_Objects<\/strong>. It lists all the possible Groups and Users that may be involved.<\/p>\n\n\n\n\n\n\n\n\n\n
    ObjectID<\/strong><\/td>\nObjectType<\/strong><\/td>\n<\/tr>\n
    jpearson<\/td>\nUser<\/td>\n<\/tr>\n
    Directors<\/td>\nGroup<\/td>\n<\/tr>\n
    Sales<\/td>\nGroup<\/td>\n<\/tr>\n
    mdali<\/td>\nUser<\/td>\n<\/tr>\n
    than<\/td>\nUser<\/td>\n<\/tr>\n
    …<\/td>\n…<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    My second table I will call ADMV_Values<\/strong>. It must include the ObjectIDs exactly as they are in the first table, matched to attribute values.<\/p>\n\n\n\n\n\n\n\n\n\n\n
    ObjectID<\/strong><\/td>\nAttributeName<\/strong><\/td>\nValue<\/strong><\/td>\n<\/tr>\n
    Directors<\/td>\nmember<\/td>\nthan<\/td>\n<\/tr>\n
    Directors<\/td>\nmember<\/td>\njpearson<\/td>\n<\/tr>\n
    Directors<\/td>\ndlMemSubmitPerms<\/td>\nDirectors<\/td>\n<\/tr>\n
    Directors<\/td>\ndlMemSubmitPerms<\/td>\nSales<\/td>\n<\/tr>\n
    Sales<\/td>\nmember<\/td>\nmdali<\/td>\n<\/tr>\n
    Sales<\/td>\ndlMemSubmitPerms<\/td>\nSales<\/td>\n<\/tr>\n
    …<\/td>\n…<\/td>\n…<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    It is now just a matter of configuring my SQL MA.<\/p>\n

    On the Connect to Database <\/strong>page I put the Objects table in as the primary table, and the Values table in as the Multivalue table.<\/p>\n

    \"Multivalue<\/p>\n

    Then on the Configure Columns<\/strong> page I click the Multi-value<\/strong> button to open the Multi-value settings<\/strong> page.\u00a0 Here I get to tell MIIS:<\/p>\n

      \n
    • where to look for the the attribute names;<\/li>\n
    • where to look for the values – unhelpfully referred to as “String attribute column”, but values is what they mean; and<\/li>\n
    • which multivalue attributes to expect.<\/li>\n<\/ul>\n

      \"MultiValue-Settings\"<\/p>\n

      Finally set your Join rules so MIIS knows where to flow the attributes and you’re away!<\/p>\n","protected":false},"excerpt":{"rendered":"

      Getting members into groups seems to be an early hurdle for many people. Microsoft provide the Group Populator – an obtusely round-about method involving SQL tables, Select queries, input text files and code that must be run outside of MIIS. Once I got the hang of the multivalue aspect of the SQL MA, I couldn’t…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":[]},"categories":[22,34,28,5],"tags":[],"class_list":["post-9","post","type-post","status-publish","format-standard","hentry","category-groups","category-ilm2007","category-miis2003","category-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pkp1o-9","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/9","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/comments?post=9"}],"version-history":[{"count":3,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/9\/revisions"}],"predecessor-version":[{"id":3340,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/9\/revisions\/3340"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=9"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/categories?post=9"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/tags?post=9"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}