Today, we're happy to support Looker users switching over to Cube and present lkml2cube, a tool for converting LookML models into the Cube data model. With this tool, you can easily migrate your existing 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. Now, we’re happy to put it into open source to assist more Looker users in their migration over to Cube.

Given Cube’s current position as a leader and fast-mover among semantic layers, there’s no surprise we see more and more companies migrating to Cube from their legacy solutions:

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 represent tables of data in Cube and are roughly equivalent to views in LookML. However, cubes are a bit more powerful since they can contain join and pre-aggregation definitions—the latter are used to provide granular query acceleration configuration and inform Cube’s aggregate awareness.
  • Views sit on top of the data graph of cubes and create a facade of your whole data model; they are roughly equivalent to explores 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 includeparameter, and generate necessary cubes and views:

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

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

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:

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...

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:

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 and give it a try.

Lastly, you're always very welcome to join our Slack community of more that 9,000 data practitioners and share your feedback on this update.