How powerful is Essbase?

Akin to the hand of God, apparently

Yesterday I went to the Oracle Essbase New Features and Roadmap Update session given by Gabby Rubin and John Baker.  Some of it I had heard at Kscope12, although now a lot of the things that were in the “maybe this will happen, maybe this won’t, have you ever wondered why the sky is blue” category were now laid out in black and white.  

But there are lots of really, really, really interesting things coming.  I am more convinced than ever that Essbase will be around for a long time.  It won’t be the same Essbase I first saw in Essbase 3.1 back in 1994, but it’ll still be Essbase.  This is a good thing as static products are dead products.

Where does the magic happen?  Right here

So I was sitting next to my MMIC, Glenn Schwartzberg, who asked me to take a picture of the last slide, you know, the good one, where all of the future steps are mapped out because he had to leave the session early.  We were allowed (I thought) to take these shots, so up I walked to do just that.

I am no photographer, but I can do family snaps and, I thought, take a picture of a screen.  It looked clear in the preview of my not-so-beloved smart phone’s photo app.

I share this all with you, no matter what Oracle says

Well, it turns out we were not supposed to take photographs.  And apparently an angry demigod named Essbase thought the same thing, because here is the 100% unaltered shot:

What is the moral of this story?

Mess with the best, die like the rest.  And don’t have Cameron do the picture taking.  :)

A simple 23 step guide to Books, Batches, and Bursting in Hyperion Financial Reports

Introduction to the guide

I was asked in in this thread over on the Network54 Essbase board to post a guide to Hyperion Financial Reports batch bursting I wrote a while back.  I’m happy to share what I came up with but first a couple of caveats:

• I have redacted identifying information from the screenshots.  It’s usually pretty obvious where this has happened.  
• If you have questions about this, go ahead and make comments but unfortunately, I can’t reproduce the environment I did this one because:
• I wrote this for EPM 11.1.2.0 (so an almost two year old release).  I have no idea if the defects/bugs/weirdness/obvious-stuff-that-I-am-too-dense-to-see have been resolved or not in 11.1.2.1 and 11.1.2.2.  Mercifully, I have not been called upon to do more with batches since writing this document.
• Some of the issues I encountered were, I think, caused by a less-than-perfect installation.  I could rant on and on about how bad it was but it would bore everyone, even me.  At the time, I asked someone I know in infrastructure what he thought it would take to resolve all of the issues (oh, I had a working issues list).  The reply?  “I wouldn’t touch that with a 40 foot barge pole.  If I were to come in, I would insist on a complete reinstallation on a clean box.”  In other words, nuke and pave.  I mention this not to reflect the frustration we application developers encounter when an install is bad, but to note that some of the general weirdness may be because the software wasn’t “right”.
• I wrote this in the form of a step-by-step tutorial.  I never did find one on the web – I thought for sure there would be one but my google-fu failed me.  Maybe there’s one now, but I sort of doubt it.
• Contrary to what I wrote in that thread, doing all of this is not 23 steps, but instead:
• 24 steps (23 to create + 1 to view) to create that scheduled batch
• 6 steps to import a bursting file (and yes, I explain what a bursting file is) into Workspace
• 6 more steps to applying the bursting file into the batch scheduler
• Although I am somewhat math-challenged, that means this is a 36 step process.  It's almost the 39 Steps.

And with that out of the way, enjoy.    

Background

Briefly, there are six kinds of Financial Reports documents typically encountered in a Planning implementation:

1. Financial Reports – the base Essbase/Planning report
2. FR Books – collections of FRs using, where applicable, a common POV to drive all reports within a book
3. FR Batches – Objects that contain reports and books.  A batch can contain a single report, multiple reports, a single book, multiple books, mixes of reports and books, etc.
4. FR Scheduled Batches – Scheduler (Workspace has its own, reports-only scheduler) of FR Batches and their Books and reports.  Scheduled objects can get written to Workspace folders, zip files, and emails.
5. Burst batching – A way to parameterize scheduled batches and overload single dimension selections (only one dimension can be bursted and yes that is a strange word for it) with either manually selected members or members driven through imported burst files.
6. Bursting files – These are comma-delimited files used to drive burst batching

