Introduction

Egeria is a multidimensional online spreadsheet service. It lets you create planning, reporting and consolidation applications with complex computational models as easy as creating an office spreadsheet. Egeria worksheets can be published to thousands of users contributing their knowledge to your model to produce precise consolidated results. A sample model for computing contribution margins for various products in an international company can be implemented in a single business day. Use cases range from simple things such as project time tracking, party planning or student grade-book to complex financial planning and reporting applications crunching hundreds of millions of numbers.

If you have a spreadsheet with multiple worksheets and need some computations across the worksheets or if you end up sending copies of the same spreadsheet to you coworkers, so that they fill some data and send them back you will probably need much less manual effort if you use Egeria.

Egeria data is stored on a server as a multidimensional cube and can be exported in various ways to be used in dashboards, pivot tables or further processed in a relational database.

Overview

When creating an Egeria model you will typically need to go through the following steps:

  1. Define your dimensions and cubes

    In a spreadsheet application you typically have predefined dimensions for rows and columns with numbers as dimension members for rows and letters as dimension members for columns. In a multidimensional spreadsheet you will have to specify your dimensions and dimension members yourself. Egeria has a flexible document based meta-data model which is used to define your cubes. See Meta-data section for details.

  2. Enter your dimension members manually or import them from spreadsheets/csv files

    If you have a lot of meta-data it will be easier to import via a CSV file. See Export/Import section for details.

  3. Create worksheets and formulas

    Egeria worksheets are used to view or edit the content (values) of your cubes. A worksheet is a 2D projection of an n-dimensional cube. Worksheets can also be used to create charts. See Worksheets section for details.

    Worksheet can contain formulas and rules of different types. Formulas are used to express computations between cells of a single cube or between cells of different cubes. Rules tell the system how to format particular cells or hide or show particular cells to express some application logic. Formulas and rule have a simple syntax very similar to common spreadsheet applications. See Formulas section for details.

  4. Import values to your cubes from spreadsheets/csv files (Optional)

    You can import cell values the same way you import meta-data. See Export/Import section for details.

  5. Protect your data using the access control system (Optional)

    Your Egeria repository is accessible by every user one your network by default. You can create an authorization concept to protect your data. See User access control section for details.

  6. Define an export format for your data (Optional)

    You will typically want to export the consolidated data to visualize or further process it. See Export/Import section for details.

Meta-data

Egeria's data is stored as visual documents. Dimensions, cubes, worksheets, formulas and your custom dimension members are all visual documents. You can view and edit meta-data documents via "Metadata Editor" tab at the top of main page. Documents are sorted by document type. There are system types and user types. System types are predefined things like cubes, dimension models or worksheets. User types are dimension models created by the user which normally contain dimension members.

Meta-data screen

Choose a type of documents you would like to edit by clicking on the "Select a type" dropdown at the left side of the "Metadata Editor" page. You will see existing documents in the tree view on the left side.

Each document has two properties: Code and Name. Code is used to uniquely identify the document and can be referenced from formulas. Code must only contain letters, numbers or underscores and must start with a letter or underscore. Name is a description for a document and can contain anything you like. Documents are always organized in a hierarchy. The root element of a hierarchy has the code * and the same name as the corresponding dimension model per default. A document type can provide an autogeneration strategy for the codes, so the user saves some typing.

You can create new documents by clicking on the parent element with the right mouse button and selecting "New ..." (the same way your create a file in file explorer). If you select a document you will see its content on the right side of the screen. Use the "Edit" button at the top of the page to edit the document's content.

Documents contain different properties depending on the document type. You can display each property's description by moving the mouse over the property's name.

