Are You Uninterested in Writing SQL?

SQL has been the lingua franca for information manipulation for over 40 years now. As we speak SQL is an establishment, one that only a few folks can think about may exchange. There isn’t a denying although that its limitations are beginning to present. Each skilled information engineer has reached a degree of desperation when having to jot down the identical SQL boilerplate code repeatedly, particularly within the space of analytics with dynamic enterprise necessities.

Let me provide you with a couple of examples of what I imply:

Easy SQL queries quickly develop in complexity when real-world enterprise necessities are utilized to them.

Can We Resolve It Extra Simply Utilizing One other Language?

Sure, with next-generation analytical languages we are able to! Take a look on the following instance, the place we condense a sophisticated SQL question into three strains of code.

However how is one thing like this potential? How does the engine know the way to join all entities collectively and generate the SQL?

The reply lies within the semantic mannequin.

Assumptions

In comparison with SQL, new analytical languages are:

• A lot less complicated, extra readable, and simpler to take care of
• Able to using metrics which can be reusable in a number of contexts (View/Slice by, filters, and so on.)
• Able to fixing all vital analytics use circumstances

The disadvantage is that it’s important to make investments your time into studying a brand new language. Is the funding value it?

Let Me Consider Such Languages for You

I’ll look at two of probably the most superior languages:

Particularly, I’ll information you thru the next three phases:

You possibly can then strive it for your self, the supply code will be discovered right here.

Instance Mannequin

Earlier than we begin with the aforementioned three phases, let me briefly present you what the underlying database mannequin we’re going to make the most of seems like:

Information collected from the Federal Aviation Administration.

Single “flights” reality desk will be damaged down by a number of dimensions — carriers, airports and aircrafts of assorted fashions.

Airports are enjoying two roles — origins and locations.

Credit score goes to builders from Malloy, who already ready corresponding information information, fashions, and analytics use circumstances. Thanks!

The Three Phases

Modeling

We have already got the bodily information mannequin, so why create yet one more mannequin (a logical information mannequin on high of the bodily information mannequin)?

There are three causes:

1. Allow extra customers to research information
   Create the mannequin as soon as, and reap the advantages without end.
   It’s a lot simpler to construct metrics and studies as soon as the information has been modeled correctly, and even enterprise customers can work with logical entities.
2. Retailer extra semantic metadata
   Semantic properties like distinguishing info/attributes/date dimensions.
   Documentation.
   Metadata will be utilized by numerous exterior methods striving for semantic data, for instance in pure language processing (NLP). My colleague Jan Kadlec lately wrote a associated article on NLP, I encourage you to learn it!
3. Decouple analytics from bodily fashions
   Refactor the bodily mannequin with out refactoring all analytics objects (metrics, visualizations, dashboards, …)

What does the logical information mannequin appear like?

Modeling Comparability

I’ve put collectively a high-level overview of how logical information fashions are dealt with in GoodData and Malloy:

(1) Developer expertise — Is it handy for builders to handle the mannequin?

Malloy gives an excellent developer expertise in IDE (VS Code with a Malloy plugin). Builders can write the mannequin (and metrics, and studies, to be lined by the next chapters) in a single file and validate the answer (preview datasets/sources, execute studies). Furthermore, the IDE plugin gives the corresponding IntelliSense — it reads bodily information mannequin entities and suggests them in the fitting locations (for instance it affords corresponding column names when attempting to rename an entity to a greater enterprise identify).

GoodData is extra oriented in direction of UI expertise and enterprise customers, nevertheless it additionally permits builders to retailer declarative definitions of fashions (and metrics, and studies) into YAML information, manipulate them and ship them into any setting (CI/CD).

Moreover, GoodData gives two use circumstances — scanning the bodily information mannequin and producing a logical information mannequin from the bodily information mannequin. It applies numerous guidelines to detect dataset (desk) relationships, distinguish info/attributes, and so on. It’s not good however it may well considerably velocity up onboarding.

(2) Final-mile ETL — i.e. transformations wanted to organize the bodily mannequin to be appropriate with analytics use circumstances.

