Looker: How to Implement Business Intelligence Analytics Software at Your Company

The data schema is mapped into Looker “models,” which regroup “views.” “Views” may be direct wrap-ups of the actual data tables or transformed tables created in order to make business insight explorations more straightforward.

What the business users see in their interface are the Looker “explores”—pages with a list of fields that you can select, format, and make visualizations of by simply clicking, drag-and-dropping, picking colors from the palette, etc. The only need to use a keyboard is when inputting a dashboard tile title.

If you want to push the exploration even further, it’s enough to define some filters, declare a couple of “calculations” (excel-like column operations), or select one of the fields to be the pivot of a table.

The cherry-on-top feature for any business analyst is the “drill-down”—the ability to zoom into the details behind any statistics. You can do it by, for example, clicking on a bar of the chart representing revenues by month, which would result in a more fine-grained slicing or even the complete listing of all items sold that month.

I’ve mentioned Looker “views” before. They are defined in LookML, a hybrid of YAML and JSON, that uses additional special syntaxes, for example, Liquid, to enable some advanced features.

This isn’t a technical article, so I won’t get much more into the implementation details and the code itself, but you need to consider that there is a steep learning curve for the technical and data team to face when dealing with Looker for the first time.

And even once you know LookML, there is still loads of work to be done before a business user can reap the benefits.

Some of the work is actually automated by the tool itself. For example, the “views” are generated automatically for the existing tables that Looker discovers for you.

This is crucial, because if your tables have several hundred fields, then the code for the corresponding Looker “dimensions,” which populate Looker “views,” can be pre-created for you.

And since those “dimensions” are translated into the ones you see in a Looker “explore,” you might think that the backend work is done, but that’s hardly the case. First, a developer needs to check if all the “dimensions” have the correct Looker data type and if it matches the data in the data source.

From my experience, working with data lakes can be a lot of work, for example, where the schema is less strictly defined. Changing this manually for hundreds of fields is really time-consuming.

source: cloudskillsboost.google

Even with all the “dimensions” defined correctly, the business user would still have very few insights to mine from. SQL can perform transformation such as:

• joining different tables by the common key, for example joining the `orders` table with the `products` one, to know which products have actually been sold;
• stacking similar tables into one with unions;
• aggregating table details, for example counting the number of orders per month or summing up the number of revenues per salesperson.

Replacing SQL will require translating those concepts to LookML.

Even though there is a possibility to define each such transformation with traditional SQL in Looker, it is not recommended.

It may prove complicated to maintain an SQL code and look after its reusability across different dashboards. However, while “joins” can be defined in Looker “models,” table unions don’t exist yet and require some special hacks depending on the use case.

Table aggregations are handled by the Looker “measures.” This vocabulary needs to be learned by the business users, as “measures” are present on the same list of fields as “dimensions” in “explores,” but they relate to aggregation functions, rather than the raw, or transformed, fields present in the original table.

For example, `amount` may be a field representing the money paid for a given order, and `sum_amount` may be a “measure” of the total amount of money paid for all orders by date.

`SELECT date, amount FROM orders` is not the same as `SELECT date, sum(amount) FROM orders GROUP BY date`, after all.

Looker automatically translates the selected “dimensions” and “measures” into SQL sent to the database, which is very useful. But if an analyst needs to know the ratio of the day’s total purchase to the month’s total, it won’t be there to click and select from the basic Looker “view” generated automatically.

Even that fact alone demystifies the bypassing of the data scientist’s bottleneck promise.

source: looker.com

Creating custom “dimensions” directly in the “explore” is less of a problem with Looker’s “calculations,” but first, you’ll need to know yet another special Looker syntax to define those “calculations.” Second, as those transformations are not performed on the database side, using them can negatively affect the performance of the Looker instance.

When using filters and pivots in “explores,” you’ll soon learn that only some of the logical conditions are possible in the GUI. I’ve personally found myself unable to easily select items related to a period in the past, for example before 3 months ago and after 5 months ago.

Pivots are operations performed on the client’s side, thus having too many of those can cause your dashboard to refresh for a noticeably long time.

Looker’s response to the problem of performance issues is, besides its handy caching mechanism, performing persistent calculations as physical tables in the data source.

This is packaged as a feature proposed to be automatically performed by the platform. But it translates to the same thing as having respective Extract-Transform-Load (ETL) mechanisms directly on the underlying data platform.

If you don’t have a data engineer on board, it might be interesting to use that functionality. But in order to set everything up correctly with schedules and triggers that perform best for your data, some data engineering skills are already required.

You might end up with a similar conclusion to mine: that including any aggregation pipelines into the existing infrastructure might make more sense than doing some of them within Looker.

Finally, the drill-down functionality doesn’t come free, either. The fields that you want to see when digging deeper into the data need to be explicitly defined and can’ be changed by the end user.

The data presented that way can’t be sorted or filtered, but you may click again to start a new exploration from that detailed view. The default drill-down Looker behavior is also limited and can prove unusable for “views” using some custom metrics—so for most of them, in reality.

In such cases, Looker has a solution: even more code.

Developers can override the built-in functionality with custom HTML enhanced with Liquid conditional syntax and a panel of system variables that you might reference.

But this also means more time to deliver meaningful components for the business user. Here, Looker also requires some more technical competencies from the tech and data team than just writing SQL queries.

source: cloudskillsboost.google

In the context of those big promises given to the business user, I’d like to mention that they might cause additional frustration for both you and your developers.

The former might feel lost in all the Looker-related nomenclature, because nothing comes easy from the first set of basic “views”—especially if those map 1-to-1 to the relational database, knowing a schema of the tables is required.

The latter would also be challenged with learning all the functionalities of this gigantic BI tool. The dev users would also feel pressure to define all the perfect “measures,” pre-calculations, and pre-formattings, so that the data is understandable and most efficiently processed from the very beginning.

There is a huge difference between presenting several tables with hundreds of fields each as they exist in the relational database, and grouping them into logical lots. The same way there is a difference between presenting them with more human-readable labels, and making more commonly used fields more accessible, too.

On the bright side, there are very few things that are impossible with Looker. You get numerous different parameters to learn and use in your projects. If you don’t find the solution in the concepts that Looker introduces, you may fall back to the custom SQL/HTML/Liquid code. And eventually, you will have to.

Developers can also use an built-in IDE and integrations with a git-based code versioning service. Some operations, like creating git branches, committing code, and pushing to and pulling from the master branch, can be performed directly via Looker.

source: docs.looker.com

Push/pull requests and code reviews can be set as mandatory but would, naturally, need to be done on a git repository service provider’s side. More complex git operations, like rebases, would require a command line intervention.

Note that there is documentation and a community forum to help you get started and going. And should you have any questions, their support chat is pretty responsive, as well.

In Looker and similar BI solutions, the advanced data exploration functionalities promised to business users with the next-to-the-null amount of dev skills come at the cost of having someone else perform those dev tasks.

And since an intermediary layer of abstraction is introduced there, mapping the data and its schema into that layer and related concepts requires an extra initial dev effort.

Ideally, this extra entry-point work should pay off later on, when the project is mature enough for the business users to really benefit from the freedom of data exploration.

But even then, every new data source and every new custom metric would still need to be introduced in the backend code, and the most popular data transformations on the client’s side might need to be implemented as ETLs for performance optimization reasons.

Should you decide to take up Looker, be aware that it’s not easy for the technical team to prepare it so that it’s flexible, maintainable, and efficient, and that there is a considerable entry cost for both data teams and business analysts to pay.