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.
| Type | Worksheet has formulas? | Worksheet has dependents? | Worksheet is dependent? |
|---|---|---|---|
| input | no | yes | no |
| isolated | yes | no | no |
| notes | no | no | no |
| transient | yes | yes | yes |
| output | yes | no | yes |
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.
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.
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.
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.
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.
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.
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.
- Transformative directives transform worksheets into other worksheets
- Annotative directives direct the Pebble Stream runtime's behavior when encountering column and row values
- Generative directives instruct the Pebble Stream runtime on how to generate cells in a given worksheet.
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.
Updated almost 2 years ago