How to create a Scheduled Batch in Workspace 11.1.2.0

It’s just a simple 23 step procedure to define a scheduled batch.

Creating a book

1)  After creating a FR report, create a FR book by logging into Workspace and clicking on the Explore button.  Then select File->New->Document,.
2)  Select “Collect Reports into a book”
3)  Pick the report you want to incorporate into the book.  Books commonly contain more than one report but that is not a requirement.

4) Move the report(s) over to the right hand list box and when complete, click “Finish”.
5) The data sources in the report(s) will show up in the book.  These will be driven by the book’s POV.  
6) If you wish to get rid of the book’s table of contents, deselect it as shown below.
7) To force the save of the book, close the document; you will be prompted to save the book.
8) Save the book to whatever name and location you desire.
9) This book will be used in the batch.

Creating a batch

10) Go to File->New->Document again, but this time select “Batch Reports for Scheduling”.
11) Batches can be just a single or multiple books and reports.  To see them in the file selector, use the dropdown at the bottom to toggle between the different kinds of base documents.
12) Move the object you want to put into the batch over to the right.
13) Again, close the document to force a save action.
14) Save the batch.

Scheduling a batch

Creating the scheduled batch

15) Schedule the batch by going to the Navigate->Batch Scheduler menu.
16) In the Batch Scheduler screen, right click and select “New Scheduled Batch”.  This will launch the scheduled batch wizard.
17) Name the batch.  You can make the batch a one-tme affair by selecting “Delete Scheduled Batch Entry from Scheduler if Completed Successfully”.  Do not do this as creating a batch is fairly painful, as you may have noticed.  Click on Next to move to the next step.
18) Select the batch you want.  In this example, it’s “Batch for XXXXXXXXXXX”.  After entering the name, click on Next to move to the next step.
19) You will be prompted to log in to both FR and Essbase with an administrator id.  I do not recommend a specific userid like CameronTheConsultant as these ids get terminated.  

Selecting members for data connections

20) Individually select the connections (the All option doesn’t work, see Oracle Support ID 1097787.1) and select (usually) identical cost centers for each data source.
21) There’s a big bug in FR batches – as far as I can observe, when the scheduled batch is edited the Selected Members appear to be defined (and are shown in the Select Members text box) but are not.  The only way around this bug is to select the individual data connections and click on the Copy Members button to reapply the Cost Center members.

I have also found that clicking on the Preview Bursting List button for a given data source will deselect the selected members for the other data source.  To make sure both data sources are selected, click on the Copy Members button for both data sources and do not use the Preview Bursting List.

Click on Next to move to the next screen.

Defining where the batch output goes

22) Select “Export as PDF” and “Export to an external directory”.  Click on Next to run the batch.
23) A confirmation dialog box will appear.
 

Reviewing the output 

24) The external directory FRExport1, as defined through the FRConfig.cmd utility on the Financial Reports server, corresponds to \\yourservername\f$\FRExport1.  The output structure is as below.

Bursting files in scheduled batches

Bursting files are comma delimited files used to externally drive batch bursting member selections.  Given the bugs in FR batch scheduling, bursting files also provide a way to quickly and consistently select members in the Bursting Options.
For Oracle’s take on the bursting file parameters see:  http://download.oracle.com/docs/cd/E17236_01/epm.1112/fr_webuser/scheduler_wizard_fr.html

Bursting file fields

dimension_dimensionname

In the CCRpt example, the column name is dimension_Cost Center.  Within the field, the member values are the cost center numbers.  Per the documentation, member names must match on case.  Only one dimension per burst batching file can be defined.

subfolder_name

The name of the folders underneath the main one defined in the batch schedule.  The CCRpt example uses <<FinancialReportingObjectName()>>-<<MemberAlias()>> which passes the name of the book and the member alias from the dimension_dimensionname column into the sub folder name.  Other valid tags are <<MemberName()>>, <<BatchPovMember(DataSrcName,DimName)>>, <<BatchPovAlias(DataSrcName, DimName)>>, <<FinancialreportingObjectDescription()>>, and <<Date(“format”)>>.

