FIX(@RELATIVE(@UDA("Market", "Major Market"), 0))

I'm sure that my article title cannot be more explicit! Indeed have you ever tried to reach level 0 members from a member list? How many people hoped doing so using the poor LOOP command available in Essbase library?

Well this was not possible at least using the good old RELATIVE calc function :

FIX(@RELATIVE(@UDA("Market", "Major Market"), 0))

A quick look at Essbase techref (11.1.1.3/11.1.1.4 in my case) and I found this calc function which will do the trick :

@LDESCENDANTS(@UDA("Market", "Major Market"), 0)

Here an extract from techref regarding @LDESCENDANTS calculation function :

Returns all descendants of the members in the specified member list or all descendents down to the specified generation or level. This function excludes the specified members.

You can use the @LDESCENDANTS function as a parameter of another function, where the function requires a list of members.

This function was made available with Essbase version 11.1.1.


Thanks to Cameron Lackpour for putting me "the flea in the ear", indeed and originally I was looking for a way to loop accros a list of members.

Le premier ouvrage en français sur Essbase est maintenant disponible au format numérique sur le site des éditions ENI, Fnac, Alapage, Google Books et Amazon. Il est également disponible dès aujourd'hui en librairie!

Ce livre a été co-écrit avec Antoine Dinimant, Wojtek Janecszek et Laetitia Terlutte.

The very first Essbase book in French is now available in your favorite e-bookstores around such as Amazon, ENI, Fnac, Alapage, Google Books. French readers will find it in the best librairies around.

This great book was co-written with Antoine Dinimant, Wojtek Janecszek and Laetitia Terlutte.

Bulky list of RSS links for Essbase and EPM blogs

Here's a list of Essbase and EPM blogs I use to follow from my SAGE RSS Firefox extension, cool extension btw.

Art of Business Intelligence

Business Intelligence

Business Intelligence - Oracle

Cameron's Blog For Essbase Hackers

Comments on Hyperion Starters Blog: Hyperion Essbase Overview

Cubegeek

EPM: Hyperion Essbase, Hyperion Planning

Essbase 101: Essbase my fist cube !WIP!

Essbase Information

Essbase Labs

Essbase Users

EveryDay EssBase

full360.com

Gary Crisci's BI Blog

Glenn Schwartzberg's Essbase Blog

Gobán Saor

Harry Gates’ blog

hyp3d on hyp3rion

Hyperion Consultant Blog

Hyperion Expert by Bob Wang,Ph.D,

hyperion-essbase

In 2 Hyperion

Intellient

jason's hyperion blog

Just Another Analytical Opinion? A Consultant's Notes on Hyperion...

Look Smarter Than You Are

Matthew Holt

More to life than this...

My Everyday Essbase
New arc|community

Olap Automation

Oracle BI Blog - EPM, Business Intelligence, and OBIEE

Oracle EPM and BI Technology

Oracle Hyperion

Oracle Infogram

OTN Discussion Forums : Popular Threads - Essbase

OTN Discussion Forums: Forum Announcements - Essbase

Ranzal & Associates Weblog

The I.T. Side

Tim Tow's Hyperion Blog

Vlamis Software

Web Analytics Powered By Bime

My first CDF : @RANDOM

I recently wrote my first CDF using Netbeans IDE 6.9.1. This CDF is really simple and is not based on JAPI or any other external libraries, @RANDOM just returns a random value! Quite basic but useful to populate your database with random data.

Have a look at the Java class below, once compiled in a jar and copied to Essbase,  you may register it using the following MaxL instruction :

CREATE OR REPLACE FUNCTION 'Sample'.'@RANDOM' AS 'myCdf.random' SPEC '@RANDOM()' COMMENT 'Return a random value';

Extract your Essbase outline with OutlineReader

OutlineReader  is a Java package (jar) made to extract Essbase outline structure (parent-child format) and its properties (alias, formula, uda, attribute...), making it a good way to document your database outline.

I got inspired by Outline Extractor utility, currently maintained and supported by AppliedOlap and originally developed by OlapUnderground. 

OutlineReader is also widely inspired by the ReadOutline.java class that is available in the Provider Services JAPI samples (please refer to Hyperion\products\Essbase\aps\samples directory).

OutlineReader is based on Essbase Java API v.11.1.1.3 (JAPI) and was pre-compiled using JDK 1.5. As IDE I used Netbeans IDE 6.8 and lately 6.9.1. I also used Apache CLI (Command Line Interface) in order to simplify the development of the command line interface.

OutlineReader is free and is open source software (MIT license), please feel free to contribute!

OutlineReader and its sources may be downloaded from here

A nice Perl script that I built in order to manage and size databases caches every night. Not sure that it is perfect but we run it in production since a few months and it seems to work fine up to now. By the way we only have buffered I/O cubes.

The following Perl script takes the following arguments : application and database name and cache type.

 How to call : perl.exe confcaches.pl "application" "database" "buffered"

Adjust menu for Essbase add-in

Adjust menu for my Essbase classic add-in toolkit. I just got inspired by Smart View's Adjust functionality, except that here you may use color to identify adjusted cells!
Find the code below, as you'll see it may not be the most beautiful VBA code but it should be clean enough for your understanding - btw I let you traduce the comments!

Merging Essbase audit logs

If you always wanted to merge your Essbase audit files for quick troubleshooting then you may have a look at this Perl code.
 
 The Java version witch include a GUI is available here

Essbase Rightlog lite version

Lately I reviewed my rightlog Perl script making a light version and correcting a few bugs. The script takes any Essbase server or application log as input.

This lite version doesn't provided a deep analysis with SQLite db as the original version and doesn't provide a native Excel output. The idea was to make it work in any Perl environment without any specific module.

You may also compile this Perl script using PAR which is a great Perl module. I installed PAR module through Perl Package Manager (graphical or command line : ppm.bat) and compiled RightLogLite.pl using the following command : pp -o x:\RightLogLite.exe c:\RightLogLite.pl

RightLogLite is also available as a download here

Validate Essbase filters

Here is a small but efficient piece of Java code (+JAPI library) made to list, verify and validate Essbase filters.

I was inspired by the JAPI code samples available in APS (Provider Services) directory (%APS_HOME%\samples\japi) and the rest was guided by my imagination and needs. By the way it also displays user and groups assigned to the filter.

We run this script every night and then add it to the server export package together with other maxl exports, Essbase.cfg and SEC's backup. So we have a daily file ready for any security audit.

I did not post any compiled code as this is not a big deal doing it in Eclipse or Netbeans. just don't forget adding JAPI library before compiling (%APS_HOME%\lib\ess_japi.jar).