OLAP cubes

Introduction to OLAP cubes

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.

Technical requirements and limitations

An exhaustive, though possibly somewhat technical, list of requirements and limitations can be found here: https://docs.devexpress.com/WindowsForms/11776/controls-and-libraries/pivot-grid/binding-to-data/olap-data-source/olap-requirements-and-limitations?v=21.2.

Requirements

• The Microsoft Analysis Services OLE DB Provider must be installed on the client machine in order to communicate with OLAP cubes.

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.

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 cube field type Value: use Column ID
  • For cube field type Dimension: 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.

Configure an OLAP cube

Configure the connection to an OLAP cube

To turn a cube into an OLAP cube:

menu User interface > Business Intelligence > 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 Configuration requirements for OLAP cubes.

To configure fields for OLAP cubes:

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

1. Enter a cube Field name.
2. Select a Cube field type (Value or Dimension). Both fields lead to different required information.
3. Select a Column.

OLAP cube field configuration