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.
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.
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:
- Display totals
- Default view
Then, fields can then be added to the cube view. You can specify for each field in which cube area it should appear:
The last settings that can be added relate to:
- Sort sequence
- Display top X
- 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.
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:
Available chart types are:
|List of chart types|
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.
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.