Skip to main content

Business Intelligence

Introduction to Business Intelligence​

The Software Factory's user interface provides business intelligence features, called cubes, which allow you to make online and interactive analyses. A cube is always based on a subject.

A cube view is based on a cube. It is a predefined format that the user can adapt to their wishes. They can access the cube views through the context menu, the ribbon, or the cube view bar. You can present a cube view as a pivot table or as a chart.

Screen types for cubes​

The following screen types are default available for cubes (menu User interface > Screen types):

  • cube
  • cube_horizontal
  • cube_no_fields

See also Design a screen type.

The following screen components are available for cubes (menu User interface > Screen types > tab Components):

  • Pivot table
  • Pivot table fields
  • Chart
  • Cube view bar

See also Available screen components.

Third-party integrations for Business Intelligence​

For more advanced Business Intelligence solutions, you can use the Thinkwise OData API to make the data from your application available to advanced third-party BI solutions, such as:

  • Microsoft Power BI
  • QlikSense
  • Tableau

The web-enabled dashboards provided by these tools can be seamlessly integrated into your Thinkwise application, using Custom screens.

Create a cube​

Set up a cube​

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

  1. Start the task Create cube .

  2. Select the table for which you want to create a cube.

  3. Select Execute.

  4. Choose which model updates you want to run.

  5. Execute the task Execute model updates and confirm. Now:

    • A cube component definition, including values and dimensions, is added
    • The screen type is changed to cube

  6. Go to the tab Cube. Here, the following settings are available:

    • Def. cube view - Optional. Select the cube view that you want to use for this cube. See Cube views.
    • Dragging fields - Required if you are using the screen component Pivot table fields. It allows users to modify a cube's series and categories and create a customized overview of the pivoted data.
    • OLAP connection - See OLAP.

Verify the cube fields​

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

  • Value - the field value is determined by an aggregation (sum, average, maximum, minimum).
  • Dimension - the field value can be plotted on the axes. Filtering is possible.

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

Make sure that the measured value(s) used in the cube are derived from the fact table. That is the data that provides the most detail.

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

For each field:

  1. Select a Cube field type.
  2. Select a Column.

Configure the cube fields

Group cube fields​

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

You can, for example, group 'Project activity' and 'Project'. In that case, a 'Project activity' will be shown as a dimension under the 'Project'. The 'Project activity' will be no longer available as an independent dimension.

In addition, you can create a group interval. You can, for example, assign an alphabetical interval to a cube field of the type dimension. This will show all the names starting with an 'A' in one group.

To group cube fields:

User interface > Subjects > Components > tab Cube > tab Cube fields

  1. In the field Group, select the field that you want to add to the group.

  2. And/or: select a Group interval.

    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. For example, 20 October will result in 20.
    • Day of the week - the day of the week for a date or timestamp. Weekdays are counted from 1 (Sunday) through 7 (Saturday).
    • 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.

It is 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 you are using a view, the PK should be set as a unique key.

Cube views​

Create a cube view​

To create a cube view:

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

  1. Enter a name for the Cube view that you want to add to the selected cube.
  2. Select the Sequence no. With this setting you can influence the order in which the cube view appears.
  3. Optional: Select an Icon for the cube view.
  4. Optional: Select the Cube view group from the drop-down list. See Create a cube view group.
  5. Select the Def. cube view type (pivot table or chart). See also Charts.
  6. Edit the cube view's appearance:
    • Total settings
    • Graph settings
    • Legend settings

Create a cube view group​

Cube views within a cube can be grouped together to display them logically.

To create a cube view group:

menu User interface > Business Intelligence > tab Tables > tab Cube views > tab Cube view groups > tab Form

  1. Enter a name for the Cube view group that you want to add to the selected cube.
  2. Optional: select the checkbox Submenu if you want to display the cube view group through a submenu instead of using separators.
  3. Select the Sequence no. With this setting you can influence the order in which the cube view group appears.

The cube view group is now available for selection if you create or edit a cube view.

Add cube view fields​

Add fields to the cube view and specify for each field in which cube area it should appear:

menu User interface > Business Intelligence > tab Tables > tab Cube views > tab Cube view fields > tab Form

  1. Select a Field.
  2. Select a Cube area.
  3. Optional. A sequence number is automatically assigned and used to sort the elements. You can change it by entering a number in the field Sequence no.
  4. Optional. Set the Field width in pixels. Universal GUI Column resizing is enabled for all pivot columns, the minimum width is 50px. If a field width is not set, the following applies:
    • If the field is a category, the column width is auto-sized based on its content.
    • If the field is a series, the column width is set to 110px.
  5. Select the Sort order (ascending or descending).
  6. Optional. In the field Sort by cube field, select the cube field that you want to sort the data by. The prerequisite for this setting to work is that the Cube field type is Value (menu User interface > Business intelligence > tab Tables > tab Cube fields > tab Form).
  7. Select if and how you want to Show totals (automatic, custom, or none).
  8. Select if you want to Expand, for example, dates (year/month/day).
  9. The following tabs are available for more settings:
    • Totals
    • Conditional layout
    • Filters

After creating the cube view and adding the fields, they are available in your end application's cube.

Totals for cube view fields​

If you have selected to show totals in your cube view, more settings for totals are available.

To select the aggregation type:

menu User interface > Business Intelligence > tab Tables > tab Cube views > tab Cube view fields > tab Totals

  1. Select a Summary type. This specifies what aggregation should be used to show the underlying column value. For example, by showing an average, a sum or a count.
  2. Select a Summary display type. This specifies how the aggregation should be displayed. This can be as is or, for example, by using a relative percentage.

To show the sum of all values in each row at the top or bottom of a pivot table:

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

  1. Select Show row grand total. This adds a row containing the summed-up values of the rows.
  2. Select Total position. This specifies the position of the row:
    • Far (default) - Shows the row at the bottom of the pivot table.
    • Near - Shows the row at the top of the pivot table.

Row grand total The row Grand total, set to position "Far"

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 layout > tab Conditional layout

See Conditional layout for Windows GUI and Web GUI.

Cube view conditional layout (Universal GUI)​

With conditional layouts for pivot grid fields, it is possible to apply formatting to specific cells when certain conditions are met. This way you can highlight specific cells to capture a user's attention.

Conditional layouts are useful in the following situations:

  • Within standard cube views (excluding custom cube views).
  • In basic conditional layouts. A single cube view can have multiple conditional layouts.
  • For grand totals and nested fields (categories).

To define conditional layouts for pivot grid fields:

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

Configure the following setting within the Applied to group:

  • Field - This specifies the target column where the formatting will be applied when a condition is met.
note

The following settings are not yet available in the Universal GUI:

  • Apply to cell - Toggle to enable or disable the conditional layout for cells within the column specified in Field if a condition is met.
  • Apply to total cell - Toggle to enable or disable the conditional layout for total cells within the column specified in Field if a condition is met.
  • Apply to custom total cell - Toggle to enable or disable the conditional layout for custom total cells within the column specified in Field if a condition is met.
  • Apply to grand total cell - Toggle to enable or disable the conditional layout for grand total cells within the column specified in Field if a condition is met.

You can format the light/dark theme and font in:

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

See Conditional layout for Universal GUI.

More cube view settings​

A number of tabs is available for further specification of your cube view:

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

  • Constant lines (for charts) - See Areas in charts
  • Filters
  • Cube view tags
  • Translations

Charts​

Dimensions​

Each cube can be displayed in a two or three-dimensional chart. Dimensions can also be linked to each other to create a hierarchy, for example:

  • Customer - Project (two dimensions)
  • Year - Month - Day (three dimensions, 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 with two dimensions

Example of a chart with three dimensions

Areas in charts​

To model an area in a chart, you can use a constant line:

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

  1. Enter a name for the Constant line. It should start with area_.
  2. Select whether the line should be on the X or Y axis.
  3. Enter a minimum Value.
  4. Enter the height or width of the area in the field Thickness.
  5. Optional. Define a title.

Configure chart properties​

Universal GUI

You can add any Fusion Chart configuration property to a chart.

warning

Not all properties have been tested, so Thinkwise does not provide official support for them. Use them at your own risk.

To configure chart properties:

  1. Open config.json.

  2. Find or add the property chartDefaults.

  3. Add any Fusion Chart configuration properties you want.

    note

    The following properties are taken from the model, and will not be overridden:

    • caption

    • decimalSeparator

    • thousandSeparator

    • stack100Percent

    • showValues

    • showPercentage

    • showLegend

    • legendPosition

    • reverseLegend

    You can override paletteColors, but if it contains only one item, that color will remain the theme's main color.

For example, to prevent cube charts from rounding values:

"chartDefaults": {
  "formatNumber": "0"
}
Was this article helpful?