This post has been republished via RSS; it originally appeared at: SQL Server Integration Services (SSIS) articles.
First published on MSDN on Jun 02, 2015The SQL Server 2016 version of Master Data Services deprecates Explicit Hierarchies (EH) and Collection and all related components. Members that before were modeled as Consolidated (EH parent) and Collection member types will henceforth be modeled as Leaf members in Derived Hierarchies (DH). This is made possible by new DH features that better enable them to take the place of EHs:
1. Recursive Derived Hierarchies (RDH) can now be used to assign member security permissions
In the above example, the simple RDH is comprised of a single recursive level representing an Employee->Manager relationship. RDHs can be more complex, by including level(s) below and/or above a recursive level. The analog of an EH is a RDH with a single non-recursive level beneath the recursive level.
Member duplication: But be aware that when a RDH contains a level above a recursive level, members at the recursive level can appear more than once within the hierarchy (under their recursive parent and under their non-recursive parent). Member duplication can also happen when the topmost level is recursive but does not anchor null recursions, in which case each member appears directly under Root and under its non-null parent. The number of times a member appears in such a hierarchy is equal to its maximum depth within the hierarchy. RDHs that allow member duplication cannot be used for member security. Otherwise, a member could inherit permissions along different inheritance paths, resulting in ambiguous resolution.
2. The DH Explorer page now shows unassigned (unused) members for each hierarchy level:
In the above screenshot, the “MyUnusedSubCategory” member of the SubCategory entity is not assigned to a category. That is, the value of its Category attribute is blank, as show in the grid on the right. Before, such orphaned members could not be easily seen in the DH Explorer. But now they and any of their children are shown under the new “Unused” node. Unused nodes are grouped by hierarchy level. Members can be dragged-and-dropped or cut-and-pasted back and forth between the Unused and Root nodes, at the applicable levels.
The Unused node is likewise visible in the Preview pane when managing DH levels from the System Administration page…
… and when managing hierarchy member security permissions:
Any member, whether under Root or Unused, can be assigned a permission. The Root, Unused, and Unused Level pseudo members can also be assigned permissions.
To reduce UI clutter, the Unused nodes will only be shown when applicable. For example, a hierarchy with only a single level cannot have unused members, because all members appear under root. So the Unused node will not be shown. Likewise for a simple RDH with no levels above or below the recursive level:
For a hierarchy that can only have unused members at a single level (like a DH with two levels or a RDH with a single level below the recursive level), the level-specific unused nodes will not be shown. Instead, all unused members will appear directly under the top-level Unused node. The below screenshot shows the Employee->Manager RDH with a single level below the recursive level, that indicates the Project Managers (PM) for the Project entity.
3. Added a stored procedure that converts EHs to RDHs and Consolidated and Collection members to Leaf members: mdm.udpConvertCollectionAndConsolidatedMembersToLeaf
For this next release, EHs and non-leaf members are deprecated but still fully supported. The conversion sproc is optional and is only executed manually. But in a future release when support for these deprecated item is removed, the conversion sproc will be mandatory and run as part of the db upgrade process. It is recommended that users of EHs and/or Collection members run this sproc as soon as practicable, to get an earlier start in identifying and resolving any issues. Backup the database before running the sproc! See the sproc’s code header for more details about the conversion process, which as of this writing is thus:
/*
==============================================================================
Copyright (c) Microsoft Corporation. All Rights Reserved.
==============================================================================
Converts deprecated collection members and consolidated members to leaf members. Also converts their corresponding deprecated explicit hierarchies (EH) to recursive derived hierarchies (RDH).
Conversion process:
Converting Consolidated members and EHs
----------------------------------------
Suppose there is an entity named Product that contains the following EHs:
EH Name Mandatory EH?
********* ***************
Bundle Yes
NonMandatory No
- Create one new entity per EH. The name of the new entity is "<Leaf entity name>_<EH name>". In the above example, two new entities will be created, "Product_Bundle" and "Product_NonMandatory".
-- Copy consolidated attributes from original EH's entity to leaf attributes on each new entity.
-- Copy all consolidated members of the EH to leaf members on the corresponding new entity. For mandatory hierarchies that have leaf members directly under ROOT, add a "Pseudo Root" member. For non-mandatory EHs that have unassigned leaf members, leave them as-is.
-- Add a new self-referencing DBA, named "Parent", to the new entity and set its value to reflect the original consolidated member's parent in the EH.
- Create new DBAs on the original entity, one per EH on the entity. The name of each DBA will be the EH name. In the above example, two DBAs will be added to the Product entity: Bundle (which references Product_Bundle as its domain entity) and NonMandatory (which references Product_NonMandatory). The value of these DBAs is set to reflect each leaf member's parent in the EH being copied.
- Copy transactions. EH parent/sibling move transactions are converted to attribute value change transactions. If the @DeleteOriginals flag is set, the original transaction rows will be updated, rather than copied, for efficiency. Otherwise, transaction annotations are also copied.
- Create one new RDH per EH. Each RDH will have a single non-recursive level beneath the recursive levels. The name of the RDH will follow the same pattern as the new entity
names, i.e. "<Leaf entity name>_<EH name>", or "Product_Bundle" and "Product_NonMandatory" in the above example.
- For each existing DH with an EH cap, create a new RDH with (potentially) multiple levels below. The name of the new hierarchy will be "<EH cap DH name>_NoCap".
- Copy business rules (BR), both rules that applied to the consolidated members being copied and leaf rules that were referencing consolidated parent attributes. In both cases, the consolidated parent attribute references are converted to instead apply to the attributes of the new entities.
Converting Collections.
For each entity with collection member(s):
----------------------------------------
- Create a new entity "<entity name>_Col". This corresponds to the deprecated "%_CN" table.
-- Copy collection attributes from original entity to leaf attributes on the new entity
-- Copy collection members from original entity to leaf members on then new entity
- Create new entity "<entity name>_ColMem" to track collection membership info. This corresponds to the deprecated "%_CM" table.
-- Add parent DBA, "Parent_<entity name>_Col", that will reference the parent "collection"
-- Add child DBAs, that correspond to collection members (exactly one will have a non-null value, per member):
-- "Child_<entity name>" for leaf members of the collection. References the original entity.
-- "Child_<entity name>_Col" for collection members of the collection
-- "Child_<EH name>", one per EH on the original entity, for consolidated members of the collection
-- Add attributes for "%_CM" table columns Weight and SortOrder
-- Create new members, one for each "%_CM" table row
- Copy collection member transactions. If the @DeleteOriginals flag is set, the original transaction rows will be updated, rather than copied, for efficiency. Otherwise, transaction annotations are also copied.
Additional steps
----------------------------------------
- Copy consolidated and collection attribute groups to leaf attribute groups on their corresponding new entities.
- Copy consolidated and collection subscription views (SV)
Old ViewFormat_ID Deprecated? New ViewFormat_ID
******************* ************* *******************
1 (Leaf members) No unchanged
2 (Consolidated members) Yes 1 (Leaf members)
3 (Collection members) Yes 1 (Leaf members)
4 (Collection membership) Yes 1 (Leaf members)
5 (Explicit Parent-Child) Yes 7 (Derived Parent-Child)
6 (Explicit levels) Yes 8 (Derived levels)
7 (Derived Parent-Child) No unchanged
8 (Derived levels) No unchanged
For the consolidated-related SVs (ViewFormat_ID 2, 5, and 6) there is a one-to-many mapping. One new SV is created per EH. To disambiguate them, the EH name is appended to the SV name.
- Security permissions
-- Copy model object permissions from old entity and its consolidated and collection member types, attributes, and attribute groups, to their corresponding new entity objects.
-- Copy member permissions from old EHs to new RDHs (collections and DHs with an EH cap cannot be used for member security, so they will have no permissions to copy).
- If the @DeleteOriginals flag is set, delete the originals of the copied items.
- Kick off the service broker to process member security.
Additional notes:
1. EH child sort order info, as stored in the "%_HR" table's SortOrder column, is lost. Derived Hierarchies do not support child sort order.
2. All copied/converted BRs are left in an unpublished state. The user must manually publish these rules, if desired, after running this script (the publish process can only be executed from the web service API, not from SQL)
3. Validation issues (as stored in tables tbl_{MID}_VL, tbl_{MID}_VLH)) are not copied. They can be regenerated after BRs are published.
4. The copy process checks for and handles any naming conflicts that would violate uniqueness constraints. For example, if the script tries to create a new entity named "Product_Bundle", but that name is already used by another entity within the model, then it will try to use "Product_Bundle1" for the new entity name. If that name is already taken, then it will try "Product_Bundle2", and so on.
5. The copy process preserves original audit info (i.e. created/lastChanged userID/dateTime, etc) for copied items (i.e. master data, DH levels, security permissions, transactions, business rules, etc).
*/
CREATE PROCEDURE mdm.udpConvertCollectionAndConsolidatedMembersToLeaf
(
@Model_ID INT = NULL -- Optional filter. When provided, only EHs and Collections within the specified model will be operated on.
,@Entity_ID INT = NULL -- Optional filter. When provided, only EHs and Collections within the specified entity will be operated on.
,@DeleteOriginals BIT = 1 -- When 0, the sproc is non-destructive. When 1, the sproc deletes the original collections, consolidated members, EHs, etc that are converted.
)
AS BEGIN ...
4 (Collection membership) Yes 1 (Leaf members)
5 (Explicit Parent-Child) Yes 7 (Derived Parent-Child)
6 (Explicit levels) Yes 8 (Derived levels)
7 (Derived Parent-Child) No unchanged
8 (Derived levels) No unchanged
For the consolidated-related SVs (ViewFormat_ID 2, 5, and 6) there is a one-to-many mapping. One new SV is created per EH. To disambiguate them, the EH name is appended to the SV name.
- Security permissions
-- Copy model object permissions from old entity and its consolidated and collection member types, attributes, and attribute groups, to their corresponding new entity objects.
-- Copy member permissions from old EHs to new RDHs (collections and DHs with an EH cap cannot be used for member security, so they will have no permissions to copy).
- If the @DeleteOriginals flag is set, delete the originals of the copied items.
- Kick off the service broker to process member security.
Additional notes:
1. EH child sort order info, as stored in the "%_HR" table's SortOrder column, is lost. Derived Hierarchies do not support child sort order.
2. All copied/converted BRs are left in an unpublished state. The user must manually publish these rules, if desired, after running this script (the publish process can only be executed from the web service API, not from SQL)
3. Validation issues (as stored in tables tbl_{MID}_VL, tbl_{MID}_VLH)) are not copied. They can be regenerated after BRs are published.
4. The copy process checks for and handles any naming conflicts that would violate uniqueness constraints. For example, if the script tries to create a new entity named "Product_Bundle", but that name is already used by another entity within the model, then it will try to use "Product_Bundle1" for the new entity name. If that name is already taken, then it will try "Product_Bundle2", and so on.
5. The copy process preserves original audit info (i.e. created/lastChanged userID/dateTime, etc) for copied items (i.e. master data, DH levels, security permissions, transactions, business rules, etc).
*/
CREATE PROCEDURE mdm.udpConvertCollectionAndConsolidatedMembersToLeaf
(
@Model_ID INT = NULL -- Optional filter. When provided, only EHs and Collections within the specified model will be operated on.
,@Entity_ID INT = NULL -- Optional filter. When provided, only EHs and Collections within the specified entity will be operated on.
,@DeleteOriginals BIT = 1 -- When 0, the sproc is non-destructive. When 1, the sproc deletes the original collections, consolidated members, EHs, etc that are converted.
)
AS BEGIN ...
----
For More information refer to: