Kscope24 Preview

In just over two weeks I'll be flying to Nashville for the best tech conference ever! I'm looking forward to catching up with friends and colleagues from around the world. The Denim & Diamonds event on Wednesday night looks like it will be a blast.

The conference will kick off early on Sunday morning with the Essbase Symposium. I'm hopeful that there will be some cool innovations as the Essbase space has been stagnant for a very long time now.

Following the Essbase session will be the Dodeca Symposium. Tim Tow will be sharing our roadmap and his vision for the future of the product. We've been heavily investing in our EPM Cloud Connector and will be showing that off. If you use EPM Cloud, you'll want to stop by and see what we've been up to.

I've been reviewing the session agenda and picked out three sessions that I'm most looking forward to attending. All three are fantastic speakers and respected experts in their field.

Monday July 15, 4:00 pm - 5:00 pm
Joe Aultman -- Converting an Excel Model into Planning -- A New Approach

Tuesday July 16, 4:00 pm - 5:00 pm
Peter Nitschke -- Form Fundamentals: From Zero to Planning Hero

Wednesday July 17, 1:30 pm - 2:30 pm
Jake Turrell -- From Manual to Magical: Transforming Oracle EPM with PowerShell Automation

Be sure to stop by the Applied OLAP booth to say hello. I look forward to meeting many new people this year. Safe travels...

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.

EPM Cloud ASO Plan Type Procedural Calcs

When learning EPM Cloud Planning, I went through a class that taught me you cannot perform ASO procedural calculations. I didn't like to hear I had to give up something so vital to Essbase ASO. While it's kind of true, I'll show you a work-around.

For an in-depth explanation of the problem, see this really old blog post HERE. In short, summing formula members across other dimensions is really, really difficult. The work-around is to run a procedural calc at level-0 thus storing the data. Then you let ASO do it's aggregation magic.

It's surprisingly easy in EPM Cloud Planning ASO Plan Types.

Step 1: Put the formula into a member.

If you're creating a bunch of calcs, I'd recommend putting these members in a hidden section of the hierarchy. For this example, I created a member in my Account dimension with two members under it.

On the ProcTestSource I put a very simple member formula of the number 9999. I'll get into more complex formulas in future posts.

Step 2: Create a business rule to run an allocation on the ASO cube.

For the formula, I put the Target on the left side and the Source on the right.

Now I run the business rule and retrieve my data in Smart View (or Dodeca). The column on the left is a formula that will evaluate to 9999 at every intersection. The column on the right has the stored value at level 0 and lets ASO handle the aggregations.

If you've got a better way of doing this, please reach out to me. I'm curious to see if there are other ways to accomplish this (other than doing the calculation in a BSO cube and moving the data over).

Change-Up

It's been over 3 years since I last posted on this blog. It's not because I haven't wanted to post, it's because there really haven't been many (any?) significant changes in the Essbase world worth blogging about. Is it because Essbase is dead? I check the Google search trends for essbase and see this:

It is a little troubling to see this as one who has been developing Essbase applications since 1998 -- a full six years before this chart even begins. But it isn't that Essbase is dead, far from it. Essbase is just behind the scenes now. It's the engine behind some of the most powerful and successful cloud applications on the planet. It's time for me to embrace that fact and learn all there is to know about Oracle EPM Cloud.

My goal is to now post regularly about this "New Essbase". I work for Applied OLAP so expect Dodeca and Drillbridge content to be sprinkled in as well. See you next time.