Technical Documentation

Administration and User Guides.

1. Introduction

This Documentation is intended for use with the MODLR™ CPM Cloud.

This Documentation Portal describes how to use the MODLR™ CPM Cloud, Workview Functions, Process Functions, REST API and Excel Add-in.

The MODLR™ CPM Cloud integrates corporate planning, performance management and operational key performance indicator reporting to enable customers to optimise business processes, the quality of decision making and the speed of decision making. MODLR provides real-time business insights, process work-flow with accountability and a consistent view of information across the business. This provides business users with an advantage over traditional business decision makers.

Getting Started

A number of step-by-step prescriptive guides have been written to allow new users to develop an understanding of developing business solutions. You can access these guides by clicking on the "Modelling Guides" section on the left side of the page.

Disclaimer

All samples used within the Documentation and Training Material are fictitious business examples with sample data used to develop sample applications for MODLR and MODLR Customers. These fictitious records include sample data for transactions, product distribution, finance and human resources. Other sample files may contain fictional data manually or machine generated, factual data compiled from academic or public sources, or data used with permission of the copyright holder, for use as sample data to develop sample applications. Product names referenced may be trademarks of their respective owners. Unauthorised duplication is prohibited.

1.1. Network Architecture

The MODLR network is structured such that all users connect to the MODLR Gateway using their web browser. This in turn connects to the clients selected MODLR Instance. Clients can have an unlimited number of MODLR Instances.

The MODLR network is designed such that new MODLR Instances can be created via the MODLR Gateway and are managed by the MODLR Team. MODLR instances can vary by location and memory capacity.

1.2. User Interfaces

There are multiple ways of accessing information within the MODLR Cloud. Understanding these methods and differentiating between them can help you decide which method is the most appropriate for your needs.

The MODLR Cloud

The Website Portal https://go.modlr.co/ is the MODLR Cloud. All administrative and development capabilities of MODLR are available here and it is expected that a majority of customers will use this as their primary way of connecting to MODLR.

MODLR Rest API

The Rest API for MODLR is a standardised method of accessing MODLR programatically. The requests are made in JSON format over HTTP directly to the MODLR Application Server. These calls bypass the Website Portal.

MODLR Excel Add-in

The Excel Add-in allows business users to build their own reports which access data within MODLR Application Servers. MODLR Excel Workbook functions provide a method for Spreadsheet Developers to refresh data feeds from MODLR Application Servers just by refreshing their Worksheets. This client is best used to build reports which use information from multiple Cubes or very specific data intersections.

1.3. User Roles

Users belong to one of two access levels; Modeller or Collaborator. Each user belongs of a specific Client account which represents their employer.

Users can be added to additional clients with either a Modeller or Collaborator level role. This can be used to provide a MODLR Developer with access to their client’s environments within the context of the given role.

Consultants often have access to many client accounts. This provides them ongoing access to the client environment for the purposes of new development, maintenance or support.

To have an account configured with access to one of your client environments, create a new support request here: https://modlr.zendesk.com/hc/en-us and include the clients contact email address.

Modeller Role

Modeller users have administrative access to MODLR Instances. There is no area of the platform which is restricted from this role.

Collaborator Role

Collaborator users are exclusively limited to the Applications which they have been specifically added to. Within these Applications, collaborators are limited to specific pages or slices of data models using security tags.

1.4. MODLR RESTful API

All communication between the MODLR Gateway and the MODLR Instances are completed over the stateless RESTfull API. The requests are made by the Gateway service to each of the MODLR Instances. Developers each have full access to this API and can make requests of their MODLR Instances.

The MODLR Gateway service includes a page called the API Sandbox https://go.modlr.co/sandbox/ which provides a full list of API functions, descriptions and required arguments for use. This page allows API calls to be directly made against a MODLR Instance.

The API is available with 5 endpoints.

Endpoint

Name

Description

/server.service

Server Service

Provides information and control from the MODLR Instance regarding available memory, storage capacity, users list, FTP server status and authentication.

/model.service

Model Service

Provides functions for managing the objects within a model.

/collaborator.service

Collaborator Service

Provides functions for accessing an application as a end user.

/datasource.service

Datasource Service

Provides functions for creating, updating and testing datasources.

/api.service

API Service

Provides an overview of the available API functions.

 

Example calling the Server.Save function

On the sandbox page the available services are listed and below them their available functions. One can select the server.save function from the task list and see that the formulated request doesn’t have any empty properties so this task may not need any additional information to run.

Note: The server save function instructs the MODLR Instance to save the objects it holds in memory to disk.

Fig 1.1 Calling the server.save function in the server service via the API Sandbox

If the call button is pressed, the request is transported to the MODLR Instance which then executes the function and provides the results in JSON format.

Fig 1.2 The results of calling server.save in the API Sandbox

For further information on the MODLR API please refer to the MODLR API Documentation.

Common Tasks performed using the API Sandbox

There are many common development tasks that modellers can perform using the API Sandbox.

Task

Service

Description

server.save

Server

Instructs the MODLR Instance to save the objects it holds in memory to disk.

server.memory

Server

Returns the current available and used memory.

server.users

Server

Returns a list of users and their details.

security.refresh

Server

Refreshes the list of users and their associated authentication tokens.

storage.check

Server

Returns the current available and used disk space.

ftp.enable

