Cube Reports

Cube report is a concept that allows additional dimensions to the more traditional Table report.

A Table report is two-dimensional, in that it consists of rows and columns. In order to find any data point in the set, you need to have two references, the row and the column.

In a cube report, additional dimensions allow representation of how this point changes over some additional reference. Cube reports are commonly used to iterate a query over multiple dates, in order to create a time-series dataset.

OmniFi has extensive support for multidimensional data on any variable, including time, numeric and textual values. Moreover, Reporting module supports complex dimensions, allowing multiple variables per dimension.

Structure of a Cube report

A Cube report is made up of three components

Base dataset

The base dataset can be either a Table report or a Query. The requirement is that the query or report is linked to a parameter, describes the dimension that we want to extend into. In the case of time, the parameter could be for example Maturity Date, but also other parameters like Portfolio or Transaction Number could be used.

Dimension dataset

One or more data sets (query or report) that defines the dimensions in which the cube should extend. The dimension data set should contain at least one column of the same type as the parameter used in the Base dataset. If multiple parameters are used in the Base dataset, it is possible link each parameter different Dimension datasets to extend into multiple dimensions. Alternatively, it is possible to link several parameters to different columns in a single Dimension dataset to create a complex dimension that describes more than one scalar.

Dimension variables

The parameters that define the result of the base data set is linked to a range of values using variables.

Creating a Cube Report

In this example we will show how to use a Cube report to fetch a portfolio value over different period.

Defining the Base dataset

For this purpose we will use a Treasury Monitor Query that uses Period Start & Period End as something to change. The two dates are linked to a parameter ‘Valuation Date’.

Defining the Dimension dataset

In this example we want to the user to be able to specify an arbitrary list of dates for which the treasury monitor should run for. The list will be defined in an Excel spreadsheet and it will make up the Dimension dataset.

To be able to feed the list defined in Excel to the Cube report we will need to create query that reads it. The query type Excel Worksheet can read content from Excel and create a Query of it.

🚧

When working Excel Queries & Dates it is typically necessary to change the Used Defined Type to DateTime instead of the automatically discovered type Double. This is because Excel handles dates as numbers, but OmniFi needs to treat it as a proper DateTime object.

Creating the Cube report

Create a new Report and select Cube report when prompted and select the Base dataset query as base query for the Cube. In our example, our base dataset is the initial Treasury Monitor Query.

In the Report Editor, click the Edit button on the Cube modifier to open the Cube Editor. The Cube Report recognizes that the Base dataset has a parameter, Valuation Date, that can be cubed. To make OmniFi loop through the list of dates, link the Parameter to the Dimension dataset and column.

Click the OK button, and the cube will execute.

Output of a Cube report

Multidimensional data is difficult to picture, a third dimension can be understood by making a rectangle into a box, but the fourth, fifth and higher dimensions are very complex to imagine.

The Cube report flattens the output to a table format consisting of the total unified set of all output generated, with and additional columns to describe at what position in the dimension the output was generated.