Oracle - Hyperion Labs......

Exploring 11.1.2.3 Planning features - Modify Metadata from Smart View

noreply@blogger.com (Celvin Kattookaran) — Mon, 20 May 2013 16:20:00 +0000

This is the 2nd blog on 11.1.2.3 new features.

Some of the features in 11.1.2.3 are quite exciting and I started exploring some of them (if not all).

First one was @CREATEBLOCK and the next one was about SET RUNTIMESUBVARS.

https://blogs.oracle.com/proactivesupportEPM/entry/epm_11_1_2_31 says that there is a glitch in documentation, however I found out that documentation is correct. (It works as per documentation)

This one works

execute calculation Sample.Basic.test with runtimesubvars 'myMarket="New York";salesNum=130;pointD="Actual"->"COGS";';

According to the blog the code below should work, however it is not. Maybe someone changed the way it works to match the documentation ;)

execute calculation "test" on Sample.Basic with runtimesubvars 'myMarket="New York";salesNum=130;pointD="Actual"->"COGS";';

I'm in the process of creating a jar/exe where you can get user inputs and then run the calc script. (Currently this new function is included in the API to prompt a user for a variable but so far this has not been implemented in any product (MaxL, SmartView, CalcManager, EAS - none of them yet).

This post is about metadata addition in Planning from Smart View and "on the fly member addition".

I'll post about adding members using Outline Load web. (You can now export data and metadata,yes you heard right, new outline load web interface can now export data and metadata from Planning)

Add Metadata from Smart View

You can install a Smart View add-in for Planning Administration which will provide you an interface to edit Dimensions in Smart View.

You can now modify members without logging into Planning Web.

Read about how to use it here.

It is a fairly simple process to add members.

You can login to the Planning Application and once you install Add-in for Planning Administration, you'll see a new member under the application tree "Dimensions". Double click on a dimension, so that it creates an ad-hoc sheet.

You can enter new member/members in Column A. You can refresh the sheet, It will ask you to refresh if you try submitting without refresh, you can change the setting by adding a property value, check Selecting the Add Mode within Planning Application Properties.

Don't get confused about Planning Web UI Application Properties, it is Application properties :)

The new member/members will be added (all new members will be suffixed with (*), you can change it by adding SMART_VIEW_MD_NEW_MEMBER_SUFFIX) to the Dimension.

I couldn't make it work after logging off from browsers and Smart View. I even recycled the services and nothing was working. Then I thought of expanding MD (like METADATA) and nothing was working.

Last option, Google (of-course) took me to John Goodwin's blog. So the properties are SMART_VIEW_DIMENSION_EDITOR_NEW_MEMBER_SUFFIX and SMART_VIEW_DIMENSION_EDITOR_PARITY_MODE.

I logged into Planning after a submit and that member is under Entity. (pretty neat)

If you want to move the member to a different parent,

First submit member so that it comes under Root member - dimension.
Change to different parent and perform a second submit will move it to different member.

You can now create/refresh database from Smart View.

On the fly members

Now lets check on the fly member addition. so I tried adding a parent to allow Dynamic children in Workforce.

Now refresh Essbase so that it creates the place holders. I can see the place holders in Essbase not in Planning.

Now change a calc manager rule to allow dynamic members.

An RTP variable is created with dynamic parent member.

There are lot of enhancements in Calc Manager and will cover them later, You can now define Dimension Name in Variable Designer

It throws an error which says the member does not exist. So is there no "on the fly member". I'll update you all if I find something on it.

HTH

Customizing EPM Workspace 11.1.2.x Part III

noreply@blogger.com (Celvin Kattookaran) — Thu, 09 May 2013 01:50:00 +0000

You can find Customizing EPM Workspace Part I , Part II

When I posted Part II, I got a question from +POLA TVU asking - "How to increase the number of items showing up in Quick Links of Workspace Home Page?"

I forgot about that and recently the same question was posted by a user in OTN.

Perform the actions described in this post with caution.

A Word of Caution:

The method described here involves changing workspace default pages. Check your licensing agreements to see whether this can be customized.

Step 1

Select HomePage and export it.

Navigate to Application Groups -> Reporting and Analysis -> Reporting and Analysis

Expand Repository Objects and Shared Workspace Pages, select HomePage.

Step 2

Open HomePage_wsp (HomePage).HomePage_wsp in a text editor.

\\RnA-Reporting and Analysis\resource\Repository Objects\Shared Workspace Pages\HomePage_wsp (HomePage).HomePage_wsp

You'll find something similar to the code below.

xsi:type="PortletSpec" name="QuickLinks"  
  src="../browse/dyn?page=/jsp/com/hyperion/tools/workspacepages/quicklink.jsp&cssUri=%2E%2E%2F%theme_dir%homepage%2Ecss&showTitle=true&showItems=4&numThreads=5&theme_dir=%theme_dir%"

Update to a desired number. (I tested this on 11.1.2.2 for 6 items)

Step 3

Restart services

HTH

Update 5/13/2013 1:15 PM

Once you update the showitems it affects both Favorites and Applications.

@CREATEBLOCK - Surprises in Essbase Part III

noreply@blogger.com (Celvin Kattookaran) — Thu, 02 May 2013 16:40:00 +0000

I was really excited about these new functions in Essbase.

We all have dealt with Block creation issues (and we've solved it using DATACOPY, @ALLOCATE, SET CREATEBLOCKONEQ, SET CREATENONMISSINGBLK, even doing a lock and send)

After 20+ years a dedicated function for creating blocks, yes we should be excited about it.

However it turns out not so exciting (Maybe I'm doing it wrong, maybe techref didn't capture the usage correctly, maybe,......)

So I started out testing my favorite command.

This script shows us the typical block creation issue.

FIX(Jan,Sales,"100-10",@RELATIVE(Market,0))

"Actual"(
IF(@ISCHILD(East))
100;
ENDIF
)

ENDFIX

After running the script I don't expect to see any values. (I'm not proven wrong, no surprises here)

Techref says use @CREATEBLOCK under a FIX (If you use it without opening a block you get this error "Error: 1012000 Invalid Syntax - Not a CALC command [@CREATEBLOCK]")

So I changed the script to

FIX(Jan,Sales,"100-10",@RELATIVE(Market,0))

"Actual"(
IF(@ISCHILD(East))
@CREATEBLOCK (@CHILDREN(East));
100;
ENDIF
)

