![The Greatest SQL Is the One You Do Not Have To Write The Greatest SQL Is the One You Do Not Have To Write](https://bizagility.org/wp-content/uploads/https://www.gooddata.comhttps://www.gooddata.com/img/blog/_1200x630/02_16_2022_buildvsbuy_registration.png)
[ad_1]
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:
![Count of flights that happened in the United States. Count of flights that happened in the United States.](https://www.gooddata.com/img/blog/_1000xauto/code-example-1.png)
![Count of flights by carrier OR by aircraft model manufacturer. The same metric (count of flights) in different contexts (Carrier / Aircraft model manufacturer) requires completely different queries. Count of flights by carrier OR by aircraft model manufacturer. The same metric (count of flights) in different contexts (Carrier / Aircraft model manufacturer) requires completely different queries.](https://www.gooddata.com/img/blog/_1000xauto/code-example-2.png)
![Percentage of flights for each carrier from overall flights. Windowing function approach on the left, JOIN of subselects approach on the right. These simple questions result in queries that are complicated, hard to write, and even harder to maintain. Percentage of flights for each carrier from overall flights. Windowing function approach on the left, JOIN of subselects approach on the right. These simple questions result in queries that are complicated, hard to write, and even harder to maintain.](https://www.gooddata.com/img/blog/_1000xauto/code-example-3.png)
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.
![Very simple metric definition in the left top corner, context (View by) is defined separately, the corresponding SQL is on the right side and the query result preview is in the bottom left corner. Very simple metric definition in the left top corner, context (View by) is defined separately, the corresponding SQL is on the right side and the query result preview is in the bottom left corner.](https://www.gooddata.com/img/blog/_1000xauto/percenage_of_carrie.png)
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.
![Dataset relationships (arrows) enable platforms to generate SQL (JOINs). Dataset relationships (arrows) enable platforms to generate SQL (JOINs).](https://www.gooddata.com/img/blog/_1000xauto/dataset_rel.png)
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:
![Federal Aviation Administration Model Federal Aviation Administration Model](https://www.gooddata.com/img/blog/_1000xauto/model-1.png)
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:
- 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. - 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! - 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?
![Malloy source (a logical dataset) on top of the flights table. Only primary key and references to other sources. Only two fields are renamed to simplify their usage. Malloy source (a logical dataset) on top of the flights table. Only primary key and references to other sources. Only two fields are renamed to simplify their usage.](https://www.gooddata.com/img/blog/_1000xauto/code-example-4.png)
![GoodData web UI for model visualization and editing. GoodData web UI for model visualization and editing.](https://www.gooddata.com/img/blog/_1000xauto/model-2.png)
![Declarative definition of GoodData dataset on top of the flights table. Primary key (grain) and references to other datasets. All entities must be declared, and attributes and facts must be distinguished. Datasets are mapped to tables, and attributes/facts to columns. Declarative definition of GoodData dataset on top of the flights table. Primary key (grain) and references to other datasets. All entities must be declared, and attributes and facts must be distinguished. Datasets are mapped to tables, and attributes/facts to columns.](https://www.gooddata.com/img/blog/_1000xauto/code-example-5.png)
Modeling Comparability
I’ve put collectively a high-level overview of how logical information fashions are dealt with in GoodData and Malloy:
![GoodData and Malloy Comparison GoodData and Malloy Comparison](https://www.gooddata.com/img/blog/_1000xauto/table-1.png)
(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?
![Malloy calls metrics ‘measures’. Both simple and more complex measures can be declared. Measures must be defined as a part of a source (dataset). Malloy calls metrics ‘measures’. Both simple and more complex measures can be declared. Measures must be defined as a part of a source (dataset).](https://www.gooddata.com/img/blog/_1000xauto/code-example-6.png)
![GoodData Metric UI editor. The metric definition is in the left top corner. “View by” enables you to put metrics into context (hereby Carrier nickname). The result preview is in the bottom left corner. The corresponding SQL statement is in the panel on the right. GoodData Metric UI editor. The metric definition is in the left top corner. “View by” enables you to put metrics into context (hereby Carrier nickname). The result preview is in the bottom left corner. The corresponding SQL statement is in the panel on the right.](https://www.gooddata.com/img/blog/_1000xauto/code-example-11.png)
![Declarative definition of GoodData metrics. Both simple and more complex metrics can be declared. Besides metric ID and metric source code, additional semantic properties can be defined (format, description). Declarative definition of GoodData metrics. Both simple and more complex metrics can be declared. Besides metric ID and metric source code, additional semantic properties can be defined (format, description).](https://www.gooddata.com/img/blog/_1000xauto/code-example-8.png)
Metrics Comparability
Here’s a high-level overview of how metrics/measures are dealt with in GoodData and Malloy:
![Metrics Comparison Metrics Comparison](https://www.gooddata.com/img/blog/_1000xauto/table-2.png)
(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
![Metrics Reusability — Shared Dimension Use Case Metrics Reusability — Shared Dimension Use Case](https://www.gooddata.com/img/blog/_1000xauto/diagram-1.png)
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
![Complex MAQL Metric Example in GoodData Complex MAQL Metric Example in GoodData](https://www.gooddata.com/img/blog/_1000xauto/code-example-9.png)
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:
![Query (report) in Malloy is in the bottom left corner, putting three metrics into the Carrier nickname context (viewing / slicing it by Carrier nickname). The result preview is in the right panel, where developers can display the corresponding SQL statement as well. Query (report) in Malloy is in the bottom left corner, putting three metrics into the Carrier nickname context (viewing / slicing it by Carrier nickname). The result preview is in the right panel, where developers can display the corresponding SQL statement as well.](https://www.gooddata.com/img/blog/_1000xauto/code-example-10.png)
![GoodData metric editor also provides a way to prototype reports. Developers can put metrics into context with View by and see the corresponding result preview and SQL statement. GoodData metric editor also provides a way to prototype reports. Developers can put metrics into context with View by and see the corresponding result preview and SQL statement.](https://www.gooddata.com/img/blog/_1000xauto/code-example-11.png)
![GoodData Analytical Designer for reports (visualizations) building. Drag&drop experience for business end users (drag&drop from the left panel containing facts/attributes/metrics). GoodData Analytical Designer for reports (visualizations) building. Drag&drop experience for business end users (drag&drop from the left panel containing facts/attributes/metrics).](https://www.gooddata.com/img/blog/_1000xauto/app-image-3.png)
![GoodData declarative definition of the same report (called insight in GoodData). It contains three metrics and the Carrier nickname attribute. GoodData declarative definition of the same report (called insight in GoodData). It contains three metrics and the Carrier nickname attribute.](https://www.gooddata.com/img/blog/_1000xauto/code-example-12.png)
Studies Comparability
And at last, here’s a high-level overview of how studies are dealt with in GoodData and Malloy:
![Reports Comparison Reports Comparison](https://www.gooddata.com/img/blog/_1000xauto/table-3.png)
(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
![Malloy nesting. Interesting concept, but we are not sure if it should not be only a feature of the visualization layers. Malloy nesting. Interesting concept, but we are not sure if it should not be only a feature of the visualization layers.](https://www.gooddata.com/img/blog/_1000xauto/code-example-13.png)
![The same report in GoodData. The visualization layer could display the result in the same way as Malloy. The same report in GoodData. The visualization layer could display the result in the same way as Malloy.](https://www.gooddata.com/img/blog/_1000xauto/app-image-4.png)
GoodData Pivoting
![Table with pivoted FAA region attribute. Sub-totals and grand-totals are calculated as well. It is even possible to sort by the pivoted attributes. Table with pivoted FAA region attribute. Sub-totals and grand-totals are calculated as well. It is even possible to sort by the pivoted attributes.](https://www.gooddata.com/img/blog/_1000xauto/app-image-5.png)
![The same pivot (by FAA region) is displayed as a nice TreeMap. The same pivot (by FAA region) is displayed as a nice TreeMap.](https://www.gooddata.com/img/blog/_1000xauto/app-image-6.png)
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.
[ad_2]