An appropriately unhinged deep dive into Kimball facts & dims

This still makes homie look like he’s about to wreck your entire life

Who among us hasn’t adopted dbt and made a handful of tables that we really could have avoided? It’s a tale as old as time. You get this shiny, self-contained, nifty lil’ tool and you start making slick little views in your data warehouse to version control that pesky report that customer success won’t stop bothering you about. Boom! You’re solving business problems left and right.

But, oh god, then customer success starts making report requests along a similar vein once a week! And now your data engineers are also building stuff in dbt! So are the data scientists! And dear god, the same view has now been made a dozen times with slight variations and nobody is completely sure which variation is the “right” one.

The answer is to curl up in an existential hole and be morose over the fact that no one can actually say what the truth is and economies are just people exchanging pieces of paper over and over again for no real reason, right?

Well, you could do that. I’ve been there. Or, you could start thinking about your data in terms of facts and dimensions, instead of in terms of onesie-twosie reports that you keep getting pestered about all the time.

What are ya, some kinda business process?

Business processes are the operational activities performed by your organization. This includes taking an order, processing an insurance claim, registering students for class, or snapshotting accounts each month.

• Kimball’s Data Warehouse Toolkit, ch. 2

(mildly paraphrased because he writes so many run-ons)

A fact table is meant to record the results of a single business process. A dimension table details (generally) non-numerical information about the business process, or descriptive attributes. Kimball, quite adorably if you ask me, calls dimensions “the soul of the data warehouse”.

Thinking about data modeling in terms of facts and dims was huge for me in developing a more re-usable approach to modeling. To remind myself what everything means, I pretend like I’m an Instagram baddie with a clothing line I sell on RedBubble.

The people aren’t going to influence themselves! So I launch a sick-ass line of T-shirts with random lines from Doja Cat songs. I want to make mad coin from her songs, and I don’t actually know anything about copyright law. So I put up 3 T-shirts for sale.

My only business process is customers placing orders, since I don’t have to handle fulfillment on either end of this shop. RedBubble handles it!1

The facts I’ll be concerned with from my sales data will be:

• Sale number
• Quantity ordered
• Sale amount (probably divided into gross & net).

The dims I’ll be concerned with from my sales data will probably include:

• Date of sale
• Which DojaShirt was ordered

It’s pretty simple when you lay it out like that, and this probably feels like a pat example. But is your business really that much more complicated? Sure, every business will have other processes of varying complexity that also merit data analysis. But the most important, revenue-generating processes, will be some variant on customer give us money some way.

Identifying and understanding my company’s core business processes was pivotal in helping me see the forest for the trees as I got better at handling our data.

You’re either a fact or a dim. The girls that get it, get it.

After you get a good handle on what your company’s core business processes are, the next level is having a good general sense of what the associated facts and dims are for each business process. Generally a good divider between those two things are that numbers that you can do math on tend to be facts, and words tend to be dims. It’s not a hard and fast rule, but it’s a good starting framework.

When you’re being asked to run a quick little analysis, ask yourself which pieces of it are measuring (how many Doja T shirts did I sell?) and which pieces of it are descriptive (which Doja T shirts did I sell? What day of the week?)? Filter your business stakeholder request through these lenses:

• Which business process is this about?
• Is this request about measuring stuff or describing stuff?

Beginning to filter all my stakeholder requests through these lenses helped me start to see the patterns therein. It’s probably a better idea to seek out those patterns in an extensive interview process before you do any data work ever, but who has the time or luxury to do that? Could not be me.

Even if you can’t have a beautiful, luxurious design period before you model anything, you can still benefit from thinking through requests in terms of business processes, facts, and dims. It’s never a bad thing to reduce repetition in your work, even if it’s repetition with slight variances here and there.

You should write the dbt model in terms of the most common business processes that customer success keeps bothering you about. You can write it flexibly enough that CS can use it to get the information they need in the BI layer, preventing you from having to rehash this same model over and over again. The “how” implicit in that statement will not be included in this post. I’ll write about it at some point, but until then, you know which of data’s bad boys has a book out to help you understand how to do this.

I asked Stable Diffusion to give me a picture of Ralph Kimball in a T shirt that says “work harder, not smarter” on it. It….it tried. He’s in a warehouse because….data warehouses, I guess?

Look, you should read the Data Warehouse Toolkit. It’s not that hard to read and it’s super helpful. In any case, subscribe for more tomfoolery next week.

Subscribe now

1

I make no guarantees about what RedBubble actually does. I’m just name-dropping.