Introduction

In a typical scenario a model designer creates a multidimensional spreadsheet ( a model ) and shares it with other (basic) users who fill the model with data. This guide is primarely aimed at model designers, as entering numbers into the worksheets is self explanatory.

Creating a model typically involves the following steps:

  1. Create lists and list items to represent business objects and measures of the model. List items can also be imported from a CSV file.
  2. Create multidimensional worksheets using lists from the previous step as its dimensions and measures
  3. Create worksheet formulas to perform computations
  4. Create cell formatting rules to improve readability
  5. Use cell visibility rules in combination with buttons to create workflows and advanced functionality
  6. Share the model with other users
  7. Import existing data into the worksheets using via CSV files

The steps are explained in detail in the following chapters.

Lists

Lists represent important business objects for your model.

Some examples of lists are:

  • Products with items like: Lemonade, Bubble gum, ...
  • Months with items: Jan, Feb, ...
  • Geographic locations with items: Berlin, Paris, ...

List items can also have attributes attached to them (like if a product is active or discontinued). The attributes are represented as columns in the list view. Attributes are mostly used to control the formulas or to logically connect items from two different lists.

Lists can be found under the 'Lists' menu. Select a list from the menu to edit its items.

New lists are created using the ListsCreate list menu item.

List screen

List screen shows list settings on the left and a table with list items on the right.

Each item has a unique identifier (first column). It can be used to reference the item from a formula or to identify it during data import. The identifier can be changed. Latin letters, numbers, underscore(_) are allowed. Items's caption (second column) is an optional text which describes the item.

Use the context menu to add/remove items to the list. The context menu can also be used to create or remove list attributes (columns).

Measures

Measure (metrics, fact or position) is a quantity which is relevant for your model such as number of units sold, revenue or costs. Measures are represented as a special list inside the system.

Click the the ListsMeasures menu item to access the measures screen. Note: Measures list can be renamed, but it will always be displayed as the first list in the menu. Adding or removing measures works exactly the same way as adding/removing other list items.

Worksheets

Multidimensional worksheets (or cubes) are used to store the bulk data of the model. Worksheets also contain formulas and formatting rules.

A normal spreadsheet has two dimensions: rows and columns. A dimension can be seen as an abstraction of the rows and columns concept. We use lists to represent dimensions. List items are used to label single rows or columns the same way numbers and letters are used to label rows and columns in a plain spreadsheet.

A worksheet can be created using the WorksheetNew worksheet menu item. You will be asked for a set of lists to use as the worksheet dimensions and for a set of measures which should be displayed in the worksheet. Dimensions and measures can also be added or removed at a later time with WorksheetAdd dimension and WorksheetMeasures menu items.

Dimensions of a worksheet can be displayed as rows, columns or filters. The assignment can be changed by dragging and dropping a dimension to a different area of the panel in the left part of the main screen.

The filter icon on row and column dimensions can be used to further reduce the visible part of the worksheet.

Formulas

Formulas are used to compute cells of a worksheet based on the content of other cells. Right click on a cell and choose New formula menu item to create a new formula. The formula editor overlay will appear.

The formula syntax is quite similar to common spreadsheet application with the exception of two new concepts:

A major drawback of a common spreadsheet is the necessity to copy formulas as new cells are added. This happends because formulas are normally bound to individual cells. We remove this limitation by attaching the formulas to worksheets instead. We use the area of effect concept to express the part of a worksheet a formula is applied to. The 'area of effect' defines a set of list items a formula is applied to for each dimension of a worksheet. The area of effect can be expressed in a flexible way, so that a formula can be applied to all list items, to a fixed subset, to all but selected items or to items with certain attributes. This way a formula will continue to do its job after new list items are added to lists.

The unique cell reference syntax makes formulas very powerful and durable. The easies way to add a cell reference is to select a cell range with a mouse while the formula editor is active. The reference will be added to the current cursor position. While this may be enough for most of the basic cases it is important to understand how cell references work.

Cells can be accessed statically using list item identifiers, dynamically based on the order of the list items (previos, next) and dynamically based on relations between list items (through attributes).

Here is an example of a cell reference: [A:3, B.prev()]

A cell reference is a comma separated list of directives which is enclosed in square brackets. Each directive defines the targeted list item(s) along a single dimension. There are two types of directives: static and dynamic.