financial_reporting_object_name

The name of the pdf files.  The same parameters as subfolder_name apply.

group_names

The Shared Services group that runs the batch, by row.  Not used in the CCRpt example.

role_names

The Shared Services role that runs the batch, by row.  Not used in the CCRpt example.

user_names

The Shared Services user name that runs the batch, by row.  Not used in the CCRpt example.

email_list

The SMTP email address that receives the batch pdf output.  Although this is set in the CCRpt example, it does not work because the SMTP configuration was not done during installation.

external_pdf_root

The root of the file output.  This overrides the output from the scheduled batch.

Importing the bursting file to Workspace

For the scheduled batch to read the burst batching file, it must be imported into Workspace.
1) After creating the batch bursting file in Excel and saving the output as a comma delimited file, import the file into Workspace by clicking on the Explore button, navigate to the target folder,  and then the menu File->Import->File and then select the file.
 
2) Click on Browse and select the file.
3) After confirming the file in the File textbox, click on the Next button to move to the Advanced screen.
4) Click on the Next button to move to the Permissions screen.
5) Click on Finish to finish the import process.
6) The file will appear in the target folder.
For your amusement, I have stuck a copy of the file here.  It’s in (as noted) comma-delimited format.

Applying the bursting file into the batch scheduler

1) In the batch scheduler, select the data source, then tick the “Run Batch for multiple members in the dimension”.  Then click on the ellipsis button to import the burst file.
2) Select the burst file – this is the file just imported into Workspace.
3) You will see the comma delimited batch bursting file’s full path.  Click on the “Copy Members” button to apply the values in the batch bursting file to the data source.
4) Once the Copy Members button has been clicked on, the members in the burst batch file will be shown in the Select Members text box.  This comma delimited list of members can be modified by clicking on the magnifying glass.
5) Select the other data source and apply the members by clicking on the “Copy Members” button.  It will look like the members are selected – this is not the case – never be afraid to click on that button.
6) Click on the Next button to continue defining the scheduled batch as defined above.

Conclusion

That was easy, wasn’t it?  :)

Okay, it wasn’t really hard at all, and it is pretty cool functionality.  However, it took me FOREVER and a day to figure out how to do this and I had to reach out to multiple people (in fact an ex-client from my ex-consulting company. Hello! Lisa Abrewczynski and Rholanda Brooks) to get the answer.  What, you think I figure all this stuff out by myself?  If only.  Oh wait, no one thinks that.  Regardless, it was super nice that they both took time to help me out – I am obliged.

And that’s it!  I hope you enjoyed this super short (hah!  25 pages in Word) guide to books, batches, and bursting.

Eye Sea Sea Bee Are Sea Eye

Introduction

Everyone has heard and/or used phonetic alphabets even if you haven’t been in the Services.  You know, it’s the word that sounds like the letter you’re trying to say, e.g., A-Alpha, B-Bravo, C-Charlie, etc.  

Pendant alert – according to Wikipedia (and we all know that an encyclopedia that anyone can contribute to is of the highest possible quality) it turns out that these alphabets aren’t phonetic but instead are acrophonic, which makes them the very opposite of phonetic.  But I digress yet again.  

While I personally plan on adopting the Royal Navy’s standard of 1917 so no-one ever figures out what I’m up to, thus ensuring that my evil plans for world domination (which, admittedly, are progressing somewhat behind my original schedule as I am in control of precisely nothing – I do however own a cat, or perhaps he owns me) can continue unabated.

A language for Essbase geeks

But perhaps I’m wrong in adopting the standard of Jack Tar in the Great War.  Essbase geeks do their own thing, in their own way.  C’mon, we’re unique.  And awesome.  Aren’t we?  Please say yes.

And if we’re awesome, we surely need our own acrophonetic alphabet.  Guess what?  We have one, courtesy of the ASO wizard, Dino, aka Dan Pressman.

