Dave's Adventures in Business Intelligence

A few years back (wow, has it really been that long since I have written here?) I wrote about BOB retiring. Today I am writing to announce my own retirement. I will be leaving my current position and trying to figure out where the sun sets and head in that general direction. 😎

It has been an amazing journey. I plan to leave this blog up for at least another year and then may consider retiring it as well.

So long, and thanks for all the fish!

BOB turned 18 this year, and has retired. We should all be so lucky as to be able to retire at 18, but BOB was special.

The original BUSOB-L Listserv started in 1996. By the early 2000’s the community had outgrown the mailing list format and was looking for alternatives. We settled on the bulletin board / forum package called phpBB2. The forum software was installed on June 6, 2002. We had several months of testing and enhancements and just over 70 members when BOB was announced publicly for the first time on August 15, 2002. That became BOB’s birthday, which occurred even before BOB was named BOB. The original discussion board had a fairly wordy header, with the phrase, “BusinessObjects Discussion Board” or something like that. It was a bit of a mouthful. The phpBB software came with a set of smilies or emoticons, one of which was named Mr. Green. Someone – and unfortunately I never remembered exactly who – suggested that Mr. Green’s first name was Bob, which was a shortened version of BusinessObjects Board. BOB it was, and BOB it remained for the next 18 years.

Over the years we have outgrown or moved servers at least four times (I think). In the early days we sold BOB t-shirts at the user conference to fund our hosting bill for the year. That was a lot of fun! I still have a few BOB t-shirts around somewhere. In later years we funded the server by selling sponsorship / banner advertisements, combined with Google ads on the bottom of the page. It was hard to imagine in 2002 that we would be running the same software 18 years later. Think about it…how many technical innovations have there been since 2002? Heck, the first iPhone wasn’t even released until 2007! BOB is older than the iPhone! 😆 But yet there we were, humming along. Yes, the database had to be upgraded periodically, and our server was replaced a number of times, but the core software package continued essentially unchanged. That’s amazing.

As amazing as it was, it was time. Earlier this year the community started to discuss what BOB 2.0 might look like. Now we know.

I present BOB 2.0.

The new platform is based on the Discourse platform, which will hopefully be more mobile friendly among other things. I wish the new project the best of luck. If it also lasts 18 years, then I will retire before it will. 😎

Really good read, in my opinion. Here’s the opening paragraph:

Almost all data visualizations are multivariate (i.e., they display more than one variable), but there are practical limits to the number of variables that a single graph can display. These limits vary depending on the approach that’s used. Three graphical approaches are currently available for displaying multiple variables:

• Encode each variable using a different visual attribute
• Encode every variable using the same visual attribute
• Increase the number of variables using small multiples

In this article, we’ll consider each.

The Perceptual and Cognitive Limits of Multivariate Data Visualization

If you don’t follow Stephen Few, it’s worth the few moments of your time to go have a look.

Years ago we implemented query banding as a way to track queries being submitted from Business Objects. As I’ve mentioned we’re moving into Tableau as well now, and Tableau also supports query banding. When we started with Business Objects there were two choices: Transaction and Session. For no reason that I can remember, we opted for Transaction mode.

When we started to implement query banding for Tableau we discovered that it only worked for Session mode. That presented a problem.

Ultimately our code went from this:

some_table.user_name = GetQueryBandValue(1,'ClientUser')

to this

some_table.user_name = GetQueryBandValue(1,'ClientUser') OR some_table.user_name = GetQueryBandValue(2,'ClientUser')

The challenge was that the OR clause impacted query performance. We tried this:

some_table.user_name IN (GetQueryBandValue(1,'ClientUser'),GetQueryBandValue(2,'ClientUser'))

…but that also did not perform well. Interestingly enough we ended up with this:

some_table.user_name = GetQueryBandValue(1,'ClientUser') || GetQueryBandValue(2,'ClientUser')

Since only one of the two entries would ever have a value, using the concatenation operation || to put them both together and avoid the OR and the IN clause seemed to work well.

Now there is a better option. Based on this article, we are now implementing query banding using a zero option, as in:

some_table.user_name = GetQueryBandValue(0,'ClientUser')

