Clear only unique members from data file – FDM

Never thought that I’ll write something on FDM (I consider myself as a beginner on FDM and still need to learn a lot). However here I’m writing about customizing FDM load script.

After lot of struggle with the tool (I’m getting old, it not easy learning from here on) I was able to get the data load automated.

Let’s say I got help from couple of sources (Tony Scalese’s blog and from my colleague Matt Tyburowski)

I started the load and it was running forever!!!!!!

I was first blaming my lack of FDM knowledge, so decided to look at Essbase log and check what is happening.

I saw that each clear (fired by FDM) was taking 4.25 secs. Now you’ll think why is he bothered about 4.25 secs.

Let’s do some math.
I've 123238 lines in the load file and FDM load script runs the clear for each single line which makes
4.25*123238=523761.5 secs, 8729 mins close to 145 hours (I don’t think I can wait that long Winking smile)

The issue was with how the Calc script is generated by FDM.


It says @Idescendants (strEntity), which is a level 0 entity and the statement is generating an empty memberset and the script runs for the entire Entity dimension. Read about the issue with EMPTYMEMBERSET here.

I decided to update it with the set commands


I kept wondering why this is not there in the default script.

Thrilled by my discovery I started loading data again. The changes did reduce the time. It is now taking 0.016 secs, which is good. But is it?

Math shows that 0.015*123238=1971.808 secs, 33 mins (at-least it is not hours). Can we reduce it further?

As you know (and I learned after a long time) that FDM runs the clear starment for each single line. If you have an entity which is repeated 10 times, it will run the clear 10 times Sad smile

Which is kind of dumb. Is there a way to get unique member list from FDM data file and run clear on those members? Yes there is Smile

I created a dictionary to read each entity from the FDM generated data file.

The good thing about dictionary is I can check whether the entity is present in it, if not present add it to the dictionary, else proceed with next entity. This way I can get the unique member list (Entity) and run the script only for those members.

Here is the updated script.

You can see that I was writing that to a file, you can remove it if you don't want it. I've 1309 entities in the file and my clear script runs 21 secs Winking smile

Hope it saves some time of yours Smile

Path too long - Hyperion Reporting and Analysis error

I know you must have seen the error while using LCM for Reporting and Analysis objects.

If the pass is too long it'll throw an error message complaining about path more than 256 characters.

pathlong error

I was working on a client where I got the same issue. (The users had a long way to go to view the reports ;) )

