The Rise and Fall of the OLAP Cube (2020)

https://www.holistics.io/blog/the-rise-and-fall-of-the-olap-cube/

by smartmic • 5 years ago

211 91 5 years ago

mwexler3• 5 years ago

"We model data according to rigorous frameworks like Kimball or Inmon because we must regularly construct OLAP cubes for our analyses."

Actually, no. We model our data this way so it can be used for business decisions. It doesn't take long for any entity of any scale to discover that the logging and eventing done for heartbeat status, debugging, and scaling is just different than what you need to make a variety of business decisions.

You can solve with more events at different scales (button clicks nested in screen views) or pick events or event rolkups that appear to be clean business stages ("completed checkout") but still, your finance team, marketing group, all have different needs.

So, you decide to have some core shared metrics, derived and defined, and make them usable by everyone. Folks agree on the defns, and due to ease and trust, you see more data supporting more decisions.

You discover that some folks are doing 10 table joins to get an answer; it's fast but difficult to extend for new questions. You decide to build a view that solves some of these pains, and refactoring to allow a better time dimension. Your version links with the metrics you created, and the resulting queries shed tons of CTEs while becoming readable to the average user.

And now, you have some ELT pipelines, some event transforms that result in counts and filters that map nicely to your business needs but still allow you to get atomic raws, and you and your teams start to trust in consistent results. Your metrics are mostly clearly summable, and ones that aren't are in table views that precalc the "daily uniques" or other metrics that may need a bit special handling.

You've started modeling your data.

No, we don't need olap cubes. But we do need some type of rigor around analytic data. Otherwise, why go to all the trouble to collect it, count it, and predict from it if it may be wrong with no measure of that uncertainty?

And yeah, Kimball et al are from a world where olap was the answr, but it turns out they solved a broader set of sql problems. So, worth learning the good, toss the dated, and see what good data modeling can do for your analysis and predictions.

mwexler3• 5 years ago

BTW, their followup piece at https://www.holistics.io/blog/olap-is-not-olap-cube/ is a nicer drill down into some of these issues. Also a good read.

tresil• 5 years ago

Could not have written this any better. I’ve been in the data business for over 10 years. A number of technologies have come and gone during that time, but dimensional modeling and solid SQL skills keep delivering remarkable value.

chrstr• 5 years ago

Indeed, it is generally good for analytics to have a conceptual data model that is reasonably efficient both on the technical side (to quickly get answers for queries), and also on the logical side (to quickly translate questions to queries).

This model doesn't need to be OLAP cubes, but it's also not that easy to find something better.

glogla5• 5 years ago

Actually, no. We model our data this way so it can be used for business decisions

Yeah, that would be my disagreement as well.

Sure, some of Kimball might be obsoleted by modern technology. But I don't think the point of Kimball is cubes, and even if it was, what better is there?

I'd be really interested in what else is there, that is more modern and suited to the modern world.

Data Vault? That probably isn't it, most of all because it's all about the "platform to build you Kimball on" but not the actual business modelling. (But the parts about Metrics Vault and Quality Vault and the "service stuff" are really good.)

Anchor Modelling? That one seems, looking by 2021 eyes, like a way to implement columnar storage and schema-on-read in MS SQL Server using store procedures ... which is probably not actually a good idea.

Puppini Bridge aka. Unified Star Schema seems like interesting concept, especially in self-service space. But even it's proponents warn you it doesn't scale performance-wise and also it is kind of incremental change on Kimball. (But the way the bridge table is kinda adjacency matrix of graph of your objects tickles my inner computer scientist fancy)

So really, what else is there?

cedricd• 5 years ago

This isn't widely known, but activity schema[1] is aiming for this space (disclosure - I co-founded the company supporting it).

It's a modeling approach that separates data modeling from data querying -- meaning that once data is modeled, it can answer any number of questions. The base building blocks of the data model can be combined at query time without having to figure out explicit foreign key joins.

[1] https://www.narrator.ai/activity-schema/

impjohn• 5 years ago

Have you looked into the VKG (Virtual Knowledge Graph) approach?

hodgesrm• 5 years ago

No, we don't need olap cubes. But we do need some type of rigor around analytic data. Otherwise, why go to all the trouble to collect it, count it, and predict from it if it may be wrong with no measure of that uncertainty?

Modern data warehouses don't need to build cubes for performance reasons. Low latency data warehouses like ClickHouse or Druid can aggregate directly off source data. The biggest driver for modeling is allowing non-coders to access data and perform their own analyses. I don't see that problem ever going away. Cube modeling with dimensions and measures solves it well.