Without further ado, here is his mad genius.

Letter	Pronunciation	Example
A	R	How are you?
C	Q	Pool cue
D	W	Double U
E	I	Eye
F	Weigh	“There’s no F’in way”
G	N	Gnat
H	Ah	Hour
I	E	Iwo Jima
J	H	Jose
K	N	Know
L	Y	Llama
M	N	Mnemonic
O	W	One
P	N	Pneumonia (or Swimming – the silent P)
Q	Key	Quay
S	C	Sea
T	Z	Tse-Tse
W	Y	Why
Y	U	You

Why Tse-Tse There’s No F’in Way

One man’s madness is another man’s genius.  I think it’s absolutely brilliant.  Your reaction may be “Whisky Tango Foxtrot.”

Stupid Planning queries #4 -- Version

Introduction

Today we consider the simple Version dimension in the seemingly-endless-but-honestly-not-that-many-more-please-God-make-it-stop-but-oh-yes-soon-soon-soon series of Planning dimension queries.  Simple?   Really?  Sure, how complicated could such a small dimension (Working, Final, etc.) be?  

Do I ask leading questions?  Why, as a matter of fact, yes I do.

As always with these queries, they are 100% unsupported by Oracle and there’s a tremendous chance that I’ve gotten them wrong, so test, test, test and remember that you are hacking the tables and if anything blows up you are completely on your own.  Got it?  Good, let’s begin.

Questions, questions

I love poking around in the odd corners of Planning – there’s always interesting stuff to be found.  

Does anyone know:

1. The purpose of the “Personal” VERSION_TYPE?
2. The difference between a Private and Public ACCESS_TYPE?  Or even what ACCESS_TYPE might mean?
3. The definition of the IN_USE field?

Send all answers care of this blog.  The world will thank you.

Some call it bragging

I call it the joy of figuring out something that really, really bugged me.  This one had me stumped for a fair bit so it made my day to figure it out.

What happens when a subquery returns more than one row?  Wait, wait, I can tell you –KABOOM!  

Unless you grab the results and concatenate it into a string.  Nah, there's no way I figured this one out on my own, but I am at least capable of stealing the idea from the interwebs.
The setup
Here's the Version's (Same old Saturday night) three UDAs:

These are the only three UDAs in the entire application (hey, it’s the Sample Planning app, no actual reflection of reality required), so this code:
SELECT * FROM HSP_UDA

Returns:

UDA_ID	DIM_ID	UDA_VALUE
1	35	Test UDA
2	35	Test UDA #2
3	35	Test UDA #3

The fix (in SQL Server at least)

The code:
SELECT STUFF((SELECT ',' + UDA_VALUE FROM HSP_UDA FOR XML PATH ('')),1,1,'')

Returns:

Test UDA,Test UDA #2,Test UDA #3

Ooh, pretty.  And think about the way ODI returns UDAs.  Yup, it’s the same thing.

I will admit to being all kinds of excited about this and sharing my geeky, gawky delight in this with two of my more SQL-oriented colleagues.  They were…unimpressed.  Sigh.  Oh well, such is the lot of the SQL FNG.

The code