Some platforms require very advanced transformations, for instance, to denormalize all the pieces right into a single desk because of the practical and efficiency limitations of those platforms. Neither Malloy nor GoodData requires such transformations — normally, we advocate to implement such transformations solely on the dataset stage, for instance, to rework values 0 and 1 into Male and Feminine. Each platforms can work with Star/Snowflake schemas.

(3) Declare logical entities solely when wanted

You need to declare all entities and distinguish info from attributes in GoodData.

This requires a barely bigger preliminary time funding, nevertheless it improves the expertise for enterprise customers by permitting for a self-service drag&drop expertise within the UI. We plan to simplify this expertise even additional within the close to future.

(4) The idea of attribute labels in GoodData.

Attribute labels are a singular function available on the market. Customers can declare that an attribute has a number of labels. GROUP BY is all the time utilized to the column that’s mapped to the attribute, e.g. customer_id.

Customers can resolve to show a label by default, e.g. customer_name. Customers can outline different kinds of attributes offering particular capabilities, e.g. URL or geo-location.

We wish to talk about it with different distributors, together with Malloy, whether or not it’s a robust sufficient idea.

(5) Outer joins

Whereas Malloy does, GoodData doesn’t enable customers to declare a relationship between datasets as “outer”, as a result of we consider that customers usually use each inside and outer joins primarily based on a enterprise case applied by a metric or a report. That’s the reason we plan to quickly present an choice to specify “outer” in metrics and likewise in studies (“present empty values”).

(6) Periodical granularities

Malloy doesn’t assist periodical granularities like dayOfWeek, weekOfYear, …

(7) Superior modeling use circumstances

There are such a lot of and no analytics platform helps all of them. This can be a big alternative for dialogue!

Examples:

• Function-playing dimensions
  e.g. consumer -> creator/final updater
• Combination consciousness
  single dataset mapped to a number of tables, e.g. aggregated by day, week, month, …
• Additive/non-additive info
  impacts during which context info/metrics can be utilized

Metrics/Measures

Why ought to customers spend money on writing metrics? As a result of they are often reused in a number of studies!

Additionally, metrics assist the one supply of fact paradigm — if you outline e.g. income metric as soon as and it’s reused throughout the group, it can’t occur that two enterprise departments deliver two totally different income values to the board assembly.

What do metrics appear like in Malloy and GoodData?

Metrics Comparability

Here’s a high-level overview of how metrics/measures are dealt with in GoodData and Malloy:

(1) Superior analytics use circumstances

No matter you think about you’ll be able to obtain in SQL (analytics use circumstances solely!), it is best to have the ability to obtain with Malloy/GoodData.

Examples:

• Calculate contribution, e.g. metropolis to area
• Interval-over-period comparisons
• Filter by advanced metric

(2) IntelliSense

Each GoodData and Malloy present an excellent IntelliSense.

In GoodData, we adopted the idea of language servers, and now we put it to use in our net IDE. We’ve applied a PoC of VS Code plugin as properly and we all know it’s possible. We consider that that is one thing all languages/platforms ought to present.

Furthermore, GoodData gives two extra superior use circumstances:

• labelElements
  suggests attribute(label) values
• validObjects
  suggests attributes/info/metrics primarily based on the context already present in a metric.
  Primarily based on the mannequin, we all know which entities will be appended right into a context.

(3) Outer joins

GoodData goes to supply an express language syntax to allow outer joins. Malloy gives outer be a part of by default, builders need to make the most of filters (the place the joined entity is just not empty) to implement inside be a part of.

That is an attention-grabbing distinction — what ought to be the default? Let’s talk about it!

(4) Metrics reusable in a number of contexts

The idea of shared dimensions is just not supported by Malloy, which means that Malloy metrics can’t make the most of entities from 2 or extra reality tables related by shared dimensions. See the shared dimension use case instance under.

(5) Language “completeness”

It looks like it’s potential to jot down any quantity of complexity into GoodData metrics, see an instance under. The Malloy measure definition is extra strict. The query is that if the identical complexity will be achieved in Malloy by nesting metrics.

