3 ms·
There are a number of query languages to address this type of reuse and composability for analytical query workloads. - MDX: created by Microsoft to provide a
by greggyb 4y ago
There are a number of query languages to address this type of reuse and composability for analytical query workloads.
- MDX: created by Microsoft to provide a dimensional query language. The language is incredibly powerful, but depends on your understanding of dimensional modeling (go read Kimball is the best starting point for learning MDX). There are several tools, both commercial and open source, which implement an MDX interface to data.
- DAX: Microsoft's attempt to make MDX more approachable. A relational, functional language built on top of an in-memory columnstore relational storage engine, and used in several Microsoft products (Power BI, Analysis Services Tabular mode).
- Qlik has its own expression language whose name I am not sure of.
- Looker has LookML
There are a lot of BI tools out there. Not all have an expression language to support reusable measures/calculations, but many do. You may want to look into one.
- fny 4y agoThanks! MDX and DAX look powerful but also unreasonably complicated. LookML seems even more constrained. I'll keep digging.
- greggyb 4y agoSuch languages offload more work onto the data model than SQL does.[0] For example, in the Tabular model (where you would use DAX), relationships are defined in the model (and supported by an in-memory data structure that you can shortcut-think of as a pre-computed hash-join) and implicit in (most) expressions you write. If you think of SQL as a general-purpose query language, these BI-centric languages are DSLs for analytical queries. They are most useful for workloads where you have many queries that all use the same data model and are reasonably served by a pre-defined set of calculations/metrics. [0] Do not mistake me, good data modeling is important for SQL as well.