lou1306• 5 years ago

The author mentions the Codd-OLAP conflict of interest, lamenting that despite what happened "Codd is still regarded as the father of the relational database". But this remark sounds like a non sequitur to me. No matter the scandal he got into in the 90s, he still invented relational algebra back in the 70s, didn't he?

data_ders• 5 years ago

the “scandal”

coined the term Online analytical processing (OLAP) and wrote the "twelve laws of online analytical processing". Controversy erupted, however, after it was discovered that this paper had been sponsored by Arbor Software (subsequently Hyperion, now acquired by Oracle), a conflict of interest that had not been disclosed, and Computerworld withdrew the paper.

https://en.m.wikipedia.org/wiki/Edgar_F._Codd

brazzy• 5 years ago

He published a scientific paper that was to some degree paid advertising, and didn't disclose the payment.

That kind of thing still totally counts as a scandal.

georgewfraser3• 5 years ago

The OLAP cube needs to come back, as an optimization of a data-warehouse-centric workflow. If you are routinely running queries like:

    SELECT dimension, measure
    FROM table
    WHERE filter = ?
    GROUP BY 1

You can save a lot of compute time by creating a materialized view [1] of:

    SELECT dimension, filter, measure
    FROM table
    GROUP BY 1, 2

and the query optimizer will even automatically redirect your query for you! At this point, the main thing we need is for BI tools to take advantage of this capability in the background.

[1] https://docs.snowflake.com/en/user-guide/views-materialized....

mulmen6• 5 years ago

But those queries aren’t equivalent so how is anything saved by materializing the second one?

I believe (I could be wrong!) you edited the second query from

  SELECT dimension, measure
    FROM table
    GROUP BY 1

  SELECT dimension, filter, measure
    FROM table
    GROUP BY 1, 2

This addresses the filtering but how is that any different from the original table? Presumably `table` could have been a finer grain than the filter and dimension but you’d do better to add the rest of the dimensions as well, at which point you’re most of the way to a star schema.

This kind of pre-computed aggregate is typical in data warehousing. But is it really an “OLAP cube”?

In general I agree there is value in the methods of the past and we would be well served to adapt those concepts to our work today.

georgewfraser3• 5 years ago

It’s much smaller than the original table. If you compute lots of these, then voila, you have an OLAP cube.

motogpjimbo2• 5 years ago

He did edit his comment, but unfortunately didn't acknowledge the edit.

ipaddr• 5 years ago

By grouping them you need to include aggregate functions like avg,max,min,sum,count for measure and dimension.

_dark_matter_• 5 years ago

looker does exactly this, though you do have to specify which dimensions to aggregate: https://docs.looker.com/data-modeling/learning-lookml/aggreg...

motogpjimbo2• 5 years ago

Did you mean to write HAVING in your first query? Otherwise your second query is not equivalent to the first, because the WHERE will not be performed prior to the aggregation.

buremba3• 5 years ago

While native materialized view feature is a great start, unfortunately they're not useful in a practical way if you have data lineage. They works like a black-box and they can't guarantee the query performance.

The new generation ELT tools such as dbt partially solve this problem. You can model your data and incrementally update the tables that can be used in your BI tools. Looker's Aggregate Awareness is also a great start but unfortunately it only works for Looker.

We try to solve this problem with metriql as well: https://metriql.com/introduction/aggregates The idea is to define these measures & dimensions once and use it everyone; your BI tools, data science tools, etc.

Disclaimer: I'm the tech lead of the project.

glogla5• 5 years ago

Is that related to lightdash.com somehow?

It seems like a very similar technology and also the webpages for both are almost identical.

willvarfar2• 5 years ago

I think the article is spot on about workflow but off on tech. Under the hood, Cubes will be back.

Snowflake is the cubeless column store.

And now firebolt comes along, promising order of magnitudes improvement. How? Largely “join indexes” and “aggregation indexes” which seem to smell like cubes.

So take the modern column store and slap some cubes in front.

In firebolt this seems to be added by programmers. But I think the future will see olap mpp engines that transparently pick cubes, maintain them, reuse them and discard them all automatically.

dominotw• 5 years ago

How? Largely “join indexes” and “aggregation indexes” which seem to smell like cubes.

Just curious. What stops snowflake from adding these too. Trying to understand what innovation firebolt is doing.

willvarfar2• 5 years ago

Hopefully nothing.

I only understand these systems about as well as you can without actually fighting them in production, so ymmv ;)

