Why I created a blog

Its been four years since I first created this blog. It has remained true to Essbase and related information over those years. Hopefully it has answered questions and given you insight over those years. I will continue to provide my observations and comments on the ever changing world of EPM. Don't be surprised if the scope of the blog changes and brings in other Hyperion topics.

Tuesday, October 9, 2012

Studio Drill Through Tips

I’m back from Oracle Open World and while I typically blog a lot during the conference, this year I sat back and listened more. It was a long week and I’m glad it is over. The biggest announcement for EPM was Planning on the public cloud. In Q1 2013 Oracle will be previewing (can you say Beta) their offering. There are still a lot of things being worked out, but it is an interesting proposition.  I think this will also allow Oracle to fill a need that they have overlooked. Smaller companies can take advantage of a cloud implementation as It will allow companies to implement planning with less hardware and IT resources. It should also allow for more power in the planning process. It should be noted this will be Planning without EPMA as EPMA requires a Windows server and the cloud will be Linux based.  There were some other changes in the works but I’m under an NDA and am not allowed to talk about them. Suffice it to say, I look forward to seeing them.

As part of my continuing series on Studio tips and tricks, I thought I would throw a few drill through tidbits your way.

First, let’s talk about how Studio (11.1.2.2 at least) handles recursive hierarchies and returning data. The easy part is if you are at level zero. Studio returns the member name. If, however, you are at an upper level and have turned allowed drill trough from that level, you get a list of the level zero members under the member you selected. What is that in English?  Think of the periods dimension. If I select Q1, Studio returns to the query Jan, Feb and Mar. This is why you would need an in clause to process them.

Where Periods in ($$Periods-Value$$)

If you look at the documentation it says you need to use the format ($$Periods-Column$$) in ($$Periods-Value$$),

but I’ve found I can just use the values if I’m not joining to the dimension table. Something associated that I found is the variable names are case sensitive It took me a long time to debug an error of “Invalid character in SQL”  when I typed PEriod instead of Period.

Also associated with how the list is generated and exposed by Tim Tow, is the in clause is limited to 1000 members. If your level is going to return more than 1000 members , you get an error. Something about line 1792 which is the generic of “oh crap, I have no clue what is happening”. 

Next, As I’ve created custom SQL, I was VERY careful that the list of items to be returned matched the report contents list

image

As it turns out, I was overly cautious. It turns out whatever is in your custom SQL ignores this list completely and returns whatever is in the SQL itself.

Finally, I ran into a little oddity with copying drill through reports.I have two reports one named “JE_Detail” and another named “JE (GL Entity, GL Account)”. I can copy and paste the first one with no issues into the same folder, but the second one fails with the error

image

To work around this, I used the export function in 11.1.2.2. After exporting the report, I edited it in Notepad and change all occurrences of the name to “JE Glenn” and reimported it.  That worked just peachy. I’ve used the export/import functionality on a number of instances now and it works really nice. In this case, it knew this was a drill through report and it allowed me to turn off associating the report to cube schemas and models as I exported it. Very nice as I wanted to modify it before activating it for users. The other times I’ve used the export and import, it worked flawlessly for individual objects or for a full export.

In the next few weeks I’ll be talking at interRel EPM roadshows in Calgary, Phoenix and L.A.  This event is open to Oracle customers (sorry consultants and competitors that read my blog). These are great events and if you are interested in getting more info contact Danielle White at dwhite@interrel.com.

I’ll also be speaking at Connection Point in November and the Michigan User group meeting.  It will be a busy couple of months for me.

6 comments:

fsot888 said...

Hi Glenn,

I am following along the exercises in your book Look Smarter..with Essbase Studio 11 and getting stuck on the deployment. Getting an error No Mapping found for class: \TBC_Sample\Members\Sales\Amount. I have no clue what this means. Any ideas on what steps I missed? This is the first deployment.

GlennS said...

While I have an idea of the problem , it is difficult to say without seeing what you misischema, Amout member, and cube schema look like. Can you send me screen shots of those to gschwartzberg@interrel.com and I'll see if I can help you

Anonymous said...

Hi Glenn,

Could you please explain on how to handle variable with morethan 1000 members in Essbase studio sql IN Clauese?

GlennS said...

I am not sure there is a solution other than limiting the drill through level so you don't get that many members. I believe the problem only occurs if you are using a recursive hierarchy, If it is possible to change it to be generations or levels, then you would get the actual member name for the intersection and could do a join between tables to get the level 0 members but that would be a redesign of your implementation.

Lu said...

Hello,

In this post you said that for a recursive hierarchy:
< If you are at an upper level and have turned allowed drill trough from that level, you get a list of the level zero members under the member you selected. >
So for my Product dimension ( recursive hierarchy), if i select Generation2 in the Define Intersections Tab,and then in my drill-through report, if I select an upper level member, the parameter passed for the product dimension, to the Essbase Studio query,will be in fact a list of all Level 0 members under the selected one?

GlennS said...

Yes, it will be in part of an in clause. and while in older versions the sql would fail if you had over a certain number of members, the Studio team now will break up yourquery and make multiple in clauses to prevent failures