So, putting the above into the ever more standard pull-the-dimension-from-Planning query:
--    Purpose:    Query the Version dimension
--    Modified:    23 July 2011, first write Cameron Lackpour
--    Notes:        There's some interesting stuff in Version,
--            much of it not visible through the UI.
--            I threw in UDAs and Formulas.
SELECT
    O1.OBJECT_NAME AS 'Member',
    CASE V.VERSION_TYPE
        --    I have no idea what "Personal" means, but it's in
        --    Dave Farnsworth's and Oracle's schema guide.
        WHEN 0 THEN 'Personal'
        WHEN 1 THEN 'Standard Bottom Up'
        WHEN 2 THEN 'Standard Target'
    END AS 'Type',
    --    I don't know what Access means, either.  Something to do
    --    with EPMA?  This is what comes from hacking mostly undocumented
    --    schemas.
    CASE V.ACCESS_TYPE
        WHEN 0 THEN 'Private'
        WHEN 1 THEN 'Public'
    END AS 'Access',
    --    Oh, this is a broken record.  I can't tell what IN_USE
    --    actually does.  Anyone with a clue please write care of
    --    this blog.
    CASE V.IN_USE
        WHEN 0 THEN 'No'
        WHEN 1 THEN 'Yes'
    END AS 'In use',
    -- NB -- The overall SELECT from HSP_MEMBER ensures that members
    --         with and without an alias are selected.
    -- ISNULL puts in zero length string in place of NULL
    ISNULL((SELECT OA.OBJECT_NAME
    FROM HSP_ALIAS A
        INNER JOIN HSP_OBJECT OA
            ON A.MEMBER_ID = O1.OBJECT_ID AND
            OA.OBJECT_ID = A.ALIAS_ID), '') AS 'Alias',
    --    Hah!  Finally, one that i can figure out.
    --    This is the date/time that the Version was created.
    V.DATE_IN_USE AS 'Date created',
    CASE M.DATA_STORAGE
        WHEN 0 THEN 'Store Data'
        WHEN 1 THEN 'Never Share'
        WHEN 2 THEN 'Label Only'
        WHEN 3 THEN 'Shared Member'
        WHEN 4 THEN 'Dynamic Calc and Store'
        WHEN 5 THEN 'Dynamic'
    END AS 'Storage',
    CASE O1.MARKED_FOR_DELETE
        WHEN 0 THEN 'False'
        WHEN 1 THEN 'True'
    END AS 'Marked for delete',
    CASE M.DATA_TYPE
        WHEN 1 THEN 'Currency'
        WHEN 2 THEN 'Non Currency'
        WHEN 3 THEN 'Percentage'
        WHEN 4 THEN 'Smartlist'
        WHEN 5 THEN 'Date'
        WHEN 6 THEN 'Text'
        ELSE 'Unspecified'
    END AS 'Data Type',
    --    No way to show the Plan Type.  Remember, a given Scenario is
    --    in ALL Plan Types.
    --    This could vary by Plan Type, so you may need to repeat this CASE.
    CASE M.CONSOL_OP1
        WHEN 0 THEN '+'
        WHEN 1 THEN '-'
        WHEN 2 THEN '*'
        WHEN 3 THEN '\'
        WHEN 4 THEN '%'
        WHEN 5 THEN '~'
        WHEN 6 THEN '^'
    END AS 'Operator',
    CASE M.TWOPASS_CALC
        WHEN 0 THEN 'No'
        WHEN 1 THEN 'Yes'
    END AS 'Two pass',
    CASE M.ENABLED_FOR_PM
        WHEN 0 THEN 'No'
        WHEN 1 THEN 'Yes'
    END AS 'PM?',
    ISNULL(F.FORMULA, '') AS 'Formula',
    --    The subquery will puke if it returns more than one
    --    UDA, so do string concateenation using FOR XML PATH
    ISNULL((STUFF((SELECT ',' + U.UDA_VALUE
        FROM HSP_MEMBER_TO_UDA MU
        INNER JOIN HSP_UDA U ON
            MU.MEMBER_ID = O1.OBJECT_ID AND
            MU.UDA_ID = U.UDA_ID FOR XML PATH ('')),1,1,'')), '') AS 'UDAs'
FROM HSP_VERSION V
INNER JOIN HSP_OBJECT O1 ON
    V.VERSION_ID = O1.OBJECT_ID
INNER JOIN HSP_MEMBER M ON
    M.MEMBER_ID = O1.OBJECT_ID
LEFT OUTER JOIN HSP_MEMBER_FORMULA F ON
    V.VERSION_ID = F.MEMBER_ID

The results

It’s way too wide for this blog.  Click here to download the Excel file with the results or squint like crazy and look at the graphic below.

Regardless of where you look, the cool concatenation is way over on the right side of the table.

One day I will be the SQL ou manne and I suppose I won’t be impressed with this sort of thing.  In the meantime, I think that’s a pretty cool hack.

Happy Planning hacking!