The Rise and Fall of the OLAP Cube
holistics.io
The Rise and Fall of the OLAP Cube
1–10 of 54 posts
Re: The Rise and Fall of the OLAP Cube
#2Interesting perspective. What do you say to someone who's been using OLAP cube for their entire BI implementation? What would be the transition plan to adopting MPP databases?
Re: The Rise and Fall of the OLAP Cube
#3After maintaining an OLAP cube system for some years, I'm not that sure after reading the article.
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.
Re: The Rise and Fall of the OLAP Cube
#4I'm conflicted; its a nice write-up, and probably generally true right now, for most stuff.
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!
Re: The Rise and Fall of the OLAP Cube
#5Interesting perspective. What do you say to someone who's been using OLAP cube for their entire BI implementation? What would be the transition plan to adopting MPP databases?
Background: Someone who has worked in the old Microsoft Analysis Services stack 8 years ago and has moved to more columnar data formats like parquet or row based MPP's.
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.
Re: The Rise and Fall of the OLAP Cube
#6I used to admin OLAP infrastructure for a large Fortune 100. I think the article makes a lot of great points, but ultimately there are many paths to the same result. I'm not entirely sure what that group is up to these days but using column stores is a big part of it I'm sure, but I think ultimately deciding between precalculated vs. dynamically calculated pivots is the big choice.
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.
Re: The Rise and Fall of the OLAP Cube
#7How does something like Tableau fit in? I know of people using Tableau with a Postgres connector, but I am not sure if that allows you the same kind of performance as you'd get with OLAP or even a columnar DB.
Re: The Rise and Fall of the OLAP Cube
#8The author don't know today‘s OLAP。
Look Tabular Modeling in SQL Server Analysis Services（Power BI） in-memory column-oriented analytics engine.
Re: The Rise and Fall of the OLAP Cube
#9The columnar database engines are powerful enough to answer the ad-hoc questions so you often don't need to materialize the summary data somewhere else or use BI tools such as Tableau that fetch the data into their server and let you run queries on their platform.
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
Re: The Rise and Fall of the OLAP Cube
#10How does something like Tableau fit in? I know of people using Tableau with a Postgres connector, but I am not sure if that allows you the same kind of performance as you'd get with OLAP or even a columnar DB.
I don't know about Tableau, but Microsoft Power BI does use a columnar engine - I believe its the same VertiPaq/xVelocity component used in Excel Power Pivot and SSAS Tabular Models.