Managing and Integrating Hyperion Smart Lists Using DRM – Part 1

Avatar

By
January 10th, 2017


Overview

Oracle Essbase is the industry leading Multidimensional Database platform. As a modeling and reporting technology, Essbase can only store numeric information, strings and other data types are not supported. However, both Essbase and Planning can get around this limitation using some functionality called Smart Lists, which are nothing more than lookup tables.

For example, consider this lookup table (Smart List) that might be used in a labor expense planning application.

ID String
1 Full-Time Employee
2 Part-Time Employee

 

Essbase only understands numbers so will store either a 1 or 2 for each employee in the database. However, when you retrieve this information via Planning Web Form, Smart View, or Financial Reporting; you will see the corresponding string value rather than its integer identifier. This is a very useful feature and can be used not only for reference data, but also to drive the business logic in your application. For example, knowing whether an employee is full-time or part-time might indicate whether that person is eligible for benefits.

In a multi-application Hyperion environment, you may very well have Smart Lists applicable to multiple applications, so managing those centrally in DRM is a great value proposition. In this article, I’ll share a design to manage both the Smart Lists themselves, as well as the association between member (in Essbase/Planning) and Smart List. In a future post, I’ll show you how to ingrate those Smart Lists (and associations) into Hyperion Planning from a database view using the Outline Load Utility.

Existing Properties

There are a couple existing custom properties in my application that are referenced in the code we’ll review (Custom.DRM.Prefix and Custom.DRM.MidName). For more detail about these properties, please see part 3 of my Getting Started with Oracle DRM series. We use prefixing to control Node Typing in DRM. So for example, if we want to push an account to Essbase called Sales, this would appear in DRM as acct!Sales; where acct is the prefix, and Sales is the MidName. Both properties are derived and cannot be overwritten.

In addition, we’ll be using 2 new prefixes (node types): list and entry.

In the next section, we’ll review the 7 derived properties I’ve designed to be used with the Planning Outline Load Utility (these will work for Essbase as well though it only requires 3 of the 7). The thing that I really like about this design is that I only have to set 4 System properties in order for the 7 in the next section to work. And, the node name, description, and parent are all set in a single action script command (ADD). One more pass is required to set the SortOrder which becomes to the entry ID (see lookup table).

  • Parent – Parent Node (used to build hierarchy)
  • Abbrev – Node Name
  • Descr – Node Description (Alias)
  • SortOrder – Used to explicitly set the order of siblings rather than the default (alphabetical)

Properties

