OBIEE: Evaluate

I haven't had a whole lot of opportunities to use this function. It's actually so rare, that I forget how to use it.

In the fine tradition of documenting everything, here I go.

Evaluate Syntax:

EVAULATE('your db function(%1,%2)', parameter list)

The reason I need to use it is because we are removing a view that has some fun SUBSTR/INSTR action in it. I tried writing it up in OBIEE SQL, but it doesn't allow you to traverse from the end of the string...well, someone out there probably could, but I like to limit this kind of thing in the RPD, preferring instead to put it in the database. As much as we'd like to believe it, OBIEE will not be the only application accessing this data. But I digress.

Here's the original Oracle SQL:

SUBSTR( col1, INSTR( col1, '/', -1 ) + 1 )

Which reminds me of something else...there is no real way to test this stuff in the admin tool (RPD). You have to create it there and then create an ad-hoc report to make sure it's working as it should. Rather than deal with that, I just started in Answers.

First go:

EVALUATE( 'SUBSTR( %1, INSTR( %1, '/', -1 ) + 1 )', '/TESTING/TESTING1/TESTING2/REPORT_NAME' )

This resulting in the following when I hit OK:



OK, my bet is that those single quotes inside the single quotes were the cause. So I tried variations of 2 single quotes, 3 single quotes and even double quotes. None of those worked either.

What if I just put that '/' in the parameter list?

EVALUATE( 'SUBSTR( %1, INSTR( %1, %2, -1 ) + 1 )' , '/TESTING/TESTING1/TESTING2/REPORT_NAME', '/')

Voila! Yay for me.

OBIEE 11g: Connect to Online Repository

I have been doing a quick and dirty proof of concept on 11g. The 32 bit installation went very smoothly on Monday, and then the hard work began.

11g was quite different from 10g and possibly the biggest differentiator was its integration with the WebLogic stack. While I do understand that move, it certainly makes things a more complex in the short run.

So I'm up and running and have the first RPD installed (that's another post, deploying an RPD...joy!), then the change requests begin to roll in. When I would try to open the RPD in online mode, I was greeted with this challenge:



OK, I know the repository password, but I don't remember setting an Administrator password (that was the default username). I tried everything I could think of, Admin123, which is the repository password for the sample application. Tried password. Tried every variation of it. No joy.

In the interest of time (and pressure), I gave up and just decided to edit the offline and then deploy. I was the only one on this system so I wouldn't be affecting anyone. In hindsight, it would have been best to ask my question (or read the docs).

A day later 2 more people joined me to help nail down some reports. I couldn't bring the server down everytime I needed to make a small change, so I asked the Twitter Machine...as usual, it comes through.

Here's the response:



@siebel_ess's link took me to the documentation. @bsousapt's link took me to the 10g way of doing it, which is actually a good compare and contrast as I'm too lazy to show the differences.

Perhaps on purpose, the first link led me to this page:



I'm sure the emphasis, for me, should have been placed on the "Understanding" part of the title.

So first off, I needed to log in to the console at localhost:7001/console. From there I would look for Security Realms:



Since I didn't do any customization of the Security stuff (naturally), I went to the default "myrealm":



Then found Users and Groups



And selected New



The screen is pretty basic, just username, description (optional), Provider (DefaultAuthenticator for me) and the password:



Annoyingly to me, you couldn't add any other attributes, like Group Membership on this initial screen. So I saved, then clicked on the user and was taken to their settings page...where I now find Groups.



This is my account, so I chose the 3 Groups related to OBIEE: BIAdministrators, BI Authors, and BIConsumers



Easy enough.

Now let's try it out. Opened up the BI Admin tool and opened my online repository. Entered the repository password, my username and the password.

Voila! I'm in.

Just an FYI, I am a glutton for punishment. I deployed 20 some-odd times before I finally looked this up. While not the most efficient, it did sear the process into my brain.

OBIEE: Hillbilly RPD Source Control

No, this isn't a reference to @hillbillyToad.

During all the hullabaloo the other day and me fretting that I might have irrevocably destroyed the metadata, Frank Davis showed me a cool little trick.

In the Admin tool go the BMM layer, right click on the model you want and select "Duplicate with Presentation Catalog."



You'll then be prompted to name your new BMM and Presentation layer.



Enter in the new names



And you now have a copy of your BMM and Presentation layer



What good is this you ask?

Well, it will allow you to do metadata development without affecting what any report writers are doing. It will also allow you to blow things up, quite easily if you wish, without major repercussions. That's nice to have.

OBIEE: Gotcha #1

When generating SQL for testing, make sure you set the database to the database you will be running against.

I found this out the fun way, of course, by going through, grabbing the SQL from the logs, and then modifying said SQL to run against Oracle. I should have paid attention to that "Check Consistency" warning.



I spent an hour or so "fixing" the SQL, removing stuff like the following:

fn ifnull
{ curly brackets }
d '2004-04-10'
ts '2004-04-10 13:44:39'

mostly small stuff, but annoying none-the-less. Strangely (to me anyway), this was the SQL submitted to an Oracle database...without errors. I'm guessing that somewhere, either in OBI or in the driver itself, the SQL is rewritten.

After I realized what had happened, I went back into the RPD and updated the database properties.



It was ODBC basic



I changed it to Oracle 10R2/11g



I tried the Consistency Check again and still received the warning. Opened up the database properties again and selected the Features tab



Then selected "Revert to Defaults"



Run the Consistency Checker again and voila!



So don't waste your time like I did. Make sure you use the Consistency Check Manager and make sure your database defaults match.

OBIEE: XML File as Data Source

I received a question in the form of a comment on using XML files here. Fittingly, we were discussing this exact topic yesterday and I decided I would try it out.

So here goes.

You'll need 2 files: a XML file and a XSL stylesheet (which defines the XML file). I found said files here. I'll also provide them here (cdcatalog.xml) and here (cdcatalog.xls).

In the Administration tool, go to File --> Import --> from Database



When you prompted to select a datasource, select XML and the window should look like this



For URL, browse to the location you saved cdcatalog.xml, do the same for XSLT



Hit OK and you'll be prompted with the Import dialog



Import the "table" and you're done.