Shared Member Problems in BSO Calcs

Shared members are an amazing feature of Essbase but they seem to always present challenges and many of those are not easy to spot.

A question arose on the Planning Cloud Customer Connect recently where the issue the poster was having was related to a shared member/alternate hierarchy. For some reason, it's never immediately apparent that this is the problem. Several people suggested a number of reasons before it dawned on me that a shared member was the culprit.

In a nutshell, they were requesting the parent of a shared member, expecting to get back the shared member's parent. However, they were getting back the protype's parent.

Suppose the following hierarchy:

If you had a calc script that was iterating through the level 0 members under Diet, and you requested the parent of each, you wouldn't get back the member name Diet. For 100-20 you'd get back 100, for 200-20 you'd get back 200 and for 300-30 you'd get back 300.

This problem shows up when you have a formula with something like the following:

@PARENT("Products")

Note: The @PARENT calculation function for Essbase returns the parent of the current member being calculated in the specified dimension.

The workaround the poster settled on was to add a new alias table and look up based upon that using the @ALIAS() function. It would be nice if there was a function, maybe @SPARENT(), that would return the parent of a shared member.

Update: I'm told that @SPARENT() does exist as an undocumented function. I tested the function and it returns the parent of the first instance of a shared member. If you need the second or greater instance, it seems, you're out of luck. Use at your own risk.

A Funeral for the Two Pass Calc

New Session Added! BSO Solve Order!

I'm happy to announce that I'll be holding a funeral for the Two Pass Calc in room 611 on Monday morning at 11AM. This is one of the new 30 minute sessions. I also plan to show off OAC live so come watch me nervously hope that the conference wifi is working. If you're planning to move from On-Prem Essbase to OAC soon you'll want to know all about BSO Solve Order.

My other session is Wednesday at 9AM

I will to be speaking about ASO Tuning and Calculations (but mostly about Calculations). If you've been hesitant to try writing ASO calculations, please come to this session. There is a new way of performing ASO calcs in OAC which I will go over. I'll also be sharing my Top 10 list of things never said by an Essbase developer. Stop by and say hello at 9AM on Wednesday morning in room 609.

Please visit me at the Applied OLAP Booth 

Please stop by and say hello. I'm looking forward to meeting a lot of new people at Kscope this year.

ASO Test Data or: How I Learned to Stop Worrying and Create Hybrid

I wanted to fill ASOSamp.Sample with some data so I could run a few tests. I wanted to use the completely undocumented "filldb" command within ESSCMDQ in order to do so. Unfortunately, it only works on BSO. So I needed to turn ASOSamp into a BSO database first. There's no way to do that, right? Wrong. A very wise Essbase developer points out in this OTN post that one need only run the Aggregate Store Partition Wizard available within EAS in order to do so.

Let's Recap:

1. Run the Aggregate Store Partition Wizard to make a BSO copy of your ASO cube. It may give you some warnings so I suggest first making a copy of that ASO cube and using the wizard on the copy. If you get warnings, go into the ASO outline and fix the issues. You'll need to remove things like solve order and other ASO specific items.
2. If you only need to turn the BSO cube into a Hybrid cube, you can use Celvin Kattookaran's utility available here that changes all upper level sparse members to dynamic calc members. Stop here if you don't need a test bed of data.
1. Don't forget to set ASODYNAMICAGGINBSO in your essbase.cfg file.
3. Download and unzip ESSCMDQ. It's probably easiest to put this file in the same place where ESSCMD or essmsh already run so you don't need to fiddle with paths, libraries and such.
4. Run ESSCMDQ. Login as you would ESSCMD (no semi-colons!):
1. login hostname user password
5. Select the database you need to fill with data:
1. select appname dbname
6. Run the filldb command. See the link for more information on this command. I would suggest starting out with a small size. I had a very small block size in my cube and it went way past the 1 GB mark even though that's what I set it to. With a normal size block it was close (within 10%) to 1 GB.
1. filldb [size density distance]
7. Export the data from the BSO cube.
8. Import the data into the ASO cube.

Now I can easily create a test bed of data for ASOSamp at whatever density I require. I'll be using this in future posts.