There are things that Firebolt are doing regards SSD caching that its easy to see are going to massively help performance but also hinder one nice aspect of Snowflake which is that one Snowflake region can read-only access the storage of another. I'm guessing that this is via direct S3 access, but the moment there is tiered storage, inter-cluster and inter-region access would have to be inter-compute-cluster instead etc.

elthor89• 5 years ago

“Be suspicious of companies that are heavily locked into the OLAP cube workflow. (Learn how to do this here).“

The opposite is true too. Be suspicious of companies trying to lock you into their selfservice of data visualization tool. I have seen many BI tool vendors trying lock their clients into their tool. As result they get unnecessary complex models running.

I find the olap a pretty good mental model. That developers and users can understand.

The sheer amount of handwritten, tailor made etl or elt pipelines is something I like to see automated or replaced with something better.

glogla5• 5 years ago

Yup. At my job we ended up some way too important stuff "modelled in BI tool" in a way that nobody could understand how it actually works, and had to do full rewrite to model it in SQL.

But not all BI tools are alike - something like Superset which internally uses SQL and expects users to do self-service transformation using views is easy to industrialize since you already have the queries. Something like Tableau, not so much.

(If only Superset were more mature)

intrasight4• 5 years ago

I've been doing OLAP for over 20 years, so I can say with authority that OLAP is generally a bag of hurt. The tooling sucks, the languages suck, the costs suck. The name itself harks back to seemingly a time before computers. "Online"? As opposed to paper tablets?

myohmy• 5 years ago

OLAP is legacy hell. I built an MDX query to solve a report problem, but now I can't replace it because my seniors failed to deconstruct it because it is stupid difficult to wrap your head around. So now it sits in limbo until I can find time to do the work myself. Let me just take a look at my calendar and find my next free week... oh, there is none.

papandada3• 5 years ago

I keep feeling like this is room for a better language than MDX... And DAX isn't it either.

goodpoint• 5 years ago

Online as opposed to batch.

intrasight4• 5 years ago

I know. But still, you must agree, a stupid name. The rest of the IT industry would say "real-time vs batch". And OLAP is batch for most implementations.

dredmorbius• 5 years ago

It's far less online contrasted to what, than analytic processing contrasted to transaction processing.

OLAP was a departure from OLTP, online transaction processing, the mainstay of business transactional networks. And of course, both are contrasted to batch processing which occurs later or after the fact, though in practice, much OLAP was specific triggered reports or ad hoc analysis, at least from the world as I saw it.

Where OLTP and OLAP differ is in that OLTP deals with a transaction at a time, typically following a single entity or account through a single transaction or operation. The data loads are small though the transaction counts are high, and there's virtually no summation of information. Database optimisation is built around these needs.

OLAP, in theory, was based around realtime or near-realitime access to transactional data, but the analysis looked across entities (typically accounts, products, divisions, regions, time, etc.), and relied heavily on summarised data (counts, sums, means, medians, min, max, standard deviation, occasionally other metrics). This was typically rationalised by either defining views, or (more often in my experience) by precomputing what were thought to be sufficient and useful summary statistics. (In practice, neither ambition ever seemed to be reliably met, requiring returning to source or disaggregated data.)

From a statistical stadpoint, one of the chief failures of OLAP is that it attempts to substitute summarisation for sampling. In practice, a small sample of a very large dataset is quite frequently orders of magnitude more easily compiled and analysed, with very little loss of information. Even aggregate computations can often be closely estimated by this method.

The article explicitly makes most of these points, FWIW.

random3• 5 years ago

Brings up nostalgic memories from my previous life building distributed analytics systems. We had a fully distributed, scalable, real-time OLAP cube 10 years ago on top of HBase and Storm (or any other realtime processing pipeline). It was less than 30 KLOC and could be used as a library as well as fully distributed system.

There are several OSS solutions but I still weep every time I think about the stupid reasons for which we delayed open-sourcing it. It was such a simple and scalable data model including many concepts that were not common at the time. https://medium.com/hstackdotorg/hbasecon-low-latency-olap-wi...

I still believe there's room for a similar simple approach, likely taking advantage of some of the developments related to Algebird, Calcite, Gandiva, etc.

unklefolk• 5 years ago

I’m not a big fan of OLAP cubes / data warehouses but it should be noted that these aren’t always just about scale and speed. They also serve to provide a single view of the data when the data is held across multiple databases / systems. You’re orders may be in PostgreSQL, your inventory tracked in MySQL etc. So bringing all that data into a single database for analytics can be valuable.