Server

Enables the FTP Server. This will return the generic login details for a “writer” and a “reader” account.

ftp.disable

Server

Disables the FTP Server.

ftp.status

Server

Returns a result of “1” if the FTP server is enabled.

Returns a result of “0” if the FTP server is not running.

Returns the generic login details for “writer” and “reader” accounts.

 

1.5. Logging into MODLR Gateway

The MODLR Gateway can be accessed by any modern web browser.

  1.      Open a web browser such as Edge, Firefox or Google Chrome.
  2.      Navigate to https://go.modlr.co/

  

    3. Login with your email address and password.

1.6. Internal Objects

Within each MODLR Instance there are many object types.

Object

Parent Object

Description

Top level Objects

 

 

Datasource

Instance

A connection to an external database or filesystem

Model

Instance

A collection of cubes, tables and associated objects which focus on a certain domain of knowledge.

E.g. “Performance Management”

Application

Instance and Model

A application is a collection of web portal pages including workviews (reports) on specific cubes. Collaborators can access applications for reporting and planning.

FTP Server

Instance

The built-in FTP Server allows for data files to be uploaded to the MODLR instance.

 

 

 

Sub level Objects

 

 

Cube

Model

The storage component for data in MODLR. The data (numbers and comments) in cubes are stored at the intersection of items within the dimensions from which that cube is made up.

Workview

Cube

Reports and data entry templates which reflect a slice of a specific cube. Often you will have many Workviews which refer to a single cube providing different ways of reporting on the same information.

Formula

Cube

Business logic is added on cubes using Formula. Formula move data between cubes in real time.

Dimension

Model and Cube

Is a way of defining information in a cube.

Hierarchy

Dimension

Are collections of elements which do not hold data. These elements include children which are dimension elements (elements without children which can hold data).

Element

Dimension or Hierarchy

come in two flavours.

  1. Dimension Elements cannot have children and exist per the dimension, these can be used as the lowest level in hierarchies.
  2. Hierarchy Elements can have children and cannot hold information. Their sole purpose is to consolidate the Dimension Elements in a predefined manner.

Process

Model

Loads information into MODLR Models creating and updating Cubes and Dimensions. Processes can use a variety of data sources such as:

  • Microsoft SQL Server Databases
  • MySQL Databases
  • IBM DB2 Databases
  • Any JDBC Datasource
  • CSV Files

Schedule

Model

An object which will execute a process at a set interval.

Variable

Model

Can be used to determine the current reporting period for reports or hold assumptions for Cube Formula.

Table

Model

A standard Database Table stored within one of the Instance Datasources.

Workflow

Model

A Workflow is a means of documenting the tasks needed to be performed at a set interval to maintain a model.

Application

Model

A collection of pre-defined workviews and dashboards that is accessible by collaborators based on their security privileges.

Screen

Application

A URL within the application. Screens display a page or workview to the user.

Page

Application

Either a custom HTML, JavaScript or a Dashboard.

Collaborator

Application

In the context of an application is the list of users (including Modellers) who can view the application.

Security Tag

Application

Within an application either Screens or Dimension Elements can be secured using Security Tags. These tags are assigned to collaborators which provides them access as defined on the tag.

 

2. Changing your Password

To change you password:

Modellers:

  • In the top right corner, click on your name. Then, click "Manage Account".

Here, go to the "My Account" tab and follow the prompts on the left hand side to change your password.

Collaborators:

  • Use the "Forgot Password" button located on the Login page and then follow the prompts

3. Platform Overview

4. Getting Started with MODLR

4.1. The MODLR Gateway Homepage

There are two landing pages when logging into MODLR.

  • Collaborators will only see the applications which they specifically have been included in.
  • Modellers can view a single MODLR Instance at a time. The homepage will display the Models and Applications included within the active instance.

Changing the active MODLR instance

The currently active instance can be changed as follows:

  1.      Click on your account name (in the top right corner of the screen).
  2.      Click “Manage Account”
  3.      Click on the “Application Servers” tab.

Your instances and all related instances (for other approved customers) will be visible in a table.

  1.      In the actions column against the selected instance click the to activate the selected instance.

4.2. Navigation within MODLR Gateway

There is a primary menu available on most pages of the MODLR Gateway which can be opened by clicking on the   icon.

4.3. New Instance Configuration

When provisioning a new instance, the following objects are installed on first access.

  • A datasource called “Internal Datasource” is an instance of the preinstalled MySQL database.
  • A datasource called “MODLR Filesystem” which is a connection to the “filesystem” folder which is the default directory for the MODLR FTP Server. This is used when CSV files are uploaded to the server via FTP.

4.4. Accessing the MySQL instance directly

The MySQL instance which comes with each MODLR instance can be accessed directly using 3rd Party software. To do this it is necessary to create a new database user account on the MODLR Instance

  1.      Click on your account name (in the top right corner of the screen).
  2.      Click “Manage Account”
  3.      Make sure the active MODLR Instance is the instance with the intended database.
  4.      Navigate to the “Settings” Tab
  5.      Under “Add Datastore User” give the new account a name and click “Add Account”.

You should now be able to use the account username and password provided along with the MODLR Instance IP address (accessible on the “Application Servers” tab) to connect to the Database.

4.5. Accessing the MySQL server via the MODLR Gateway

