Monday, June 24, 2024

EPM Cloud Planning Smart Lists

The Backstory:

I never had much use for Smart Lists in Essbase. I remember people asking for the functionality since I started working with Essbase in 1998. Here's a post from the Essbase Network 54 Board in 2000 essentially asking for this capability.


They finally got around to adding "Typed Measures" to Essbase somewhere around 2007. Here's a link to Tim Tow blogging about them in 2008.


A Basic Primer on Smart Lists:

Essbase stores numerical data, specifically 8-byte floating point numbers. Essbase is what Planning/PBCS/EPM Cloud Cubes use to store data. As a work-around to store some text, you can create a list of text values, assign them some numerical value and store that in the database. The application layer then interprets those values as text using the Smart List.


Some Use Cases:

Displaying some status for an Account/Entity/Product/etc.

Displaying the hire month for an employee.

You want an indicator for a member but don't want to create an Attribute dimension.

You want to show text in the numeric area of a report.


How to Create a Smart List in EPM Cloud Planning:

Step 1: Create a Smart List

Find the Create and Manage menu and select Smart Lists.


Press the + sign to add a new Smart List.

Now name your Smart List and decide on options. In this case the Label will be Status. Any missing values will show as Active by default.

Click on the Entries tab and edit your list of values. The Name needs to be alphanumeric and contain no spaces but the Label can contain spaces. Once this is complete, save the Smart List.


Step 2: Update The Outline

Navigate to the Create and Manage menu and select Dimensions.
Add a new member in a dimension and change its Data Type to SmartList. Then set the Smart List value. In this case I added a member in the Account dimension called Entity Status and assigned it the Status Smart List.



Before leaving the Dimensions editor, click on the Evaluation Order tab. This setting tells the application how to resolve conflicts if you have Smart Lists in multiple dimensions. You will need to select the dimension in which your Smart List resides and Save this setting. 

Once the dimension change is saved, be sure to refresh your database.

Step 3: Test Your Change in Smart View

Now you can go into Smart View and retrieve some data to see the Smart List working.

When you click on a cell, you'll get a drop-down box allowing you to change the cell's value. You can write this back to the database.

Good luck using Smart Lists! Let me know the creative ways you're using this feature.

No comments: