Querying a Semantic Data Model
A semantic data model contains pre-built calculations and relationships for a set of data. How do you query it?
A semantic data model contains pre-built calculations and relationships for a set of data. This article will show you the basics of querying a semantic Data model in Malloy.
Let’s start simply, we'll define: dimension, measure, aggregating query, lookup query.
Data for Kindergarteners
Given that we learn about data in Kindergarten, It is really surprising how hard it is to query data in the "big" world. In kindergarten data we notice attributes about things, make piles of things, and count things in the piles. We do this even before we learn any "real" math. Strangely, noticing attributes about things and counting things is almost all there is to working with data.
Two types of queries, "Where's Waldo" and "Making Piles"
There are really two types of queries in the world, lookup and aggregating.
Lookup queries are pretty easy. Google search is a lookup query. To search, type in some terms then see a list of results. Searching in SQL this often looks like `SELECT * FROM WHERE ``.
The interesting queries, the kindergarten queries, are aggregating. Aggregating queries tell you something about a set of data. An aggregating query has two main parts, the dimensions and measures.
Dimensions are the attribute you use to decide which pile the thing goes in.
A measure is something you can say about the pile. "How many objects?", "How much does this pile weigh?", "What is the average size of an object in this pile?".
"Ok class let's take this pile of coins and separate them. How many coins are pennies? How many coins are nickels? Dimes? Quarters?"
Aggregating queries tell us things about datasets. Lookup queries find things.
SQL World
In the SQL world, the interface to your data is just that, data. The data sits in tables. Everytime, you ask a question (run a query), you need to restate all the things about the data, the calculations, the relationships between tables. The unit of reusability is a table. You can run a query that makes a new table. You can turn a query into a SQL View, which is basically a table based on a calculation.
The Semantic Data Model is the interface to your data
In a Semantic Data Model, the calculations (dimensions and measures) are the reusable. The join relationships are built into semantic data model. The calculations, common ways of looking at data, are coded into the semantic data model. The act of querying becomes simply picking dimensions and measures, filtering the input and output data and sorting the results. The simplification is powerful in that the measuring calculations are always vetted and that it becomes much harder to get incorrect results.