Skip to main content

Cubes

Introduction​

The Software Factory's user interface offers powerful business intelligence features known as cubes. Cubes let you perform online and interactive analyses, helping you to gain deeper insights and make informed, data-driven decisions.

A cube is a structured dataset that is optimized for querying and reporting and is always based on a specific subject. Each dimension within a cube represents a different aspect of the data, such as time, geography, or product categories. This setup allows you to pivot, slice, and dice data to uncover trends and patterns.

Cubes are the foundation for cube views. These views can be displayed as charts or pivot tables, which help you visualize and interpret complex data relationships.

Cube terminology​

A cube is a structured dataset that is optimized for querying and reporting and is always based on a specific subject. They allow you to pivot, slice, and dice data to uncover trends and patterns. A cube consists of fields that are categorized into dimensions and values.

  • A dimension within a cube represents a different aspect of the data, such as time, geography, or product categories. A dimension can be an underlying column or an interval of underlying columns and can be nested.
  • A value is a data point that is associated with a dimension, such as sales revenue or the number of units sold. A value represents aggregations of underlying columns or formulas based on other values.

A cube view is a predefined configuration of dimensions and values, as well as other configurations that affect the way the pivoted cube data is displayed to the user. You can define multiple cube views for a single cube. Cubes can be visualized in a cube view as a pivot table or a chart:

  • A pivot table is a table that displays data in a grid format, containing rows and columns that can be easily rearranged. This is similar to how a pivot table works in Microsoft Excel.

    • A row in a pivot table is a horizontal line of data, for example, a list of products or regions.
    • Values are always shown in the context of columns in a pivot table, for example as a list of months or years. When there are multiple fields placed in a pivot table as values, these values will always be shown as columns at all expansion levels.

  • A chart is a graphical representation of data containing series and categories. Dimensions are placed on the axis and legenda as categories or series.

    • A series is a set of data points that are plotted on the chart, for example on the y-axis of a chart.
    • A category is a group of data points that are plotted together, for example on the x-axis of a chart.

Users can set up their own cube views and save them when a Cube panel is present and enabled on the screen. For more information, see Create a cube.

Example

If you are analyzing sales data for a tech company using a cube that contains sales information, the dimensions might look like this:

  • Product - such as a laptop, smartphone, or tablet.
  • Region - such as North America, Europe, or Asia.
  • Time - such as year, quarter, or month.

The value in this cube represents a measurable fact, such as the total sales revenue in a specific region. For example, the sales revenue in Q1 for laptops in North America is $200,000.

In the example below, this data is displayed as a pivot table:

Example of a pivot table

In the example below, this data is displayed as a chart:

Example of a chart

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.

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 Design > panel Abstract components):

  • Pivot table
  • Chart
  • Cube panel
  • Cube view bar

See also Available screen components.

Set up a cube​

Create a cube​

menu User interface > Cubes > tab Default > 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 of the table is changed to cube

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

    • Default cube view - Optional. Select the cube view you want to set as the default for this cube. See Set up a cube view.
    • Enable cube panel - Required if you are using the screen component Cube panel. Enabling the cube panel allows users to modify a cube's series and categories and create a customized overview of the pivoted data.
    • OLAP connection - See OLAP cubes.

  7. Go to the tabs Dimensions and Values to verify the definitions of the dimensions and values. The task for creating a cube has generated the best possible proposal automatically, but you should still verify them:

    • 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.

Configure dimensions​

Cube fields with type Dimensions, or in short, dimensions, contain categorical data. Examples include product names, regions, dates, or customer segments. You can use them to group and segment your numerical data, the values.

Dimensions can have a hierarchical structure, allowing you to 'drill down' into more detailed levels of analysis. By using dimensions, you can analyze your data in different contexts. For instance, you might want to see the summary of costs (values) broken down by product category (dimension) and market segment (dimension).

To configure a dimension:

menu User interface > Cubes > tab Default > tab Dimensions > tab Form

For each dimension:

  1. Enter a name for the dimension in Field.

  2. Select a Column.

  3. Optional. Select whether you want to Group dimensions. 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.

  4. Optional. Select whether you want to set up an interval. For example, you can assign an alphabetical interval to a dimension. This will show all the names starting with an 'A' in one group.

    Possible interval types

    The possible 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 of year - week no. of the year for a date or timestamp.
    • Week of month - the week of the month for a date or timestamp.
    • Day of year - the day of the year for a date or timestamp.
    • Day of month - the day of the month for a date or timestamp. For example, 20 October will result in 20.
    • Day of 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.

