Today, we're happy to support Looker users switching over to Cube and present `lkml2cube`, a tool for converting [LookML](https://cloud.google.com/looker/docs/reference/lookml-quick-reference) models into the Cube data model. With this tool, you can easily migrate your existing [Looker](https://cloud.google.com/looker/) projects to Cube.

## Benefits of using Cube

Cube is a code- and API-first semantic layer that enables data engineers and developers to make their data consistent, secure, performant, and accessible across every application. Cube is commonly used to implement a *semantic layer*: a middleware between your data source and your application that provides a unified view of your data, regardless of where it is stored or how it is formatted.

By migrating your LookML models to Cube, you can achieve the following:

- **Reduced development time.** Once you have migrated your LookML models to Cube, you can start using Cube's powerful features, such as its pre-built calculations, dimensions, and measures, to create your data applications. Using Cube can save you a significant amount of development time.
- **Improved data quality.** Cube's semantic layer can help you improve your data quality by performing data transformations, validations, and aggregations. Using Cube's version-controlled semantic layer definition can help you to ensure that your data is consistent, accurate, and reliable.
- **Enhanced performance.** Cube's in-memory data store can help improve your data applications' performance by caching frequently accessed data, thus providing faster user response times.
- **Increased accessibility.** Cube's API-first design easily integrates Cube into your existing data applications.

