In the December 2020 release of EDMCS a new feature was released called extracts. It adds a great deal of flexibility to the tool and I am utilizing it more and more in my projects to create view based extracts with customized columns.
While working with the feature I noticed that the record count extracted for a Dimension Export and Extract were different.
The difference in the record counts where caused by the way the two methods handle implicit and explicit shared members.
What is an Explicit Shared Node?
An explicit shared node is when a node is given a direct additional parent.
In the above example NY is explicitly shared under the member MGR1.
What is an Implicit Shared Node?
An implicitly shared node is where the member still has the same direct parent but is part of another rollup because the parent or another ancestor has been shared.
In the above example, the second NY is implicitly shared because its direct parent EAST is shared under SUPER.
The Location Tab
EDMCS has the ability to display how many times a node exists in a hierarchy and you can tell if they are explicit or implicit shares.
The Node NY exists in this hierarchy five times, however there are only three locations. The other two instances under the EAST location are implicit shares.
Dimension Export verse Extract
The legacy dimension export out of EDMCS will ignore implicit shared members in the file. So in the standard dimension export the NY record will show up 3 times. Here is the dimension export with the records highlighted
When running an extract for the same dimension the extract will show both the explicit and implicit shared members.
Most downstream systems do not want the implicit shared members and there is currently no method to remove them in the extract. The remainder of the blog post will focus on a method to remove the implicit shared members in the extract to mirror the functionality of the Dimension export.
Node Properties
The first thing is to create the following two properties in your node type. The naming of the properties is not that important but the meaning of properties are.
Property Name = Node Parent
Level = Relationship
Data Type = String
This property will return the value of the node’s parent. It is necessary because we are going to be utilizing locations to enable this functionality and a location can only return defined properties.
I set this property as Derived and defined the following formula.
I know pretty simple but necessary! If its working properly this should return the node parent like this:
Next we need to setup the ancestors property.
Property Name = Ancestors
Level = Relationship
Data Type = String
This property will return a list of the current node’s ancestors. I would also set this property up as derived with the following formula:
If the formula is working correctly it will display like the following:
This will show a unique path for each node to the top of the hierarchy.
Modify Your Extract
Once you have these formulas in place you can create a Node Expression on your extract to limit the implicit shares from your extract file.
Navigate to your Extract and then go to the Options tab. Click Edit and then open the Node Filter Editor.
Here is the whole expression:
Lets focus on the first part:
If the node only has one location defined in the hierarchy then export it. Basically it has no shared members.
The next part is where the action is!
The IF statement utilizes the find function to return the first location of that node that has the same parent as the current node. This is key because the parent may be part of many locations but the find function returns the first instance of it. Then from that first location, we are returning the ancestor path for that node and comparing it to the current node’s ancestor path. If the two paths match we are exporting the node, if they don’t we are ignoring the node on export.
Here is what the output looks like:
The node NY is only in the file 3 times matching the legacy Dimension Export.
Conclusion
I am hopeful that Oracle will enable this as an option on the extract functionality in the future but this example can help you utilize the Extract functionality in your projects now. It definitely forced me to learn how I can utilize locations in formulas!
Hi Antony,
I have a requirement
My client has alternate roll ups in the same hierarchy (explicitly shared). When he adds a base account under primary parent, then the system should remind him of adding the same base account in 3 other alternate roll up’s. Is this possible? If yes, kindly suggest. Work around is also fine.
Well you would need to add a validation rule that checks after they add the account for the count of the node under the parents you want to check. This code does not do that exactly but you can change it as needed.
This one is checking if the members exists more than once under a parent parent.
{
if (node.locations.count(location => location.properties.Custom.Current_Ancestors.indexOf(“ParentName”).greaterThan(1)).greaterThan(1))
{
return false
}
return true
}
Hi Anthony, what’s the best way to get a hold of you regarding EDMCS?
For only the base members… i want the level 2 parent’s description populated on the base members
for all the non base members the value should be “Default”
I Want to derive a property on account please help me please…