Hacker News Reader

Comment by georgewfraser | Hacker News Reader

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....

View on HN

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.

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

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

Well the size savings is a function of the number of included dimensions and the original table grain.

I wouldn’t call this an “OLAP Cube”. It’s just an aggregated fact table. A collection of those with their corresponding dimensions is a “data mart”.

A data mart is a logical organization of data to help humans understand the schema. What I am describing is a physical optimization, extremely similar to what an OLAP cube would do, but implemented on top of a SQL data warehouse. It’s an orthogonal concept to a data mart.

I guess I still don’t know what an OLAP Cube is.

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

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

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.

Is that related to lightdash.com somehow?

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

We both use dbt as the data modeling layer but we don't actually develop a standalone BI tool. Instead, we integrate to third-party BI tools such as Data Studio, Tableau, Metabase, etc.

We love Looker and wanted bring the LookML experience to existing BI tools rather than introducing a new BI tool, that's how metriql was born. I believe that Lightdash is a cool project especially for data analysts who are extensively using dbt but metriql targets users who are already using a BI tool. I'm not particularly sure which pages are identical, can you please point me?

Compare https://metriql.com/introduction/creating-datasets and https://docs.lightdash.com/guides/how-to-create-metrics

I though you are affiliated somehow, but looking at it now, it seems you just use the same documentation website generator :)

Ah, that makes sense! We use https://docusaurus.io/ primarily because dbt uses it as well. :)

It would be great if we can team up to build an open specification for the metric definitions though.

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.

Reply on news.ycombinator.com