ENDFIX

What I liked about it is you can use it in an IF statement (at least syntax is correct)

I ran it expecting a magic, miracle,......(nada). No EXCEL returned all #Missing (no blocks created) :(

In order to prove that the function is doing something I turned on Messaging in calcs. Still nothing that says blocks are created (or copied)

Going back to basics

I started suspecting my scripting skills and then decided to lock and send a value to a single intersection.

Before lock and send

After lock and send

Now the puzzle starts ;)

Run the script again. You won't believe what you see.

It created the blocks (East has 5 children, 5 blocks). It shows up in the log also that it did create blocks.

Hold the excitement right there. Let's see what is the result in SmartView.

BUMP, it just calculated one single block....so where are those 4 blocks..... I think we'll have to wait more time so see how it works (if it works)

Update May 2nd 2013 1:33 PM

All this started from because of this thread in OTN (https://forums.oracle.com/forums/thread.jspa?threadID=2531198&tstart=0)

Cameron Lackpour pointed out something (But creating blocks at those other sparse members isn't the same thing as valuing them)

That made me change the calc to what is given below.

FIX(Jan,Sales,"100-10",@RELATIVE(Market,0))

"Actual"(
IF(@ISCHILD(East))
@CREATEBLOCK (@CHILDREN(East));
ENDIF
)

"Actual"(
IF(@ISCHILD(East))
100;
ENDIF
)
ENDFIX

So first block for creating block and then second for calculating..... Ta da

It works, it works (however it requires 1 block for creating the rest)

Update 3rd May 2013 1:10 AM

Gabby Rubin commented on the same OTN thread where he explained how it behaves.

"Using a dense member in the formula will use a bottom-up approach which will create new blocks based on the existing ones. If you want to create the blocks in the top-down approach (similar to CREATEONMISSINGBLK), you should use a sparse member (but not the same one as the one in the @CREATEBLOCK formula)"

I was prejudiced against that approach (because it is a sparse assignment). However Cameron added some sense to the prejudiced mind and we have a script which works on a blank database.

So this was the modification.

FIX(Jan,Sales)
"100-10"
(
@CREATEBLOCK (@CHILDREN(East));
)
ENDFIX

It created 5 blocks (for 5 children of East). I can sleep now.

EPM 11.1.2.3 installation

noreply@blogger.com (Celvin Kattookaran) — Tue, 30 Apr 2013 04:58:00 +0000

Quick sneak peek on the new features of 11.1.2.3 installer.

First noticeable difference is about configuring databases of all products in a single shot (I really like this change, earlier version you've to do this 1 by 1)

You can now configure EPMA dimension server.

You can now configure Planning RMI server.

After configuring I was looking at the services panel and figured out this one. (all start with "Oracle" now)

Configuration Manger still holds the record for "World's longest service name" :)

Update May 1st 2013

I installed 11.1.2.3 on a fresh image and see that Display name of Configuration Manager is changed to "Oracle Hyperion Configuration Manager" however service name is still the same (old long name, so I'm correct in saying "World's longest service name")

For some reason Essbase was not showing up in HSS (however the configuration didn't show any error messages. I went and looked into Essbase logs and found this error.

[Wed May 01 14:19:05 2013]Local/ESSBASE0///1600/Error(1051549)
Can not convert Essbase to Shared Services mode when Essbase is not configured with Shared Services or the initialization process has failed

[Wed May 01 14:19:05 2013]Local/ESSBASE0///1600/Warning(1056822)
Failed to migrate Essbase to HSS security mode during startup. Check SharedServices_Security_client.log. May need to remove essbase.sec and any of the newly migrated Essbase applications from HSS, then rerun configuration tool.

What I did was to delete the SEC file and start Essbase. This will automatically migrate the users to HSS.

[Wed May 01 15:24:39 2013]Local/ESSBASE0///4956/Info(1051458)
User/Group hierarchy Check Starts ...

[Wed May 01 15:24:39 2013]Local/ESSBASE0///4956/Info(1051459)
User/Group hierarchy Check Completed Successfully and application Migration to Shared Services Starts ...

[Wed May 01 15:24:39 2013]Local/ESSBASE0///4956/Info(1051654)
Application Migration to Shared Services Completed Successfully and Retrieval of Provider Names Starts ...

[Wed May 01 15:24:39 2013]Local/ESSBASE0///4956/Info(1051460)
Retrieval of Provider Names Completed Successfully and Group Migration Starts ...

[Wed May 01 15:24:39 2013]Local/ESSBASE0///4956/Info(1051461)
Group Migration to Shared Services Completed Successfully and User Migration to Shared Services Starts ...

[Wed May 01 15:24:39 2013]Local/ESSBASE0///4956/Info(1051407)
User/Group [admin@Native Directory] Has been successfully migrated to Shared Services Server

[Wed May 01 15:24:39 2013]Local/ESSBASE0///4956/Info(1051462)
User Migration to Shared Services Completed Successfully and Refresh from Shared Services Starts ...

[Wed May 01 15:24:39 2013]Local/ESSBASE0///4956/Info(1051463)
Refresh from Shared Services Completed Successfully

[Wed May 01 15:24:39 2013]Local/ESSBASE0///4956/Info(1056798)
Security file write started by SYSTEM

EPM 11.1.2.3 available for download

noreply@blogger.com (Celvin Kattookaran) — Tue, 30 Apr 2013 02:04:00 +0000

If you have not noticed, Oracle released EPM 11.1.2.3 over the weekend. (I noticed it only today :( )

You can download it from www.edelivery.oracle.com

I'm downloading it now, will keep you all posted on the new release and the installation.

HTH

Extract Webform Layout in a text file

noreply@blogger.com (Celvin Kattookaran) — Thu, 18 Apr 2013 05:49:00 +0000

I've worked on projects where I've created documents on the layout of Hyperion Financial reports (what is in Row, Column, Page,....), however was it was interesting to see the same question get applied on Planning.

OP (there was a question on network54) was looking at extracting Grid properties and I got it wrong :).

However it was a nice exercise to pull the Webform information and see everything without going into each Webform. (There was a question posted in OTN which dealt with the "How to extract Page/POV/Row/Column information of Planning Webform)

Here it is

Run this query against your Planning application repository

select d.object_name "Form Name", b.object_name "Member Name",c.subst_var "Variable",

(CASE a.objdef_type

WHEN 0 THEN 'POV'

WHEN 1 THEN 'Page'

WHEN 2 THEN 'Row'

WHEN 3 THEN 'Column'

ELSE 'Unknown'

END) "Layout",

(CASE c.query_type

WHEN 8 THEN 'Descendants'

WHEN 9 THEN 'IDescendants'

WHEN 3 THEN 'Ancestors'

WHEN 4 THEN 'IAncestors'

WHEN 12 THEN 'Siblings'

WHEN 13 THEN 'ISiblings'

WHEN 21 THEN 'Parents'

WHEN 22 THEN 'IParents'

WHEN 5 THEN 'Children'

WHEN 6 THEN 'IChildren'

WHEN -9 THEN 'ILvl0Descendants'

WHEN 0 THEN 'Member'

ELSE 'Unknown'

END) "Selection Method"

from

hsp_formobj_def a,

hsp_object b,

hsp_formobj_def_mbr c,

hsp_object d

where a.objdef_id=c.objdef_id

and c.mbr_id=b.object_id

and a.form_id=d.object_id order by 1 asc;

How it looks

HTH

Update

Seems like this is an in built capability (sigh, you discover that there is an easy way to achieve it rather than writing all the queries) you do not need to do this the SQL way.
Go to Tools -> Reports -> Forms (Earlier releases Administration -> Reporting)

Run Batch file in Workspace

noreply@blogger.com (Celvin Kattookaran) — Thu, 04 Apr 2013 08:32:00 +0000

One sleepless night (it's 4 am!!!!) and I decided to write a sequel to "Run MaxL in Workspace".

Year is 2023 and run maxl has new super hero skills :) (reminds me of Neo's superman flying skills....)

This is similar to running MaxLs in Workspace, trick is to create another generic job application.

Here is how it is done

Follow the steps given below, and you can run bat files in Workspace.

Step 1: Create a Job Application

Create a generic job application as shown below. (Navigate -> Administer -> Reporting and Analysis -> Generic Job Applications)

Provide a Product Name (this is the name that shows up as Job Factory Application) when importing a job.
Select Product Host. (This is the Workspace server)
Type $PROGRAM $PARAMS, you can also click the buttons to insert Command Template. (this is where it is different from the MaxL job application)
Provide the Executable as %WINDIR%\System32\cmd.exe

Step 2: Import a bat file

Import a bat file into Workspace as a job. (Import file as job)

Check "Import as Generic Job" check box.

Select the newly created Job Application and click Finish.

Step 3: Run the job

You can now execute the bat file by double clicking on it or right click -> Run as Job

Provide a path for output and click "Run"

I've MaxL files kept on Workspace server, which was executed by the bat job.

HTH

Generate Security report for Planning

noreply@blogger.com (Celvin Kattookaran) — Tue, 02 Apr 2013 02:07:00 +0000

I've seen this question being asked by many people. "How can I generate a security report for Planning?". This report should provide all access information about Webforms, Dimension.

Of course you can use ExportSecurity.cmd and export the information from Planning. Yes you are right there.

However let me ask you this, how about the inherited access (i.e. the access a user gets from a group he/she is part of). You've to now search for group and then derive the access of the user.

What if we could do this easily? (Easy works for me :))

You can log into Shared Services and generate a PDF report on Planning security.

Here is how it is done

You can select Users/Groups from this screen.

You can select the objects. (Yes you can even extract the just WebForm security as opposed to ExportSecurity.cmd)

There are lot of filters available with this mode of export

You can generate the report based on matching access (Read, Write or None access)
You can generate the report based on matching relation (Children, Descendants,.....)
You can get the inherited access (access inherited from a group)
You can generate the effective access (This will expand the relation @Children(Market) to East, West, Central)

11.1.2.x Shared Services you can even manage Forms, Dimensions and Task Lists, no need to login to Planning after provisioning a user, I like that one :)

HTH

Extract Supporting detail information from Planning

noreply@blogger.com (Celvin Kattookaran) — Tue, 19 Mar 2013 03:50:00 +0000

You might have wondered, tried and some might have succeeded in extracting this information, however I (consider myself lazy and forgot about picking this from my drafts) couldn't answer this question when someone (Ramesh Paul) asked me this question.

I planned to write about it and then forgot (I created a draft that time) about it. All on a sudden I had to face that question again and I thought of answering it right away.

You can read more about supporting detail here.

Supporting detail is stored in Planning relational tables (not in Essbase). HSP_COLUMN_DETAIL and HSP_COLUMN_DETAIL_ITEM holds the required information.

Supporting Detail in Planning

SQL for extracting Supporting Detail

select b.label, b.generation,

(CASE b.operator

WHEN 0 THEN 'Ignore'

when 1 THEN 'Add'

when 2 THEN 'Subtract'

when 3 THEN 'Multiply'

when 4 THEN 'Divide'

END) Operator, b.value, c.object_name, d.object_name, e.object_name, f.object_name, g.object_name, h.object_name, i.object_name, j.object_name, k.object_name, l.object_name, m.object_name, n.object_name, o.object_name, p.object_name

from

hsp_column_detail a,

hsp_column_detail_item b,

hsp_object c,

hsp_object d,

hsp_object e,

hsp_object f,

hsp_object g,

hsp_object h,

hsp_object i,

hsp_object j,

hsp_object k,

hsp_object l,

hsp_object m,

hsp_object n,

hsp_object o,

hsp_object p

where

a.detail_id=b.detail_id and

a.dim1=c.object_id (+) and

a.dim2=d.object_id (+)and

a.dim3=e.object_id (+)and

a.dim4=f.object_id (+)and

a.dim5=g.object_id (+)and

a.dim6=h.object_id (+)and

a.dim7=i.object_id (+)and

a.dim8=j.object_id (+)and

a.dim9=k.object_id (+)and

a.dim10=l.object_id (+)and

a.dim11=m.object_id (+)and

a.dim12=n.object_id (+)and

a.dim13=o.object_id (+)and

a.dim14=p.object_id (+);

How it looks

Generation will tell you about Supporting detail hierarchy.

Note: You can add more joins if you've more than 14 dimensions.

HTH

Run MaxL in Workspace

noreply@blogger.com (Celvin Kattookaran) — Mon, 11 Mar 2013 02:42:00 +0000

This post started from a question of my colleague Sudhir, "How to run a VB script in Workspace?"

I know the answer for "How to run MaxL in Workspace" because I was following the thread in network54 (you can follow the discussion here)

But this was different and I'm not that familiar with jsp pages ;) (Robb created a jsp page to run MaxLs in Workspace)

Since this is the season of reboots (Total Rekall, Amazing Spiderman, GI Joe....) I thought of rebooting Robb's MaxLRunner (I'm just going with the trend ;), not sure how many (more?) times we'll have to watch Peter Parker getting bitten by that spider.)

Before beginning the action I would like to thank couple of people (it's more than couple) who inspired me to come up with this.

Sudhir, for asking How can I run a VB Script in Workspace.

Jomish, for inspiring to get it done.

Cameron Lackpour, for providing the hint.

Robb, for his nice jsp page (I really liked it, it allows you to view the contents and can use Smart cuts)

I'm someone who likes Essbase a lot and I told myself "First figure out how to run a MaxL script in Workspace, running VB script will be solved automatically".

I figured out that MaxL can be imported as jobs from Cameron's post, and later found that all jobs require a job application.

Jomish helped me understand the Job service and here we are.

Follow the steps given below, and you can run MaxL scripts in Workspace.

Step 1: Create a Job Application

Create a generic job application as shown below. (Navigate -> Administer -> Reporting and Analysis -> Generic Job Applications)

Provide a Product Name (this is the name that shows up (Job Factory Application) when importing a job.
Select Product Host. (This is the Workspace server)
Type $CMD $PROGRAM, you can also click the buttons to insert Command Template.
Provide the full path of essmsh.exe as the Executable. Essbase client should be installed on Workspace Server.

Step 2: Import a MaxL script

Import a MaxL script into Workspace as a job. (Import file as job)

Check "Import as Generic Job" check box.

Select the newly created Job Application and click Finish.

Step 3: Run the job

You can now execute the MaxL script by double clicking on it or right click -> Run as Job

Provide a path for output and click "Run"

The MaxL script will be executed on Workspace server. If you are spooling (spool command) in MaxL, then make sure that Workspace server has that folder.

I think this went way too detailed and I never talked about running VB scripts, so that calls for a sequel :)

(Coming soon "How to run a bat/cmd file in Workspace?", "How to run VB script in Workspace?")

Essbase Error 1020034 - Binary spreadsheet table token error - Solutions

noreply@blogger.com (Celvin Kattookaran) — Thu, 21 Feb 2013 23:56:00 +0000

Someday, Somewhere (no I'm not singing "Someday Somewhere" by MLTR, I'm already together with her ;) ) you might have seen or you might end up getting this error while retrieving data using Smart View.

When I ended up getting this error, the first thing I checked was whether there are any hidden instances of EXCEL and there were none!!!

I was thinking that probably "Reduce Excel File size" option was causing the problem (not sure why I thought that way, maybe because it was not reducing the file size), so I disabled it and went ahead with a refresh.

I was under the impression that I'll get the same error but to my surprise the refresh went fine!!!

Now I wanted to prove that it is because of "Reduce Excel file size". I enabled the option, performed a refresh believing that I'll get the error and I could file a bug (or even curse people).

Nope again no errors!!!! This was a forgotten story and then yesterday a colleague of mine called up and asked "Have you seen this error?" (You should have seen the smile on my face).

He was going to re-create the sheet and I advised him not to, "Just perform a refresh". (with a heavy tone) (He might have thought this guy is crazy, didn't say anything and went with the refresh).

He didn't get the error and his refresh returned data from Essbase.

Back of my mind I wanted him to get the error and prove that it was because of "Reduce Excel file size". But it seems like that is not the reason.

If you are getting this error, wait for some time (or even disconnect your connections), and perform a refresh.

I'm listing out possible solutions for the error here.

Check whether you've a correct version of Smart View/ EXCEL Add-in
Check for hidden EXCEL instances, close them and perform a refresh.
Perform a refresh and see whether it solves the issue.
Check whether you've any EXCEL formulas that is returning strings.
If there is another solution please post it here. :)

HTH

Import Text Lists in Essbase

noreply@blogger.com (Celvin Kattookaran) — Mon, 18 Feb 2013 17:52:00 +0000

I don't think I need to provide an introduction to Text Lists in Essbase. (however if you are not aware of it, please go through http://docs.oracle.com/cd/E12825_01/epm.111/esb_dbag/frameset.htm?dtypmeas.htm)

This blog is about "How to Import a text list?"

I was once trying to import a text list into EAS and I never ever (not even in my wildest dreams) had an idea that 'Importing a Text List" will make a blog entry.

As it turns out there is no import button (or it is not highlighted) "Where is my import button?" :(

Since I couldn't find out a way to import a text list I thought creating it from scratch. (Then I made the accidental discovery, I think most of them are accidental :) )

How to import a text list

Create a new text list

Do not click on "Apply" button, instead press "Enter"

There you go, when you hit "Enter", the "Import" button will be highlighted.

Too many steps and tricks to perform an import operation :)

HTH

Essbase Studio Custom SQL error "No mapping found for class"

noreply@blogger.com (Celvin Kattookaran) — Mon, 28 Jan 2013 15:29:00 +0000

Maybe you've seen this error while updating custom SQLs of an Essbase Studio Model.

Error in the external procedure 'getConnectionByModelSettings'. Line = 81.

No mapping found for class : \'system'\'sysMODStore'\'MFF_GEN2_DUMMY'.'caption'.

We (a colleague of mine, Ashok Shikhare and me)were getting this error when we tried to update the custom SQL of an existing Essbase model in Studio.

We tried to search for MFF_GEN2 to see if that is a reserved word and hit nothing!!!!! after lot of search and re-search we finally came up with this.

"The model is corrupted or out-of-sync".

First step was to synchronize the data sources (We didn't want to believe that the models were corrupt) and again we hit the same error!!!!

Since we had 12 models and we didn't like the idea of re-creating all of them. I think if it was one then we would have re-created it and solved the issue :)

So we took our "jadoo ki chadi" (for the rest, it is "the magical wand") and did a simple magic!!!!! :)

Everything in the Essbase properties looked fine, except for the outline order.

Measures (Accounts) dimension was coming first and then it was followed by a sparse member, then followed by dense member!!!!

We then arranged it to follow the hour glass model (oh yes that name pops-up anytime and all the time) and voila!!!!!

The issue was resolved. However that change made the model go out-of-sync

So we did a synchronization of Cube Schema and to our surprise the outline order was changed again!!!!

I couldn't find a reason why it is changing the order, maybe because our model had a sparse dimension called "Account" and it was always pushing that dimension just after "Measures" dimension. Not sure whether this is a common behavior in 11.1.2.2.

So if you came across this error, check for the outline order.

HTH

Essbase Origins

noreply@blogger.com (Celvin Kattookaran) — Mon, 14 Jan 2013 00:49:00 +0000

For all those who are following this blog, I really apologize for not updating this blog.

I was on a vacation and missed the update about "World is not ending." Glad that we all survived the Mayan apocalypse :)

Hope you all had a rocking New Year. My daughter had her "puppy" New Year cake and she learned the word "Happy". (I figured out after some days that she is calling a "cake" as "happy").

You might be thinking why this is titled as "Essbase Origins", I'm a big fan of Hugh Jackman and I like X-men Origins :). (I couldn't find a better title for this one.)

This New Year I received a surprise gift and this blog is about that gift. I believe many of us (in our early Essbase days) might have googled for "essbase" and read about it in Wiki.

However till now I was not aware about "Who created Essbase?", "What went behind naming it "Essbase?".

I know it stands for Extended Spreadsheet Database, but didn't know there were names like "S-base", "Number Buddy" and "eSSbase" in consideration.

Here is the answer to all those questions - (I would like you all to read till the end of this thread)

http://www.network54.com/Forum/58296/thread/1258337658/Trivia+Question

I wish I was the person who started that thread!!!!!

"Who created Essbase?" and the person who created it, "Bob Earle" replying "It's great to see the product still in use and liked after so many years". (That would be my dream come true moment :) ).

