The Rise and Fall of the OLAP Cube
holistics.io
holistics.io
Whatever system you have can do that, but the real work IMHO is understanding the org enough to cover the 80/20 of what people will want to see. Ideally you want to get to a higher level of abstraction such that you continuously codify your method of analysis or pivots to traditional tables if possible in order for maximum repeatability.
Sometimes I wonder if graphs or trees really make more sense though and OLAP being a tree of sorts is just a symptom of the RDBMS ubiquity, but this is just a meandering notion perhaps.
Compressed column-stores hurt OLAP, because update throughput (the “on-line” in OLAP) is relatively bad. Uncompressed / array stores are quite good.
The quantity and complexity of SQL necessary for a non-trivial OLAP view is daunting: time-series views (mix YTD and current-period calcs for transactional and balance accounts, get the ratio measures calculated, and handle the joins necessary for calculating and aggregating from the many (easily dozens) of fact tables that have different dimensionality and granularity and need to be joined at their finest detail. The user will want to see a set of metrics for a pair of orgsnizational units, and also as a percentage difference between the two org units. SQL does not have inter-row calculations, so quite a bit of work goes into re-shaping the SQL cursor’s results to something the user wants to see.
All that query generation and result transformation is part of the value-add of the “cube” server.
So the OLAP cube as a logical construct is definitely not fallen. Just the bad ones. They’re less flexible than SQL but provide way more productivity within the query space they’re built for.
But why do you want high update throughout when you're doing mostly reads? Every definition I read about OLAP says this is one of the fundamental differences, or am I misunderstanding something?
Ya just gotta push back, and hep people understand what the trade offs are for real-time. Most people don’t need it.
However, I still live with databases big enough to still need cubes, although these cubes can afford to be less refined these days. Saying 'bigtable can do a regex on 30M rows per second' isn't saying it can't be done cheaper and quicker without paying google etc, if you just have some cubes.
And I think its going to track the normal sine wave: over time, data sets get bigger, and we keep oscillating between needing to cube and being able to have the reporting tool 'cube on the fly' behind the scenes.
I think there's a general move not mentioned in the article as data-lakes become faster, and then data outstrips them, and so on too.
The strength will be tooling that transparently cubes-on-demand. I wish there were efficient statistics and CDC that tracked metadata so tools can say 'this mysql table has been written to since I last snapshotted something', and, even better, 'this materialized view that I have in this database is now out of date because of writes that affect the expression it is used from on that other database over there' etc. Basic classic data-sources can do a lot of new things to make downstream tools able to cache better.
I have a slight problem with the terminology in the middle of the article, as I'm so far down the rabbit-hole that I think of cubes _as_ databases; I suffer cognitive dissonance when I read about shifts from cubes to databases etc. To me, a cube is just a fancy term for a table/view for a particular use-case.
One tool that I'm terribly excited about these days is presto. https://prestosql.io/ allows you to take a constellation of different normal databases and query them as though they were one big database. And you can just keep on adding data-sources. Awesome!
Would you view presto in its current state as a replacement for vanilla Postgres with FDW for standard data analysis queries? I don't fully understand the Postgres/Presto relationship.
In a way, presto is like a bunch of FDWs on steroids, and a query planner that has above average cost model for hive etc.
There are plenty of things that presto isn’t, such as a good replacement for Postgres in classic oltp workloads.
ELT solutions such as Airflow and DBT let you materialize the data on your database with (incremental) materialized views similar to the way how OLAP Cubes work but inside your database and only using SQL. That way, you won't get stuck to vendor-lock issues (looking at you, Tableau and Looker), instead manage the ELT workflow easily using these open-source tools.
These tools target the analysts/data engineers, not the business users though. Your data team needs to model your data, manage the ETL workflow and adopt a BI tool for you. When you want to get a new measure into a summary table, you need to contact the analyst in your company and make him/her change the data model. As someone who is working in this industry, I can say that we still have a way but the BI workflows will be much more efficient in a few years thanks to the columnar databases.
Shameless plug: We're also working for a data platform, you model your data (dimensions, measures, relations, etc.) and build up ad-hoc analytics interfaces for the business users. If the business user wants to optimize a specific set of queries (OLAP cubes), they simply select the dimension/measure pairs and the system automatically creates a DBT model that creates a summary table in your database similar to OLAP cubes thanks to the GROUPING SETS feature in ANSI SQL. Here are some of the public models if you're interested: https://github.com/rakam-io/recipes
The nice thing of an OLAP cube is the UI and how business users can easily drag and drop items to explore data (standard reports are best created automatically and don't need an OLAP layout/setup).
If the UI (Tableau, Excel Power Pivot) is the same, then yes, OLAP cubes are a thing of the past. Otherwise not.
It's true that _often_ OLAP cubes are not needed. That's simply because the amount of data and the latency requirements are _often_ not too demanding.
Also, materialized views don't solve the major issue with OLAP cubes: the need of maintaining data pipelines.
I wonder if a solution to this problem could come from a different way of caching result sets: new queries that would produce a subset of a previously cached result could be run against the cached result itself. Of course this opens up a new set of problems, cache invalidation etc..
> ...Amazon, Airbnb, Uber and Google have rejected the data cube...
Airbnb uses Druid which is essentially an OLAP cube.
> BigQuery, for instance, doesn’t allow you to update data at all
It's not like that anymore since several years.
Apparently the article's been updated to reflect that
An example does not an argument make.
The kind of relational database you're familiar with is probably OLTP (Online Transactional Processing).
I think the OLAP term has been around a long time, some OLAP tasks of the past are probably not so huge today, I wonder if the shrunked-by-time tasks are still called OLAP or if the smaller ones are implemented differently.
OLAP's strength is that the platforms that implement it can precompute aggregations across all of your data and let you quickly answer questions that you might not have known you had.
All these reports can be kept up to date in a live fashion by the OLAP service as new relevant data comes in, much like a materialized view.
Many accounting systems support OLAP based queries in order for the accounting department to design reports and export them to excel.
If you want an alternative take on where OLAP is today and what it is capable of, I usually recommend this article: https://kyligence.io/blog/olap-analytics-is-dead-really/
At this point you might say, "oh, OLAP cubes refer to an abstraction, it can be implemented using columnar stores!" — and I would point you to 40 years worth of academic research that stretches back to the early 80s. The OLAP cube or data cube refers to a specific type of data structure. It just so happens that vendors like to use the term 'OLAP cube' even when they are using a columnar engine under the hood, because it sells well.
OLAP = A category of databases meant for analyzing data. These are eventually consistent db's, and not OLTP db's. OLAP db's include Redshift, Teradata, Snowflake, BigQuery, and others. Generally what makes a database an MPP database is partitioning compute and storage. Generally what differentiates one MPP db from another is whether or not data and compute are colocated.
OLAP Cubes = A feature built into SQL Server, that includes has its own dialect of SQL called MDX. OLAP Cubes are decreasing in popularity because you can achieve the same results through other means and less effort.
I think this confusion exists because too many vendors conflate the two terms. They say OLAP when they mean OLAP cube. BigQuery and Redshift, however, do not: they are very clear that their dbs are designed for OLAP workloads but are not cubes.
Dynamic query services in the cloud basically charge by processed data volume, like Google BigQuery and Amazon Redshift/Athena. For small and medium dataset, this works well. But for big data close to or above billions of rows, the cost will make you reconsider.
In the recent Apache Kylin Meetup in Berlin, OLX Group shared their comparison between OLAP cube and dynamic query in real case. Given 0.1 billion rows, cube technology (Apache Kylin and SSAS) prevails over MPP+Columnar (Redshift) easily. Especially Apache Kylin is 3.8x faster and 4.4x cheaper than Redshift for their business. (https://www.slideshare.net/TylerWishnoff/apache-kylin-meetup...)
For me, a mix of precalculation (80%) and dynamic calculation (20%) should hit the sweet point between cost effectiveness and query flexibility.
While this article accurately captures the issues with traditional OLAP Cubes, it failed to recognize the latest development in this domain.
Projects like Apache Kylin, and its commercial version Kyligence, leverage modern computer architectures such as columnar storage, distributed processing, and AI optimization to build cubes over 100s of billions rows of data that covers 100s of dimensions. The performance result is unprecedented in either traditional OLAP cubes or today's MPP data warehouses. That's why the world's largest banks, retailers, insurance companies, and manufactures are turning to Kylin/Kyligence for the most challenging analytical problems.
Not to mention the rich semantic layer that modern OLAP cube technology provides, which greatly simplifies analytics architecture in the enterprises.
And, comparing columnar stores to OLAP cubes is like comparing apples to oranges. The former is a storage format and the latter is an analytical pattern. Modern OLAP cube technology like Kylin/Kyligence stores cubes in columnar stores anyway.
This is mistaken. I went back to read most of the academic literature on OLAP cubes while working on this piece (which, unlike vendor marketing, is used with consistency since the early 80s). OLAP cubes or data cubes refer specifically to the data structure that grew out of nested arrays. An OLAP cube may be materialized from a column store, but a column store isn't an OLAP cube.
Relevant sources are included at the bottom of the piece.
https://philip.greenspun.com/wtr/data-warehousing.html https://philip.greenspun.com/sql/
Although technically obsolete (as in talking about 90s database systems that have bitten the dust since then), that's a minor defect. He spends most effort on teaching timeless principles.
Transition plan I would say is find the dataset that is exploding in size or complexity and start your POC there. I did customer service datasets on OLAP so it only grew at a pretty small scale and the data model didn't change that much. So OLAP was fine except for the fact that nobody else knew how to maintain it.
The main growing pain is find what your front end developer flow will be. It will be the same governance as the OLAP but more democratized so be ready to make your back end more front end. For MSAS it was excel but more modern systems are also more wide open. The article suggests just SQL but that can get out of control. How do you reduce reinventing the wheel etc? How do you prevent a lineage mess of derivative on top of derivative if you give users write access. Etc. IMO Tableau is a great product that allows the OLAP like exploration but can use SQL as an input. Just make sure people get the sql behind under some kind of source control and governance.
From the data model perspective it I think the main difference is make the tables wider and "pre join" in your immutable dimensions with higher carnality (ie customer). Just be careful of highly mutable data and keep those in separate tables because it is very painful to rewrite columnar data. Ie if you partition by date to update a single record you rewrite the entire date.
(About governance) I mean more passive governance not gatekeeping. Pretend each end user and dataset costs you money. How do you track them passively with some thin yet easily trackable logging? Business Unit and unique Job are bare minimums.
Currently the BI team doesn't do much dimensional modelling as far as I see. Every thing is taken from Kafka and dumped into some wide tables with all columns that we the analysts need. Actually there is no data modelling at all.
In the overwhelming bulk of cases firms have a column store that they generate cubes from. OLAP is run against the cubes. Some put warehousing in between, though that changes little. Cubes are fundamentally a form of caching because historically it was prohibitive to do large-scale aggregations in real-time. With massive memory servers, and more importantly flash storage that improves aggregate performance at the enterprise scale by many magnitudes -- a million times faster analysis and aggregation is entirely possible -- that historic caching step becomes a hindrance and maintenance/timeliness issue. So it's discarded.
That's all 100% true. It has happened in many orgs.
Not all, of course. But as a general trend.
The proof of this? Go to any serious columnar database provider and search for the words 'OLAP cube'. You will find that they are careful to say 'OLAP workload', but not 'OLAP cube' — because in the strict definition of the term, an OLAP cube or data cube is an entirely different architecture.
Relevant sources are included at the bottom of the piece.
endorphone's comment has it right.
There's also a proprietary relational query language shared among Power BI, Power Pivot, and SSAS Tabular, called DAX.
Tableau has a proprietary extract engine called Hyper that uses a sort of columnar storage. Then you can use the extract in dashboards.
I found this passage confusing. He is regarded as such because of his work on the relational algebra, and the shady OLAP backstory is unrelated to that.