chipsa• 5 years ago

This isn't against data warehouses, but just the cubes. Bringing everything into a single database for analysis is still required. But you extract load then transform, instead of extract transform load.

rossdavidh• 5 years ago

While that's true, it feels to me that your comment made this point a lot more clearly than the article did.

AtlasBarfed• 5 years ago

Which is why hadoop took off?

dang• 5 years ago

One past thread:

The Rise and Fall of the OLAP Cube - https://news.ycombinator.com/item?id=22189178 - Jan 2020 (53 comments)

crazydoggers3• 5 years ago

I always felt the value of an OLAP cube was uncovering questions you may not know to ask. If you’ve got a cube with a good interface, it means more people in your organization can inspect the data looking for trends across dimensions that might not be obvious. As an example in a previous organization, someone just playing with an olap cube once found that certain DUI searches became much more common on Weekend mornings (perhaps obvious in retrospect). That insight allowed better targeting and sales, and made concrete what otherwise is just a guess.

Sometimes just exploring the data allows stuff like that to pop out.

Are there similar interfaces with columnar stores? Or do all the analytics need to be pre compiled? The ability to slice/dice/filter/aggregate your data in perhaps non obvious ways is really the value of business analytics in my opinion.

mulmen6• 5 years ago

I don’t think this benefit is unique to OLAP cubes. This is just generally the reason you have a data warehouse at all. The implementation is up to you but if you model the business process then you will be ready to answer questions that have not been asked.

crazydoggers3• 5 years ago

But are there tools that allow non developers to analyze the data ad-hoc? If there’s a gatekeeper in either direction than it really slows down the process of understanding the data.

Cubes usually allow ad-hoc real-time queries that business users can play around with and explore.

ryanmarsh2• 5 years ago

You can pry the star schema database from my cold dead hands. You can get a lot of work done, without much effort, in multi-dimensional row stores with bare SQL. The ETL's are easier to write than the map reduce jobs I've seen on columnar stores. ETL pipelines get the data in the structure I need before I begin analysis. ELT requires me to know too much about the original structure of the data. Sure, that's useful when you find a novel case, but shouldn't that be folded back into the data warehouse for everyone?

garethrowlands• 5 years ago

Columnar stores speak SQL these days, though they may map-reduce under the hood. ELT vs ETL just moves some transformation into SQL the data normally still ends up in the data warehouse.

mulmen6• 5 years ago

I absolutely love Looker for this reason. It understands foreign keys so if you model everything as stars it just writes SQL for you.

So simple, so powerful. I wish something open source would do this painfully obvious thing.

marcinzm• 5 years ago

Sure, that's useful when you find a novel case, but shouldn't that be folded back into the data warehouse for everyone?

And this isn't possible to do in the data warehouse why exactly? Most every company seems to use DBT so even analysts can write transforms to generate tables and views in the warehouse from other tables. Hell, even Fivetran lets you run DBT or transformations after loading data.

lightbendover• 5 years ago

While I agree about your core point (star schema ETL is great at most DW use cases due to simplicity and breadth of roles supported), I fail to understand how columnar stores are any more difficult to work with. My team routinely joins over trillions of records using standard SQL queries without any issue, all the map-reduce happens behind the scenes. In fact, it has been so successful for us that we have replaced almost all of our historical ETL infrastructure based on Spark with a single columnar store; while infrastructure costs have gone up (generally the price to pay for generalization), we have gained a great deal of human efficiency and DW maintainability.

coldacid2• 5 years ago

If you’re running a query like “give me the average price of all transactions over the past 5 years”, a relational database would have to load all the rows from the previous 5 years even though it merely wants to aggregate the price field; a columnar database would only have to examine one column — the price column.

Wouldn't that be two columns?

Animats• 5 years ago

If the number of transactions is small enough to query quickly, a simple SQL query will give you that. If the number of transactions is very large, querying a random sample of perhaps 1 in 1000 will give you reasonably accurate results. You can calculate the probable error from the sample size.

There are questions which require examining all the data, but often you don't really need to.

citrin_ru• 5 years ago

Wouldn't that be two columns?

Columnar databases can also have indices. If there is an index by date (or time) then you DB will know row range from the index and will read only price column within given row range. If there is no index by data it would be two columns, but it is still much less than a full row with many columns.

glogla5• 5 years ago

Yeah but it's nitpicky :)

Maybe "past 5 years" mean "over everything" and then it might technically be one column.

