Guest Post! Guest Post! Today, Curt Budd (hero #1) has a really nice approach to discuss based on work that Wilson Po (hero #2) did for a customer. Heroes all up in this place. BTW I promised not to make fun of Curts deep, abiding love for cubes. And I won’t. Although it is hard not to do so.
The post:
I’ll never be known as an ‘early adopter’ oftechnology. I actually had an 8-track player as a kid. I just sentmy idle VCR to the e-recycling place about a year ago. I
still listen to the real radio more than I use Spotify or Pandora. And so
it has been with my adoption of new business technology. I’ve used old,
outdated technology long past its prime. I’ll spare you the
details. But, I’ll wager that there aren’t that many people around who
can actually claim that they’ve loaded their data onto a computer via a
physical card reader. The OLAP model has been my personal
albatross. It’s like old luggage: you just never get rid of
it. The stuff still works. It (sort of) serves your needs. If
you’re still operating in the paradigm that there’s a swarm of developers
behind every end user, then OLAP is your friend.
Time has moved on since I bought my first BETAMAX tape
player and since my I built my first OLAP cube. (Does anyone remember the
Express database from IRI?) The relational model has withstood the test
of time. Technological innovations have enabled that model to deliver the
speed and analytical power once reserved to the OLAP domain. End users
today ‘get’ data. Modern tools know how to leverage the relational model
for speed. We can argue all day long about whether or not I’m
right. But I am keenly interested in dropping my OLAP knowledge into the
technology recycling bin right next to my WalkMan and my CB Radio.
Enter Tableau 9.0’s new Level of Detail (LOD)
calculations. They help take away one of the things that OLAP is
admittedly good at doing: Dealing with semi-additive measures.
Think about something like headcount in your department. If you have 10
people in January, 15 people in February, and 20 people in March, then you
can’t say that you have 45 people in Q1! You can’t add heads across
time. The same thing is true with account balances in a bank (mortgage
scandal of 2008 notwithstanding), or with inventory levels in a
warehouse.
This is a tough nut to crack with a flat or a relational
database. You could deal with it in Tableau before v9. But, it
wasn’t very elegant. Our new LOD calculations really help. Here’s
how.
Suppose we have data that represents inventory over time for
a few of products stored in three warehouses. A snapshot of this data
might look like this. Each row represents the quantity of a product
on-hand at a warehouse on a date:

When you look at this for a product across time, you might
have a view like the following. Here someone can see that inventories are
falling dangerously low at a few points – introducing the risk that we might
not have the product available on the shelves when a customer comes looking for
it:

But, what happens if you try to look at inventory levels at
the end of each quarter? You get this:

Clearly this isn’t true. Just because we had 10 units
in January, 9 units in February, and 8 units in March doesn’t mean that we have
27 units in Q1.
What we really want is to understand the inventory levels on
the last day of Q1, the last day of Q2, and so on. Like this:

How do you do this in OLAP? Back in the
dark ages before we trusted business people with their own data, we’d build a
nice OLAP cube that protected people from making this bush-league error.
The guardians of our data would hide behind a curtain and produce an OLAP cube
with these fancy things called ‘semi-additive measures’.
That’s awesome. I love it. I get my answers and
someone has guaranteed that the answers are right. But, it took several
weeks to get on that OLAP guy’s schedule for the last project, and I need some
answers in a hurry. I need to get this done TODAY, and I know that our
OLAP data guardians are busy. I guess I’m stuffed, right?
WRONG.
How do you do this in Tableau? It turns
out that this is pretty easily done in Tableau with those new v9 Level of
Detail calculations. Here’s what you need to do.
1.
Figure out what the last date is in any
given period. Remember, we’re dealing with a ‘snapshot’ table that contains
inventory values at specific points in time. If you want to know
January’s inventory…take January 31st. If you want to know
Q1’s inventory….take March 31st. In my case, I
wanted this to be a little bit dynamic. I wanted to enable selection of
either a quarterly or an annual view. So, I started with a parameter:

So, the user can select either ‘Quarter’ or ‘Year’.
Great.
Now the fun stuff: Figure out what the last day of the
period is for any given ‘Quarter’ or ‘Year’:

This little LOD-calc gem is the crux of the solution.
This sucker will make your OLAP guy’s head spin. He’ll start looking for
a job as a curator at the Museum of Arcane and Forgotten Databases. All
we’re doing is asking the database a very simple question: “Gimme the
last date for any given year or quarter, please.”
Next, I need a measure that dynamically adjusts based on the
last day within any period:

Finally, I need to make sure that I’m dynamically throwing
out the right values on the columns shelf. So, I’ve got a calculated
field for that, too…..

That’s it. No crazy table calculations. No need
to bring EVERY date into Tableau and then use LAST()=0 to find the latest
date. It took me about 15 minutes to conceive of and implement this –
faster than I could’ve asked for a new semi-additive measure to be defined and
implemented in one of those OLAP cubes.
I’m one step closer to using Tableau for everything that I
used to expect from OLAP. Maybe now I can start shedding some of that old
luggage from my attic, too.
Curt Budd (Lover of Cubes)