Text members in Hyperion Planning

In this blog, I would be talking about text members in Hyperion Planning and exploring how they get stored and mapped internally by Hyperion Planning. Now the motivation for this. I was actually working on converting an excel sheet into a Hyperion Planning application and one of the columns in the application was a comment field. The excel sheet had some 1000 rows and I was not going to update it manually. Hence, necessity became the mother of innovation.

The below snapshot shows me creating a text member in my sample application.

I initially named the member to be Text Measures but finally renamed it to Comments.

Once the member is created, I created a sample data form for this member which is shown in the below snapshot. 

Entered some data in the grid and saved the data form. The data entered is as shown in the above snapshot.

Now, let us see the data that gets stored in Essbase. For this, I created a report script as shown in the below snapshot.

The output on running the above report script is as shown below.

Observe that for the Comments, Essbase is storing number values. Thus there should be a table in the Planning schema that has the mapping for the numbers and the respective comments.

The table that stores this mapping is called as HSP_TEXT_CELL_VALUE. The contents of this table is as shown in the below snapshot.

(By design, Hyperion Planning can be thought of as a Hybrid OLAP system since it combines the best features of MOLAP (Essbase) and ROLAP (Relational OLAP). By design, Essbase acts as a data store for numeric values. Any text or smart list values that you have is stored in the relational part of the application as a key-value pair, with the text value acting as key and value being a number. This mapping of key value is handled by the ROLAP part of Planning. So any representation in Planning will have the same abstraction logic applied and so the forms will show text comments and smart list text as is, rather than numbers which is fetched from the Essbase data store.)
Now, let us try to update the HSP_TEXT_CELL_VALUE to be something a bit more dramatic. This is done using the UPDATE query as shown in the below snapshot.   

The contents of the table after UPDATE are as shown in the below snapshot.

Now I check the Planning form as shown in the below snapshot. Observe that the comments are not updated at all to reflect the new ones. (You could argue that this is the way cache is supposed to work... Planning assumes unidirectional data flow from cache to database...Since entry is found in cache data is not updated in the data form)

Now, in order to get the value reflected, you will need to stop and start the services. The Planning form reflects the text value changes as shown in the below snapshot after the services were restarted.

From a design perspective, it looks like Hyperion Planning stores the text values in some sort of application cache and this is the reason why the change did not get reflected. Still researching this…