If you are not aware of Reporting structure here is what it does. (or I think what it does ;))
When you create a report, it adds an entry (more entries, we'll stick with one for now) in V8_CONTAINER table and then stores the report in a physical location. (this is the data folder location you select while configuring BI+)

Data folder
This is the entry for a report called "Sample"

id path 11.1.2.2

Relational Table
This is the how it gets stored in Relational

11.1.2.2 sample rel

When you change fileSystem.friendlyNames to "false", it forces LCM to extract the reports based on their IDs (not names).

With that in mid I did follows the steps mentioned under Editing Migration Properties

exp for edit

prop edit

Import the properties file, restarted Shared Services and LCM’ed the report again.

To my surprise it didn’t work. (I got got the names and path too long error). A little bit of research took me to the Readme of 11.1.2.2 where it is listed as Know Issues under

LCM
13862629 - Artifact export, by default, uses friendly artifact names instead of artifact IDs even if the friendly name setting is set to false.
Financial Reporting
13367741 - Some new artifacts are not identified using their friendly names.

I raised an SR to check the status and got the reply as follows
friendlynames false is not supported from 11.1.2.1 onwards. 7-zip has been the recommended approach to overcome the windows path limitation issues.

So by default BI+ artifacts are saved with name and id which is the behavior as when freindlynames is set to true. The reason for disabling the false property was, during our further testing we realized in BI+ each artifact has more than one file with the same name and hence the files cannot be stored by names uniquely on the file
The response made me look at an older version (11.1.1.3) and see what were those changes and to my surprise it didn’t work there too. I do remember it was working on an earlier version. (Can’t remember which one though Sad smile)

I don't see a change in how RnA stores files in the filesystem, so not sure why the property is not working.

It is great that 7zip can handle this. You can use 7zip to compress the contents and move them across servers.

I think it is high time for Oracle to clean up the documentation. (LCM guide still says friendlynames can be used)




XREF on a text member in Planning

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:\temp\comments.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.



Customize Planning Webforms

I’m not a big fan of performing customization at a client side. However you’ll find that I’ve written a handful of posts about customizing Workspace and Planning.

I’ll explain why I still write posts about customization in a while.

With the new ADF release most of the Planning customizations (ValidateData.js, Grid Spread) are going away (they might come back later, who knows).

How about the form given below, isn't is nice to have the headers stand out? (I would love to get a different formatting according to the levels in hierarchy)

Form with bold header

I did a little bit of research and figured out that it is possible. (Ok it is not possible to do selective formatting, but something is better than nothing Winking smile) Starting from 11.1.2.2.300 you can customize the style of header cells!!!!!!

Sounds fun isn't it? (Now you know why I write posts about customization, it is fun Winking smile)

Before you start performing the steps, remember to take a backup of HyperionPlanning.ear file

Changing header for all forms

If you like to change the style of header for all forms, then you can follow the steps below.
Open HyperionPlanning.ear file under <EPM_ORACLE_HOME>\products\Planning\AppServer\InstallableApps\Common using 7Zip.

7zip ear

Open HyperionPlanning.war file in HyperionPlanning.ear file and navigate to custom folder

7zip war

1.    Create a class in HspCustom.css as given below.
.customheaderStyle{


     font-family:Garamond;
     font-size:14;
     font-weight:bold;

}
2.    Create a file called trinidad-skins.xml as given below.
<skins xmlns="http://myfaces.apache.org/trinidad/skin">
<skin>
<id>HspCustom.desktop</id>
<family>HspCustom</family>
<extends>blafplus-rich.desktop</extends>
<render-kit-id>org.apache.myfaces.trinidad.desktop</render-kit-id>
<style-sheet-name>custom/HspCustom.css</style-sheet-name>
</skin>
</skins>
3.    Add trinidad-skins.xml to HyperionPlanning.war\WEB-INF
4.    Update the file with the changes.
5.    Redeploy the application server.
6.    Restart the application server and clear the browser cache.
7.    Add a new application property called SKIN_FAMILY ( Administration -> Application -> Properties)
      Set the property value to reference the custom skin "HspCustom"

Manage Properties

9.    Log out, and then log in.


Changing header for one form

In order to change the header of one single form, we’ve to first get the ID of the form. You can query Planning application relational database and get the ID from there.
Select object_id AS FORM_ID,object_name AS FORM_NAME from hsp_object where
object_name like 'Plan Revenue - COS';

Class in HspCustom.css will now look similar to the one given below.
.customheaderStyle_51120{


     font-family:Garamond;
     font-size:14;
     font-weight:bold;

}
Follow the steps from 2 on-wards.

Once you log back, you can see the changes.


only one form with bold header

Form with bold header color

For the record, I did try playing around with ADF and Trinidad skinning, however none of them worked. It's good to get back column level (colheader_0, colheader_1 etc etc) customization back.

Let's hope someone is listening.

Deleting a rule in Calculation Manager - Surprises in Calc Manager Part I

I’m sure people who love HBR are not going to like Calculation Manager a lot (but you don't have a choice, do you?Winking smile).

I was not a big fan of it, however when I started using it I fell in love with it. I admit that there are some issues.

I wrote couple of posts (limitations) about Calc Manager, and the latest release solves one issue which I faced. There are some nice features in the latest release, launching MaxL scripts from a Calc Manger rule – to name one. I did promise that I’ll write about the new features, which will come soon (or later Smile)

This post is about (as the title suggests) a surprise in Calc Manager.

I’m sure by this time you are familiar with the Calc Manager terminology like “Deploy” and “Validate and Deploy”. If you are not have a look at Deploying Business Rules and Business Rulesets from the Deployment View.

When you deploy a Calc Manager Planning rule, it copies the rule from a table in Calc Manager’s repository called CALCMGRRULES to a table in Planning repository called HSP_CALC_MGR_RULES. (Well let’s say on a higher level this is what it does Winking smile).

Let’s imagine that you are getting a call from a user saying that he is seeing wrong values. You started looking at it and found that the numbers that appear there are derived using an old logic. (Which you are sure that you deleted them a long time ago).

You go back to Planning and what you see there surprises you.

All the old rules that you deleted are still there!!!!
Let’s prove it that you are not insane and you did delete them Smile

Proof
Below sheet is my input sheet and you can see that AggAll calculated the price for me as Revenue/Units.

excel values

I took a copy of AggAll rule and named it as “CAggAll” and made a slight change. (That was a mistake on purpose)

CAggall

Deployed the rule, so far so good

Entry from HSP_CALC_MGR_RULES

HSP_CALC_MGR_RULES

After some testing I realized that I made a mistake and deleted the wrong rule. The surprise starts from here. Now what happens when you delete a Planning rule in Calc Manager. Are you going to answer that it’ll delete the rule from Planning too? “Wrong answer”. You don't believe me huh

Let’s make a copy of a Calc Manager rule and delete the original one. (Note: If you are planning to perform this, peform this only in a DEV environment and make a note of the access)

The rule will still appear in Planning(still there in HSP_CALC_MGR_RULES), what happens if the user who has access to it runs it?
I was expecting that it’ll throw some error. (John Goodwin was suggesting that it'll run even when Calc Manager is down)

run deleted rule

It will execute the old rule (which was deleted) and will populate the wrong/old logic.

excel values new

Surprise, surprise….wouldn't it be nice if it was automatically deleted?

How you can solve it?

Well unfortunately there is no easier way.
Don’t create new rules for new logic, just update the older ones.(I know you don’t do it just like the way I did/explained)
If you are ever deleting a rule from Calc Manager, make sure that you deploy all the rules in the application and it’ll ensure that the deleted rule is removed from Planning.

deploy full

Let’s hope that this will get fixed in a later release.

Views discussed in this blog are my personal views.
They do not represent the view of organizations, businesses or institutions that I'm part of