Creating a model typically involves the following steps:
- Create lists and list items to represent business objects and measures of the model
- Create multidimensional worksheets using lists from the previous step as its dimensions and measures
- Create worksheet formulas to perform computations
- Create cell formatting rules to improve readability
- Use cell visibility rules in combination with buttons to create workflows and advanced functionality
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 Lists → Create list menu item.
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).
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 Lists → Measures 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.
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 Worksheet → New 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 Worksheet → Add dimension and Worksheet → Measures 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 right 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 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 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 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
Below is the complete list of the functions for dynamic references:
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_nameis 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:
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 using 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:
Cell visibility rules let the designer hide a cell range from a user or make a cell range read only. Model designers can use them them to implement workflow functionality by making submitted cells read-only. Cell visibility rules can also be used for advanced data access scenarios, when cells are dynamically hidden based on user's privileges and other cells content. 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 (just always hide the area) or dynamic. Dynamic rules must return a number which 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
Cell visibility rules uses the same area of effect concept as formulas.