To that I say: (╯°□°)╯︵ ┻━┻Īs far as I can tell, popular BI tools at the time had built-in features to view data this way, so you didn’t actually create one spreadsheet tab per value along the z-axis - I’ve just used Excel’s Pivot Tables here since most people are familiar with them, and it’s relatively easy to visualize the z-axis through different spreadsheet tabs. Most of the time when someone is talking about “building a cube” they just mean building the underlying table that powers this kind of analysis.īecause many datasets will have more than three dimensions (things to group by), sometimes they will be called OLAP hypercubes. So the OLAP cube gets its name because it can be analyzed in this “cube-like” shape. We can kind of see a “square” here (read: rectangle): Each cell is populated with (the sum of) total_sales_amount. Here, we have state along the x-axis, and product_category along the y-axis, with a filter for year = 2021. ^ yup, also an OLAP cube, and one we’ll forever be updating as more requests come in!īut I know what you’re thinking, “I see no cubes, this is just a two dimensional table, but the Wikipedia article has lots of illustrations of cubes…” So where does this “cube” term come from?īack in 2001, our pre-aggregated table would be loaded into a world-class BI tool like Excel, and analyzed through Pivot Tables. 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.
Olap cube excel example code#
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.
Olap cube excel example series#
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.
0 kommentar(er)
