@verybigthings/semantic-layer

Quick Start Guide

Welcome to the semantic layer library! Let's dive in and create a simple data model in just a few steps.

Installation

First, let's get the library installed (use npm or a package manager of your choice):

npm install @verybigthings/semantic-layer

Building Your First Semantic Layer

Imagine you're running a music store. You have customers, and they make purchases. Let's model this!

Step 1: Create Your Models

We'll create two models: customers and invoices.

import * as semanticLayer from "@verybigthings/semantic-layer";

// Our Customers model
const customersModel = semanticLayer
  .model()
  .withName("customers")
  .fromTable("Customer")
  .withDimension("customer_id", {
    type: "number",
    primaryKey: true,
    sql: ({ model, sql }) => sql`${model.column("CustomerId")}`,
  })
  .withDimension("first_name", {
    type: "string",
    sql: ({ model }) => model.column("FirstName"),
  })
  .withDimension("last_name", {
    type: "string",
    sql: ({ model }) => model.column("LastName"),
  });

// Our Invoices model
const invoicesModel = semanticLayer
  .model()
  .withName("invoices")
  .fromTable("Invoice")
  .withDimension("invoice_id", {
    type: "number",
    primaryKey: true,
    sql: ({ model }) => model.column("InvoiceId"),
  })
  .withDimension("customer_id", {
    type: "number",
    sql: ({ model }) => model.column("CustomerId"),
  })
  .withMetric("total", {
    type: "number",
    description: "Invoice total.",
    sql: ({ model, sql }) => sql`SUM(COALESCE(${model.column("Total")}, 0))`,
  });

Step 2: Create a Repository

Now, let's put these models together in a repository:

const repository = semanticLayer
  .repository()
  .withModel(customersModel)
  .withModel(invoicesModel)
  .joinOneToMany(
    "customers",
    "invoices",
    ({ sql, models }) =>
      sql`${models.customers.dimension(
        "customer_id"
      )} = ${models.invoices.dimension("customer_id")}`
  );

Step 3: Build a Query

With our repository set up, we can now build queries:

const queryBuilder = repository.build("postgresql");

const query = queryBuilder.buildQuery({
  members: [
    "customers.customer_id",
    "customers.first_name",
    "customers.last_name",
    "invoices.total",
  ],
  order: { "customers.customer_id": "asc" },
  limit: 10,
});

Step 4: Execute the Query

The query object contains the SQL string and bindings. You can use these with your preferred database client:

const result = await someSqlClient.query(query.sql, query.bindings);

For example, with the pg package for PostgreSQL:

const result = await pg.query(query.sql, query.bindings);

And there you have it! You've just set up a semantic layer for your music store data. This layer will make it easy to analyze customer purchases without writing complex SQL queries each time.

Read the documentation for more information.

Acknowledgments

@verybigthings/semantic-layer draws inspiration from several BI libraries, particularly Cube.dev. While our API was initially inspired by Cube.dev, it has since diverged based on our own needs and preferences.