Instead, the idea is to make the table have as many dimensions (things we group by) and measures (pre-aggregated numbers) as we might want to use in our reports: product_category But, let’s keep going with our OLAP cube strategy. Perfect! Now our query is really quick to run:īy this point, in 2021, we’re probably saying “oh crap, building this table was a mistake, I should have just given Joel the sales table and done the aggregates in the BI tool”. The solution? Let’s pre-aggregate the data into a table! Sure you lose the ability to interrogate individual records, but it will be so much faster! (Sure we lose all the flexibility of querying our raw data, but back in 2001, we needed to do something like this) product_category columnar storage - this is a great primer)

(There’s a lot more nuance to understand why modern data warehouses can handle this query, we might cover it in another post, but it has to do with row vs. Rewind 10-20 years though, and this kind of query was really expensive¹ to run, especially when we have tens of thousands of sales each year. Where date_trunc( 'year', sold_at) = 2021 group by 1, 2 Code language: SQL (Structured Query Language) ( sql ) When I went to source ideas for a glossary of these terms, I quickly learned that it’s not just me who struggled to understand this term.Ĭount( distinct customer_id) as number_of_customers

Let us know if you find this useful, if there’s a concept you’d like explained, or just want to say hi! Oh and one last thing before we dive in - applications for our fall cohort are now open.įor the last few years, every time someone mentioned an OLAP cube, I nodded a little, quickly checked out the Wikipedia article, got to the section about the “Mathematical definition” and quickly tapped out - “better to just remain blissfully ignorant”, I decided. We’re going to do a series of posts on some of these concepts, especially for those that work with a modern data stack. I’ve been working with data for six years, and always in the context of a “Modern Data Stack” - the first data stack I used included Redshift, Fivetran and Looker! In contrast, many data modeling concepts were coined in an era when analysts used on-prem databases like Oracle and IBM.Īs I got further into my career, I came across more terminology that didn’t make sense to me, and I was never sure whether it was because the concept was irrelevant for today’s tools, or whether it was a valuable concept that I just hadn’t wrapped my head around yet.

Online analytical processing cube