There are different parameter options now that I feel certain were not available when we started or we would have been using them. According to the Teradata document linked above, the codes are:

• 0 = Return the first value found for the name=value pair. If the same name is found in both Transaction and Session then Transaction will be used
• 1 = Transaction mode
• 2 = Session mode
• 3 = Search the name=value pairs and find the item specified in the QBName input parameter

We now use mode 0 (zero) to check for both. I suspect 3 is used only when you need to override the default behavior of zero to check Transaction Mode first.

As a long-time Business Objects user / consultant / trainer it has been interesting to watch the growth and evolution of BI tools. I work for a fairly large company with a significant investment in Business Object technology. It’s not going anywhere. But a few years back Tableau got a foothold with their “try it free for 30 days” strategy. Tableau became the flavor-of-the-month because people could download it for free, put it on Excel data (which no doubt was provided via a Business Objects extract), and make pretty pictures.

Fast forward to today and we have a Tableau infrastructure with over 100 cores and the number of Tableau users is in five digits. And the new flavor-of-the-month is Microsoft Power BI because, quote, “Tableau is too expensive.” 😆 But ultimately the tool should not matter. A plastic spork gets food into your mouth as effectively as a silver spoon, right? A good (or bad) dashboard design is good (or again bad) no matter which tool is used to deliver it.

I think Tableau today is where Business Objects was years ago. Business Objects was one of the first tools to eliminate the need for business users to learn SQL to work with data. Tableau was one of the first tools to focus on visual exploration of data. Business Objects started out as a “departmental” BI tool. Anyone could set up an environment, build and distribute a universe, and let users start building reports. Tableau has also nailed departmental functionality and is working towards becoming a true enterprise tool. They’re missing some important parts like a true shared semantic layer, enterprise scheduling and distribution, and security. Business Objects has all of these things, plus the capability of exporting to Excel. Remember how long it took for us to get that feature? Anyone want into the pool to see how long it takes Tableau to bow to the same pressure? 😉 In my opinion Tableau also needs to get better at working with live data connections instead of requiring extracts for everything.

I actually anticipated this way back when I started this blog, which is why I named it “Dave’s Adventures In Business Intelligence” rather than Business Objects. The goal is to distribute smart, intelligent, useful information. I should not care about the tool.

But tools are important, so next week I will be at the Tableau user conference in New Orleans. It will be weird to go to a conference with so many people (20,000+) and none of them know who I am. 😎

It was brought to my attention recently that someone has taken content from my blog and re-posted it to blogspot. I have used the contact form available on their blog to request the removal of my content and have gathered the information I need to file a DMCA complaint if that is not successful.

I don’t have advertisements on this site, and I don’t ask for donations. Everything I have done over the years remains in place and available for the broader user community. Why someone would think they should (or could) take what I have created it and post it as their own work is beyond me. 😕 Bottom line: if you find my content posted elsewhere in the Internet (quotes are fine, references are fine, copying is not) please let me know. Thanks.

I’ve been inactive on BOB for a while as well as here…recently I was reminded that some of the old questions still come up periodically. For example, how do you reset a running total? It’s not hard, but the syntax is not immediately obvious. Continue reading “Resetting a Running Function”

…the more they stay the same.

I’ve been fairly quiet here for a long time. Part of the reason is I’ve been busy with family and work. My older son will be a senior in high school this year, and my younger is just starting driving lessons. But something happened a few weeks ago that made me want to write again.

At work I’m now overseeing the Tableau team. We still have a lot of Business Objects users, but Tableau has become the “flavor of the month” that everyone wants to use. Tableau certainly has the right idea in that their product works identical whether viewed on the web or via a mobile device, and their mapping options are really nice.

But at the core, it’s still a data presentation engine.

A few weeks back, I was trying to solve a problem that I knew how to solve in Web Intelligence. Specifically, I wanted to generate a grand total that would return the same value no matter where I dropped it on my dashboard. In Web Intelligence this is done via Calculation Context and it looks something like this:

=Sum([Revenue]) In Report

