Hacker News Reader

Comment by beefield | Hacker News Reader

Sorry, could someone ELI5 what is OLAP? And while you are there, what is Tabular Model? As background,I have worked with SQL and relational databases, and occasionally keep on hearing these, but nobody ever explained to me what these are and why I should be interested. So far I have just shrugged and thought that I guess my workloads/datamodels/whatnot just do not need these fancy things, but always I see them, there is someone nagging at the back of my head that maybe you should have a look...

View on HN

OLAP: Online Analytical Processing. Cranking through large amounts of data with a focus on aggregations like sums, averages, medians, etc. Measures (numbers) are defined by dimensions (attributes with usually discrete domains). Aggregations are frequently precomputed on many (or all) dimension axes so that they are immediately available. Models can be built from something as simple as a wide CSV file or as complicated as a snowflake schema.

The kind of relational database you're familiar with is probably OLTP (Online Transactional Processing).

What are typical data sizes for this?

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.

It's been about 15 years since I did any OLAP work, so "big" back then was a source database measured in gigabytes with queries that took minutes to run even with a lot of optimization.

So, when my aggregate queries/views are too slow even after indexing and tuning, then I should start to google what OLAP is? My go-to tool for this has been materialized views (or in some cases simply a new table that is refreshed every now and then). What would be the cases when OLAP is better/worse than materialized view? (based on the main article, it sounds like pretty much no other advantage for OLAP than smaller resource requirements)

If your needs are well-defined in advance, you are fine with a materialized view. What I mean by "needs" is, "What questions are you trying to answer?"

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.

OLAP is a way of constructing queries and persisting/refreshing their results. Some OLAP based systems have a query designer where you can drag and drop columns, set values for certain columns to reduce the data, aggregate certain columns or pivot certain columns. They also have special handlers for pivoting such as pivoting datetime columns by a given granularity.

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.

Reply on news.ycombinator.com