Static directives have the syntax <list id:list item id> or <list id:(list item id1, list item id2, ...)> and reference a fixed set of list items. Reference A:3 for example will always refer to the item with the id 3 inside the list A.

A dynamic directive has the form <source list id>.<function>(<parameters>). Reference B.prev() uses the function prev() which will refer to the list item located just before the list item which is currently beeing computed. Reference C.join(D, ATTR1) will refer to all items of the list D which relate to the C list item which is currently beeing computed via the attribute ATTR1.

Below is the complete list of the functions for dynamic references:

  • next(), prev(): functions will return the next or previous item in the list order
  • all() function will return all the items of a list
  • join(<list id>,<attribute_name>) function will return the items of the list <list id> associated with the source list via the column attribute_name. If the optional parameter attribute_name is missing the first reference attribute of the respective type will be used.

Cells from a different worksheet can be referenced by prepending a cell reference with the worksheets identifier (like: W2.[A:3]).

Cell formatting

Cell formatting rules uses the same area of effect concept as formulas. It means we do not format single cells, but create formatting rules with an area of effect. A formatting rule can be created by right clicking on a cell and selecting the Format cell menu item.

Possible cell formats are decimal numbers, date and time values, boolean values (checkbox), dropdowns (with list items as options), sliders and buttons. Cell format can either be defined statically, by selecting a respective option in a listbox or dynamically with a formula by checking the dynamic checkbox on the right side of the screen. A dynamic formatting formula must return o format object, which can be created using one the format...() functions. Some format types (like buttons and sliders) are only available through the respective format functions (like formatbutton(...)). See the functions docs for more information on formatting functions.

More information on custom format strings for number and dates can be found under the following links:

Numeric format strings

Date and time format strings

Buttons and workflows

CubeWeaver cells can be formatted to display a button. A click on the button triggers a single computation of a set of formulas on a designated cell range. The cell range is specified relative to the cell where the button was clicked. This simple mechanism can be used to model various one-way operations:

  • Workflows (submitting a range of cells): A click on a button changes the content of a cell in a hidden worksheet from 0 to 1. A cell visibility rule can be used to make a cell range read-only depending on the content of the changed cell. The submit logic can be as complex as needed with multiple submit/approve buttons visible to specific groups of users.
  • Onetime bulk changes to input values like increasing a range of input values by a certain percentage or distributing certain costs among input values.
  • Copy a range of values from one element to another like rolling forward the planning values from the previous to the next year. Or copying results of a simulation to a certain scenario.

Buttons can be created using the dynamic cell formatting (context menu → Format cell than check the dynamic checkbox on the right). The formatbutton(label, updates...) function is used to specify the button parameters. The first parameter is the label for the button. The label is followed by one or more update operations. An update is specified by the update(destination, value) function, where destination is a cell range and the value is a formula expression evaluated for each of the destination cells.

Some examples:

  • formatbutton("Submit", update([ACC:19], 1)) - will display a button labeled "Submit" which will save the value 1 into the cell [ACC:19] relative to the cell where the button was clicked.
  • formatbutton("Apply %", update([M:(1,2,3)], [] * (1 + [M:4])), update([M:4], 0)) - will display a button labeled "Apply %" which will change values in cells M:1, M:2, M:3 by the percentage stored in the cell M:4 and delete the value stored in the cell M:4 afterwards.
  • formatbutton("Fill from previous year", update(W01.[], [YEAR.prev()])) - will copy the values from the previous year to the current one (the year where the button was clicked) in the worksheet W01. The worksheet where the button is located must have a year dimension for this to work.

Cell visibility

Cell visibility rules can be used to conditionally hide a cell range or make it read-only. For example certain measures can be hidden for certain list item types. Cell visibility rules can also be used for advanced data access scenarios, when cells are dynamically hidden based on user's privileges or the values of other cells. A cell visibility rule can be created by right clicking on a cell and selecting the Cell visibility menu item.

Cell visibility rules can either be static (always hide the area of effect) or dynamic. A dynamic rule must specify a formula which returns a number as a result. The result is interpreted as follows:

  • 1 - hide cell content
  • 2 - make the cell read only
  • 3 - make the cell editable
  • 4 - do not change cell access (can be used in an if() for example)

Cell visibility rules use the same area of effect concept as formulas.

Share a model

