XREF on a text member in Planning 2

This entry was posted in Hacks Planning SQL on by (updated 2480 days ago)
Life is full of uncertainties. Now don’t think that I became philosophical.
It’s been a long time since I blogged, looked at OTN, Network54 and used PS3, I was busy house hunting and learning FDM (not FDMEE).

I cannot talk about FDM part, however house hunting was successful and we moved into our new home last week. Now that I don’t have much to do I thought of writing this blog.
I do remember answering what is a text measure in Planning (I think that was an interview 6 years back). I’m sure that you all know what a text measure is, if not

Data Type and Exchange Rate Type
Data type and exchange rate type determine how values are stored in account members, and the exchange rates used to calculate values. Available data type for account members’ values:
  • Currency – Stores and displays in the default currency.
  • Non-currency – Stores and displays as a numeric value.
  • Percentage – Stores a numeric value and displays as a percent.
  • Date – Displays as a date.
  • Text – Displays as text.
Here is how it looks on a webform.

image

I once remember answering a thread where OP was trying to copy text values to a different member, today there was a similar thread where OP is trying to perform an XREF on a text member.

When you look at a text member (member whose data type is tagged as “Text”) it is similar to a Smart List, only difference is in this case the list is maintained internally by Planning.

If you look at the Webform shown above there is a corresponding “Smart List” in Planning and it is stored in “HSP_TEXT_CELL_VALUE” table.

image

If you look at the values in Essbase, you’ll see that the TEXT_IDs are stored as numbers.

image

I think what Planning does is it runs an internal mechanism which matches this ID and shows us the VALUE. So what will happen if I use @XREF on a text member.

I created two identical applications and used XREF on one member.

image

I looked at the webform and nothing is populated there.

image

I tried retrieving the values from Essbase and I do see values there!!!!

image

The only missing part is the values under HSP_TEXT_CELL_VALUE table. So I wrote an insert statement to insert App1 Text Values to App2

INSERT INTO hsp_text_cell_value
SELECT * FROM smpplnadmin.hsp_text_cell_value;

Restart Planning service…..Ta da Smile

image

How good is this in a real environment
Not so good Sad smile, what if there are different text measures and they all are not XREFed. We’ll get issues when we try to copy the values from HSP_TEXT_CELL_VALUE.

So what alternative do we have, well I can create a DATAEXPORT script and pull all the values for the text measure.

//ESS_LOCALE English_UnitedStates.Latin1@Binary
SET DATAEXPORTOPTIONS
  {
   DataExportColFormat ON;
   DataExportColHeader Account;
   DataExportOverwriteFile ON;
  };

FIX(FY12,
            “HSP_InputValue”,
            Working,
            Forecast,
            @RELATIVE(“TotalGeography”,0),
            @RELATIVE(“YearTotal”,0),
            Local,
            Comments)
           
            DATAEXPORT “File” “,” “C:tempcomments.txt” “0”;
           
ENDFIX          

In the above example I’ll get a file similar to this.

“Comments” 
“HSP_InputValue”,”FY12″,”E01_101_1110″,”BAS”,”Local”,”Forecast”,”Working”,”Jan”,1
“HSP_InputValue”,”FY12″,”E01_101_1110″,”BAS”,”Local”,”Forecast”,”Working”,”Feb”,2

Load that to an external table and create a load file by joining the data to TEXT_ID which can be used by OutlineLoad and load that in the target application.

Value,Driver Member,Point-of-View,Data Load Cube Name
Pittsburgh,Comments,”HSP_InputValue,FY12,E01_101_1110,BAS,Local,Forecast,Working,Jan”,Consol
Chicago,Comments,”HSP_InputValue,FY12,E01_101_1110,BAS,Local,Forecast,Working,Feb”,Consol

Update 03/12/2014

After I wrote this I thought of testing the export data feature of 11.1.2.3 environment and see whether it can generate the load file.

It generates the file in the required format.

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on “XREF on a text member in Planning”

  • Reply ↓
    Kapil Bankeraika

    Hi Celvin,

    If it is an EPMA application, then I might also explore the data syncronizer option with scheduled load activity. I presume that might of some help here.

    I presume there might a workaround available through ODI for this as well, but need to check the same.

  • Reply ↓
    MG

    Hi Celvin,

    Thanks for your blog. It is very helpful. But The line where you have mentioned that “Load that to an external table and create a load file by joining the data to TEXT_ID which can be used by OutlineLoad and load that in the target application.” that is not clear. Can you please explain it in more detail ?

    Regards,
    MG