Bob, that is really an understatement and this product is here to stay.

So anyone interested in knowing about Hyperion can visit this link -

http://www.answers.com/topic/hyperion-software-corporation

I was searching for "Hyperion Spiderman" and now I know the answer :)

First blog in 2013 and I wish I could provide more insights, more solutions, more tricks......

@SHARE - Surprises in Essbase Part II and Part III

noreply@blogger.com (Celvin Kattookaran) — Tue, 20 Nov 2012 23:34:00 +0000

I was planning to write about this first. (I wanted to beat Cameron, but he beat me ;)).

You can read about the surprises with @SHARE here. (I think Cameron has done a marvelous job of defining what works and what doesn't work).

I was working on something (which I'll publish later) and forgot about spreading the word. (better late that never I guess).

I'm getting older, I think I'm getting smarter too (maybe my wife has a different opinion :)), so I combined Surprises Part II and Part III together.

Surprises in Essbase Part II - is about @SHARE not working as documented. You can read about it from Cameron's blog.

Suprises in Essbase Part III

This was an accidental discovery while I was working on the @SHARE issue. When I realized that @SHARE is not working, I thought of going back to old tech ref and figure out whether I'm not getting it. (maybe because I'm getting older)

So the example given in Tech Ref says

@REMOVE(@DESCENDANT(Product),@SHARE(@DESCENDENT((Product)))

The example has multiple errors (misspellings, parenthesis issue) and I was thinking "Why can't they give a correct working example!!!!"

But then I was so lazy in correcting those and I tried (I thought I could raise a documentation bug) the same command and to my surprise it validated.

Then I thought if this one works, why not this @IDESCENDNATS. To my surprise (that was a big one) that worked too.

This was also a part of the above (@share) discussion and John Booth pointed out that Essbase only matches the first four characters of a function.

I experimented more on it and then figured out that what John said is partially true!!!!!

Essbase recognizes all functions with first 4 letters if the argument is a mbrname

So @CHILDNER works. Try it if you don't believe me.

I don't know how many of you got a hoax mail which claims that "Brain can read jumbled words if the first and last letters are not jumbled."

Now I can spread one which says "Essbase can read functions if first 4 letters are correct"

SET EMPTYMEMBERSETS - Surprises in Essbase Part I

noreply@blogger.com (Celvin Kattookaran) — Thu, 01 Nov 2012 22:10:00 +0000

I couldn't think about anything other than "Suprises in Essbase".

I was working on a Calculation Script and all on a sudden it started behaving weird. (I know that "weird", "strange" all are strong words)

The feeling was like someone telling you that Baa Baa Black Sheep is having only a bag full of wool!!!!

Coming back to our surprise, I was working on a script were I was using @ATTRIBUTE function and it has EMPTYMEMBERSET command to stop the calculation where it hits a "EMPTYMEMBERSET" (isn't that kind of obvious).

The script was running for long time than expected and I started investigating.

I know that, I'm not getting some of the attributes and I was not expecting Essbase to run the calc for those FIXes.

I ended up googling and landed here. I thought of not to re-iterate what Cameron has written there, however what followed made me wrote this.

I fixed my script, added NESTED IFs and now the problem is solved.

Now you would be imagining why I wrote this much where I could have said. "Hey guys look at what Cameron has posted about EMPTYMEMBERSETS" and here is the reason.

I talked to someone about the problem and he/she replied saying "No buddy I've used it all the time and I didn't face such an issue."

Then I realized......

Some of us don't know the effect of hitting an empty member set (How it'll *****-up the data?) when it not ON.

Some of us don't know that for EMPTYMEMBERSET we shouldn't be combining members in FIX. (I fall under this)

This is a bigger problem (or highly overlooked SET command) if you are a Planning developer.

I've seen many people using @IDESCENDNATS({RuntimePrompt}) in FIXes. It's not a misspell that command works, more on that in next post.

What will happen if the user select a level 0 member as the prompt then it'll be similar to the calc given below.

"100-10" was selected by the user from the prompt. I've not included EMPTYMEMBERSET here and see the result of the calc!!!!

Yes it populated entire Product dimension with a 1!!!!!

Ohhhhh so that's the effect of not using EMPTYMEMBERSET!!!!! (I think the issue drove me crazy I'm mocking reactions)