By adding the key words “In Report” I am telling the tool that I want to see the grand total. I’ve covered calculation context before (Calculation Context Part I: Overview) and it remains one of my more popular posts. It’s definitely the one that has the most people asking, “Where’s part 2?” Other calculation options in Web Intelligence include “ForEach” and “ForAll” to go along with “In” that I have already mentioned. Here are the definitions I wrote the first time around:

• In is used to specify exactly which dimensions to include in a context. Other dimensions in the block are ignored. Adding / removing elements from the block does not impact this calculation unless a removed dimension was specified in the context. In that case a #MULTIVALUE error is displayed.
• ForEach is used to include a dimension in a context. The calculation context is still affected by other values in the block.
• ForAll is used to exclude a dimension from a context. Other dimensions of the block will still be considered. Adding or removing values from a block might change the value, but it will always ignore the ForAll items.

Why bring this up again? Because Tableau has exactly the same options. 😆 Instead of Context they call their option Level of Detail. You activate a Level of Detail operation by using curly braces { } around your calculation. Suppose you want a grand total, it looks like this:

{Sum([Revenue])}

That is the equivalent of “In Report” for Web Intelligence. What about the others?

Tableau provides FIXED, INCLUDE, and EXCLUDE as additional tags for their Level of Detail operations. Each of those is a direct equivalent to a Web Intelligence option.

FIXED is the equivalent of IN. These two statements are an equivalent way to request sales by year, even if other dimension values are present in the block or worksheet.

{FIXED [Year]: Sum([Revenue])}
=Sum([Revenue]) In [Year]

INCLUDE is the equivalent of ForEach. These statements are going to make sure the Year is included in the calculation, even if it’s not part of the displayed block or worksheet.

{INCLUDE [Year]: Sum([Revenue])}
=Sum([Revenue]) ForEach ([Year])

Note that if there are multiple Years then Web Intelligence will show a #MULTIVALUE error, and Tableau will show a * as the result.

EXCLUDE is the match to ForAll. These formulas will specifically ignore the Year value when generating Total Revenue, even if it’s present in the block or worksheet.
{EXCLUDE [Year]: Sum([Revenue])}
=Sum([Revenue]) ForAll ([Year])

The person who was starting to explain the Level of Detail function in Tableau started out by saying, “Now this is one of the more complex techniques that Tableau offers, so it may take a bit to get a handle on it.” As soon as he started the technical explanation I cut him off, saying, “Oh, it’s just like Calculation Context. I’m good.”

🙂

So yes, the more things change, the more they stay the same…

Keeping with the food them from my prior blog post, I present to you: 40 Maps That Explain Food in America. In fact, it was this article that lead me to the Waffle House article to begin with.

The 40 maps use a variety of visualization techniques to show different food-related subjects. For example, what’s the most typical barbecue in Texas? It would appear to be brisket. Do you drink Soda? or Pop? Or that red drink? (I’m not allowed to type out the name…) How far are you from a McDonalds?

There are some predictable visualizations including color-coded maps, line charts, bar charts, and of course even a pie.

Mmmmm, pie.

With “reporting tools” we have become extremely good at letting people see things they already know. At some companies it can be very difficult to break out of that mode. I can’t tell you how many consulting engagements that I had where I was simply asked to take something they already had and recreate it in a newer tool. While possible, work like that was hardly exciting.

In an unrelated note, FEMA, or the Federal Emergency Management Agency, apparently likes waffles.

What do these two concepts have to do with each other?

FEMA realized that Waffle House was an excellent indicator of whether electricity was available in an area, and therefore could be considered an indicator of an area that they would have to assist. If a Waffle House was open, the area had electricity and passable roads. If the Waffle House was closed, maybe not so much. Even better, Waffle House locations were sprinkled all along the Gulf Coast as well as the Eastern seaboard; both hurricane-sensitive areas. You can read the full article (originally published in Popular Science) at the link below.

What I found interesting about the article was the fact that something that on the surface would seem to be completely unrelated becomes interesting when viewed through the proper perspective. I have been looking within my current company, trying to find something similar, and perhaps I am too close to things but so far I have not been successful.

What about you? Have you found your Waffle House?

Related Links

• How Waffle House Became a Disaster Indicator for FEMA
• What is FEMA?