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.

Customer Service

Recently, I received a message via LinkedIn from a Product Manager at Oracle.

I won't say who, just because I wouldn't want to get them into any trouble.

The email was fairly general, mostly asking if I had used their product and if I had any questions. Well, I hadn't used that particular product, but I did have questions as it falls into my DW/BI realm.

So I sent my questions.

Within a day or so, I had this long, very detailed description about the differences between the 2 products and why one would choose one over the other (both products were Oracle products). It also included customer examples (sans customer names of course) of who is choosing what and when.

I love these kinds of exchanges. I love having the ability to ask questions of people who manage products because you get real insight into how they are thinking.

I believe this speaks volumes for this individual specifically and Oracle in general. I know Oracle isn't perfect by any means, but what company of that size is? This person wanted to make sure that I was informed, reached out to me, and provided me with the answer to my questions.

I certainly wish there were more people like this (not just at Oracle). After having sat in on an Exadata PoC, I can appreciate it that much more since I never get to spend the money. So thank you anonymous Oracle employee, you set a good example for quite a few people.

Google Is Stalking Me

Last week I was doing some research on buy vs. leasing a server/workstation.

Ultimately, I decided to buy one, this one.



I bought it because Amazon was just too expensive (for my purposes) and so was collocating a server (I wanted something a little more beefy than normal). I wasn't about to pay $1000 a month.

During that research, I made it to Storm website (on demand hosting). I'm not sure if I actually clicked on an advertisement on the result page of my Google search or what...but now they are everywhere.

I went to 4 sites today, and here's what I found:

wunderground.com



forum.xda-developers.com



somethingawful.com



linuxquestions.org



addictivetips.com



factmag.com



It's just a tad creepy, but I may be to blame for click on an advertisement (still unsure). Anyone out there experience something like this before?

DDL Auditing - Revisited

Of course I can't find the post(s) that I read recently, but they were all concerning auditing DDL. The most desirable solution is to use the built in auditing providing by Oracle...the only problem with auditing, in general, is that no one really monitors it. I'm sure there are numbers out there (read about that too, but can't seem to find the post). Oh wait, I found it, it was a video that I got via the Database Insider newsletter. You can see it here.

The gist of that video (as cheesy as it may be), is that some broker figures out a way to submit trades with his account that don't really exist. He's paid the commission, automatically, some $10 million. The Risk/Security Officer talks about the amount of time (months) they spent trying to track down how it was done. They were at least smart enough to have auditing turned on, however, they never monitored it. Somehow, all this led me to DDL auditing. No idea how, it just did.

After thinking about it for a few days, I realized the similarity between that and my PMDV project I did last year. It only made it to version 0.1, but this gave me the idea to revisit it. Maybe it was just poor naming/branding (PMDV stands for Poor Man's Data Vault)? Maybe it did just suck eggs? Tough to say for sure.

To sum it up quickly, it required a "ticket number" or "work order" to set an application context. Without that application context set, you could not perform any DDL. On top of that, it would record all the DDL changes performed during that deployment window and give you decent reporting through an APEX front end. Like I said in one of those other posts, a former company of mine had to provide the security team with audit reports for each deployment. While the built-in auditing would capture all this, what I created will at least capture that in one place and provide the objects that change.

So perhaps this falls into the Change Management landscape (which is on my radar again...big, big topic).

Guess what, I just remembered how I came to remember this...a conversation on Twitter with @hillbillytoad and @krisrice...that's what led me to the DDL trigger and auditing. I think Kris suggested that a DDL trigger should be installed to prevent little old me from doing damage. :)



That link led to here and then a few other places...