Dave's Adventures in Business Intelligence

My first guest blog post was published last week, complete with an error on my part that was immediately called out in the comments. 😳 I was writing about Captain James T. Kirk and the Star Trek series. Somehow, with “James” on my mind, I credited a “James Roddenberry” as the creator of the series! Obviously that was wrong, it was Gene Roddenberry.

The post was updated, and I addressed my error by responded to the comment…

Part one, titled, “Business Intelligence Lessons From Star Trek” is out now. Part two will be out in a week or two. Please check it out!

I’m willing to bet that over half of my blog readers know what the “T” stands for in James T. Kirk… before googling the result. 😉

I’ve been asked to provide some blog material for another site called The Decision Factor. I’m get to be a guest blogger! My first post for that site will be published soon, and it’s titled “Business Intelligence Lessons From Star Trek.” You know you’ll want to read it, and it won’t be published here. My guest blog posts for that site won’t be the technical solutions I often publish here, but instead will be more high-level conceptual business intelligence topics. They won’t feature any specific product or solution, whether from SAP or otherwise.

My posts will be tagged as part of the “BI 101” series. The first post in that series is out now, and was written by Christine Mykota. It’s titled, “Turbocharge Your Career with Intelligent Business Intelligence.” It’s worth a read, even if she doesn’t mention Captain Kirk… 😉

This afternoon SAP is hosting a virtual launch event for Visual Intelligence. You can read about it and register here.

BAM! That’s what you’ll be saying when you revolutionize the way you make business decisions. When you register for the forum, you can:

• Hear from Howard Dresner of Dresner Advisory Services, who will present his learning from Wisdom of Crowds Business Intelligence Market Study®.
• Attend the virtual launch of SAP® Visual Intelligence software, which means chances are, you’ll see what it can do long before the rest of your competition does.

I had a question from a business user the other day. I was confident that I knew the answer as soon as I saw the first part of her question. 🙂 To be sure, I waited until we got through the entire discussion. Her question started off with:

I am doing date math — using nvl to set a date to jan 01 2099 when it is null…

Right at that point I could have made good money by betting that she was getting a “non-numeric character was found where a numeric…” message. Sure enough, I was correct. The code she was trying to run looked like this:

NVL(TABLE.DT_COL, '01-JAN-2099')

What’s wrong with that? On the surface, nothing. She was looking at a specific date column from a table, and if that date was null, she was replacing it with a specific (constant) date value. Or at least that was her intent. The problem was that the intentions were being interpreted differently depending on where the report was refreshed. It was working in Desktop Intelligence and failing in Web Intelligence. Why was that, and how could it be fixed?

Oracle Implied Conversions

The problem is that if I don’t specifically tell Oracle how I want my string to be converted to a date, then it will make a guess. Oracle will attempt to parse the string and determine the date, and it will look at the NLS_DATE_FORMAT for hints. However that configuration setting can be overridden by a variety of factors. Business Objects also has a variety of places where date formats can be specified and they can get in the way too.

The way I have generally solved this is to remove any guessing from the process by explicitly defining my date format. Rather than provide the code as written above, I would do the following:

NVL(TABLE.DT_COL, TO_DATE('01-JAN-2099','DD-MON-YYYY'))

Now my string is converted to a date using my supplied format mask, and the dreaded “non-numeric found…” message can be avoided.

I made this suggestion to my business user, and after a quick universe update all of her issues were resolved. The bottom line is that I don’t always have to explicitly define everything, but if I have a chance to do so, I generally try to do so. It may take me a little longer up front, but it will me save time in the long run. Even if someone changes the Oracle standard date format on my server the code I provided will continue to work. 😎

One of the marketing strategies from Business Objects has always been to tout their semantic layer as the “single version of the truth.” Which brings me to today’s quiz on geography:

1. What is the highest mountain on Earth?
2. What is the tallest mountain on Earth?
3. Which mountain on Earth has the biggest elevation gain?

Continue reading “It Depends On Your Reference Point”