Observable tutorial: Analyze information in a JavaScript-native information pocket book

Over the previous few months, the Observable crew has celebrated the first anniversary of the Observable Plot charting library, launched the Knowledge Desk Cell sort, and supercharged SQL Cells with abstract charts.

Let’s examine how information notebooks, constructed with Observable, can work with Dice and fetch information from the just lately up to date SQL API in addition to the REST API.

What’s Observable?

Observable is collaborative information canvas that has just a few distinctive options in comparison with different trendy information notebooks available on the market:

1. Observable implements the rules of interactive and reactive information exploration popularized by Bret Victor in his visionary talks equivalent to Inventing on Precept and Media for Pondering the Unthinkable.
2. Observable relies on JavaScript which isn’t so frequent within the Python-dominated information notebooks house. Additionally, it natively works with Observable Plot, an open-source JavaScript library for visualization of tabular information based mostly on D3.js. Not that shocking provided that Observable is co-founded by Mike Bostock, the creator of D3.js.
3. Observable supplies a super-rich set of cell varieties in addition to an entire library of tutorials and examples, templates, and notebooks contributed by a neighborhood of creators.

Observable notebooks run within the cloud and in your net browser. They will also be embedded as an entire and as particular person blocks.

What’s Dice?

Dice is the headless BI platform that sits between your information sources (e.g., information warehouses) and downstream information functions (e.g., information notebooks). Due to its place within the information stack, Dice is ready to present constant metrics and efficiency for all of your information flowing downstream.

Dice works with all trendy information shops, creates a metrics layer with constant metrics definitions, and delivers them downstream to any information pocket book, software, or BI instrument through its SQL, REST, or GraphQL APIs.

Prolonged connectivity. Observable natively helps fetching the info from an API and a lot of databases: BigQuery, Snowflake, Postgres, and MySQL. With Dice, you possibly can broaden the vary of obtainable information sources to nearly any information warehouse, question engine, or database (e.g., Athena, ClickHouse, Databricks, or Redshift).

Consistency for Knowledge Explorers

One of many advantages of utilizing Observable and Dice collectively is offering a standard floor for all groups working collectively inside a corporation.

It isn’t unusual that inconsistent information is getting used throughout totally different groups inside an organization. It typically originates to various metrics definitions throughout the numerous instruments that every crew makes use of. In some unspecified time in the future, groups understand that they use totally different SQL queries to calculate metrics and, sadly, mislead their customers with inconsistent insights, regardless of utilizing the identical information.

Let’s undergo an excellent high-level instance. Think about each the product and buyer success groups wish to calculate product success “during the last 12 months”. The product crew queries the 2021 information (01/01/2021 to 12/31/2021), whereas buyer success queries it as year-over-year (06/01/2021 to 06/01/2022).

For the reason that information pipelines of each groups are siloed, neither crew essentially has visibility into how the opposite outlined the question; it’s possible these groups use totally different instruments too. This discrepancy in question outcomes can completely misalign the groups working in direction of a single aim, however working off of disparate information.

In case of Dice used upstream of Observable notebooks and all different information functions, a misalignment has no likelihood to occur. Dice will ship constant metrics through its varied APIs to all instruments.

Defining Constant Metrics

Let’s see how Dice enhances Observable within the information pipeline. On this instance, we’ll use a e-commerce dataset and construct a knowledge mannequin round it. We’ll additionally customise the info mannequin to show how Dice helps keep and evolve metrics definitions.

Working Dice. To maintain issues easy and save time, we’ll run totally managed Dice in Dice Cloud. Please proceed to Dice Cloud’s enroll web page and comply with alongside the directions to attach your individual information supply and generate a knowledge mannequin.

For comfort, you can even use the next credentials to stand up and working very quickly:

Hostname:  demo-db-examples.dice.dev
Port:      5432
Database:  ecom
Username:  dice
Password:  12345

That is what your deployment in Dice will appear to be:

Defining the info mannequin. To see the info mannequin you created above, navigate to the Schema tab. You will notice recordsdata named LineItems.js, Orders.js, Customers.js, and many others. below the schema folder which defines the cubes in your information mannequin.

Key concepts on what we’re seeing above:

• The dice is a logical entity that teams measures (quantitative information) and dimensions (qualitative information) collectively into metrics definitions.
• On this case, we now have outlined a dice over your entire public.line_items desk.
• Measures are outlined as aggregations (e.g. depend, sum, and many others.) over columns within the desk.
• Dimensions are outlined over textual, numeric, or temporal columns within the desk.
• You’ll be able to outline complicated measures and dimensions with customized SQL statements and references to different measures and dimensions.

Customizing the info mannequin. We’ll outline a totally new rolling window measure within the information mannequin.

Let’s assume you wish to calculate the whole income introduced in from bought line gadgets over all time. This may be completed by writing a SQL question in your native Jupyter pocket book or another BI instrument you could be utilizing. However what occurs if you wish to observe the whole income over totally different time granularities? Or add one other dimension to look at the income information?

