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

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

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.