Or if you're using something like S3 with Parquet for "columnar database" and it is partitioned by date, then the date values are stored in metadata - so you would logically read two columns but physically only read one from storage. Same story for something like Redshift and using date as sortkey.

coldacid2• 5 years ago

Oh, I'm definitely being nitpicky :D

ridaj• 5 years ago

I would say yes it's now possible to do analytics without cubes, except when butting up against two things:

* Really big data (~ PB/day), which makes it impractical to process every analytics query by going back to the source

* User data subject to GDPR-like retention constraints, where you cannot usually hold on to the base data for ad long as you need analytics for

malkia• 5 years ago

At previous job, in an an human ads evaluation team, we had a big row table that got turned into column oriented one every couple of hours. I think it got close to million columns (expanded from leaf protobuf fields). We had some black/white listing as the schema grew too big (~20GB? just the schema, and the limits were few gigs). That was years ago, so probably changed a lot.

But the use case was for various analysts (statisticians, linguists, etc., or any internal users other teams of the table). As their quires might use any column they like - there was probably a way to track down which columns got used over a time, but since it was used for launching new things, you would not know - better be safe than sorry I guess.

Anyway, I'm still fascinated that this terabyte thing got converted using 20k machines for few hours and worked! (it wasn't streaming, it was converting over and over from the begining, this might've changed too!)

RantyDave2• 5 years ago

I'm not sure we can call NoSQL "a fad that has gone away".

cwbrandsma• 5 years ago

OLAP is not SQL or NoSQL in any way, shape, or form. Completely separate concept and completely separate technologies.

RantyDave2• 5 years ago

Indeed. Yet TFA insists on referring to NoSQL as "a fad" in its opening.

mwexler3• 5 years ago

I think the original author was referring to the wave of popularity a few years ago when Mongo / redis / cassandra/ etc were getting tons of press, "SQL is dead" was the going phrase, and document pre-joins and intense searching of object/documents via map-reduce (elastic, et al) was the metaphor du jour.

Like every other hype, it settled down to a realistic set of use cases that NoSQL databases rock at, and the world recognized that other use cases weren't their thing, and you stopped hearing about the death of SQL, the expectation that you'd never need a SQL DB or join again, etc.

So, not a fad, but past the hype-beyond-belief stage and now into the "gets stuff done" stage.

didibus• 5 years ago

One thing about columnar store that annoys me, is that when you do want to retrieve the records for something you've found it's really slow. I wish there was something of a hybrid.

scrollbar• 5 years ago

I’m pretty sure Snowflake is hybrid like you’re describing. It certainly performs like it. Check out their original white paper for the architecture details

AdamProut• 5 years ago

We have something similar to that at Singlestore (formerly memsql). We support secondary keys and compression schemes that are incremental so you can decompress a few rows out of our columnstore without needing to decompressing the data for other rows stored near by like a traditional columnstore (Redshift, snowflake, etc). We call this subsegment access [1].

[1] https://www.singlestore.com/blog/memsql-singlestore-then-the...

debarshri• 5 years ago

I am not the subject matter expert but have spent first few year of my career doing data warehousing. I think OLAP cubes are falling out of fashion because traditional BI and reporting in general is falling out of fashion.

With self-service BI tools like looker and other tools, Business folks are running more adhoc queries than traditionally piped data through ETL processes.

gengstrand• 5 years ago

My first exposure to OLAP was on a team developing a front end to Essbase that semi-automated report generation after reloading the cube. Since then, I have worked with various OLAP technologies from Microsoft to Mondrian.

I agree with Mr. Chin who is the author of this blog. OLAP cubes can scale up but not out which limits their potential size so they can't really accommodate big data scenarios. My previous company used Apache Druid instead. Druid can do a lot of what OLAP did but using a distributed approach. Although Druid does not provide tight integration with spreadsheets, it does provide near real-time ingestion, on-the-fly rollup, and first class time series analysis.

iblaine2• 5 years ago

I'm a bit opinionated on data modelling, so a couple points.

1. "OLAP Cubes" arguably belong to Microsoft and refer to SQL Server cubes that require MDX queries. It's a solution given to us by Microsoft that comes with well understood features. "OLAP" itself is a general term used to describe any database used for analytical processing, so OLAP has a wide range of uses.