Worksheets can be filled with data by multiple users simultaneously. Perform the following steps to share your model:

  1. Click FileShare menu item to open the sharing dialog.
  2. Create a new share with Share by link or Share with user buttons.
    • Use Share by link button to create a share which is accessible anonymously by anyone in possession of a secret link.
    • Use Share with user button to share the data with a single registered user. You will be asked for the user's email. Please make sure that the email is typed correctly. The user will NOT be notified by the system. Please notify the user manually.
  3. Share settings will appear on the screen. Go through the following steps to select which parts of the model should be shared:
    1. Select the system privileges: System privileges are required to perform special tasks like exporting data or sharing the model with others. Certain privileges let users access all worksheet or all lists (even those which do not exist yet).
    2. Select the worksheets the user should access. You can share a worksheet in read-only or editable mode.
    3. Select additional worksheet access limits for each dimension if necessary. Setting this option will limit worksheet access to certain measures or list items.
    4. Select which lists should be shared with the user. You can share a list in read-only or editable mode.

Data exchange

Worksheet content and list items can be transferred between CubeWeaver and 3rd party tools via files (.CSV or .XLSX) or using the ODATA protocol. A developer API is available to directly connect a model to a database system.

CSV import

Follow these steps to import worksheet content or list items from a .CSV file:

  1. Click the FileImport data menu item to open the import file dialog.
  2. Select a worksheet or a list to import data to. Than select a file using the 'Choose file' button.
  3. Choose the columns to use for import:
    • A list item file must contain one line for each item. At least the identifier or the caption column is mandatory. If the identifier column is missing the caption is used instead of the id to identify rows.
    • A worksheet file must contain an identifier or a caption column for each dimension of the worksheet. There are two import options for the worksheet measures:
      1. If the Measures as columns checkbox is checked each measure is expected in it's own column.
      2. If the Measures as columns checkbox is unchecked a signle VALUE column which contains the measure value is expected along with the Measure column which designates the measure.
  4. Click the 'Start import' button and wait until the import process has finished.
  5. Check the import log for errors.

You can use the FileExport data menu item to generate a sample .CSV file with worksheet values.

NOTE: List items and worksheet data must be imported separately. To import one large denormalized file import each dimension first into it's list. After that you will be able to import the measures. Each hierarchy level of a hierarchical list must also be imported separately.

File based export

Use the FileExport data menu item to export data as .CSV or .XLSX. An export dialog will apper. There are two options to export data:

  • denormalized: list item data is injected into every worksheet table
  • normalized: list items and worksheets are exported into separate sheets/files

When Measures on columns checkbox is checked a separate column will be created for each measure in the worksheet tables. If the checkbox is unchecked all the measures will end up in a single VALUE column and a separate Measure column will designate the measure.

ODATA export

To export data via the ODATA protocol create a new link based share (FileShareShare by link) and check the Export any table user privilege. A text field with the OData access link will appear. This link can be pasted into a 3rd party application which supports the ODATA protocol.

Developer API

It is possible to connect your model to your database system using an ETL tool or custom scripts, but the developer documentation is not public yet. Please contact support with some details about your scenario (source/target system, data transfer direction, etc...) to get more information on the available APIs.

Troubleshooting

Below is a list of common error messages and pitfalls with explanations on how to solve them:

Error messages

  • Cannot delete item(s) X. Following items still reference it: A, B, C: A list at the end of the error message contains objects which still reference the item to be deleted. Those references must be removed first. Here is a list with preconditions, for deleting an object:

    • List item: to delete a list item it must be remove from all the formulas which reference it first. The list item must not be referenced by other list items via a Reference-type column.
    • Measure: to delete a measure it must be remove from all the formulas and from all the worksheets (WorksheetMeasures menu item).
  • A formula must reference cells: This limitation is introduced to efficiently evaluate formulas in very large sparse cubes. Each cell is either an input cell or a computed one. Computed cells must reference other non-empty cells to produce a non-zero result. Formulas without cell references are prohibited completely. In certain cases cell range references may return 0 cells (f.e. [MONTH.prev()] if the first month is computed). A formula which effectively references 0 cells (like [MONTH.prev()] + 2 for January, but NOT [MONTH.prev()] + [M:03]) will evaluate to 0. Though this behavior does not impose any limitation in most of the real world scenarios a workaround exists for certain edge cases: reference a non empty input cell and multiply it by zero to ignore it (like [MONTH.prev()] + 2 + W02.[M:11] * 0).