There are following system document types at the moment:

  • Dimension Model:

    Dimension Models are used to create custom document types which in turn can be used as dimensions of your cubes or can be referenced from other documents. After creating a new dimension model a new custom type will appear in the types dropdown (you need to refresh the page manually at the moment). The content of a dimension model specifies hierarchy levels for the type with code generation strategy and a schema (content model) for each hierarchy level.

  • Cube:

    Cubes bundle dimensions to form a data cube. See descriptions on cube's properties for details.

  • Worksheet:

    Worksheets are 2D views inside multidimensional cubes. Users will use worksheets to view and edit the data inside cubes. Worksheets can also contain formulas. Worksheets are grouped in worksheet groups/views. Each group has a cube assigned to it. Worksheets are too complex to be edited in the Metadata Editor so a link to the Worksheet Editor will appear when you select a worksheet. See Worksheets section for details.

  • Cube Mapping:

    Cube mappings are used inside formulas to transfer data between cubes. See Formulas section for details.

  • Export/Import Definition:

    Export/Import definitions are used to specify export/import formats (only CSV is supported at the moment). See Export/Import section for details.

Worksheets

Worksheets are 2D views inside multidimensional cubes. Users will use worksheets to view and edit the data inside cubes. After creating a worksheet in Metadata Editor you can click the "Launch worksheet editor" button to open the worksheet in the worksheet editor. You can also choose the "Edit worksheet" button from the Worksheet Viewer menu to edit an existing worksheet.

Worksheet editor

On the left side of the screen you see three panels (for rows, columns and filters) with dimensions of the cube the worksheet belongs to. Use the mouse to drag dimensions between the panels. Click "Edit"-button on a dimension to choose dimension members you would like to see along the dimension. You can choose items individually by checking them in the tree or use the "Show all items satisfying condition:" checkbox to select a set of items based on their properties.

When you have specified all the dimension settings click the "Refresh preview" button on the left side of the screen to preview the worksheet. Click the "Save design" button to save the worksheet document. Worksheet changes will not be available in the Worksheet Viewer before the cube refresh (via "Apply metadata changes"-button in the "Tools" menu).

After saving the worksheet you can switch to "Worksheet Viewer" tab at the top of the page to enter data or create or edit formulas for your worksheet. You will find more information on formulas in the Formulas section.

Formulas

Egeria worksheet can contain formulas and rules. Formulas are used to express computations between cells of a single cube or between cells of different cubes. Rules tell the system how to format particular cells or hide or show particular cells to express some application logic.

Start creating/editing formulas by opening a worksheet in the Worksheet Viewer. Use the context menu on cells to create/edit formulas/rules and to check which formulas/rules are active for the current cell. "Formulas/Rules" dropdown in the Worksheet Viewer menu display an overview over all formulas for this worksheet. Here is a screenshot of the formula editor:

Formula editor

A formula is specified by its name, type, priority, area of effect and the actual formula expression.

The name is used by the user to describe the purpose of the formula/rule.

There are currently 4 types of formulas/rules:

  • Formulas are simple computations and work the same way the formulas in a typical spreadsheet application do. The result of formula is recomputed every time its source data changes. Cells with active formulas on them will turn grey to show that the content is computed.
  • Commands are formulas which are only recomputed on after the user clicks the corresponding button from the "Run" menu in the Worksheet Viewer. The result of commands is handled the same way the user input, so cells with commands on them can be manually overwritten. Commands are useful to prefill some cells with data or to copy data from one part of the cube to another.
  • Formatting rules are used to specify how a cell is beeing formatted. The format is specified as a result returned by the rule's expression, so dynamic formatting (f.e. based on a property of a dimension member) can be implemented. Currently avaialalble formatting options are: number, data, time, boolean, dropdown/select and text. See functions starting with the word format for details on each format type.
  • Cell access rules can be used to hide a cell or to make a cell readonly or writeable depending on some condition. Cell access rules override the default user authorization logic, so a custom data access policy can be implemented. See functions starting with the word access for details.

Area of effect for the formula specifies which cells are affected by it. The formula editor allows you to specify the area of effect in a simple manner. Just select dimension members the formula should apply to from the dropdown of each dimension. You can also type a boolean expression (a formula expression which returns a boolean (true/false) value) into the "Additional filter" column to select only those members which satisfy a condition. The area of effect of a formula is restricted by the worksheet itself, so that the formulas are only applied on cells inside the worksheet.

