Skip to main content
Version: 2023

Business Intelligence

The user interface provides business intelligence features, called cubes, which allow you to make online and interactive analyses. The cubes can be presented both numerically (using a pivot table) and chartically (using a chart). A cube is always based on a subject.

Creating a Cube​

menu User interface > Business Intelligence > tab Tables > tab Cube

Cubes are created with the Create cube task. This task automatically adds a cube component definition, including values and dimensions, and changes the screen type to Cube.

Task to create a cube

If the data source is an OLAP cube, the OLAP connection mark must be checked on the form after creating the cube. Three new fields will appear:

  • OLAP server name: refers to the SQL Server instance on which Analysis Services runs.
  • OLAP database name: the name of the OLAP database.
  • OLAP cube name: the name of the OLAP cube.

For more information on OLAP cubes, see OLAP.

Cube Fields​

menu User interface > Business Intelligence > tab Tables > tab Cube fields

A cube is made up of cube fields that can be applied by the user. There are two types:

  • Values - fields for which the value is determined by an aggregation (sum, average, maximum, minimum)
  • Dimension - fields for which the values can be plotted on the axes and on which can be filtered.

The task for creating cubes makes the best possible proposal, but the field definitions still need to be verified. In any case, you should remove all ID columns with no semantic value for the user from the cube. They then just provide the connection to the other subjects.

Adding Cube Fields to the 'Cube Fields' tab

It is also important that the measured value(s) used in the cube are derived from the Fact Table. That is the data that provides the most detail.

Cube field grouping​

It is possible to group cube fields. A group can be collapsed and expanded.

A cube field can be linked directly to another cube field, for example, 'Project activity' to 'Project'. In that case, a 'Project activity' will be shown as a dimension under the 'Project'. The 'Project activity' is then no longer available as an independent dimension.

In addition, it is possible to create a group interval. An example is a cube field of the dimension type for names in an alphabetical interval. This will show all names starting with an A in one group.

The possible group interval types are:

  • Alphabetical - grouping by initial letter.
  • Numeric - interval for a numerical range (for example, per 100 or per 1000).
  • Date - the date of a date or timestamp.
  • Year - the year of a date or timestamp.
  • Quarter - the quarter of a date or timestamp.
  • Month - the month of a date or timestamp.
  • Week no. of the year - week no. of the year for a date or timestamp.
  • Week of the month - the week of the month for a date or timestamp.
  • Day of the year - the day of the year for a date or timestamp.
  • Day of the month - the day of the month for a date or timestamp.
  • Year age - age in years based on the current date. For this purpose the numerical range can be used, for instance an age group per year or per 10 years (0-9, 10-19 etc.).
  • Month age - see year age, but then for the age in months.
  • Week age - see year age, but then for the age in weeks.
  • Day age - see year age, but then for the age in days.

Show cube title in the active graph​

To show the cube title in the active graph, set the extended property ShowTitleActiveChart to Yes.

Cube views​

menu User interface > Business Intelligence > tab Tables > tab Cube views

Apart from defining cubes the user can work with, the cube views can also be specified in the Software Factory. The user can access the cube views through the context menu, the ribbon, or the cube view bar. A cube view is essentially a predefined format that the user can adapt to their wishes for personal use.

A cube view consists of several mandatory and optional components:

  • Cube view.
  • Cube view fields.
  • Constant lines (optional).
  • Filters (optional).
  • Field totals (optional).
  • Conditional formatting (optional).

Create a cube view by adding it. Specify a cube view ID and optionally a group. Subsequently, you can specify the view's appearance:

  • Icon
  • Display totals
  • Default view
    • Cube
    • Chart
  • Labels
  • Legend.

Then, fields can then be added to the cube view. You can specify for each field in which cube area it should appear:

  • Values
  • Row
  • Column
  • Filter
  • Menu.

The last settings that can be added relate to:

  • Sort sequence
  • Display top X
  • Totals
  • Expand
    • For example, for dates (year/month/day).

After creating the cube view and adding the fields, they are available in your end product's cube. Now you can further embellish the cube with constant lines, filters, totals, and conditional formating for Windows and Web GUI and Universal GUI.

With the cube view toolbar, the available cube views can be displayed in a bar on the screen, in the same way as prefilters, tasks, and reports. In combination with the new cube components, screens can be developed, for example, with only charts and a toolbar to select the view.

Cube view conditional layout (Windows GUI and Web GUI)​

To use a conditional layout for cube views in the Windows GUI and Web GUI:

menu User interface > Business Intelligence > tab Cube views > tab Conditional layouts > tab Conditional layout

See Conditional formatting for Windows GUI and Web GUI.

Cube view conditional layout (Universal GUI)​

To use light/dark theme settings for cube views in the Universal GUI:

menu User interface > Business Intelligence > tab Cube views > tab Conditional layouts > tab Universal

See Conditional formatting for Universal GUI.

Charts​

Each overview can be displayed chartically in a two or three-dimensional chart.

Dimensions can also be linked to each other so that a hierarchy is created. For example:

  • Customer - Project
  • Year - Month - Day

The last example requires three dimensions that are linked via the same date field. Such a field is defined as a group interval with the respective type. This is done automatically for dates.

Example of a chart

Example of a 3D chart display

Chart type per measured value​

In order to combine different types of charts in a chart, measured value can be linked to a particular type of chart. This is done by including the name of the type of chart in the name of the measured value, separated by an underscore, for instance: number_of_hours_spline3d.

Available chart types are:

List of chart types
Area2DCandleStick2DRangeArea2D
Area3DDoughnut2DRangeArea3D
AreaFullStacked2DDoughnut3DRangeBar2D
AreaFullStacked3DFullStackedLine2DRangeSideBySideBar2D
AreaStacked2DFullStackedLine3DScatterLine2D
AreaStacked3DFullStackedSplineArea2DSpline2D
Bar3DFullStackedSplineArea3DSpline3D
BarFullStacked2DFunnel2DSplineArea2D
BarFullStacked3DFunnel3DSplineArea3D
BarGrouped3DGantt2DStackedLine2D
BarSideBySide2DGanttSideBySide2DStackedLine3D
BarSideBySideFullStacked2DLine2DStackedSplineArea2D
BarSideBySideFullStacked3DLine3DStackedSplineArea3D
BarSideBySideStacked2DPie2DStepArea2D
BarSideBySideStacked3DPoint2DStepArea3D
BarStacked2DRadarArea2DStepLine
BarStacked3DRadarLine2DStepLine3D
Bubble2DRadarPoint2DStock2D
SwiftPlot2D
note

The Universal GUI only supports the following chart types: all Area types, Bar, Line, Spline, StepLine, StepArea, single series Pie, and Doughnut.

Areas in charts​

To model an area in a chart, you can use a constant line, the name of which starts with area_. The minimum value can be specified with the Value field and the height or width of the area with Thickness.

Executing tasks​

It is also possible to link tasks to a cube table. In that case, it is important that the PK of the table is unique since the GUI uses this to identify the rows in the pivot table. Even if a view is being used, the PK should be set as a unique key.