Within the MODLR Gateway is a page which allows modellers to view, search and modify records in the local MySQL Database called “Manage Data”. Tables created within a model will show here.

Uploading CSV files into the MySQL Database

The Manage Data page allows a modeller to upload CSV files to either an existing table with the same format or uploads the CSV into a newly created table.

Note: CSV files can also be uploaded via FTP and accessed directly using processes (bypassing the MySQL database). To do this set the Process data source to “MODLR Filesystem”.

4.6. What is a Datasource?

A Datasource is an object which holds all the authentication information about a data system. Supported data systems include local files and remote or local databases which support a JDBC interface.

Adding new Datasources via MODLR Gateway

To add a new datasource to MODLR, navigate to the “Manage Datasources” page.

  1.      Fill out the Datasource connection information.
  2.      Test the connection details. If the Datasource cannot be reached, it will not be possible to save the Datasource.
  3.      Save the Datasource

To add a new JDBC Drivers to your instance please log a support ticket with MODLR Pty Ltd here https://modlr.zendesk.com/.

5. Creating a new Model

Models are needed to hold cubes and dimensions pertaining to a business model of a business function such as performance management, value driver modelling, waterfall analysis, or inventory management.

You can create a new model by navigating to https://go.modlr.co/model/create/.

Fig: The options for creating a new model.

The settings collected when creating a model are stored in model variables. These are then considered when generating the default objects within a new model.

5.1. New Model Configuration

When creating a new model, several new items are created by default.

Object

Type

Description

Time

dimension

A Time dimension which matches the specifications of the model:

  • Time granularity
  • Financial Year
  • Month Build

System.Dimension.Time

process

A process to create the Time dimension. This includes code for the all combinations of the different model time settings.

Database Schema

MySQL schema

A database schema is created within the MySQL database which comes with the MODLR instance. This uses a sanitised version of the model name. A variable with this schema name is created inside the new Model.

 

 

 

 

Note: The Time dimension may not be ideal for all Models and can be deleted or ignored.

5.2. Navigation within a Model

By selecting a model, the user is presented with a new screen for developing and browsing model objects.

Fig: Navigation within a Model

5.3. Managing Model Variables, Schedules and Styles

Model Variables, Schedules and Styles are all maintained in the “Manage Model” page. This is accessible via the Model “Actions” Menu.

Fig: The model “Actions” menu “Manage Model” link.

The manage Model page has forms and tables displaying the Model name, Variables, Schedules and Styles.

Fig: Managing a Model

Note: Models can be hidden from Modellers. This prevents any access by that selected modeller to the model.

6. Creating Dimensions

Dimensions are comprised of elements, hierarchies and aliases. There are three types of elements in MODLR which each have specific uses.

Type

Description

Numeric Element

Numeric Elements arebjects of the dimension.

String Element

String Elements are sub-objects of the dimension.

Consolidation Element

Consolidation Elements only exist inside specific Hierarchies. Consolidation elements can have either Numeric Elements (from the dimension) or other Consolidation Elements (from the same hierarchy) as children.

 

Note: Consolidation Elements behave differently to how elements work in other multidimensional databases. Due to the unique behaviour within MODLR, the dimension object forms a N-Level list of elements and hierarchies can be created to perform multiple aggregation views of these.

Note: Due to the nature of separating Dimension elements and Hierarchy elements the process of managing dimension hierarchies can become complicated.

6.1. Creating a new Dimension manually

Dimensions can be created using the various MODLR frontends or via a process. To create a new Dimension using the MODLR Gateway use the “Actions” menu within the Model page.

Fig. The Model Action menu. Used for creating new objects.

Fig. Creating a new dimension within the MODLR Gateway.

6.2. Creating a new Dimension using a Process

When creating a dimension using a process the primary functions needed are:

Dimension.create(dimName, dimType);
Dimension.exists(dimName);
Dimension.wipe(dimName);

These functions will be further explained under the Processes Section.

Tip: When creating elements within a process. Use the hierarchy.structure(dimName, hierName, parentElement, childElement); function to add a consolidation element under another consolidation element.

Use hierarchy.group(dimName, hierName, parentElement, childElement); when adding a dimension element under a consolidation element.

6.3. Managing Dimensions Manually

Clicking “Manage” on an existing dimension within the model page will open the Dimension Manager for the selected dimension.

When editing a hierarchy MODLR provides a text area and interprets tab depth to define the tree of elements. This works well when copying and pasting hierarchies from MS Excel workbooks.

Fig. Managing the “Default” hierarchy of a “Product” dimension.

7. Creating Cubes

7.1. What is a Cube?

Within MODLR all data is all stored within Cubes. Each data point in a cube is at a specific intersection of a single element from each of the dimensions in the cube. The last Dimension within a Cube is always the Measures dimension. Where other Dimensions categorise the information by Business Entities (Department, Account, Product etc.), the Measures Dimension tells us what the information being stored is (Units Sold, Dollars, Cost per Sale etc.).

Fig: What is a Cube?

This diagram shows a simplistic view of a MODLR Cube with three dimensions. Time, Product and Measures. Time and Product Dimensions are categorising the Cube Intersections (Cells) and the Measures Dimension is telling us what the Data is Representing.

Fig: What are the Intersections of the Highlighted Cells?

 

7.2. Creating a new Cube

