In the opinion of Zenlytic’s founders, Looker’s LookML was the primary reason for their success. It was an innovation in Business Intelligence that improved:
• Improved Self-serve: It allows users to speak in terms of metrics and KPIs instead of columns
• More consistent metrics: Metrics could be centralized in one set of definitions - fixing the very common issue of having 6 different definitions of ‘active users’ floating around an org.
Introduction to LookML:
However, due to path dependencies that occurred early in Looker’s evolution, LookML didn’t deliver an optimal experience. Some of the most common complaints are:
• It’s verbose: It’s not uncommon to see 10,000-line LookML deployments. To this day, data teams often cringe at the thought of writing a new LookML deployment because they’re aware of the size of the task.
• It’s redundant: Looker adds the base concept of ‘Explores’ - each explore is a set of joined tables in the data warehouse. And Explores commonly overlap - when a join appears in multiple Explores, it needs to be defined multiple times.
• It doesn’t use software engineering principles: Looker doesn’t integrate well with Git or CI/CD tools. It’s such a big problem that an entire third-party business was built to address this.
• It’s still too hard to use for true self-serve: Using Looker adds several unnecessary steps in the self-serve workflow. A couple (of many) potential examples:
• A marketing user trying to track total leads needs to know if those are stored in the ‘Marketing’ Explore or the ‘Sales Ops’ Explore.
• A user needs to ensure proper date columns are selected - if someone selects ‘Total new users’ but selects ‘User Create Date’ instead of the correct ‘User Join Date’, they’ll get erroneous results.
Zenlytic and Looker use the same principles common to most semantic layers - predefined metrics based on columns in the data warehouse. But Zenlytic is more concise, easier to write, and automates away many steps in self-serve workflow for the end-user.
Zenlytic’s advanced architecture offers 3 main benefits:
• It makes it easier for a self-serve user to explore data
• It’s easier for data teams to build & maintain
• It runs queries and returns results faster
Below, we will look at several of the major innovations Zenlytic has introduced into our own semantic layer. We first provide a table that explains how each innovation enables the three benefits above. Below this, we provide more (very nerdy) detail on how each of these innovations improves on the previous (Looker) SOTA.
SUMMARY OF ZENLYTIC SEMANTICLAYER INNOVATIONS AND BENEFITS
THE NERDY, GORY DETAILS OFINNOVATIONS LISTED ABOVE
This section is more details of what each of the innovations above actually is. We focus on definitions here - benefits are discussed above.
The Relationship Graph:
Most BI calculations pull data from multiple tables in a data warehouse. Looker handles this with the concept of an Explore - a pre-specified list of tables with predefined joins.
In contrast, Zenlytic is able to dynamically join tables across the entire warehouse. Using advanced graph theory, it will trace connections across distant corners of the warehouse and identify optimal ways to join necessary tables.
Because of the Explore paradigm, it’s very difficult to calculate metrics that pull from multiple tables. For example, Customer Acquisition Cost is usually calculated with(new users from a users table) / ( campaign spend from a marketing attribution table). In Looker, this requires advanced ‘merged result’ functionality with a table calculation or an entirely new ‘derived’ table specified in SQL.
Zenlytic performs this automatically when defining metrics.
In Looker, selecting a metric without a corresponding date column usually returns no data.
Zenlytic adds a feature that doesn’t exist in Looker - the concept of a ‘default date’ .In the example below, ‘order date’ is listed as the default date for ‘total sales’. So ‘order date’ is added dynamically and ‘follows’ total sales around the GUI.
Often in data, the same concept will be expressed in two different tables. In the example below, ‘last click marketing channel’ is captured both in a traffic sources table and a Shopify orders table. In Looker, it can be confusing to decide which ‘marketing channel’ to use in a given context.
In Zenlytic, Cross-table Mappings allow the user to define these as the same thing. Zenlytic will show the end user one ‘marketing channel’, and decide which table to use under-the-hood.
Merged results are the infamous ‘bandaid’ solution Looker uses to correct Explore’s weaknesses. It allows technical users to join two Explores. It’s very complicated and has a limit of 5000 rows.
Zenlytic automatically does this under-the-hood (in SQL) as a user selects metrics.The end user never even needs to realize it’s happening.
This is technically not a semantic layer innovation but a UI innovation for creating the semantic layer.
When making a new semantic layer in Zenlytic, the UI will automatically import all relevant information from the data warehouse and pre-populate it into a template. It’s able to auto-generate roughly 90% of the code
Built-in compare periods
‘How does this compare to last year-to-date’
is one of the most common questions in
analytics. But it’s surprisingly hard in any BI
tool (or SQL).
Zenlytic has a checkbox in the UI, which auto-
generates the SQL using metric definitions
in the semantic layer.
LookML has no native support for event tables. These are tables of ‘userID clicked button X at time Y’ often used in product analytics to track user journeys or conversion funnels.
ZenML recognizes these as a special type for native funnel building in Zenlytic.This allows the self-serve user to perform Mix panel-style product analytics that can be used in sync with the rest of their BI.
LookML uses ‘drill fields’ to allow users to drill down on certain metrics. This was a powerful feature in their ability to sell against Tableau (and a high-value feature for any BI user).
Zenlytic expands this to the concept of Entities. Entities are a more powerful drill field that can be accessed from more places. It also improves LLM comprehension and lets users ask questions in more familiar language.