If you think that there could be a chance of empty set use the SET command. (Use it wisely)

Also remember to use Nested IFs while using SET EMPTYMEMBERSETS ON; command.

HTH

Update

After publishing I realized that this is my 100th post!!!!!

Variable limitations in Calc Manager 3

noreply@blogger.com (Celvin Kattookaran) — Mon, 29 Oct 2012 16:53:00 +0000

This post is about a limitation of limit :)

Why it took so long to write this one

I don't why I didn't write this one earlier after posting about Variable limitations in Calc Manager 1 and 2 (you can find them here and here. This was there in my drafts for almost an year)

There is a reason (rather two) why this one came back to my mind.

Reason 1

One sleepless night I was running an analysis (on my blog) and found that Mr.Cameron Lackpour is referring one of my post in his blog about Calc Manager (Moment of pride, joy,......)

I was glad that I could help someone whom I admire.

Reason 2

I got a comment from Mr.Brian Francis saying telling me that the post about Variable limitations in Calc manager 2 probably saved hundreds of other people's many hours of frustration.

I think I did lot of self praising today and should start with this post. ;)

This one came as an accidental discovery. I was doing a project with Mr.Joe Cajic and I wanted to prove him wrong :)

Joe if you are reading this then I would like to tell you that, now I don't owe you any dinner ;)