Configure the dimensions

Configure values​

Cube fields with type Value, or in short, values, contain numerical data. They are determined by an aggregation, such as summed, averaged, counted, or otherwise calculated.

They can be grouped and segmented by dimensions.

To configure a value:

menu User interface > Cubes > tab Default > tab Values > tab Form

For each value:

  1. Enter a name for the value in Field.
  2. Select an Aggregation 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. See also Calculated cube fields.
  3. Select a Column.
  4. Optional. Universal GUI Select if the field should be Editable in a pivot table. For more information, see Make values in a pivot table editable.
  5. Select an Aggregation display type. This specifies how the aggregation should be displayed. This can be as is or, for example, by using a relative percentage.
  6. Optional. Select the No. of decimals.

Configure the values

Calculated cube fields​

Universal GUI

Calculated cube fields are fields that are not directly available in the database but are calculated based on other cube fields. They are evaluated on cells and (sub)totals after intervals have been applied to dimensions and after values have been aggregated. You can use them to analyze data without modifying the database. Furthermore, they are evaluated separately for totals, which allows for advanced aggregations that are not possible with regular aggregation types.

For example, a profit margin calculation ((revenue - costs) / revenue) cannot be calculated in the database and aggregated as an average. The weighting of each profit margin and revenue is not taken into account for each (sub)total. Instead, this average must be re-evaluated for each (sub)total based on the summed revenue and summed costs.

Some other examples of when calculated cube fields can be useful:

  • Average selling price: total revenue / number of units sold
  • Year-over-year growth: (revenue this year - revenue last year) / revenue last year * 100%

To create a calculated cube field:

menu User interface > Cubes > tab Default > tab Values > tab Form

  1. In the field Aggregation type, select SQL expression.
  2. In the field Query, enter the SQL expression that calculates the value.
    • Use t1.cube_field_id to refer to another field. The alias t1 is mandatory. The cube_field_id is the name of the cube field.
note
  • Windows GUI In the field Aggregation type, select Formula (deprecated).
  • If you are transitioning from the Windows GUI to the Universal GUI, you can add both settings to your cube view. SQL expression is ignored by the Windows GUI, and Formula (deprecated) is ignored by the Universal GUI.

Set up a cube view​

Create a cube view​

To create a cube view:

menu User interface > Cubes > tab Defaults > 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. Enter 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. To make the cube view available, select the checkbox Show cube view.
  6. Optional. Select the Default cube view type (pivot table or chart). See also Pivot tables and Charts.
  7. Optional. To show a cube view in a Cube view bar or (Custom) action bar, select the same Screen area as the bar. For more information, see Screen areas.
  8. Optional. Select a Custom display type.

    For example, Icon + text (> text > icon) means that the report is displayed as an icon with text. On a smaller screen with less space, it is displayed as text (the first fallback: > text). With even less space, it is displayed only as an icon (the second fallback: > icon). The overflow menu is always the last fallback type, except for the display type Hidden.

You can now add fields to the cube view.

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 > Cubes > tab Default > 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 change 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 fields to a cube view​

The Cube set-up panel allows you to configure the cube view visually, similar to how you would create a cube view in your application. You can use drag-drop to place fields into a cube area, to remove them from a cube area, or to change the field order within an area.

To add fields to a cube view:

menu User interface > Cubes > tab Default > tab Cube views > tab Cube set-up panel

  1. Select a Field.
  2. Use drag/drop to drag the field to the desired cube area:
    • Filters
    • Series / Columns
    • Categories / Rows
    • Values

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

Cube set-up panel The cube set-up panel

Configure the cube view filter​

The dimensions you added to the areas Series / Columns and Categories / Rows in the Cube set-up panel are available for filtering in the cube view. You can also make additional dimensions available by adding them to the area Filters. See Add fields to a cube view.

To configure the cube view filter:

menu User interface > Cubes > tab Default > tab Cube views > tab Filter

  1. Optional. Select a Filter value for the field.

Configure the cube view sort​

The fields you added to the areas Series / Columns, Categories / Rows, and Values in the Cube set-up panel are available for sorting in the cube view. See Add fields to a cube view.