The following table was taken from Oracle’s Hyperion Planning 11.1.2.4 documentation regarding Smart List Dimension Properties for the Outline Load Utility. Again, we’ll work on the integration into Planning in a subsequent article, but these are all the fields that can be loaded using the Utility. I have solved for all of these except Display Order (because I always want to use the default setting to order by entry ID), and Missing Label (because I am okay with having the #Missing label as blank). Only 4 of these properties are actually required and those are SmartList Name, Operation, Entry Name, and Entry Label.

Column Header in Load File Planning Property Value Default Required
SmartList Name Name of the Smart List Text, name of the Smart List/enumeration None Yes
Operation Operation Text, addsmartlist—creates a new Smart List; addEntry—adds an entry to the Smart List None Yes
Label Label field of Smart List Text, label field of Smart List Empty No
Display Order Display Order Integer or text value for display order of the Smart List: 0 or ID—order by entry id; 1 or Name—order by entry name; 2 or Label—order by entry label ID No
Missing Label #Missing Drop Down label Text, Missing Drop Down label LABEL_NONE No
Use Form Setting #Missing Form True—Form Setting; False—Drop Down Setting Form Setting No
Entry ID Smartlist entry ID ID for the Smart List entry Default entry ID No
Entry Name Entry Name N/A None Yes
Entry Label Entry Label N/A None Yes

 

These are the properties I’ve created, each is derived using JavaScript and cannot be overridden. Let’s look at the settings and code for each.

Custom.PLN.SmartList.Name

var prefixPropValue = node.PropValue("Custom.DRM.Prefix");
if ( node.HierAbbrev == "HSP_SmartLists" ) {
     if ( prefixPropValue == "list" ) { return node.PropValue("Custom.DRM.MidName"); }
     else if ( prefixPropValue == "entry" ) { return node.Parent.PropValue("Custom.DRM.Midname"); }
     else { return ""; }
}
else { return ""; }

Custom.PLN.SmartList.Label

if ( node.HierAbbrev == "HSP_SmartLists" ) {
     if ( node.PropValue("Custom.DRM.Prefix") == "list" ) {
          if ( node.Descr.length > 0 ) { return node.Descr; }
          else { return node.PropValue("Custom.DRM.MidName"); }
     }
     else { return ""; }
}
else { return ""; }

Custom.PLN.SmartList.Operation

var prefixPropValue = node.PropValue("Custom.DRM.Prefix");
var listCmd = "addsmartlist";
var entryCmd = "addEntry";
if ( node.HierAbbrev == "HSP_SmartLists" ) {
     if ( prefixPropValue == "list" ) { return listCmd; }
     else if ( prefixPropValue == "entry" ) { return entryCmd; }
     else { return ""; }
}
else { return ""; }

Custom.PLN.SmartList.UseFormSetting

if ( node.HierAbbrev == "HSP_SmartLists" ) {
     if ( node.PropValue("Custom.DRM.Prefix") == "list" ) { return false; }
     else { return ""; }
}
else { return ""; }

Custom.PLN.SmartList.EntryID

if ( node.HierAbbrev == "HSP_SmartLists" ) {
     if ( node.PropValue("Custom.DRM.Prefix") == "entry" ) { return node.PropValue("Core.SortOrder"); }
     else { return null; }
}
else { return null; }

Custom.PLN.SmartList.EntryName

var prefixPropValue = node.PropValue("Custom.DRM.Prefix");
if ( node.HierAbbrev == "HSP_SmartLists" ) {
     if ( prefixPropValue == "list" ) { return ""; }
     else if ( prefixPropValue == "entry" ) { return node.PropValue("Custom.DRM.MidName"); }
     else { return ""; }
}
else { return ""; }

Custom.PLN.SmartList.EntryLabel

var prefixPropValue = node.PropValue("Custom.DRM.Prefix");
if ( node.HierAbbrev == "HSP_SmartLists" ) {
     if ( prefixPropValue == "list" ) { return ""; }
     else if ( prefixPropValue == "entry" ) {
          if ( node.Descr.length > 0 ) { return node.Descr; }
          else { return node.PropValue("Custom.DRM.MidName"); }
     }
     else { return ""; } }
else { return ""; }

Member to Smart List Association

We’ve covered the properties required to build and export the Smart Lists themselves, but we haven’t yet covered how we are going to maintain the association between member and Smart List. Before we create the 2 properties we need, we need to create a Hierarchy Group for the Smart List hierarchy. Having the Smart List hierarchy in its own group will allow us to enforce a constraint so that a member (in Account dim for example) can only be associated with a node in the Smart List hierarchy.

To enforce this constraint, we need the Smart Lists hierarchy to be in its own Hierarchy Group.

  • In the Browse section of DRM select the Version you wish to modify
  • In the lower pane, mouse over Hierarchies -> New -> Hierarchy Group…
  • I’ve named mine HSP_SmartLists (Description is optional)
  • Don’t add any hierarchies to this since we haven’t created the Smart List hierarchy yet
  • Click OK to save
  • Since there are no hierarchies in this group, you will not see it until we create the Smart List hierarchy

Custom.PLN.SmartList.Associate

This property will manage the association between member and Smart List. As such, it will be set on the members that have Smart Lists which are most often Accounts, but can be in any dimension. The data type of this property is Global Node, which means the value of this property will be a Global Node object within the same version. If you don’t apply any constraint, then you could select any node from any hierarchy. We want to constrain the user to select a node from the Smart Lists hierarchy that we’ll create, so it should now be clear why we needed the Hierarchy Group. While this configuration is good, there is still one limitation; the DRM user could select an entry (within a Smart List) from the Smart List hierarchy instead of an actual Smart List. We’ll mitigate this risk in the export property by confirming that a Smart List was selected and if not, we’ll raise an error.

Custom.PLN.SmartList.Associate.Export

This property is required because every node in our application is using a prefix to indicate its Node Type. For example, if we have a Smart List called SL_EmployeeType, the node in DRM will be list!SL_EmployeeType. Before we can export the association for integration into Planning, we need to confirm that it is indeed a Smart List that was associated, and if so, strip off the prefix.

var smartList = node.PropValue("Custom.PLN.SmartList.Associate");
var smartListNode = node.NodeByAbbrev(smartList);
if ( smartList.length == 1 ) {
     if ( smartListNode.PropValue("Custom.DRM.Prefix") == "list" ) { return smartListNode.PropValue("Custom.DRM.MidName"); }
     else { return "ERROR: This member is not associated to a Smart List"; }
}
else { return ""; }

Build the Smart List Hierarchy

Using Action Script commands, we’ll create the Smart List hierarchy, set some hierarchy properties, and lastly create 3 smart lists. I’ll be using a pipe ( | ) delimiter for all commands, and a Version Variable called Current.

In this first set of commands we are adding the hierarchy, placing it in the HSP_SmartLists hierarchy group, setting a few other hierarchy level properties, and adding a Level 2 node that will be parent to each of the Smart Lists.

AddHier|Current|HSP_SmartLists|HSP Smart Lists|lists!SmartLists|||
UpdateHierPropValue|Current|HSP_SmartLists|Hierarchy Group|HSP_SmartLists|||
UpdateHierPropValue|Current|HSP_SmartLists|Enable Shared Nodes|TRUE|||
UpdateHierPropValue|Current|HSP_SmartLists|Default Display Properties|Core.Abbrev,Custom.PLN.SmartList.Name,Custom.PLN.SmartList.Label,Custom.PLN.SmartList.EntryID,Custom.PLN.SmartList.EntryName,Custom.PLN.SmartList.EntryLabel|||
UpdateHierPropValue|Current|HSP_SmartLists|Standard Hierarchy Sort|Core.SortOrder|||
UpdateHierPropValue|Current|HSP_SmartLists|Hierarchy Node Type|Custom.DRM.NodeType|||
Add|Current|HSP_SmartLists|lists!All_SmartLists|lists!SmartLists|FALSE||

Next, we add the 3 smart lists which will be parents (non-leaf) to each of their respective entries. The Smart Lists are SL_Boolean, SL_EmployeeType, and SL_Month.

Add|Current|HSP_SmartLists|list!SL_Boolean|lists!All_SmartLists|FALSE||
Add|Current|HSP_SmartLists|list!SL_EmployeeType|lists!All_SmartLists|FALSE||
Add|Current|HSP_SmartLists|list!SL_Month|lists!All_SmartLists|FALSE||

Next, we add the entries to each smart list. If we want to have a different label for the entry name, we use the Core.Descr property to set that; see the records below for SL_EmployeeType.

Add|Current|HSP_SmartLists|entry!No|list!SL_Boolean|TRUE||
Add|Current|HSP_SmartLists|entry!Yes|list!SL_Boolean|TRUE||
Add|Current|HSP_SmartLists|entry!FullTime|list!SL_EmployeeType|TRUE|Full-Time Employee|
Add|Current|HSP_SmartLists|entry!PartTime|list!SL_EmployeeType|TRUE|Part-Time Employee|
Add|Current|HSP_SmartLists|entry!Jan|list!SL_Month|TRUE||
Add|Current|HSP_SmartLists|entry!Feb|list!SL_Month|TRUE||
Add|Current|HSP_SmartLists|entry!Mar|list!SL_Month|TRUE||
Add|Current|HSP_SmartLists|entry!Apr|list!SL_Month|TRUE||
Add|Current|HSP_SmartLists|entry!May|list!SL_Month|TRUE||
Add|Current|HSP_SmartLists|entry!Jun|list!SL_Month|TRUE||
Add|Current|HSP_SmartLists|entry!Jul|list!SL_Month|TRUE||
Add|Current|HSP_SmartLists|entry!Aug|list!SL_Month|TRUE||
Add|Current|HSP_SmartLists|entry!Sep|list!SL_Month|TRUE||
Add|Current|HSP_SmartLists|entry!Oct|list!SL_Month|TRUE||
Add|Current|HSP_SmartLists|entry!Nov|list!SL_Month|TRUE||
Add|Current|HSP_SmartLists|entry!Dec|list!SL_Month|TRUE||

Finally, we set the Core.SortOrder property which will become the Entry ID.

ChangeProp|Current|HSP_SmartLists|entry!No|Core.SortOrder|0||
ChangeProp|Current|HSP_SmartLists|entry!Yes|Core.SortOrder|1||
ChangeProp|Current|HSP_SmartLists|entry!FullTime|Core.SortOrder|1||
ChangeProp|Current|HSP_SmartLists|entry!PartTime|Core.SortOrder|2||
ChangeProp|Current|HSP_SmartLists|entry!Jan|Core.SortOrder|1||
ChangeProp|Current|HSP_SmartLists|entry!Feb|Core.SortOrder|2||
ChangeProp|Current|HSP_SmartLists|entry!Mar|Core.SortOrder|3||
ChangeProp|Current|HSP_SmartLists|entry!Apr|Core.SortOrder|4||
ChangeProp|Current|HSP_SmartLists|entry!May|Core.SortOrder|5||
ChangeProp|Current|HSP_SmartLists|entry!Jun|Core.SortOrder|6||
ChangeProp|Current|HSP_SmartLists|entry!Jul|Core.SortOrder|7||
ChangeProp|Current|HSP_SmartLists|entry!Aug|Core.SortOrder|8||
ChangeProp|Current|HSP_SmartLists|entry!Sep|Core.SortOrder|9||
ChangeProp|Current|HSP_SmartLists|entry!Oct|Core.SortOrder|10||
ChangeProp|Current|HSP_SmartLists|entry!Nov|Core.SortOrder|11||
ChangeProp|Current|HSP_SmartLists|entry!Dec|Core.SortOrder|12||

This is the resulting Smart List hierarchy.

This is an example of an action script command that will associate an account called EmpStartMonth to the Smart List we created SL_Month.

ChangeProp|Current|Account|acct!EmpStartMonth|Custom.PLN.SmartList.Associate|list!SL_Month||

I hope this was helpful, stay tuned for the next installment in which we’ll integrate these Smart Lists into Hyperion Planning from a database view using the Outline Load Utility.


Avatar

About TopDown Team

The TopDown Team includes members of TopDown Consulting who want to let the community know about webcasts, conferences, and other events. The team also conducts interviews on various EPM industry topics.

Leave a Reply

Your email address will not be published. Required fields are marked *