Many of us are familiar with "Limits" in HBR variables and the similar concept is followed in Calc Manager.

Let's imagine a scenario where you are setting limits for a variable - Variable should show only those members which starting with a 2.

Easier way to do this run a search in the member selector panel for 2*

It'll return all the members* (the window shows that I selected 100 items) which starts with a 2. Fortunately (for all of us) I didn't do it this way, I went to pull the members using SmartView. (Why I took that approach, I still don't know)

Let's look at what SmartView Member selection returns, it returned a whopping count of 12468 members!!!!!!

That's the limitation of limit (Member search is returning only 100 items)

I checked this in 11.1.2.2 and unfortunately the search function is returning only 100 items.

If you've applied limits using the search feature in Calc Manager (Member Selection), go back and make sure that the limits are correct.

An easier way to get this done (if your search returns more than 100 members), create a dummy webform in Planning and perform the same search. (Yes it works fine everywhere except Calc Manager)

Copy the member list from webform and use that in Calc Manager limits

HTH

Encrypting Dynamic MaxL scripts (which uses environment variables)

noreply@blogger.com (Celvin Kattookaran) — Mon, 15 Oct 2012 17:00:00 +0000

I've written posts on encrypting MaxLs and how to use Environment Variables in MaxLs here and here.

Today we'll discuss on how to encrypt a MaxL script which uses an Environment Variable.

I encrypted the MaxL using the command given below.

However the resulting mxls file was still showing the same contents.

Spool file and on-screen data was revealing the user name and password, which is not the desired output.

So how to achieve this (encrypt).

I decided to encrypt the username and password separately as shown below and pass them to MaxL.

Later I updated the mxls file and added the keys as given below.

The bat file was updated to pass the keys (not the actual username and password)

Old Bat File

New Bat File with Keys

Now the spool file is not revealing the user name and password.

HTH

1000th IT title of Packt Publishers

noreply@blogger.com (Celvin Kattookaran) — Fri, 28 Sep 2012 13:50:00 +0000

I recently receive a mail from Packt Publishing about their 1000th IT title and about their celebration.

As a part of the celebration they are inviting anyone to choose and download any one of its eBooks for free over its celebration weekend of 28-30^th Sep 2012. Packt is also opening its online library for a week for free to give customers an easy to way to research their choice of free eBook. All you have to do is login to Packt's website http://www.packtpub.com/login and register

Below given is the press release about the same.

Birmingham-based IT publisher Packt Publishing is about to publish its 1000^th title. Packt books are renowned among developers for being uniquely practical and focused, but you’d be forgiven for not yet being in the know – Packt books cover highly specific tools and technologies which you might not expect to see a high quality book on.

Packt is certain that in its 1000 titles there is at least one book that everyone in IT will find useful right away, and are inviting anyone to choose and download any one of its eBooks for free over its celebration weekend of 28-30^th Sep 2012. Packt is also opening its online library for a week for free to give customers an easy to way to research their choice of free eBook.

Packt supports many of the Open Source projects covered by its books through a project royalty donation, which has contributed over $4,00,000 to Open Source projects up to now. As part of the celebration Packt is allocating $30,000 to share between projects and authors as part of the weekend giveaway, allocated based on the number of copies of each title downloaded.

Dave Maclean, founder of Packt Publishing:

“At Packt we set out 8 years ago to bring practical, up to date and easy to use technical books to the specialist tools and technologies that had been largely overlooked by IT publishers. Today, I am really proud that with our authors and partners we have been able to make useful books available on over 1000 topics and make our contribution to the development community.”

Essbase Excel Add-in and SmartView free form retrieval

noreply@blogger.com (Celvin Kattookaran) — Mon, 10 Sep 2012 16:05:00 +0000

I was once working on automating some Essbase retrievals and one of my sheet was pulling values based on UDA.

I knew that I could use Pattern search (by making use of Subset option), however I don't want any User Interaction and had to look for a different option.

Many of you might have used Free Form retrieval mode of EXCEL Add-in.

For those who have not used it yet.

Free-Form mode provides additional retrieval capabilities. First, it enables you to enter member names into a random location in the worksheet. In this mode, Essbase scans the names in a worksheet and picks a default view for you. In addition, Free-Form mode enables you to use Essbase report script commands to retrieve data into a worksheet. The report script commands are most useful for defining member range references that can dynamically bring back the most current member information.

In Free-Form mode, Essbase retrieves data with these characteristics:

Essbase interprets the member names and creates a default view based on the location of the labels.
Essbase removes blank rows and columns on a retrieval action.

So I created a VBA program which will change the mode to Free form mode and retrieve the report script.

' Change retrieval mode to free form

'15 Enable template retrieve mode Boolean

'16 Enable free form/Version-2.x mode Boolean

sts = EssVSetSheetOption(Null, 15, True)

sts = EssVSetSheetOption(Null, 16, True)

range("A1").Value = "

application.StatusBar = "Retrieving Small Markets..."

sts = EssMenuVRetrieve()

' Change to advanced interpretation mode

'

sts = EssVSetSheetOption(Null, 15, False)

sts = EssVSetSheetOption(Null, 16, False)

For those who are looking for a similar option in SmartView can use the below VBA code for the same.

SmartView you've to change report scripts to MDX queries.

Sub Sample_HypExecuteQuery()

Dim vtQuery As Variant

vtQuery = "SELECT {Year} ON COLUMNS, {UDA([Market],""Small Market"")} ON ROWS FROM Sample.Basic"

sts = HypConnect(Empty, "system", "password", "private connection")

sts = HypExecuteQuery(Empty, vtQuery)

sts = HypDisconnect(Empty, True)

End Sub

HTH

SQL queries for finding folder location of Planning Webforms and more...

noreply@blogger.com (Celvin Kattookaran) — Mon, 27 Aug 2012 13:25:00 +0000

You might have spend many hours (during documentation phase of your project) to get the information about Webforms like Webform folder location, Business rules attached to the form, Menus associated with the forms,......

So here is an easier way to do it. (Thanks to my friend Jomish for helping me with the SQL queries)

To find out the folder location of Webforms

Run the below given query to extract the folder information of Webforms in an application

select

o4.object_name||'\'||o3.object_name||'\'||o2.object_name Path, o.object_name form_name, ofrm.object_name cube_name
from hsp_form f
left outer join hsp_object o on (f.form_id=o.object_id)
left outer join hsp_object o2 on (o.parent_id=o2.object_id)
left outer join hsp_object o3 on (o2.parent_id=o3.object_id)
left outer join hsp_object o4 on (o3.parent_id=o4.object_id)
left outer join hsp_object ofrm on (f.cube_id=ofrm.object_id)

order by o4.object_name||'\'||o3.object_name||'\'||o2.object_name

The example works for up-to 2 sub folders under root, you can add more outer joins if you have more sub folders.

To find out the folder location of Task Lists

Run the below given query to extract the folder information of Task lists in an application

select

o5.object_name||'\'||o4.object_name||'\'||o3.object_name||'\'||o2.object_name
Path, o.object_name "Task List"
from hsp_task t
left outer join hsp_object o on (t.task_id=o.object_id)
left outer join hsp_object o2 on (o.parent_id=o2.object_id)
left outer join hsp_object o3 on (o2.parent_id=o3.object_id)
left outer join hsp_object o4 on (o3.parent_id=o4.object_id)
left outer join hsp_object o5 on (o4.parent_id=o5.object_id)

order by o5.object_name||'\'||o4.object_name||'\'||o3.object_name||'\'||o2.object_name;

The example works for up-to 3 sub folders under root, you can add more outer joins if you have more sub folders.

To find out the calculations associated with Webforms and their properties

Run the below given query to extract the Calculation script association with Webforms.

select

o.object_name form_name, frmcalc.calc_name, frmcalc.run_on_load, frmcalc.run_on_save,
frmcalc.calc_type, frmcalc.use_mru "Use Members on Form", frmcalc.hide_prompt
from hsp_form f
left outer join hsp_object o on (f.form_id=o.object_id)
left outer join hsp_form_calcs frmcalc on
(f.form_id=frmcalc.form_id)

where frmcalc.calc_name is not null
order by o.object_name;

Different values for Calc_Type are

0=graphical HBR;
1=Normal HBR;
2=Sequence HBR;
3=Native Essbase

To find out the menus associated with Webforms

Run the below given query to extract the Menu association with Webforms.

select

o.object_name form_name, menu.object_name
from hsp_form f
left outer join hsp_object o on (f.form_id=o.object_id)
left outer join hsp_form_menus frmmenu on (f.form_id=frmmenu.form_id)
left outer join hsp_object menu on (frmmenu.menu_id =menu.object_id)

where frmmenu.menu_id is not null
order by o.object_name;

HTH

Rotate Planning logs in 11.1.2.1

noreply@blogger.com (Celvin Kattookaran) — Wed, 15 Aug 2012 02:38:00 +0000

I had to enable once enable DEBUG option in Planning (For monitoring the login time of users : http://www.orahyplabs.com/2012/07/monitor-last-login-time-in.html).

This made the logs grew in size and every re-start of Planning was deleting the logs. I was looking for an option to set a maxlogfilesize and then rotate the logs.

I did google :) and found out more information on log rotation using ODL. (Oracle Diagnostic Logging)

http://docs.oracle.com/cd/E15523_01/core.1111/e10105/logs.htm

Most EPM System products have adopted Oracle Diagnostic Logging (ODL) as the logging mechanism. The ODL framework provides uniform support for managing log files, including log file rotation, maximum log file size, and the maximum log directory size. For more information, see the “Using EPM System Logs” chapter of the Oracle Hyperion Enterprise Performance Management System Installation and Configuration Troubleshooting Guide.

Unfortunately (for me at least) Planning 11.1.2.1 does not use ODL.

From 11.1.2.2 ODL is the standard and you can use ODL for log rotation.

For pre 11.1.2.2 environments you'll have to look for other options and I created a bat file to handle the rotation. This bat file will move Planning logs to an archive folder if HyS9Planning-sysout.log is larger than 500 KB.

I use this bat file after stopping Planning service to archive the logs and then start Planning services.

You can download the file from here.

HTH

Monitor last login time in Planning/Essbase

noreply@blogger.com (Celvin Kattookaran) — Mon, 23 Jul 2012 18:39:00 +0000

Once I had to face this question "How can I monitor the users who are not using the system?"

I modified that to "How can I monitor the last logged in time of a user?"

There are multiple ways to track user statistics in Essbase.

You can create Log charts from EAS.
You can use jrightlog to analyze Essbase log files http://code.google.com/p/jrightlog/

EAS method

You can generate Log Charts for Server (or Application) and select "Logged Users" as filter and refresh the chart.

This will upload the log entries into a table called SERVERLOGDETAIL in EAS schema (relational database).

You can then run a SELECT statement as given below against that table and it’ll give you the last login time of users.

SELECT username as "User Name", MAX(entrydate) AS "Last Login Date"

FROM serverlogdetail where username is not null

GROUP BY username;

If you are looking for an Application specific record then you can use the below given SELECT statement.

SELECT username as "User Name", MAX(entrydate) AS "Last Login Date"

FROM serverlogdetail where msgtext like 'Setting application ASOsamp %'

GROUP BY username;

Jrightlog method

Download jrightlog from here. Run JRightLog.jar file and select Essbase.log and specify a target file.

You can add a filter "Logging in user" which will pull all the login records.

Sample log file

Planning Login details

There is no straight forward check if you want to check the last login time of a user in Planning.

You can make use of auditing options in Planning. You can enable Auditing for Data and Business Rules.

I’ve written something on this here http://www.orahyplabs.com/2012/01/how-to-check-whether-user-ran-rule.html

This was done with a different purpose, but if you enable Auditing for Data and then you can run a query as given below. (This should give the last data entry made by the user)

SELECT USER_NAME AS "User", time_posted AS "Last Login Time" FROM HSP_AUDIT_RECORDS WHERE TYPE = 'Data';

Similarly you can expand the SQL to check whether a user ran a Business Rule or not and then get his Last Login details.

Just for kicks

When you perform a last login check on Planning, there can be users who logs in and just do a data check and log-off.

Unfortunately there is no means to track those users with above given methods. (they are not performing a data entry and Planning Auditing won't be able to record those.)

So I was thinking how will I track these users.

You can enable debugging in Planning by (Won't recommend doing this, however if you are in need) adding an entry in System Properties (DEBUG_ENABLED to true).

This will save user login information in the log file. You can use this bat file to extract the information.

Modify BASE_DIR, EPM_LOG_DIR, LOGFILE according to your environment.

I'll post a blog on how to rotate the log files soon, so that you can archive the logs and then use that to extract the information.

HTH

Create Hyperion Financial Reports from EXCEL

noreply@blogger.com (Celvin Kattookaran) — Thu, 05 Jul 2012 20:09:00 +0000

I was going through a question posted in LinkedIn Oracle Hyperion Financial Reports API?

The person who raised the question was looking for a way to bypass the not so user friendly Financial Reporting Studio.

I always wish that FR Studio had

Undo
Copy (works many times, but buggy)
Cut and Insert

So I thought of using the very friendly EXCEL to create FR reports (part of this research was to answer the query of creating a report with more than 400 rows).

It is a tedious process to create report in FR if you have lot of rows. So the tool helps you use all EXCEL functionality of Cut, Copy, Paste and with the click of a button it'll generate the required XML of the FR report.

Prerequisites

Create a dummy report with columns required for the original report. (The tool right now is designed to create rows only).
Export the report and save the .des file.
Make a note of the following - You can find these information from the 3rd line of .des file
- DATASOURCE_NAME
- SERVER - Essbase server name
- APPNAME
- DBNAME
- TYPE - Essbase/Planning
- DATASOURCE_ID
Click Create FR XML
- How many rows are required?
- How many columns are present in the report?
- Whether the current row is a Data/Text/Formula row
- Member name and Selection method (Children, Parent,....)
Copy the generated xml and paste the information into .des file (from line 7 <GRIDOBJECT ALIASTABLE="" till <GRIDAXIS AXISNAME="Column")
Save .des file and import the report into Workspace

Example

I created a dummy report with a single row and 3 columns (these are my required columns)

Sample FR .des file (before change)

I exported the report and filled in all required information in EXCEL.

After the XML file was generated, I copied the xml file and replaced the contents of my dummy report (from line 7 <GRIDOBJECT ALIASTABLE="" till <GRIDAXIS AXISNAME="Column")

Save .des file and import the file into Workspace. Open the report in FR studio and make the cosmetic changes (like formatting)

You can download the tool from here. This is tested on 11.1.2.1 version

Customizing EPM Workspace 11.1.2 Part II

noreply@blogger.com (Celvin Kattookaran) — Tue, 19 Jun 2012 18:14:00 +0000

Before I deep dive into the process of Customizing Oracle EPM Workspace (Part II).

A Word of Caution:

The method described here involves changing jsp pages. Check your licensing agreements to see whether this can be customized.

This post is a follow up to the customization described here.

When I posted Customizing EPM Workspace 11.1.2 Part I, I got a query "How to change the heading text that appears in the top bar of the browser?"

My first thought was "Why would someone even think of changing the browser title?". I tried to reason the question and came up with some scenarios.

Scenario 1

Imagine the situation where you are working on two different workspace instances

Dev Server
Test Server

I don't think (I'm not aware of a way) there is a way to determine the server name.

One answer could be that "You can look at the splash screen for the url". However it becomes difficult when there are multiple instances.

Scenario 2

Second answer "Open Workspace using http://servername:port/workspace/index.jsp" then you will always know which server you are in.

However I once worked on a project where we were not allowed to show any urls and we added an additional code to close the splash screen after logging in and then there is absolutely no means to track the url.

I got a good reason to dig up the jsp pages and see whether something can be done to solve this. :)

Solution

Inorder to change the title of the Splash page (Launch Application page, the same page which comes up when you use http://servername:port/workspace) we've to change launch.jsp page which is located in a war file (workspace.war), which is in an ear file. (workspace.ear).

Location

<Hyp drive>:\Oracle\Middleware\EPMSystem11R1\products

\Foundation\workspace\InstallableApps

Open the ear file and then the war file using 7zip, and edit launch.jsp page

Changes

Locate line 5 in launch.jsp page

String rstTitle = ConfigHelper.getApplicationName(application);

and change it to (rstTitle2 is the string that you can use to identify the server)

String rstTitle1 = ConfigHelper.getApplicationName(application);

String rstTitle2 = " Dev Server";

String rstTitle = rstTitle1 + rstTitle2;

Save the changes and update the ear and war files.

Inorder to change the title of Workspace Console (page which is launched right after the Splash page, page which is launched when you click on "Launch Application") we've to change index.jsp page which is located in a war file. (bpmui.war)

Location

<Hyp drive>:\Oracle\Middleware\EPMSystem11R1\common\bpmui-common\11.1.2.0

Open the war file using 7zip, and edit index.jsp page

Changes

Locate line 26 in index.jsp page

String rstTitle = ConfigHelper.getApplicationName(application);

and change it to (rstTitle2 is the string that you can use to identify the server)

String rstTitle1 = ConfigHelper.getApplicationName(application);

String rstTitle2 = " Dev Server";

String rstTitle = rstTitle1 + rstTitle2;

Locate line 157

String rstWindowName = (rstTitle + "_" + request.getServerName() + "_" + request.getServerPort()).replaceAll("\\W", "_");

and change it to

String rstWindowName = (rstTitle1 + "_" + request.getServerName() + "_" + request.getServerPort()).replaceAll("\\W", "_");

Workspace Console title and masthead title

Workspace Splash Head title

line 5 (var used in line 19) of launch.jsp is responsible for this title

Welcome description

line 268 of launch.jsp is responsible for this

Splash image tool tip

line 260 of launch.jsp is responsible for this title

Even though I won't recommend this myself, sometimes you've to do what you've to do.

HTH