Copy Text data (Planning) as Copy Data (DATACOPY) in Essbase...Yes..It works!!!

Yes. You have read it right and I am talking right.

There is a saying that if you understand a process end-to-end you open lot of possibilities for improvement / betterment and it suits perfect in this case

As the title goes, we all have started our career in Essbase (atleast in my case) and moved to to different other tools. We might have received multiple requests / written different types of calculation scripts where we have to copy a subset of data from one combination to other combination. The real problem comes when a user who is well-versed with Essbase and even knows the technical stuff better comes with a requirement which blows your mind and you had to think / try every single possibility to convince him that this is not possible. The same thing happened with me

Background
We use planning application for Planning sessions, Yearly Budget and monthly forecast and initially the monthly forecasting in Excel (where they have numbers as well as text) and after moving to Oracle Hyperion Planning, they wanted to do the same way (store data and text). So, we have created a new version with data type as "Text" to accommodate the user requirement. So far so good. Everything is clear and fine. Business do forecasting to a max of 5 versions (VR01, VR02...VR05) before making it a final version. 
We usually get requests to copy over the Forecast data from one version to another. This can be done pretty straightforward by using a DATACOPY command. But, one particular day we got a request from this user where he asked to copy text data from one version to another. We never done this before (OR) have seen anyone doing it and the first thing that came out of our mind is "We can copy data, we cannot copy text data". But, the user is very adamant and he doesn't want every user to input text data for every version and this will impact their deadlines

So we have decided to dig deep inside to understand the process and we we able to find a solution to it and below is how we did it

Understanding Text Data in Planning
I have created few members and a form to demonstrate how we did it
Below is a screenshot of simple form with one comment

We have 3 accounts and a version memeber "VR_Commentary_V01" and we have defined the version dimension member as data type "text".

Note: Do not forget to set the evaluation order as "Version" and then followed by any other dimension. In this case we have data type text defined for version dimension and if you have any other dimension which has text data type / smartlists then you can define the evaluation order accordingly

When we submit any text data in planning, The text information gets updated to HSP_TEXT table in planning repository. The HSP_TEXT has two fields "ID" and "Description" and the ID is sequence and it gets incremented with 1 for every text data that is submitted and the ID gets submitted to essbase as a number.

 Below is how the table will look like

Let's have a look at the below form where we have submitted the text for T_Account_2 -> Nov and below is how it will look like

Below is the Essbase retrieve of the same

Let's copy the data that we have submitted in Nov to Dec and see what will happen when we retrieve the form. Below is how the form will look like

The text at T_Account_2 -> Dec is exactly same as for Nov and if you look at the table above, there is only one entry in the table. So, how does it work.

Planning table does not store the duplicate entries and when you submit any text data in the form, it will do the below steps

1. Once the text data is submitted for a blank cell, it will check if the same text data exist in the HSP_TEXT table
1. If the text does not exist, A new entry is added to the table with a new ID which is the max(ID) + 1 and submit the ID to Essbase (As Essbase store only numbers)
2. If the text already exists, it doesn't create a new entry in the HSP_TEXT table. It will pick the ID from the table and submit it to Essbase
2. If you modify an existing text data, it will not update the corresponding ID text but rather check if the modified text data is available in HSP_TEXT
1. If the modified text is not available, a new entry is created in the table with the ID and that ID is submitted to Essbase
2. If the modified text already exist, it doesn't create a new entry in HSP_TEXT table but it will pick up the ID for that text and submit it to Essbase

There is an exception to the above process. Let's look at that

If you look at the above screenshot, the text data got aggregated to Qtr4 and if you look at the text in Qtr4 it doesn't make sense at all. It is always best to never (^) aggregate the text data members 

I hope this gives you an overall picture of how the text data works. Always have backup in case if something goes wrong

I hope this gives you an overall picture of how the text data works. 

Happy Learning!!!