That is the place your LineItems dice will be modified to summary away your various information requests. Merely outline the metrics in your information mannequin as soon as, after which use them repeatedly throughout groups to realize what you are promoting targets.

For this instance, we’ll customise the info mannequin within the cloud by including the “complete income” as a measure to the LineItems dice.

income: {
  sql: `worth`,
  sort: `sum`,
  rollingWindow: {
    trailing: `unbounded`,
  },
}

Whereas Dice Cloud has a ton of time-saving options, we’ll use a very helpful one now. Click on on Enter Improvement Mode to unlock the schema recordsdata for modifications. This basically creates a growth setting (or “department”) the place you can also make modifications to the info mannequin.

The next code snippet reveals the contents of the LineItems.js file (which defines the metrics for the LineItems dice) with the income rolling window measure added to the measures part.

Navigate to the LineItems.js file and change its contents with the snippet beneath.

dice(`LineItems`, {
  sql: `SELECT * FROM public.line_items`,
  joins: {
    Merchandise: {
      sql: `${CUBE}.product_id = ${Merchandise}.id`,
      relationship: `belongsTo`
    },
    Orders: {
      sql: `${CUBE}.order_id = ${Orders}.id`,
      relationship: `belongsTo`
    }
  },
  measures: {
    depend: {
      sort: `depend`,
      drillMembers: [id, createdAt]
    },
    amount: {
      sql: `amount`,
      sort: `sum`
    },
    worth: {
      sql: `worth`,
      sort: `sum`
    },
    income: {
      sql: `worth`,
      sort: `sum`,
      rollingWindow: {
        trailing: `unbounded`,
      }
     }
  },
  dimensions: {
    id: {
      sql: `id`,
      sort: `quantity`,
      primaryKey: true
    },
    createdAt: {
      sql: `created_at`,
      sort: `time`
    }
  }
});

Click on Save All to use the modifications to the event model of your API. Lastly, click on Commit & Push to merge your modifications again to the principle department. You will notice your modifications deployed within the Overview tab.

Superior! You’ve simply constructed a custom-made information mannequin to fit your mission. The following half includes bringing your information to Observable.

Connecting Observable to Dice

Dice affords three totally different APIs to ship information (SQL, REST, and GraphQL) to downstream functions. SQL API and REST API are each nice choices to entry Dice from Observable notebooks, with their very own execs and cons.

Utilizing SQL API

Clearly, the very first step is to enroll for an Observable account if you do not have one already. Then, let’s create a brand new clean pocket book:

To learn information from the SQL API, we have to add the Database question cell:

After including the cell, click on Choose a database… after which + Create database to navigate to the Databases web page in Settings:

On that web page, click on on + New database after which navigate again to Dice Cloud for a second. On the Overview tab, click on on Deploy SQL API to generate your credentials:

You’ll be able to then enter these credentials to the New database popup in Observable. For comfort, you can even use the next credentials:

Host:      grim-dola.sql.aws-us-west-2.cubecloudapp.dev
Port:      5432
Database:  grim-dola
Username:  dice
Password:  4457642218d9552e74db8bafff1e3047

Now you possibly can navigate again to your pocket book and choose Dice from the dropdown in your Database question cell:

Now you possibly can run queries towards the SQL API:

It is price noting that there is one other cell sort referred to as Knowledge desk which may be very handy. Just like Database question, you possibly can choose Dice because the database. Nonetheless, you need not write the SQL by hand. You’ll be able to proceed by deciding on a desk, representing to a dice in your information mannequin:

Then, you possibly can filter, choose choose measures and dimensions, kind, and slice the info. Observable will generate the SQL for you below the hood:

To visualise the info, you must give the cell a reputation (e.g., orders_by_date) and choose from a plethora of obtainable chart varieties:

If wanted, you can even apply a last-mile transformation with JavaScript:

orders_by_date_t = orders_by_date
  .map(e => ({ ...e, depend: parseInt(e.depend) }))

Then, you’ll in all probability choose and customise one of many many Observable Plot snippets:

Plot.plot({
  marks: [
    Plot.ruleY([0]),
    Plot.lineY(orders_by_date_t, {x: "createdAt", y: "depend"})
  ]
})

Alternatively, you possibly can import and use Vega-Lite library which is broadly standard and utilized by 1000’s of information fanatics:

import {vl} from "@vega/vega-lite-api"
vl.markLine()
  .information(orders_by_date_t)
  .encode(
    vl.x().fieldT("createdAt").timeUnit("utcyearmonthdate").title("Date"),
    vl.y().fieldQ("depend").title("Rely")
  )
  .render()

If you would like to get inspiration for extra superior information visualizations that you may construct with Observable, try the obtainable templates and featured works by the Observable neighborhood. Additionally, take into account diving deep into Observable Plot library, the Swiss knife of information visualization in Observable.

Utilizing REST API

Now let’s examine how one can additionally learn information from the REST API. First, we have to add the generic JavaScript cell:

