5 ms·
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, mea
by georgewfraser 5y 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.html https://docs.snowflake.com/en/user-guide/views-materialized....
- mulmen 5y agoBut those queries aren’t equivalent so how is anything saved by materializing the second one? e: I believe (I could be wrong!) you edited the second query from SELECT dimension, measure FROM table GROUP BY 1 To 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.
- georgewfraser 5y agoIt’s much smaller than the original table. If you compute lots of these, then voila, you have an OLAP cube.
- mulmen 5y agoWell 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”.
- georgewfraser 5y agoA 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.
- mulmen 5y agoI guess I still don’t know what an OLAP Cube is.
- motogpjimbo 5y agoHe did edit his comment, but unfortunately didn't acknowledge the edit.
- ipaddr 5y agoBy grouping them you need to include aggregate functions like avg,max,min,sum,count for measure and dimension.
- _dark_matter_ 5y agolooker does exactly this, though you do have to specify which dimensions to aggregate: https://docs.looker.com/data-modeling/learning-lookml/aggregate_awareness https://docs.looker.com/data-modeling/learning-lookml/aggreg...
- motogpjimbo 5y agoDid 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.
- buremba 5y agoWhile 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 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.
- glogla 5y agoIs that related to lightdash.com somehow? It seems like a very similar technology and also the webpages for both are almost identical.
- buremba 5y agoWe 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?
- glogla 5y agoCompare https://metriql.com/introduction/creating-datasets https://metriql.com/introduction/creating-datasets and https://docs.lightdash.com/guides/how-to-create-metrics 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 :)