Metrics Reusability — Shared Dimension Use Case

In Malloy, if we mannequin duties, exercises, and coronary heart charges as separate sources, we can’t construct metrics using entities from all these sources.

For instance, attempt to correlate exercises and coronary heart charges by time or by the consumer.

We must create a supply on high of the consumer and be a part of exercises and coronary heart charges. We couldn’t do it in any respect with the time dimensions (it’s digital).

That is potential with none limits in GoodData and evidently it’s potential in PowerBI(DAX) as properly.

Advanced MAQL Metric Instance in GoodData

In GoodData MAQL language, you’ll be able to mix any language constructs collectively, nest by “sub-selects”, even in filters.

Malloy requires a fairly strict type of measure definition. You possibly can implement advanced expressions, and you’ll outline a filter as part of a measure, however you can’t nest measures in such an easy method as is feasible in GoodData MAQL, or at the very least that’s my impression.

Studies (Queries, Visualizations)

Lastly, we get to the actual enterprise worth — studies (queries in Malloy, visualizations in GoodData). Studies are metrics (constructed within the above chapter) in context — seen/sliced by attributes, filtered, sorted, and so on.

Right here is how studies appear like in Malloy and GoodData:

Studies Comparability

And at last, here’s a high-level overview of how studies are dealt with in GoodData and Malloy:

(1) Pure report object

The GoodData report object (visualization) additionally accommodates presentation properties like colours. There isn’t a pure report object which could possibly be reusable.

(2) Reuse studies in one other studies

Malloy gives an idea of pipelined queries, every with a full feature-set (group by, filters, …). Very highly effective! GoodData doesn’t present something like this.

(3) Ordering and limiting

GoodData studies can’t be ordered by a number of entities (attributes/metrics).

Then again, GoodData gives features like RANK, which may present extra flexibility, even inside metrics.

(4) Report nesting

We’re not satisfied it’s a good design to nest studies in different studies. Nonetheless, it may well save quite a lot of pings from purchasers to the server.

Report Nesting

GoodData Pivoting

Conclusion

Comply with the Malloy/MAQL Method

Each languages deliver vital added worth to (not solely) builders. They’re comparatively to select up and begin utilizing and supply a really easy-to-use and maintainable strategy to information analytics going ahead. Each languages are developer pleasant, although we at GoodData are going to study from Malloy on this entrance 😉

There are extra options to SQL, like dbt metrics, Microsoft DAX, and so on. You might count on follow-up articles.

It is best to take into account studying these languages, it’s value it!

Present Shortcomings of the Malloy/MAQL Method

Usually, Malloy is just not but prepared for a self-service expertise. Possibly a call will likely be made to port Malloy into Looker and make this expertise obtainable.

Malloy, by design, doesn’t assist metrics on high of a number of reality datasets (with shared dimensions). I wish to talk about this design determination with folks from Malloy, as there’s quite a lot of potential for progress on this space! By the way in which, PowerBI helps it. 😉

In GoodData, it’s not potential to jot down the logical information mannequin, metrics, and studies in a unified and programmatic approach and check it immediately in an IDE. We wish to give attention to this within the close to future. As an example, due to the idea of a language server, we already applied a PoC of VS Code plugin for MAQL.

Concerning the analytics function set: each languages cowl much more superior analytics use circumstances (contribution, interval over interval, …). As distributors of such languages, we should always take heed to developer wants and add assist for lacking use circumstances. If that’s accomplished, builders shouldn’t have to context-switch between these languages and SQL too usually (or hopefully, ultimately, by no means).

Let’s Be a part of Forces

The semantics are related in all these languages. We, as language suppliers, ought to begin speaking collectively. We should always discover a stability between specializing in builders and on the tip enterprise customers. We should always allow producing one language from one other.

What about open-sourcing the language interpreters underneath Apache 2.0 license?

May we converge to a brand new normal? Let’s strive it!

Attempt GoodData Your self!

My aforementioned demo that this text was primarily based on.

GoodData Cloud trial — cloud SaaS model.

GoodData Neighborhood Version — run GoodData in your laptop computer.