Then, let’s navigate again to Dice Cloud. On the Overview web page, we will copy the endpoint URL for the REST API:

This is what I’ve copied from my Dice Cloud deployment:

https://awesome-ecom.gcp-us-central1.cubecloudapp.dev/cubejs-api/v1

You will additionally must generate a long-lasting JSON Net token. (For those who want a hand, pleae examine the Dice docs on JWT technology.) This is what I’ve generated for my deployment:

eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjEwMDAwMDAwMDAsImV4cCI6NTAwMDAwMDAwMH0.OHZOpOBVKr-sCwn8sbZ5UFsqI3uCs6e4omT7P6WVMFw

Now we’re able to insert this code snippet within the JavaScript cell:

orders_over_time = fetch("https://awesome-ecom.gcp-us-central1.cubecloudapp.dev/cubejs-api/v1/load", {
    methodology: 'POST',
    headers: {
      'Authorization': 'Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjEwMDAwMDAwMDAsImV4cCI6NTAwMDAwMDAwMH0.OHZOpOBVKr-sCwn8sbZ5UFsqI3uCs6e4omT7P6WVMFw',
      'Content material-Kind': 'software/json'
    },
    physique: JSON.stringify({
      question: {
        measures: ['Orders.count'],
        timeDimensions: [{
          dimension: 'Orders.createdAt',
          granularity: 'month'
        }]
      }
    })
  })
  .then(response => response.json())
  .then(json => json.information)
  .then(information => information.map(e => ({
    depend: parseInt(e["Orders.count"]),
    createdAt: d3.utcParse(e["Orders.createdAt.month"])
  })))

Let’s spotlight just a few issues right here:

• We have to append /load to your API URL specified as a fetch argument.
• We have to add the token subsequent to the Bearer a part of the Authorization header.
• We have to put the Dice question contained in the JSON.stringify name.

It is sufficient to make an API name and fetch the info. Similarly, this is how one can visualize the info with Vega-Lite:

my_published_cell = vl.markLine()
  .information(orders_over_time)
  .encode(vl.x().fieldT("createdAt").timeUnit("utcyearmonth").title("Date"),
          vl.y().fieldQ("depend").title("Orders"))
  .render()

Selecting an API to make use of

It’s best to choose an API with these execs and cons in thoughts:

Dice SQL API:

• permits to make use of Database question cells the place you possibly can write concise SQL queries
• permits to make use of Knowledge desk cells the place you possibly can compose queries within the UI
• requires a separate service to arrange multitenancy and fetch safety context
• will not work in printed Observable notebooks on a free plan (Observable turns database connections off after a pocket book on a free plan is printed)

Dice REST API:

• requires to make use of JavaScript cells to put in writing the code to fetch the info which may typically get overly verbose
• permits to move safety context in a JSON Net Token for multitenancy
• will work in printed Observable notebooks on a free plan

Nonetheless, since Observable has very sound pricing plans, compatibility with printed notebooks shouldn’t be a sole deciding issue right here.

Embedding Observable notebooks

Observable supplies a really wealthy set of embedding choices, far exceeding what different information notebooks present by way of customizability.

Pocket book. Clearly, you possibly can publish your entire pocket book by clicking Publish… on the highest of your pocket book.

As an example, you possibly can browse the printed pocket book that we have constructed throughout this tutorial. (Word that cells utilizing the SQL API do not work as a result of the pocket book is owned by an account on the free plan.)

Cells. You’ll be able to publish a person cell or a set of cells as properly. An fascinating quirk is that you may solely publish a cell if it has a reputation: to do this for JavaScript cells, assign the contents to a variable. Then, you possibly can click on Embed within the dropdown menu:

You’re free to decide on whether or not you are publishing all cells within the pocket book, a subset of cells, or only a single one:

Then, you must select between a number of highly effective embedding choices:

1. Iframe will provide you with a code snippet that you may Copy and embed into any HTML doc, together with a web page in your content material administration system like Gatsby and even one other Observable pocket book. Wrapped into an iframe tag, the code and types of embedded content material are totally remoted out of your software.
2. With Iframe, you can even Copy URL solely and paste it into environments that help the oEmbed commonplace, e.g., Notion, Medium, or Reddit.
3. Runtime with JavaScript will provide you with a code snippet that you may embed into an HTML doc as properly. The distinction is that it’ll load a tiny JavaScript library and render the cell in place, supplying you with the liberty to customise how the cell is styled and displayed.
4. Runtime with React works equally, the one distinction is that your cell is represented as a React part. For those who’re constructing a React app, it is essentially the most native option to embed a cell into it.

Wrapping up

For those who’ve picked Observable as your information pocket book of selection, think about using it with Dice. It’s going to present constant metrics on your information evaluation and information apps you will construct and publish.

Would you wish to be taught extra about Dice? Examine the documentation and create a free Dice Cloud account immediately.

Additionally, please be happy to be a part of our neighborhood of greater than 6000 builders and information engineers on Slack, drop us a line, or give Dice a star on GitHub. Good luck!