To configure the sort for a cube view:

menu User interface > Cubes > tab Default > tab Cube views > tab Sort

Each area has its own tab for sorting. In these tabs, you can specify the sorting of dimensions and values in the cube. This should not be confused with changing their relative order; this can be done in the Cube set-up panel.

The following options are available:

In the tabs Series / Columns and Categories / Rows:

  • Optional. Sort order: Ascending or Descending.
  • Optional. Sort by cube field - Use this to base the sort order for a series or category on the value of a cube field. For example, sort companies by revenue instead of alphabetically.

In tab Values:

  • Optional. Show top x - Enter a number to show the top x values.
  • Optional. Type of show top x - Select how you want to show the top x values, Absolute or Percentage.
  • Optional. Show other - Select this checkbox to show the values that are not in the top x in the cube view as 'Other'.

Pivot settings for a cube view​

Universal GUI

A pivot table helps you view your data in a spreadsheet-like format. It is useful to dynamically summarize, organize and manipulate large datasets.

To set up a pivot table:

menu User interface > Cubes > tab Default > tab Cube views > tab Pivot settings > tab Form

The following settings are available:

  • Show grand total for rows - adds an additional column that presents the row grand totals.
  • Show grand total for columns - adds an additional row that presents the column grand totals.
  • Show total for rows - adds an additional column that presents the row totals.
  • Show total for columns - adds an additional row that presents the column totals.
  • Total position - specifies the position of the additional row:
    • Far (default) - Shows the row at the bottom of the pivot table.
    • Near - Shows the row at the top of the pivot table.
  • Drill down pivot cell - expands the pivot cell to show the underlying data.

Row grand total The grand total for rows, shown in an additional column set to position "Far"

Pivot field settings​

The following pivot field settings are available:

menu User interface > Cubes > tab Default > tab Cube views > tab Pivot settings > tab Pivot field settings

  • Field width 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.
  • Expand - When multiple dimensions are placed as rows or columns in the pivot, they can be set to be collapsed or expanded by default. For example, when the columns contain year, month, and day of the month, you can choose to have year expanded by default. All months will be visible but collapsed by default. The day of the month cells will not be visible until the user expands one month or all months.

Pivot field totals​

Windows GUI

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

To select the aggregation type:

menu User interface > Cubes > tab Default > tab Cube views > tab Pivot settings > tab Totals

  1. Select an Aggregation 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 an Aggregation display type. This specifies how the aggregation should be displayed. This can be as is or, for example, by using a relative percentage.
  3. Optional. Select the No. of decimals.

Cube view conditional layout​

With conditional layouts for pivot table 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 table fields:

menu User interface > Cubes > tab Default > tab Cube views > tab Pivot settings > 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 > Cubes > tab Default > tab Cube views > tab Pivot settings > tab Conditional layout > Tab Universal

See Conditional layout for Universal GUI.

Make values in a pivot table editable​

Values in a pivot table can be made editable, which allows users to directly edit the fields and make quick changes to their data. This is especially useful for managing and analyzing data where updates or recalculations need to be done frequently.

note

To make a pivot table editable for the Windows GUI, use the extender default_editable_cube. If you transition to the Universal GUI, the extender is no longer necessary.

To make a pivot table editable, the following conditions must be met:

  • The user must have permission to manipulate data in a table, by selecting Update.
  • The cube field type must be a value.
  • The column type must be set to Editable.
  • The aggregation type must be set to min, max, sum, or avg.
  • The count of the aggregation type must be 1 (exactly one row behind the aggregation). For example, the sum of ages for Ben (47) and Anna (30) equals 77, with a count of 2 (based on two rows, which cannot be changed). If you add "person" as a dimension, you get Ben (47, count = 1) and Anna (30, count = 1), and these counts can be adjusted since they correspond to individual rows.

When you have made a value in a pivot table editable, you need to check if the editing rights are set up correctly.

To set up the editing rights for a cube field:

menu Access Control > Roles > tab Tables > tab Cube > tab Cube fields
or
menu Access Control > Model rights > tab Tables > tab Cube > tab Cube fields > tab Roles

  1. Select the task Explain to check if rights are granted to a cube field.
  2. Select the checkbox Editable for each cube field of type Value that you want to grant editing rights to. For more information on configuring roles, see Configure a role.