Formula expressions have a simple syntax very similar to formulas in common spreadsheet applications. Here are some examples of formulas:

Normal formulas (return numbers):

  • 3 + 2
  • [account:A01] * 3
  • [account:A06] * [account:A02]
  • sum([product:children()])

Filter formulas (return booleans):

  • cur='usd'
  • account.isCurrency='true'

Referencing cells

The biggest difference to a spreadsheet application is the way cells are referenced. A cell reference is always enclosed inside square brackets []. The cell is always referenced relative to the cell the formula is applied to. [] means: take the value of the result cell. A formula []+1 would increment the cell it is applied to (and produce an infinte computation loop, so don't do that!). A cell reference consists of a list of so called accessors separated by commas ,. An accessor has the following syntax:

  • dimension:memberCode or
  • dimension:accessorFunction(argument)

The first one will simply move the referenced cell or cells to the specified dimension member. A cell (account:A01,currency:LCL) of a two dimensional cube would be mapped to the cell (account:A02,currency:LCL) by the referece [account:A02]. The cell (account:A01,currency:LCL) would be mapped to the cell (account:A02,currency:USD) by the referece [account:A02,currency:USD].

The second one applies an accessor function to a cell to get the mapped value. The reference [product:children()] would return a cell for each child element of the current cell's product dimension member. children accessor function has no arguments. Currently following accessor functions are supported:

  • children() will return a cell for each child element of the current cell's dimension member
  • parent() will return the cell with the parent of the current cell's dimension member
  • parent(level) will return the cell with the ancestor on the level level of the current cell's dimension member
  • map(cubeMappingCode) will map a cell of a cube to one or more cells in the same or in a different cube using a transformation described by the cube mapping object. (See cube mappings section for details)
  • mapby(expr) will map a cell of a cube to one or more cells in the same cube by changing the current dimension member to the dimension member returned by the expression. The expression can only reference the same dimension the accessor is applied to. Example: [year:map(year.prev)] where the prev attribute of each year references another year.
  • next() or prev() will return a single cell with the next or previos member of the selected dimension. This accessors sort the the dimension members by code to build a sequence which can be navigated in both directions. Appling this accessors to the last/first dimension member will return an empty set.

Operators

Egeria formulas currently support following operators:

  • * - multiply
  • / - divide
  • + - add
  • - - substract
  • = - equal (returns boolean)
  • > - greater than (returns boolean)
  • >= - greater than or equal (returns boolean)
  • < - less than (returns boolean)
  • <= - less then or equal (returns boolean)

Functions

Egeria supports (or will support) most standard spreadsheet functions like sum or yearfrac. Please use the documentation of your favorite spreadsheet application for now. The list of functions is available in the autocomplete of the formula editor. Here is the list of functions specific to Egeria:

  • level(dimMember) - return the level identifier of the dimension member dimMember as a string. F.e.: level(product)
  • label(dim) - return the display text of the dimension member dimMember as a string. F.e.: label(account)
  • member(number) - converts the cell content of a cell formatted as a dropdown into a dimension member. F.e.: label(member([account:A03])) would return a label for the member stored in the cell [account:A03].
  • filter(accessor, condition) - filters cells returned by an accessor according to a condition. The current cell can be referenced via the $cell variable inside the condition. F.e.: sum(filter([store:children()], $cell.store.currency='USD')) would only sum up values from stores with currency 'USD'.
  • orderbynumber(accessor, sortingSelector) - order the cells using the selector. The current cell can be referenced via the $cell variable inside the sorting selector. F.e.: npv(orderbynumber([year:children()], $cell.year.ordinal)) would compute net present value of the sequence sorted by the ordinal attribute of the year dimension.
  • validate(number, condition, errorMessageString) - show the specified error message on a cell if the specified condition is not met. Function returns the first argument. The value of the first argument can be referenced via the $value variable inside the condition. F.e.: validate([account:A06] * [account:A02], $value >= [account:A07], 'Discounts cannot exceed gross sales')

Formatting functions

Formatting rules must return a value created by one of the formatting functions below:

  • formatnum(formatString) - format cell as a number. The format string is a .NET number format string. See https://msdn.microsoft.com/library/0c899ak8.aspx for details.
  • formatdate(formatString) - format cell as a date. The format string is a .NET date format string. See https://msdn.microsoft.com/library/8kb3ddd4.aspx: for details.
  • formattime(formatString) - format cell as a date. The format string is a .NET date format string. See https://msdn.microsoft.com/library/8kb3ddd4.aspx for details.
  • formatbool() - format cell as a checkbox.
  • formattext(text) - show the argument as a readonly text inside the cell.
  • formatselect(formatString) - format cell as a dropdown where a user can choose an object of specified type. Format string must have the following format: typeCode:levelCode1,levelCode2, where typeCode is a dimension model code and levelCode1 and levelCode2 are valid level identifiers of the type (if the list of levels is omitted all possible members can be selected). Examples: formatselect('account') or formatselect('product:model').
  • format(cellTypeString, formatString) - dynamically format a cell according to the cellTypeString value. cellTypeString must be one of the follwing: number, date, time, boolean, text, select.

Access control functions

  • accessnone() - Hide a cell. If all cells of a row or column are hidden the whole row or column will be hidden too.
  • accessdefault() - Use the default access control logic.
  • accessread() - Make a cell readonly.
  • accesswrite() - Make a readonly cell writeable. Warning: this function wont make a computed cell writeable. Only a cell which is readonly because a user does not have a permission to write into it can be made writeable by this function. Use it in a conditional expression to implement custom access control logic.

Export/Import

Egeria supports exporting and importing data to CSV files or via copy/paste from spreadsheet applications. CSV files must be UTF-8 files delimited by tab-symbols and must contain a header to assign columns to egeria-data. There are two types of export/import formats: metadata export/import and cube data export/import.

The content of the columns is described via so called column expression. Column expression can be written directly in the header of a CSV file or can be mapped to more readable column names using an "Export/Import Definition" metadata object. Following are some examples of column expressions:

  • code
  • product.code.category
  • name
  • $value
  • $level(product)

There are two types of columns: object-attribute-columns, value-columns, formula-columns. Object attributes must be qualified with dimension name for cube data export/import (product.code for cube data and simply code for metadata).

Following object-attribute-columns are supported:

  • code or <dimension>.code - references the full code (including path) of the meta object.
  • code.<level> or <dimension>.code.<level> - references the part of the code on the level <level>.
  • name or <dimension>.name - references the name of the object
  • data.<property> or <dimension>.data.<property> - references a data property <property> of an object. The content of a property reference column must be a valid JSON.

Following value-columns are supported (only cube data export/import):

  • $value - references the value of the current cell
  • <dimension>=<member code> - is a way to reference multiple cube values in a single row. If for example you have a dimension called currency with two members usd and eur you could define two columns currency=usd and currency=eur to have both values in a single CSV row. Cannot be used togather with $value.

Formula columns must begin with a dollar sign ($). The following formula would return the hierarchy level of the current element of the dimension product in every row: $level(product). Formula columns are read only and cannot be used to import values. See the Formulas part of the documentation for more details.

Following is simple example of a valid table for metadata import/export of a hierarchical dimension (must be delimited by tab characters):

code.category code.subcategory code.model code.product name
01 Bikes
01 001 Mountain Bikes
01 001 019 Mountain-100
01 001 019 BK_M82S_38 Mountain-100 Silver, 38

User access control

Notice: User access control is not available in personal edition of the system. User access system is disabled by default. Grant the "Administrator" system privilege to a new user to enable it.

Egeria contains a role-based hierarchical access control system, which can be managed via the Metadata Editor. Following metadata objects are part of the access control system:

  • Users are metadata entities which represent user accounts. A user's code is his login name. Every user must have an initial password, which he will have to change on first login. Users can be temporary disabled via the Disabled attribute.

  • User groups are named user groups or roles. Granting access rights via groups saves a lot of work for the user administrator and helps him to simplify the authorization concept. Users are assigned to groups via a special access control option on the group itself (read on).

  • System privileges represent special system privileges like the right to export or import data or create worksheets. Egeria system already contains a predefined set of privileges which cannot be changed.

User access rights are granted via an access control dialog which can be accessed through the context menu of the metadata editor tree view (just make a right click on the element you want to grant access to and select "Access control").

The dialog consists of two tabs. The "View effective access rights"-tab shows which users already have access to the object. This list is computed by evaluating access rules on the object's ancestors and access rights granted via groups: Worksheet editor

The "Edit access right rules"-tab is used to grant or revoke access rights for a single user or a group of users: Worksheet editor

Choose a user or group you would like to edit in the dropdown at the top. You will see a list of possible access options for the current object (the individual access options are discussed below) and a corresponding access level. Every access option has an "Inherit"-access level per default which means the access rules defined on the parent object apply. Changing access level to "Write", "Read" or "Deny" would override the access rights for the object and all its child objects if they don't specify own access rules.

Objects of user defined types have two possible access options: Metadata and Cube data. Metadata access option means a user can view or change the objects definition via the metadata editor (Notice: a user must have access to all objects referenced by the current object to edit it). Cube data access options means a user can view or edit worksheet cells (via Worksheet Viewer) associated with the current metadata object. To view or edit a cell a user needs the corresponding access to all the dimension members the cell is associated with plus the corresponding access to the worksheet itself.

Every system type object except the system privileges also provide the Metadata access option which allows the user view or change the objects definition. Apart from this some system types have their own special access options:

  • Export/Import Definitions has the "Export/Import Data" access option which allows a user to export (read) or import (write) the data using the object.
  • System privileges have the "Hold the privilege" access options which grants the user the privilege.
  • Users have the "User Access Control" privilege which allows the user the option is granted to, to manage the access control rules for the user represented by the object.
  • User Groups have two additional access options: the "Group Access Control" option which works exactly the same way the "User Access Control" works for users and the "Participate in the group" option which make the user the option is granted to a member of the group.
  • Worksheets have the "Worksheet Content" access option which lets the user view or edit the cells of the worksheet.

Access rules evaluation order

To check if a user has access to an object access rules for the user defined on the object itself and on its ancestors are evaluated first. If an access rule is found it is applied. If no access rule is found (every object has the "Inherit" access level defined) then every group the user participates in is checked the same way. If multiple groups have access rules defined for the object the highest access level is chosen. If no rule is found the user is denied access to the object.

Server installation / command line

Server version is distributed as a portable command line application (Egeria.Web.exe). It can be started from the command line or can be registered as an OS service using the sc command on windows or mono-service on linux (see the last example of the usage text).

Usage:

Usage: EgeriaWeb <options>

  -r, --repository    (Default: egeria://egeria) Repository url. Use egeria://<dir> for file based repos and mongodb://<connStr> to specify mongodb connection string.

  -p, --port          (Default: 8080) HTTP server port. Must be reserved manually under windows via the netsh utility.

  -f, --file          (Default: ) Input/output file for the backup or restore operations.

  --backup            (Default: False) Create backup of the specified repository and exit.

  --restore           (Default: False) Restore backup to the specified empty repository and exit.

  -s, --service       (Default: False) Run as a service. Use this option to run Egeria as a windows service.

  --help              Display this help screen.


Examples: 

Run a server on port 80 with repository called 'egeria' located in the current directory (default arguments):
Egeria.Web

Run a server on port 8080 with repository located in 'D:\data\repo1':
Egeria.Web -r egeria://D:\data\repo1 -p 8080

Register egeria instance as an autostarted windows service called Egeria1 on port 80 with binary under 'D:\bin\Egeria.Web.exe' and repository under 'D:\data\repo1':
sc create Egeria1 binpath= "D:\bin\Egeria.Web.exe -r egeria://D:\data\repo1 -p 80 -s" start= auto