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.

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. 😎

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.

Wait… what’s this?

Yes, my blog has a heart beat again. It has been a long time since I’ve been here, and I hope to fix that. I started this morning by reviewing all of the comments that were queued up for approval. I approved most of them and answered quite a few as well. One common question was related to whether my universe comparison macro would work with BI 4.1 or not; I have not had to use it in production on 4.1 yet, but I do have a sandbox environment where I can test that out. Hopefully I will have that done in the next week or two… along with posting the promised update.

Thanks for your patience.

For a while now I have been whining about the fact that there were features dropped from XI 3.1 during the upgrade to BI4, including how the dimension merging process works. Another gap? In XI 3.1 we had the ability from the query panel to run only a selected data provider, leaving the others alone. When working on complex multi-query documents this could be a big help, especially if some of the data providers had longer refresh times.

A few days ago I was grumbling about this yet again and discovered a way to refresh a single data provider! It’s not perfect (nor was it the most obvious workflow for me) but it does work. I had one data provider that was scanning a huge multi-billion row fact table. To supplement this data I needed to run an additional query against an Excel data provider. I had to make several changes to the Excel file in order to help my data match up correctly, and each time I updated the XLS I had to refresh the entire document in order to see the changes… which was annoying and time consuming. Now I have a solution.

Note: Since I’m talking about joining with Excel, clearly I was using the rich client application. However the same technique outlined below works online with multiple universe data providers as well. Continue reading “Yes, Virginia, You Can Refresh One Data Provider At A Time”

There’s an interesting article over at fivethirtyeight.com that discusses various ways that airlines could save money. Like many airline profitability studies this one focuses on things that Southwest Airlines does to remain in the black while other airlines seem to go through bankruptcy every five or ten years. For example, Southwest does not have any in-flight entertainment systems. That saves money in a whole variety of obvious ways (they don’t have to pay for the systems up front, and they don’t have the on-going costs of keeping the systems running) but also in less obvious ways.

Southwest runs about 1.6 million flights every year. At the most basic level, they are in the business of moving cargo (human or otherwise) from point A to point B, therefore one of their primary costs is fuel. The more cargo they carry, the more fuel is required. When I carry my cell phone I hardly know it’s there. Southwest jets have an average of around 140 seats. If I assume 100 of those are filled with passengers with cell phones, the weight becomes a bit more noticeable. Imagine loading up a box of 100 cell phones. 🙂 and then repeat that for 1.6 million flights and the extra cargo weight adds up! In the article they crunched the numbers and determined that Southwest probably pays $1.2 million dollars in extra fuel costs just to carry all those phones. If passengers carry laptops that would add almost $21M in fuel costs. Entertainment systems – if they were present on Southwest – would add almost $40M a year in additional fuel costs. I found it interesting that entertainment systems apparently weighed more than laptops, but then I realized one factor was that planes are not always full. A laptop won’t fly by itself, but an entertainment system might.

Which brings me to what I thought was one of the more interesting propositions made in the article. Airlines that currently offer in-flight entertainment systems could save a ton (pun intended) by eliminating those systems and giving everyone an iPad instead! They could save even more by allowing passengers that bring their own iPad free access to the in-flight media server. Content could be uploaded to each plane while they’re loading passengers. The airline wouldn’t need to provide high-capacity iPads since everything would be viewed from the server, so the 16GB model would be plenty. The cost of a 16GB iPad currently hovers around $400, so they’re not cheap, but given the weight savings (and the fact that iPads could be loaded on an as-needed basis rather than always being on board) the fuel savings would likely more than make up for the added cost.

Using Southwest’s network as a proxy for similar-sized airlines carrying embedded in-flight entertainment systems, we found that fuel costs to carry these systems are approximately $39.7 million per year. When compared with installing embedded systems in the seats, simply handing everyone an iPad when they stepped onboard could save about $32.7 million per year in fuel costs.

Plus I have to imagine if Southwest Airlines called up Apple and ordered 100,000 iPads they would get a better price than $400. 😉

Related Links

• Why Budget Airlines Could Soon Charge You to Use the Bathroom