Cubes can be created within the MODLR Gateway via the “Actions” menu within the Model page.

Fig. Creating a new cube within the MODLR Gateway.

7.3. Cube Validation

Cube validation can be applied to the measures dimension, on String Elements. This provides users performing data-entry a list of valid options derived from an existing, mapped, dimension.

To access the Data validation menu:

  1.      Open a workview and ensure the target measure (or measures) are visible in the report.
  2.      Select a data cell associated with the target measure.
  3.      Click the top right menu option   for data validation.

In MODLR Cubes, validation can cascade from one measure to the next allowing for contextual menus to be used for complex mapping processes.

Fig: Data validation being applied to a “Mapping” string element. This example limits valid options to the list of months from the Time dimension.

Fig: The validated string measure “Mapping” upon data-entry displays a list of Months according to its validation rules.

8. Writing Cube Formula

Cube formula allows business logic to be embedded within cubes and calculated in real-time. The formula can be restricted to elements or hierarchies within a cube and can calculate at the bottom level of the cube or at every level of the matching combinations of elements.

8.1. How to Open the Formula Editor

To add a new formula, right click on a cell within a workview and select “Edit Formula”.

Fig: Reviewing a “Thousands” measure formula.

The “Formula editor” is part of the “Workview editor” within the MODLR Gateway.

The syntax guidelines:

  • Reference a relative cube location using square brackets and double quotes around elements. Commas can be used to include multiple dimensions in the reference.
  • Function names should be in capital letters.

The full list of workview functions can be found within the online documentation.

Tip: A user can trace the result of a Formula within both the Modeller Workview Editor or within a Workview displayed to Collaborators within an Application.

Fig: Explaining a “Thousands” measure value. The result is an aggregation of three months of amounts.

8.2. Types of References in Formula

Reference Types:

  • Direct References
  • LINK References
  • SEQUENCE References
  • LINKBY References

Direct References

When to use:
When making simple links/calculations between cells

Description:
The equivalent of linking to different cells in standard spreadsheets. Since axis items are named elements we use a combination of one or more elements names (instead of row or column references such as A1) to refer to a cell that is against those elements relative to the cell being calculated.

Simple Syntax:
Used when changing from the current cell to another by adjusting the reference to a specific element from one of the dimensions:

["Element Name"]

or

["Element Name A", "Element Name B"]

...assuming that "Element Name A" is from one dimension and "Element Name B" is from another dimension.

Full Syntax:

["Dimension Name:Hierarchy Name»Element Name"]

Dimension Name and Hierarchy Name are optional and can be omitted. When the specified element name exists in two or more dimensions within the cube the dimension prefix is required to prevent ambiguity.

The Hierarchy Name is used when referencing a specific parent element as parent elements in hierarchies do not need to be unique within a single dimension.

The full syntax example can be used to refer to a target cell which spans more than one dimensional change like the below example:

["Dimension Name A:Hierarchy Name A»Element Name A","Dimension Name B:Hierarchy Name B»Element Name B"]

Simple Example:
Restrict to "Revenue"

["Units"] * ["Sell Price"]

Complex Example:
In a business Product B sales is a fixed percentage of Product A sales. So we calculate Product B to be based on Product A and a percent which the user enters against Product B.

Restrict to "Units", Restrict to "Product B"

["Units","Product A"] * ["Upsell %"]

Since this formula applies to only Product B, it will take the "Upsell %" entered against "Product B" and multiply it by the number of "units" sold for "Product A"

LINK References

When to use:
When making simple links/calculations between cells.

Description:
The link function returns data from a specified cube at a relative location. If common dimensions exist, they can be excluded entirely from the relative location argument.

Simple Syntax:

LINK("SourceCubeName", ["Element 1","Element N"])

Example:
Restrict to "Amount" Measure, Restrict to Account "Domestic Travel Spend"

LINK("Travel Planning",["All Trips","Domestic Travel Spend"])

Considerations:
Besides having the ability to specify another cube to refer to the second argument of the LINK function behaves the same as a Direct Reference including the ability to prefix an element with its Dimension and/or Hierarchy.

SEQUENCE References

When to use:
Cumulation calculations, Rolling Balances (e.g. in a Balance Sheet), Averaging multiple months or time elements.

Description:
The sequence function alters the provided Direct Reference by a specified number of positions along a specified hierarchy and dimension.

Syntax:

SEQUENCE("DimName","HierarchyName",PositionalChangeNumber,["Direct Reference"])

Simple Example:
Commonly in a Balance Sheet we want to cumulate the movements of monthly transactions into a rolling balance across accounts. Assuming we have a measures dimension with a Default hierarchy as follows:

  • Closing Balance
    • Opening Balance
    • Movement

Commonly in a Balance Sheet we want to cumulate the movements of monthly transactions into a rolling balance across accounts. Assuming we have a measures dimension with a Default hierarchy as follows:

Restrict to "Opening Balance"

SEQUENCE("Time","Month List",-1,["Closing Balance"])

Complex Example:
Sometimes we may want to calculate the average of the last three months balances to create a rolling average.

Assuming we have a Profit and Loss model and we are populating the Forecast with Actuals up until the current month and then from there we want to take a rolling 3 month average and apply a modification % to that value monthly.

Restrict to: "Amount" from the Measures dimension and "Forecast" from the Scenario dimension