2. OLAP Cubes (as defined above) started to decrease in population in 2015 (I'd argue).

3. Any solution to fixing "OLAP" that comes from a commercial vendor is suspicious. As painful as Kimbal & Inmon are, they are ideas that don't require vendor lock in.

4. At my current company, we recently wrapped up a process where we set out to define the future of our DW. Our DW was encumbered by various design patterns and contractors that came and went over the past decade. We analyzed the various tables & ETLs to come up with a clear set of principles. The end result looks eerily close to Kimball but with our own naming conventions. Our new set of principles clearly solve our business needs. Point being you don't need Kimball, Inmon, Looker, etc to solve data modelling problems.

5. Columnar databases are so pervasive these days that you should no longer need to worry about a lack of options when choosing the right storage engine for your OLAP db.

6. More and more data marts are being defined by BI Teams w/little modeling/CS skills. To that end, I think it's important to educate your BI teams as a means to minimize the fallout, and be accommodating in your DW as means to work efficiently with your BI teams. This is to say settle on a set of data modelling principles that work for you, but may not work for someone else.

papandada3• 5 years ago

I'm struggling to figure out what you mean by number 4. Isn't the result of that exercise proof that the Kimball approach is a worthwhile one?

iblaine2• 5 years ago

We evolved our guidelines for data modeling to something that closely resembles Kimball, but it's not Kimball.

Some of our ETLs were created by people that were not familiar with Kimball. We went about defining new data modeling guidelines, taking the good parts out of this work, eliminating the bad parts, and many of the good parts share a lot in common with Kimball.

darepublic• 5 years ago

I remember on a dark gloomy day going to the bookstore and picking up a book about SQL server report server. Some of the toughest time in my life.

drc500free• 5 years ago

I just spent the first 6 months on a new reporting product ripping out SSRS because the engineers couldn’t get performance within 2 orders of magnitude of a native implementation on top of SQL Server. :(

eyeball• 5 years ago

One big table combining all of your facts and dimensions for me.

Less compute time, less time for business users since they don’t need to figure out star schema joins. Storage is cheap.

slt2021• 5 years ago

OLAP Cube is not dead, as long as you use some form of:

1. GROUP BY multiple fields (your dimensions), or

2. partition/shuffle/reduceByKey

and materializing/caching the result of this query for later reuse, you are rolling your own OLAP Cube with whatever data processing ecosystem you have on hands.

The technology itself has clear use cases and incredible business value on a daily basis, only implementation details differ depending on surroinding ecosystem

ineedasername• 5 years ago

I never liked OLAP cubes because they discouraged ad-hoc data exploration since if there wasn't already a cube that modeled the data a certain way you couldn't do it. You had to develop it, or hope a DBA had time to develop it sometime in the next two months. They were fine if you already knew the questions you needed answered, but they probably contributed to some companies focussing on the wrong things because they couldn't easily explore data for new insights.

I much prefer the current setup I work under: We have an ODS with a whole bunch of denormalized views for the most commonly needed data built on incremental updates to a clone of the production database. If one of the views doesn't have the data required or it's not structured as required I can simply bring one of the cloned prod tables into my SQL query.

If I find myself repeating that process for the same data often, I create another view for it, but I always have all of the data needed for any analysis or exploration.

If I need up-to-the-second data I can write the boilerplate using ODS and cloned prod, then join to only live prod data required.

Things like the added complexity of a constructing a pivot table in SQL are a non issue. Report writing software handles that and with a lot more flexibility to the end user for interaction.

speby• 5 years ago

This is why sometimes you end up with BOTH a traditional ETL data warehouse, for common, structured reporting and analytics and also an unstructured data lake(s) that pull raw data in from many sources to allow for ad-hoc exploration.

But like others have said here, eventually you might find a novel pattern or something worth tracking on a regular, go-forward basis, and will want it to built into the data warehouse, for all of the various benefits a data warehouse provides for everything else it does so well.

blaydator• 5 years ago

What about the MDX language, is it still relevant today ? What are you using as OLAP tools ?

ashes2• 5 years ago

We still use MDX. Did some testing with DAX, but I don't believe its step in the right direction. As for tools, we mostly use in-house tools build either on Mondrian or connecting to MS Analysis Services trough XMLA - depending on the use case.

ashes2• 5 years ago

Related question; does anyone use JVM-based OLAP that is not based on Mondrian (https://github.com/pentaho/mondrian) but still supports MDX queries? We've been looking for alternatives for a while.

systems• 5 years ago

i think the article misses a big point parametric queries

cubes whether tabular (columnar database) or multidimensional provide a language mdx or dax, that basically allows parametric measures (or queries)

which is essential in data analysis

sql for now, doesnt allow parametric functions or queries in the same way dax or mdx does

The Rise and Fall of the OLAP Cube (2020)

Related Stories