note

Universal GUI If a user edits a pivot table, the table is refreshed as configured in the field After update. It is not recommended to set the refresh behavior Document, since this may cause the pivot table to re-render.

Chart settings for a cube view​

Each cube can be displayed in a chart, which is a graphical representation of data used to visualize patterns, trends, and comparisons.

To set up a chart:

menu User interface > Cubes > tab Default > tab Cube views > tab Chart settings > tab Form

The following settings are available:

  • Chart:
    • Chart type
      • Universal GUI The Universal GUI maps any 3D chart type to its 2D equivalent. The following charts are shown as a column chart: candle stick, gantt, point, range, scatter line, stock, swift plot.
    • Palette
    • Chart type rotated
    • Show labels
    • Show percentage
    • Transparency
  • Series:
    • Show series
    • Alignment horizontal
    • Alignment vertical
    • Direction
    • Max. horizontal percentage
    • Max. vertical percentage

Cube view constant lines​

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

menu User interface > Cubes > tab Default > tab Cube views > tab Chart settings > tab Constant lines

  1. Enter a name for the Constant line.
    • Windows GUI The name 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​

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

Universal GUI

Also, 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"
}

It is possible to link tasks to a cube table. For more information, see Create a table task.

menu Processes > Tasks > tab Table tasks > tab Form

note

When you link a Task to a cube table and connect Task parameters to Columns, the primary key columns of the cube table need to be available as Cube fields.

OLAP cubes​

Windows GUI (2-tier)

OLAP is short for Online Analytical Processing. It is a method for obtaining multi-dimensional data that can be sliced to obtain limited amounts of data or be viewed from a different viewpoint.

In the Software Factory, cubes and OLAP cubes are different in their data and the way the data is processed:

  • The default cubes are based on real-time data. The computations are processed at the client level (Windows .NET GUI application or ASP.NET web server).
  • The OLAP cubes are based on cached data rather than real-time data (depending on the cube's settings to gain a performance increase). The computations are processed at the database level.

Requirements and limitations​

Requirements:

Configuration requirements:

  • All of the tables in the database that are used by an OLAP cube must also be described in the Thinkwise Software Factory.
  • If you want to use an OLAP cube as a detail screen of a table, we recommended to create a dummy table for the cube. The dummy table should contain every primary key column of every table used by the cube. Then, create references between the tables used in the cube and this dummy table.
  • Every primary key column of the cube's source tables (read: every column of the dummy table mentioned above) needs a corresponding cube field in the cube definition. For these cube fields, the Table id must point to the dummy table and the Column id must point to the corresponding column in the dummy table. For all the other cube fields, the table ID and column ID are technically irrelevant, which is why these columns need not be included in the dummy table.
  • Rules for the cube field IDs in an OLAP cube:

    • For Values: use Column ID

    • For Dimensions: use Table Name.Column ID

      note

      The Table name and Column ID do not refer to the tables and columns of the database but those of the cube definition.

Limitations:

  • Only cubes made with Microsoft Analysis Services are supported by the GUI applications.
  • At this moment, only the OLE DB Data Provider is supported to retrieve data from the cube.
  • Calculated fields are not supported. When defined, the GUI applications will ignore them.
  • Group intervals are not supported. When defined, the GUI applications will ignore them.

Configure the connection to an OLAP cube​

To turn a cube into an OLAP cube:

menu User interface > Cubes > tab Tables > tab Cube

  1. Select the checkbox OLAP connection.

    Three new fields will appear.

  2. Enter the OLAP server name. This is the SQL Server instance on which the Microsoft Analysis Services runs.

  3. Enter the OLAP database name. In the Business Intelligence Development Studio, this is the name of your Analysis Services Project.

  4. Enter the OLAP cube name. This is the name of your OLAP cube within your model.

OLAP cube connection configuration

Configure fields for OLAP cubes​

A default cube in the Software Factory points to a view on the relational database system. Therefore, all the cube fields will map to columns of one and the same view.

An OLAP cube does not point to a view but rather to each table from which you want to retrieve data. The cube fields that you define in the Software Factory can map to a column of any table in the definition of your OLAP cube.

See also the configuration requirements.

To configure fields for OLAP cubes, see Configure dimensions and Configure values.

Was this article helpful?