(
SEQUENCE("Time","Month List",-1,["Amount"])+
SEQUENCE("Time","Month List",-2,["Amount"])+
SEQUENCE("Time","Month List",-3,["Amount"])
) / 3 * (1+["Modifier %"])

This will take the previous 3 months and then average them and multiply the result by a modifier %.

If the "Modifier %" is -10% for the month being calculated it will take the last 3 months average and reduce that by 10%.

LINKBY References

When to use:
Either:

  1. Scenario A: When a part of the information needed to reference the target cell is held in the data of the current cube against a string-type measure.
  2. Scenario B: When linking to another cube via a mapping object. Mapping objects are used when the relationship between two cells is not easily supported by other referencing options.

Description:
The LINKBY function us used when retrieving values based on either a Mapping or a value held within this cube. The function can (but doesn't have to) refer to cells in another cube. The initial two arguments are the same as the link function. The third, fourth and fifth arguments are the relative location of the value to assist in locating a destination cell in the source cube. The fourth and fifth arguments are optional and can be excluded altogether.

Syntax - Scenario A:

LINKBY("SourceCubeName", ["Element 1","Element N"],LINKDIM("Dimension 3",["Element 3"]))

Syntax - Scenario B:

LINKBY("SourceCubeName", ["Element 1","Element N"],MAPPING("Mapping Name","LR"))

The second argument to mapping is the direction:

  • LR - Left to Right
  • RL - Right to Left

8.3. Linking Cubes Using Formula

Cube data can flow from one cube to another using Formula. The key functions for this are the LINK and LINKBY functions.

Function

Description

LINK

The link function returns data from a specified cube at a relative location. If common dimensions exist, they can be excluded entirely from the relative location argument.

Syntax:

LINK("SourceCubeName", ["Element 1","Element N"]) 

Example:

LINK("Travel Spend",[ "Total International Spend","All Trips"]) 

LINKBY

The linkby function us used when retrieving values based on another value held in this cube. The initial two arguments are the same as the link function. The third argument is the relative location of the value to assist in locating a destination cell in the source cube.

Syntax:

LINKBY("SourceCubeName", ["Element 1","Element N"], ["MappedValueStringLocation"]) 

Example:

LINKBY("Travel Rates",["Flight Costs"],["Destination"] 

 

Example of using the LINK function

Consider an Annual Planning Model which feeds planned travel costs from a Travel cube to a central Profit and Loss cube. The travel cube has costs consolidated into Domestic and International Cost measures.

Profit and Loss Cube

Dimensions

Travel Cube

Dimensions

Travel Rates Cube

Dimensions

Time Time  
Scenario Scenario Scenario
Account    
Department Department  
Profit and Loss Measures    
  Trips  
  Travel Measures  
    Destination
    Travel Rates Measures

The table layout highlights common dimensions between cubes.

It is likely that in a commercial model the Travel Rates cube will also have a Time dimension.

 

Fig: Displaying two workviews - 1. Profit and Loss cube, 2. Travel cube

The formula is restricted to only the measure “Amount” and account “12301001”. The formula uses the LINK function to create a link to the “Travel” cube using the relative location “International Travel Expenditure” and “All Trips”. The specified elements are required as there is no commonality for these dimensions between cubes.

Since both cubes have “Scenario”, “Department” and ”Time” dimensions, and we want the elements from the source cube to match the target one-for-one, there is no need to include these in the LINK function.

Note: The Scenario and Time dimensions are hidden in the “Profit and Loss” workview and instead a custom header is used to show the headings.

 

Example of using the LINKBY function

Consider a Travel planning component to a Performance Management Model. In the example, there are two Travel cubes used.

  • Travel Costs – The calculations and data entry is performed here.
  • Travel Rates – The travel assumptions accommodation, flights and allowance rates per night.

For the example both workviews will be opened with a vertical break:

Fig: Displaying two workviews - 1. Travel Rates, 2. Travel Costs

Cascading data validation is used for the data entry for Type of travel (Domestic or International) and Destination (a list of Domestic or International Destinations). Both of these validations use the “Destination” dimension which exists within the “Travel Rates” cube.

Fig: The Data validation settings on the Destination measure.

A formula is applied to Accommodation Costs, Flight Costs and Allowances which pull the appropriate rate for the given Destination and use this with the number of people and in some instances the number of nights.

Fig: The Accommodation Costs formula. This takes the rate from the Travel Rates cube for a given destination and

multiplies it by the Number of People and the Number of Nights measures.

Fig: Explaining the calculated “Accommodation Costs” value.

8.4. Rolling Values Using Formula

Rolling balances and depreciation calculations can be implemented using the SEQUENCE workview function.

Function

Description

SEQUENCE

The sequence function returns data from a specified relative location in the same cube but changes one element from one dimension based on a relative position number and a hierarchy.

Syntax:

SEQUENCE("DimName",”HierarchyName”,PositionalChange,["Relative Cell"]) 

Example:

SEQUENCE("Time","Month List",-1,["Closing Balance"]) 

This example will return the closing balance of the previous month.

ISLEVELZERO

The isLevelZero function returns 1 if the element from the specified dimension has no child elements.

Syntax:

ISLEVELZERO("DimName") 

Example:

IF( ISLEVELZERO("Time") = 1 , CONTINUE , 0 ) 

ELEMENT

The element function returns the name of the element (excluding the hierarchy prefix) for the cell being evaluated.

Syntax:

ELEMENT("DimName") 

Example:

ELEMENT("Time") 

POSITION

The position function returns the index of an element within a specific dimension and hierarchy.

Syntax:

POSITION("DimName","HierarchyName","ElementName") 

Example:

POSITION("Time","Month List","2017 - Jan") 

This example will return 1 if the element “2017 – Jan” is the first element in the “Month List” hierarchy. 

 

Note: When using the sequence function, it is possible to create a circular reference. MODLR has some detection for circular references however if the circular loop spans enough distinct cube cells it will likely terminate the MODLR Instance. A log will be generated in this instance. 

 

Fig: The rolling component to a straight-line depreciation formula which uses the SEQUENCE function.

In the above example, the “Time” and “Measures” dimensions are being hidden from the workview. For the time dimension the initial six columns are against a “No Time” element and the subsequent columns reflect 12 months of the current year. For the “Measures” dimension the first six columns use elements which are reflected in the custom header, the subsequent columns use an element called “Depreciation”.

9. Creating Workviews

Workviews act as a window to a section of the underlying cube. Each workview pulls data form a single Cube. Users with a role of Modeller can perform multiple development actions while within the Workview Editor.

Development Activity

Description

Add and Remove Dimensions from the underlying Cube

Within the Dimension Management panel within a Workview, Modellers can add and remove dimensions from the underlying cube. This will wipe the data held within the cube and may also break many workviews which are attached to the same cube.

Add Data Validation to a Measure Dimension Element

Select a String Measure Element and the Data Validation Menu Icon becomes enabled. This button will bring up the data validation menu. Data validation exists on the Cube and will then affect any workview which reports the same measure.

Add, Edit, Reorder and Remove Cube Formula

 

Create Visualisation Workviews

 

 

 

 

 

9.1. Arranging and Stacking Dimensions

Within the Workview editor dimensions are represented as grey boxes and can be placed in one of four positions.

  • Selectable Dimensions (Titles)
  • On Rows
  • On Columns
  • Hidden Dimensions

9.2. Workview Sets and Set Editor

When a dimension is a selectable or hidden dimension, they can have one set definition. Dimensions on rows or columns can have multiple sets. A set is a list of instructions which determine which elements to display, how they are formatted, sorted, indented. Additionally, instructions can be used to suppress zeros or display the alias for the set elements.

Elements can be added individually or as a group in a single instruction.

Fig. Set Editor

9.3. Simple Set Functions

Set Function

Description

Add Blank Space

Adds a blank row or column between the results of the preceding set instructions and subsequent instructions.

Expand

Parses each element in the set so far and includes each parent elements children. The children are added below the element being processed unless a “Reverse (Subtotals at the Bottom)” set instruction is used before this instruction.

Expand All

Behaves the same as Expand except this instruction will include all descendants of the current set elements.

Expand using the Next Instruction

Expands the element next mentioned.

Enable Drill Down

Enables drill-down across the resulting set elements. The children are added below the element being processed unless a “Reverse (Subtotals at the Bottom)” set instruction is used before this instruction.

Indent prior set

Indents the result of previous instructions by one space.

Reset prior set Indents

Removes all indentation levels on previous instructions.

Remove Items using the Next Instruction

Will remove elements if they exist in the results of the previous instructions.

Remove Consolidations

Removes all elements which have child elements. This is often used with “Expand All”, “Reset prior set Indents” and the advanced instruction “Suppress Zeros” to provide a list of elements (without children) which are populated with data.

Sort by Name

Sorts the results of the previous instructions based on the element name. If an alias has been set using a instruction prior then the elements are sorted by the alias.

Format 0,000.00

Data Number formatting

Format 0,000.0

Data Number formatting

Format 0,000

Data Number formatting

Format 0.000%

Data Number formatting

Format 0.00%

Data Number formatting

Format 0.0%

Data Number formatting

Format 0%

Data Number formatting

Ignore New Formatting on Prior Members

Prevents new formatting instructions from affecting the resulting elements from previous instructions.

 

9.4. Advanced Set Functions

Set Function

Description

Hide Dimension Headers

When a dimension is on rows or columns this function will remove the associated title row or column. This is often used in junction with a custom header.

Suppress Empty Elements

Removes rows or columns (depending on set position) where the entire row or column has only zeros (for numeric cells) or blank strings (for text cells).

Conditional Format

Enables conditional styles to be used based on numeric ranges applied to numeric cells.

9.5. Using Model Variables

Model variables can be resolved into Set Elements when the variable is named with a prefix which matches the dimension name.

Fig. Using two model variables “time.Current Month” and “time.Previous Month” to restrict the Time dimension

options available to users of the workview.

9.6. Sequential Set Functions

Sequence functions are available for each dimension hierarchy which will insert the next member of a hierarchy at the end of the set or the prior member of the hierarchy at the beginning of the set.

These functions can be used in junction with Model Variables Instructions to create rolling reports which may show many historic months and many future months based on the current reporting month variable.

Fig. Restricting the Time dimension options to the current month and five months prior.

9.7. Using Cell and Heading Styles

Cells displayed within MODLR Workviews are styled using CSS (cascading style sheets). There is a default style attached to each cell. Modellers are given the ability to create new styles at the Model level which can then be attached to row headings, column headings or data cells.

The style instructions are listed under the instruction group “Model Styles”.

10. Creating Processes

Processes are objects which can create, manipulate and remove objects within a model (including the model’s applications). Typically, Processes are used to read data from a data source to create and maintain dimensions and cubes. They can also be used to move data between cubes and push data into a Datasource connection.

Process logic can be mappings of the processes data source or JavaScript code.

10.1. Creating a new Process

Within a Model use the “Action” menu to create a new Process.

Action

Description

Build a Cube from Data

Provides the fields from the source dataset and the ability to map these to dimensions and data values for a new or existing cube.

Build a Hierarchy from Data

Provides the fields from the source dataset and the ability to map these build a hierarchy within a new or existing Dimension.

Custom

Provides the scripting panel and scripting language selection.

10.2. Scripted Processes

Processes can either have mappings which build dimensions, hierarchies, cubes and aliases or a script which can do a wider range of activities.

MODLR Supports two scripting languages; JavaScript and R.

To use scripting in a new process, change the “Action” to “Custom”.

Fig. a new process with an action of “Custom”.

 

When writing the scripted process there are four functions which are needed in the process.

Function

Description

pre()

This function runs before the process is executed and is used to provide the user with prompts for additional information required to run the process.

 

See also:

script.prompt() function in the process function reference.

 

begin()

This function runs once at the beginning of the process once the prompt data has been collected from the user. Use this function to create new objects or to wipe existing objects ready before loading data.

 

data(record)

This function runs once for each line in the datasource. If there is no datasource then this function is not executed. Data values can be returned from the record using the field name.

 

Example:

If the datasource table provides a field called “account_code” this value can be accessed using “record.account_code”.

end()

 

This function runs once at the end of the process. Use this function for cleaning up created objects or notifying users of the processes result.

10.3. Using Process Prompts

When used in the pre () function, upon execution of the process this function will raise a question to the user executing the process.

Usage:

script.prompt(messageToUser,variableName,defaultValue); 

Parameters:

  • messageToUser - the name of the request to the user (who is executing the process).
  • variableName - the variable within which to store the users response.
  • defaultValue - the default value to prepopulate the prompt with.

Example:

script.prompt("Current Month","current_month","2014 - Jan"); 

Upon executing this process the user will be asked for the “Current Month”. The value will default to “2014 – Jan” and when the process is executed the value the user provided will be stored in a variable called “current_month”. This variable is a global variable and can be accessed in any of the other functions (or in user created functions).

Fig. the resulting form when executing a process with the example prompt defined above.

10.4. Aborting a Process Programmatically

Scripted processes can be aborted using a function. This function is used cancel the execution of a process when an error has occurred.

Usage:

script.abort(reasonText); 

Parameters:

  • reasonText - the line of text to output which explains why the process is aborting.

Example:

script.abort('Something was not as expected.'); 

When this example is executed the process will halt and a log will be added to the server log with the message “Something was not as expected”. This message will also show against the process in the process log.

10.5. Logging within a Process

Usage:

script.log(outputText); 

Parameters:

  • outputText - the line of text to output to the server log.

Example:

script.log('The processes has commenced');

10.6. Scheduling Processes

Processes can be scheduled to run at a set interval, Monthly, Weekly or Daily. The schedules can be created and maintained on the “Manage Model” page found under the Model “Actions” menu.

Fig. the form for creating a new schedule. Manage Model page.

10.7. Sending Emails and SMS messages via Processes

The MODLR function library has built-in functions for sending emails and SMS notifications.

Sending a SMS Message.

Usage:

notification.sms(int userId, String content); 

Parameters:

  • userId – the numeric identifier for a MODLR user
  • content – the string message to be sent

Example:

notification.sms(security.currentUserId(), “The Process has started”); 

This example will send a SMS message to the user who ran the process with the message contents “The Process has started”.

If the user has not added their mobile phone number on their account, the message will not be sent. No error message will be generated.

Sending an Email Message.

Usage:

notification.email(String to, String from, String subject, String html); 

Parameters:

  • to – the numeric identifier for a MODLR user
  • from – the sender email address for the message
  • subject – the subject line of the email
  • html – the string message to be sent

Example:

var html = "The process ‘"+script.processName()+"’ has begun execution."; 
notification.email("admin@modlr.co", "info@modlr.co", "Process Started", html); 

This example will send an email to “admin@modlr.co” which explains that a process has begun execution.

Note: the from address must be “info@modlr.co” unless a custom from address is organised by submitting a ticket to MODLR support. 

11. Creating a new Application

11.1. What is an Application?

An application is a curated section of a single Model. Applications provide users with a set of screens which assist in directing the user through a business function such as a Budgeting Round, Profitability Analysis or Regular Report.

Applications a comprised of five objects:

Object

Description

Contributors

People who can access the reports, enter information and play with modelling workviews within an Application are called contributors.

 

Planning Screens

Planning Screens are the pages within the Activity which collaborators can see. They are either custom pages containing text, instructions or visualisations or workviews for planning.

 

Access Tags

Your contributors are tagged with access rights so that they can read and write to their responsibility areas. You can provide or deny access on the following areas of an application:

  • Planning Screens
  • Intersections of information within a cube.

 

e.g. Users can change the budget in FY2017 and have read only access to Actuals.

 

Custom Pages

These are webpages which can display simple text and images created using a document editor or HTML, CSS and JavaScript coded to create beautiful visualisations and interactive planning systems. Custom pages can also be Dashboards or server-side scripts.

 

 

 

11.2. Planning Screens

Planning screens form the navigation structure of the Application. Any screens added will appear within the Applications navigation bar. More advanced Applications hide the default navigation bar and display a custom HTML heading and menu.

When an Application is opened the first Planning Screen will be used as the default homepage. Collaborators will only be able to access planning screens provided to them via their access tags.

Planning Screens can either refer to a Workview from the underlying model or a Custom page of the Application.

Steps to add an application/planning screen

  • To add an application/planning screen, go to your Application page and select Application Screens on the left.
  • Click on the plus icon on the right side
  • Title refers to the Screen Title - this can be set to anything descriptive related to the screen
  • Page Route defines where the screen can be inside in the application. Here, if your application is accessed at https://example.modlr.co, then this screen can be accessed at https://example.modlr.co/home
  • Header allows you to set a header page in the screen which loads at the top of the screen. A header page can be anything but is commonly set to have default navigation, logo, etc. This is optional.
  • Contents allows you to define the main content of the page. This will be shown between the Header & Footer if defined.
  • Footer allows you to set a footer page in the screen which loads at the end of the screen. It usually contains Copyright text, Contact Us, Addresses for Support, etc.
  • Add To Access Tags allows you to grant/restrict permission to Collaborators and Modellers in your application based on Screen Tag.

11.3. Contributors

In the context of an application, a contributor is any user who has access to that specific application. Contributors are tagged with access tags which provided access to planning screens or the dimensional elements of the model.

Both Modellers and Collaborators within a client need to be specifically added to an Application (as a collaborator) for them to gain access to it.

To add a contributor to your application, go to the Application Screen and select Contributors panel on the left. 

  • Click on the Plus icon on the right to open up the Adding Contributor screen.
  • To add an existing user onto your application as a Collaborator, choose "Existing User" tab. Then select the username and the respective Access Tags and click on Add Existing User.
  • To add a new user to the system, go to the "New User" tab and then fill in the respective details and click on Add User. For new users, the system will send an email to the address provided for activating their account along with credentials.

 

11.4. Custom Pages

There are several types of custom pages.

Object

Description

Formatted Page

A HTML page developed using a visual editor.

 

HTML Page

A HTML page which is developed using a HTML editor. These can include client-side JavaScript and can be used to developed pages that acts as a frontend for the users to use the application.

Server Javascript A Javascript page which executes on the server side. This page can be used to execute process functions.
Dashboard A dashboard which can hold Selectables (Dropdowns), custom HTML pages, workviews or workview vizualisations. A dashboard can be used to show several pages at the same time.

 

HTML Page

A custom HTML page provides a way to access various options. All of these tags are to be enclosed between the HTML comment tags.

  • variable: Provides a way to retrieve the value from a model variable (brand.Logo) as shown in the example below. 
  • include: Can be used to include another custom HTML page. This provides reusability and is commonly used to implement navigation bar or CSS styles.
  • tag: Provides a way to control pages inside an application using Access Tags using conditional statements. Below example displays a statement to show Administration navigation bar to users who have admin access tag and Public navigation bar if they do not.
  • user: Can be used to fetch information about the user who is currently logged in such as full name, id, email and phone number (if provided).
  • model: Can be used to retrieve information about the current model such as it's id and name.
  • application: Can be used to retrieve information about the current application including it's id, name and route.

11.5. Access Tags

Access tags provided access to planning screens or the dimensional elements of the model.

Screen Access Tags

Screen access tags provide collaborators with access to specific planning screens of the Application. Planning screens can either be visible or hidden from the collaborator.

Fig. the form for adding and maintaining a screen access tag.

 

Dimension Access Tags

Element access tags provide collaborators with access to specific elements from dimensions of the underlying model. Element access provides Write, Read or No access to any element of the selected dimension.


Fig. the form for adding and maintaining a dimension element access tag.

 

12. How to guides

A number of short tutorials on performing one off actions.

12.1. How to switch your active instance

In the current version of MODLR gateway you can have one active instance at a time. This short guide will explain how to change which instance is your currently selected instance.

  1. Click on your name at the top right of the screen and select "Manage Account"

  1. Select the "Coud Instances" tab

  1. Find the instance you would like to select. 
  2. Click on the first blue, check button  on the instance row.

This will change your active instance to the selected instance and redirect you to the homepage.

 

12.2. How to open a Workview (Report)

From the home page within gateway:

  1. Select the Model which holds the Workview you would like to open

  1. From the model homepage click open  on the workview you would like to view.

  1. This will open a new tab in your browser which will show the selected workview

 

12.3. How to run a Process

From the home page within gateway:

  1. Select the Model which holds the Workview you would like to open

  1. Select the Processes Section

  1. Click execute  for the process you intend to run

  1. You will be presented with a confirmation window. This will ask you to complete any additional information that the process needs in order to run. If there is no input required or once you have filled in the form you can click "Confirm Execution" to run the process.
  2. Optionally, after you confirm the process execution, you can monitor the process by viewing the logs. Click on the logs icon  in the top heading to view the process logs.