Adventures In Groovy – Part 11: Accessing Metadata Properties
Introduction
Groovy opens up a lot of things above and beyond performance improvements and improving the user experience. One example is the possibility to interact with the metadata. Dimensions and members can be queried for all types of things which can be useful in many situations. Is the POV at a level 0? What is the parent of the current POV member? Does the member exist in another application? What about pushing data for specific UDAs and dynamically generating the Data Map? How about dynamically generating the Data Map to ignore dynamic calculated members? These are just some examples to get you thinking about where this could be useful.
Code Example
This article won’t get into the logic to accomplish the above examples once the property is identified but will explain how to extract properties for its use. Below is an example of retrieving every property of an account named Regular_Cases. This iterates through every metadata property and writes it to the log.
// Get the dimension of the member in question
Dimension AccountDim = operation.application.getDimension("Account")
// Get the member
Member AccountMbr = AccountDim.getMember("Regular_Cases")
// Print the map to the log
println AccountMbr.toMap()
def memberProps = AccountMbr.toMap()
// Print the member name
println AccountMbr.toString()
// Print every property and corresponding property value
for ( e in memberProps ) {
println "${e.key} = ${e.value}"
}
When this is executed, the following is sent to the log.
println AccountMbr.toMap() produces
{Formula (rGP)=<none>, Plan Type (GP)=true, Solve Order (rGP)=0, Formula (Fin)=<none>, Data Storage (OEP_WFSC)=never share, Time Balance=flow, Formula=<none>, UDA=HSP_NOLINK, Skip Value=none, Variance Reporting=non-expense, Data Storage (GP)=never share, Essbase Name=Regular_Cases, UUID=c842d186-6d83-4b90-8d1e-49474a6a8a1d, Member=Regular_Cases, Data Storage=never share, Data Storage (rFin)=never share, Formula (rFin)=<none>, Aggregation (rWFP)=+, Formula (GP)=<none>, Data Storage (rWFP)=never share, Data Storage (OEP_REP)=never share, Data Storage (rGP)=never share, Data Type=currency, Formula (OEP_WFP)=<none>, Plan Type (rFin)=true, Aggregation (OEP_WFP)=+, Data Storage (OEP_WFP)=never share, Parent=GP_Accts, Two Pass Calculation=false, Aggregation (GP)=+, Plan Type (rGP)=true, Process Management Enabled=true, Plan Type (rWFP)=false, Source Plan Type=GP, Aggregation (OEP_WFSC)=+, Exchange Rate Type=none, Plan Type (Fin)=true, Alias: English=Regular Cases, Plan Type (OEP_WFP)=false, Aggregation (OEP_REP)=+, Solve Order (rWFP)=0, Data Storage (Fin)=never share, Hierarchy Type=dynamic, Allow Upper Level Entity Input=false, Account Type=revenue, Formula (OEP_REP)=<none>, Aggregation (Fin)=+, Aggregation (rGP)=+, Plan Type (OEP_WFSC)=false, Formula (rWFP)=<none>, Formula Description=<none>, Aggregation (rFin)=+, Solve Order (rFin)=0, Formula (OEP_WFSC)=<none>, Solve Order (OEP_REP)=0, Valid For Consolidations=false, Plan Type (OEP_REP)=false}
for ( e in memberProps ) {println “${e.key} = ${e.value}”} produces
Regular_Cases
Formula (rGP) = <none>
Plan Type (GP) = true
Solve Order (rGP) = 0
Formula (Fin) = <none>
Data Storage (OEP_WFSC) = never share
Time Balance = flow
Formula = <none>
UDA = HSP_NOLINK
Skip Value = none
Variance Reporting = non-expense
Data Storage (GP) = never share
Essbase Name = Regular_Cases
UUID = c842d186-6d83-4b90-8d1e-49474a6a8a1d
Member = Regular_Cases
Data Storage = never share
Data Storage (rFin) = never share
Formula (rFin) = <none>
Aggregation (rWFP) = +
Formula (GP) = <none>
Data Storage (rWFP) = never share
Data Storage (OEP_REP) = never share
Data Storage (rGP) = never share
Data Type = currency
Formula (OEP_WFP) = <none>
Plan Type (rFin) = true
Aggregation (OEP_WFP) = +
Data Storage (OEP_WFP) = never share
Parent = GP_Accts
Two Pass Calculation = false
Aggregation (GP) = +
Plan Type (rGP) = true
Process Management Enabled = true
Plan Type (rWFP) = false
Source Plan Type = GP
Aggregation (OEP_WFSC) = +
Exchange Rate Type = none
Plan Type (Fin) = true
Alias: English = Regular Cases
Plan Type (OEP_WFP) = false
Aggregation (OEP_REP) = +
Solve Order (rWFP) = 0
Data Storage (Fin) = never share
Hierarchy Type = dynamic
Allow Upper Level Entity Input = false
Account Type = revenue
Formula (OEP_REP) = <none>
Aggregation (Fin) = +
Aggregation (rGP) = +
Plan Type (OEP_WFSC) = false
Formula (rWFP) = <none>
Formula Description = <none>
Aggregation (rFin) = +
Solve Order (rFin) = 0
Formula (OEP_WFSC) = <none>
Solve Order (OEP_REP) = 0
Valid For Consolidations = false
Plan Type (OEP_REP) = false
Data Storage (GP) = never share
Getting A Specific Property
Typically, there would not be a need to pull every property. There might be times when having access to these, however, is useful in calculations. If a currency calculation is being executed, for example, the rate applied is different if the member is a balance sheet account. Getting one value can be retrieved by building on the above script.
def keyProp = "Account Type"
if(memberProps[keyProp] = "Revenue"
{do something}
elseif(memberProps[keyProp] = "Balance Sheet"
{do something}
Wrap Up
This may seem a little worthless at first, but if you think about all the BSO functions (getting UDAs, Account types for VAR functions, and member relation functions) that require this information, mimicking them in Groovy requires access to the metadata properties. So, don’t underestimate its use for things like variance, currency, and other calculations, that are done outside of Essbase/Planning calculations and member formulas.
Can i create a shared member using groovy?
I don’t know if you can create a shared member or not. I would think so. I will warn you that the ability to edit members from a calculation will not be available in the future unless the user is an administrator. They may have already removed it, but they said at KScope that they didn’t mean for the ability to be there, so it would be removed.
Hi Kyle,
Thanks for the update. I was able to create a shared member using AddChildAsMap and then updating the data storage property to shared using replace function.
Regards,
Vishakha
Thanks for sharing your example and giving back.
Dimension project = operation.application.getDimension(“Project”)
Member approvedProject = rtps.Project.Member
String draftProject = rtps.Project.toString()
/*to check for draft projects only*/
if (draftProject.substring(13,15) == “99”)
{
String newProjectName = rtps.Project.toString().substring(1,13) + “00” + rtps.Project.toString().substring(15,17)
println newProjectName
approvedProject = approvedProject.rename(newProjectName)
}
/*Add as a shared member in Approved Project Hierarchy*/
Member approvedParent = project.getMember(“Approved Projects”)
Map sharedProject = approvedParent.newChildAsMap(approvedProject)
sharedProject.replace(“Data Storage”,”shared”)
sharedProject.replace(“Data Storage (OEP_PFP)”,”shared”)
sharedProject.replace(“Data Storage (OEP_CPX)”,”shared”)
Member updatedsharedProject = project.saveMember(sharedProject)
Can this be used to change the parent of a member?
By updating it’s parent member property.
Thanks, Alex.
You can’t that I am aware. There are a few options in the dimension and member classes that can alter some things but the person running the calc has to be an admin. You can however use the rest api in a calc now and you can do what you are looking for that way.
Hi Kyle,
can we change the alias name in webform using the groovy script .
could you please let me know .
Regards,
Bala
There is no way to change the alias on load, if that is what you are asking. If you want to allow a user to update the alias from a form, then yes, that is possible. Groovy can update metadata, but a user can’t run the method. What you would need is a free form text account that allows them to enter a what would be alias. Then a process could run through jobs, or at night, that would update the aliases (run as admin).
Hi Kyle,
Is it possible to set an attribute of a member when adding a member to the outline using Groovy? In context, each of our entities have attributes assigned to them, in our cases a size attribute – e.g. large / small. I have a variable that captures the user input of what the attribute is, however, I cannot assign it to the entity at runtime.
I was thinking something like this could work… (It does work for mapping the data storage for example)
/* RTPS: {varSiteStatus} */…
Map newMembermap
String newSiteStatus = (rtps.varSiteStatus)
newMembermap[“SiteSize”] = newSiteStatus
Thanks.
Dermott.
The short answer is yes, you can do what you are asking. The example provides is way off what it needs to be. I would highly recommend taking the training available. It goes through examples in great detail on maps, updating maps, and you will leave with a complete understanding of how to do these types of activities. You still have to use the toMap method to get the existing attributes and then to update the attributes that are there.