Getting Started with Pebble Stream

This page will help you get started with Pebble Stream.

📘

Pebblers are spreadsheet users that design Pebble Stream compatible spreadsheets.

We believe Pebblers are bonafide software developers, and the spreadsheet is the perfect Integrated Development Environment (IDE)!

Introduction

This document contains information a business analyst would need in order to convert an Excel spreadsheet into cloud-enabled software.

It covers Pebble Stream’s capabilities and limitations, as well as best practices for creating performant spreadsheets.

It also introduces the powerful concept of spreadsheet directives.

Philosophy

Business Analysts use Excel to create models to solve business problems.

Traditionally, business analysts relied on software developers to convert Excel models into cloud executable software.

Pebble Stream technology automates the conversion of models written in Excel into cloud executable software called pebbles. Business analysts that are hip to Pebble Stream development are called Pebblers.

Developers can embed and run pebbles in their applications using the Pebble Stream runtime.

Methodology

Pebble Stream treats spreadsheets as functions. By definition, functions take input data, perform operations on that input data, then produce output data. Pebblers use the Pebble Stream compiler to compile spreadsheets into libraries that software developers use as functions. These compiled spreadsheets are called Pebbles.

Software developers can embed Pebbles in cloud applications using the Pebble Stream runtime. The Pebble Stream runtime is available in several languages, including Java, Groovy, Scala, Clojure, C, C++, C#, and JavaScript.

Upon compilation, the Pebble Stream compiler classifies worksheets into five different types. These worksheets are categorized based on whether or not the worksheet has a formula and its relationship to other worksheets.

Input

Input worksheets contain no formulas. They only have values in their cells. Input worksheets are not dependent on any other worksheets in the spreadsheet.

Developers must provide input worksheets to the Pebble Stream runtime when executing a Pebble. If software developers do not provide an input worksheet on Pebble execution, the Pebble Stream runtime will use the templated input by default. This behavior is configurable.

Isolated

Isolated worksheets are worksheets that are not dependent on any other worksheet in the spreadsheet. No other worksheets in the spreadsheet are dependent on isolated worksheets. They may contain formulas or values in their cells.

Notes

Notes are a form of isolated worksheets, except they do not contain formulas in their cells. The Pebble Stream compiler recognizes notes as a constant.

Transient

Transient worksheets depend on other worksheets, while other worksheets reference transient worksheets. Typically, transient worksheets perform most of the computations in a spreadsheet.

Output

Output worksheets are the final worksheets created in a spreadsheet's computation. Output worksheets reference other worksheets in the spreadsheet, but no other worksheet's computation depends on output worksheets.

TypeWorksheet has formulas?Worksheet has dependents?Worksheet is dependent?
inputnoyesno
isolatedyesnono
notesnonono
transientyesyesyes
outputyesnoyes

📘

Pebbles can be programmatically inspected.

Software developers can programmatically inspect Pebbles to determine its expected input and expected output.

Worksheet Design

Spreadsheet users are used to creating free-formed worksheets with many contiguous sections of calculations that perform different but related operations. The diagram below provides a high-level representation of this type of worksheet creation.

923

Irregular worksheet designs are not compatible with efficient spreadsheet computing.

Pebblers avoid this type of computation. Designing worksheets in this manner is problematic for several reasons. One reason is that worksheets so organized are poor candidates for interoperability with enterprise data stores. Another reason is that this haphazard arrangement counters software developers' ability to use the worksheet as a computing function.

❗️

Avoid irregular worksheet presentations

Irregular worksheets are inefficient to compute, cannot be used with Pebble Stream directives, and cannot interoperate with cloud and enterprise data stores.

Instead, Pebblers create rectangular-shaped worksheets for efficient computation. These worksheets are tabular in presentation, with the first row representing a header for each column and subsequent rows containing the calculations.

The following illustrates this standard and optimal worksheet layout.

932

Pebblers design compact worksheets that are easily imported into relational or key-value stores.

Pebblers should arrange worksheets in a rectangular manner. The reasons for this follow:

  • Rectangular worksheets ease workflow creation, that is, the automatic wiring of pebbles’ outputs to other pebbles’ inputs.
  • Rectangular worksheets can be treated as black-boxed functions or enclosed libraries by software developers.
  • Rectangular worksheet data is easily transferred to and from database tables.
  • Rectangular worksheets can be mapped to a data ontology. Whenever Pebblers map worksheets to an ontology, worksheet information may be automatically copied to and from enterprise or cloud data stores.
  • Rectangular worksheets run faster.

👍

Pebblers should create regular rectangular shaped worksheets

These worksheets can be computed quickly, operated on by Pebble Stream directives, and can interoperate with other applications and enterprise and cloud data stores.

Supported Spreadsheet Functions

Pebble Stream supports the popularly used spreadsheet functions.

We are adding more functions every day. Our intent is to support the vast majority of spreadsheet functions available in Excel, LibreOffice, OpenOffice, and Google Sheets.

If there is a function you would like to use that we do not currently support please contact technical support.

Excel Errors

The Pebble Stream runtime will immediately stop the execution of a worksheet if any formula cell returns any of Excel’s standard error codes such as:

#N/A   #DIV/0   #NAME?   #NULL!   #NUM!   #REF!   #VALUE! #SPILL!

This ensures unexpected errors are not allowed to cascade throughout the spreadsheet.

Use Excel’s IFERROR function to catch expected errors. This will allow Pebble Stream to continue processing.

Unsupported Excel Features

Pebble Stream does not support the compilation or execution of the following Excel spreadsheet functionality:

  • Array formulas
  • @ symbol
  • VBA
  • Goal seek and subtotal functions
  • Pivot tables, images, and charts
  • Worksheet cycles
  • Spillable functions

The choice to not support these Excel features was deliberate. Consider creating a new worksheet or pebble instead in all of these cases.

Worksheet Dependencies

Pebble Stream does not allow worksheets to reference worksheets that are not yet computed. If worksheet cycles are detected during the compilation of a spreadsheet a time travel error will be reported.

160

Spreadsheets with cyclical worksheet dependencies are not allowed. We call this time traveling.

Instead, Pebblers should strive to create spreadsheets with worksheets that do not have cyclical dependencies.

241

A spreadsheet with no cyclical worksheet dependencies.

Cyclical worksheet dependencies may not be as evident as in the first example. Time travel issues can sneak in if the Pebbler is not careful. Here is a more complicated example. Note that isolated worksheets are OK.

371

Cycles may span more than 1 worksheet. Pebblers avoid cycles in order to have a successful spreadsheet compilation.

Here is a more complicated spreadsheet design that does not contain a worksheet dependency cycle. Pebblers often create spreadsheets with worksheet dependencies as complicated as this illustration. Pebble Stream can efficiently compute spreadsheets with similar worksheet dependencies.

332

Even with the time traveling constraint in place, Pebblers can create complicated workflows

Pebble Stream Directives

Pebble Stream introduces the powerful concept of computing directives to spreadsheet computing.

Analysts use directives to control how the runtime performs drag downs, detects failures, ensures correct inputs, and produces outputs. Analysts can also use directives to generate new worksheet models from existing worksheets.

There are three categories of directives, Transformative, Annotative, and Generative.

Pebblers declare directives using the general form “pebblestream:". Directives may also take parameters.

Analysts use Excel’s commenting feature to embed directives in the columns' headers (row 1).

You may embed multiple directives in a single comment.

Analysts may also add additional comments along with a Pebble Stream directive.