Initially, we’ve created `lkml2cube` to facilitate our work while supporting the migration of Looker users to [Cube Cloud](https://cube.dev/product/why-cube-cloud). Now, we’re happy to put it into [open source](https://github.com/cube-js/lkml2cube) to assist more Looker users in their migration over to Cube.

Given Cube’s current position as a [leader and fast-mover](https://cube.dev/blog/cube-recognized-as-a-leader-and-fast-mover-by-gigaom) among semantic layers, there’s no surprise we see more and more companies migrating to Cube from their legacy solutions:

[![](https://ucarecdn.com/d266a78b-3dcf-4355-a59d-17a15007afa1/)](https://cube.dev/gigaom-rates-cube-as-a-leader-for-semantic-layers)

Here’s how one of Cube Cloud users describe their experience:

> We recently transitioned from Looker to Cube in an effort to enhance the user experience on our data-intensive internal platform, both in terms of performance and user interface. Our aim was to seamlessly embed data and analytics into our team's workflows.
> 
> With the invaluable assistance and robust tools provided by the Cube team, we successfully migrated a significant portion of our LookML project, encompassing hundreds of views and explores, to Cube’s data modeling language in just a matter of weeks.
> 
> *Director, Data Products at an investment firm*

## Migration example

Cube’s data modeling language has a few similarities with LookML, however, it’s arguably more powerful, flexible, and fit as a data modeling language for a modern semantic layer.

* [Cubes](https://cube.dev/docs/reference/data-model/cube) represent tables of data in Cube and are roughly equivalent to [views](https://cloud.google.com/looker/docs/reference/param-view) in LookML. However, cubes are a bit more powerful since they can contain [join](https://cube.dev/docs/reference/data-model/joins) and [pre-aggregation](https://cube.dev/docs/reference/data-model/pre-aggregations) definitions—the latter are used to provide granular query acceleration configuration and inform Cube’s [aggregate awareness](https://cloud.google.com/looker/docs/aggregate_awareness).
* [Views](https://cube.dev/docs/reference/data-model/view) sit on top of the data graph of cubes and create a facade of your whole data model; they are roughly equivalent to [explores](https://cloud.google.com/looker/docs/reference/param-explore) in LookML. However, when referencing cubes, views allow to define an exact full join path and remove the ambiguity of searching the join path within the data graph. `lkml2cube` accounts for this quirk of LookML and performs a breadth-first search in the data graph to find the most suitable join relationship.

Consider a simple Looker project that is structured in the following way: 

```
.
├── my_explores.model.lkml
└── views
    ├── line_items.view.lkml
    ├── orders.view.lkml
    ├── product_categories.view.lkml
    ├── products.view.lkml
    ├── suppliers.view.lkml
    └── users.view.lkml
```

After applying `lkml2cube`, an equivalent Cube project would be structured similarly: 

```
.
└── model
    └── cubes
        ├── line_items.yml
        ├── orders.yml
        ├── product_categories.yml
        ├── products.yml
        ├── suppliers.yml
        └── users.yml
    └── views
        └── orders_view.yml
```

You can generate the Cube data model for your entire Looker project by executing a single command against the file with LookML explores. `lkml2cube` would inspect that file, follow the references in the `include`parameter, and generate necessary cubes and views:

```bash
lkml2cube views my_explores.model.lkml --outputdir model/
```

For example, the following `views/orders.view.lkml` file…

```javascript
view: orders {
  view_label: "Orders"

  sql_table_name: "public.orders" ;;

  dimension: unique_key {
    type: string
    primary_key: yes
    sql: ${TABLE}."id" ;;
  }

  dimension: status {
    type: string
    sql: ${TABLE}."status" ;;
  }

  dimension_group: created_at {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year
    ]
    sql: ${TABLE}."created_at" ;;
  }

  measure: count {
    type: count ;;
  }

  measure: total_order_amount {
    type: sum
    sql: ${TABLE}."number" ;;
  }
```

...would be converted into the following `model/cubes/orders.yml` file:

```yaml
cubes:
  - name: orders
    sql_table: public.orders

    joins:
      - name: users
        sql: "{CUBE}.user_id = {users}.id"
        relationship: many_to_one

      - name: products
        sql: "{CUBE}.product_id = {products}.id"
        relationship: many_to_one

    dimensions:
      - name: id
        sql: "{CUBE}.id"
        type: number
        primary_key: true

      - name: status
        sql: "{CUBE}.status"
        type: string

      - name: created_at
        sql: "{CUBE}.created_at"
        type: time

    measures:
      - name: count
        type: count

      - name: total_order_amount
        sql: "{CUBE}.number"
        type: sum
```

Consequently, the following `my_explores.model.lkml` file...

```javascript
explore: orders {
  label: "Orders Summary"

  join: users {
    relationship: many_to_one
    sql_on: ${orders}.user_id = ${users}.id ;;
    type:  left_outer
  }

  join: products {
    relationship: many_to_one
    sql_on: ${orders}.product_id = ${products}.id ;;
    type:  left_outer
  }

  join: line_items {
    relationship: one_to_many
    sql_on: ${orders}.id = ${line_items}.order_id ;;
    type: left_outer
  }

  join: product_categories {
    relationship: many_to_one
    sql_on: ${products}.product_category_id = ${product_categories}.id ;;
    type: left_outer
  }

  join: suppliers {
    relationship: many_to_one
    sql_on: ${products}.supplier_id =${suppliers}.id ;;
    type: left_outer
  }
}

```

...would be converted into the following `model/views/orders_view.yml` file:

```yaml
views:
  - name: orders_view

    cubes:
      - join_path: orders
        includes: "*"

      - join_path: orders.users
        prefix: true
        includes: "*"

      - join_path: orders.products
        prefix: true
        includes: "*"

      - join_path: orders.line_items
        prefix: true
        includes: "*"

      - join_path: orders.products.product_categories
        prefix: true
        includes: "*"

      - join_path: orders.products.suppliers
        prefix: true
        includes: "*"
```

Note unambiguous join paths like `orders.products.product_categories` and `orders.products.suppliers` in the example above that `lkml2cube` was able to detect and express in the Cube data model. Over time, it would save hours, if not days, of work when building and debugging complex data models.

## Conclusion

`lkml2cube` is a powerful tool that can help you convert LookML models into Cube’s data modeling language and assist your migration from Looker to Cube. In case this tool is relevant to your data stack, please feel free to [check the documentation](https://cube.dev/docs/reference/python/lkml2cube) and give it a try.

Lastly, you're always very welcome to join our [Slack community](http://slack.cube.dev/) of more that 9,000 data practitioners and share your feedback on this update.

Paco Valdez

Looker

Supporting the transition to Cube's semantic layer from legacy solutions

Introducing a tool for Looker to Cube migration

The ongoing story of the digital age is still about [taking products apart](https://hbr.org/2014/06/how-to-succeed-in-business-by-bundling-and-unbundling). The wave of [bundling](https://towardsdatascience.com/the-great-data-debate-unbundling-or-bundling-7d7721ee8514), [unbundling](https://blog.fal.ai/the-unbundling-of-airflow-2/), and [rebundling](https://dagster.io/blog/rebundling-the-data-platform) happening in the data space has subsided, but the point remains. Generations of tools in the modern data stack, whatever it is, succeed each other primarily for two reasons:

- Emerging tools provide more flexibility and a lower entry barrier for practitioners.
- Alternatively, teams escape into a safe harbor of a well-known integrated solution that promises big returns on even bigger investments. Indeed, has anyone ever got fired for buying Looker?

Today, we’re going to talk about Cube and Apache Superset—a bundle of tools representing a finer alternative to Looker in many use cases.

> We've had a webinar on replacing Looker with Cube and Superset. [Check the recording and slides.](https://cube.dev/events/replacing-looker-with-cube-and-superset)

## Free as in “open source”

[Cube](https://cube.dev) and [Superset](https://superset.apache.org) belong to different categories in the data stack. The former is the [headless business intelligence](https://cube.dev/blog/headless-bi) platform, and the latter serves as the data visualization and exploration platform. However, being open source and standing on the shoulders of giants are among many things that they have in common.

Both tools are licensed under Apache License 2.0 and actively developed on GitHub. Over the last 3 years, Cube’s [repository](https://github.com/cube-js/cube.js) has acquired almost 14,000 stars (an unmatched [leader in the category](https://twitter.com/sspaeti/status/1570044606338990081)). Written mostly in Rust, Cube’s data processing and storage are based on the [Arrow DataFusion](https://github.com/apache/arrow-datafusion) query execution framework which uses [Apache Arrow](https://arrow.apache.org/) as its in-memory format. Superset has a longer history (born at Airbnb in 2015 where it replaced Tableau, joined the Apache Incubator program in 2017), has a [repository](https://github.com/apache/superset) with 48,000 stars, and is powered by [Apache ECharts](https://echarts.apache.org/en/index.html), one of the most mature and popular charting libraries. 

![Cube and Superset](https://ucarecdn.com/5f3f2313-c055-4b9e-b345-56568c53aca2/)

You’re definitely free to take and use Cube and Superset at your own discretion, including self-hosting them in your private or public cloud. You’re also free to explore and get started with [Cube Cloud](https://cube.dev/cloud) and [Preset Cloud](https://preset.io/product/), fully managed platforms with freemium pricing models and plenty of quality of life features on top of open source products. Compare that to Looker’s “[request a demo](https://cloud.google.com/resources/looker-demo/?_ga=2.157282873.347220352.1663670208-344442256.1663670208)” paywall that would cost your team a few weeks of qualification calls on Google Meet before you start building.

## Simple as in “separation of concerns”

Cube is intentionally [headless](https://cube.dev/blog/headless-bi) and agnostic to the data presentation layer. However, it does a number of things and does them well:

- connects to a plethora of [data sources](https://cube.dev/docs/config/databases), from cloud data warehouses like BigQuery, Snowflake, and Redshift to streaming platforms like Kafka and Redpanda;
- provides a [data modeling](https://cube.dev/docs/schema/getting-started) layer as the centralized source of truth for metrics definitions;
- secures data with its [access control](https://cube.dev/docs/security) layer;
- makes sure any query runs well under a second with its [caching](https://cube.dev/docs/caching) layer;
- delivers data to any downstream tool, including BI tools like Superset, via its API layer that provides [SQL API, REST API, and GraphQL API](https://cube.dev/docs/config/downstream#ap-is-references).

Superset works by connecting to a data source and querying the data where it lives. The ideal way to use Superset is with a [dataset-centric approach](https://preset.io/blog/dataset-centric-visualization/) to modeling and visualization. 

A dataset-centric approach implies that a well-normalized dataset is used to “derive” richer datasets with extra semantics, making them valuable for analysis and visualization. By creating derived datasets that incorporate aggregate metric calculations earlier in the ETL/ELT process *before* surfacing for visualization, many of the issues with change management, maintenance, logic reuse within dashboards, etc. are significantly reduced for a better experience. 

Cube complements Superset in this way, and this allows for Superset-generated SQL queries to work with no issues when connected to a Cube environment (which is treated as a PostgreSQL database connection). 

![Looker vs Cube + Superset](https://ucarecdn.com/59031c52-890f-402f-9400-0d0d7034c887/)

You’re free to configure and fine-tune each tool and each layer within every tool when building with Cube and Superset. Want to build and schedule a report? Check Superset dashboards. Need to expose new metrics in that report? Cube’s data modeling layer is at your service.

## Compatible as in “no vendor lock-in”

Speaking of data modeling, Cube provides a [data modeling language](https://cube.dev/blog/open-source-data-modeling) that is not tied to any specific interface, visualization, or user experience. Unlike proprietary LookML definitions that require Looker IDE for syntax highlighting and linting, Cube’s data model is written in JavaScript (and [soon-to-be-written](https://forms.gle/4CZRRKK8nD4PvtxZA) in YAML) and feels native in your editor of choice. On top of that, you are able to generate a data model dynamically or even fetch it from a remote endpoint.

Consider an excerpt of Looker view from the recent [deep dive](https://pedram.substack.com/p/what-is-the-metrics-layer) by Pedram:

```yaml
view: workspace_activation {
  sql_table_name: "METRICS"."WORKSPACE_ACTIVATION" ;;

  dimension_group: date {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year
    ]
    sql: ${TABLE}."REPORTING_DATE" ;;
  }

  dimension: is_active_workspace {
    type: yesno
    sql: ${TABLE}."IS_ACTIVE_WORKSPACE" ;;
  }

  dimension: workspace_id {
    type: string
    primary_key: yes
    sql: ${TABLE}."WORKSPACE_ID" ;;
  }

  measure: count_workspaces {
    type: count_distinct
    description: "# of Workspaces"
    sql: ${workspace_id} ;;
  }

  measure: count_active_workspaces {
    type: count_distinct
    description: "# of Unique Workspaces Active within 1 Day"
    sql: ${workspace_id} ;;
    filters: [is_active_workspace: "yes"]
  }

  measure: activation_rate {
    type: number
    sql: ${count_active_workspaces} / ${count_workspaces} ;;
    value_format_name: percent_1
  }
}
```

Compare with the equivalent Cube data model (way less of `;;`, to say the least):

```javascript
cube(`WorkspaceActivation`, {
  sql: `SELECT * FROM public.active_workspace_details`,

  measures: {
    count: {
      type: `count`
    },

    n_workspaces: {
      sql: `workspace_id`,
      type: `countDistinct`,
    },

    n_active_workspaces: {
      sql: `workspace_id`,
      type: `countDistinct`,
      filters: [ {
        sql: `${is_active_workspace} IS TRUE`
      } ],
    },

    activation_rate: {
      sql: `ROUND(100 * (1.0 * ${n_active_workspaces} / ${n_workspaces}), 2)`,
      type: `number`
    }
  },

  dimensions: {
    workspace_id: {
      sql: `workspace_id`,
      type: `number`,
      primaryKey: true
    },

    reporting_day: {
      sql: `reporting_day`,
      type: `time`
    },

    is_active_workspace: {
      sql: `is_active`,
      type: `boolean`
    }
  },

  preAggregations: {
    main: {
      measures: [ activation_rate ],
      timeDimension: reporting_day,
      granularity: `week`,
      refreshKey: {
        every: `1 day`
      }
    }
  }
});
```

In a similar fashion, Cube’s API layer is compatible with practically any data consumer. When the time comes, and you realize that, alongside business users using dashboards and reports in Superset, you’d like to have data analysts crunching numbers and seeking insights in data notebooks, Cube will be there to help liberate your data.

![Data consumers](https://ucarecdn.com/b35f44ef-12a5-42d7-a618-74637fc3e0cd/)

You can expect any SQL query generated by your data exploration tool like Superset to work with Cube’s SQL API. Needless to say, equivalent queries to Cube’s REST API and GraphQL API will yield the same data:

```sql
SELECT
  DATE_TRUNC('WEEK', reporting_day) AS reporting_week,
  activation_rate
FROM WorkspaceActivation
ORDER BY 1 DESC;
```

Let’s have a bird’s-eye view here.

The generation of BI tools like Looker encouraged end-users to invest heavily in building out tons of LookML models to populate the semantic layer. While this established Looker as a source of truth in an organization for business metrics, it also created an immense amount of lock-in in your BI tool. You couldn’t take your LookML models with you and use them with another tool if you decided to switch BI tools. Google’s acquisition of Looker accelerated organizations’ anxieties around this lock-in, to the point that Google was actually [forced to integrate LookML with Tableau](https://roundup.getdbt.com/p/look-ml-tableau-metrics-layer). This way, organizations could use LookML for transformation (Looker’s strength) and Tableau for visualization (Tableau’s strength).

The future however is looking to be very different from the past:

- Open-source headless BI platforms like Cube provide unified semantic layers to sit between the database and BI later, eliminating the need, or providing alternate implementations, for expensive solutions like Looker that lock you in.
- Open-source BI tools like Superset with an intentionally thin semantic layer complement Cube and enable *last-mile data transformation* for the explicit purpose of data visualization in your BI tool.

When the need for a complex yet flexible metrics/semantic layer arises, Cube and Superset is a winning open source-based combo!

## Ready-to-go as in “today”

Now, you’re very much welcome to try Cube and Superset today and see them in action. Please join the Slack communities of [Cube](https://slack.cube.dev) and [Superset](https://join.slack.com/t/apache-superset/shared_invite/zt-16jvzmoi8-sI7jKWp~xc2zYRe~NqiY9Q) to share your feedback.

Please tune in to the [webinar on October 4](https://cube.dev/events/replacing-looker-with-cube-and-superset) with [Shreesham Mukherjee](https://www.linkedin.com/in/shreesham/), Developer Relations Engineer at Preset, and [Igor Lukanin](https://www.linkedin.com/in/igorlukanin/), Head of Developer Relations at Cube. We’ll discuss Cube and Superset as an open source alternative to Looker in popular use cases, demo both tools, and take your questions.

See you!

Looker

Introducing a tool for Looker to Cube migration

Open source Looker alternative with Cube and Apache Superset

Get Cube updates to your inbox

Upgrade your data stack today