Сoncept of Cubes and Creating Cube-Based Reports

Cubes (OLAP Technology)

OLAP is an abbreviation for Online Analytical Processing. The user gets a convenient, intuitive model of data, organized in the form of multidimensional arrays — CUBES.

Each cube has its own multidimensional coordinate system. Dimensions of the coordinate system are the main attributes of the analyzed business process (dishes, currencies, discounts, time, etc.).

At the intersection of coordinates, there are data quantitatively characterizing the process, i.e., Facts. In other words, the process characteristics, for which the addition operation or finding min (max) makes sense, can be assigned as facts.

By slicing and dicing the cube in different directions, you can get summary (for example, by year) or detailed (by day) reports.

A multidimensional cube is very difficult to display on a monitor screen, so two-dimensional cubes are used, i.e. table representation with complex hierarchical headers of rows or columns.

You can create any reports based on cubes by selecting the necessary data.

The reference is located in Options > OLAP Reports > Cubes and Reports.

Hierarchically, the relationship can be represented as follows:

Cube schemes -> cubes -> reports (one-to-many relationship).

Fields (dimensions and facts) of cubes are selected from the database structure using the Cube Fields property.

Each cube scheme is characterized by its own main table (cube scheme property) and its own structure. To work with the cube structure, double-click the cube in the Elements area or in the Cube Fields property value area.

To form a cube, it is necessary to properly know the placement of data in the database tables.

The window of cube fields forming is displayed below:

The main properties of cubes to pay attention to:

• Purpose
• Periodicity - the periodicity of cube generation
• Source type
• Cubes building timeout (in the report server properties).
  

Before viewing a cube-based report, recalculate the cube! Report building after cube recalculation is only possible if you have the report server license and a Guardant key!

Report Creation

Important Notes

• in the query text, the use of group by / order by constructions is not allowed — the query from the cube is saved in the SQL database as a view. Grouping and sorting are not allowed in views
• the query must return at least one numeric field value (int, float) — this restriction is imposed by the cube engine in the manager station. The cube should have at least one fact, so the totals must be calculated by the fact. It is impossible to calculate the totals by a string field
• nested queries are incorrectly processed when parsing a cube query. The solution is to collect data from tables via join and specify the required fields from the general selection
• the GENERATEDPROPDATAS table stores the values of all extended properties for all DB entities. To restrict the entity type, you must include a link to the reference (OBJECTREFNO) and the property ID (RKTYPEIDENT) in the request.

Creating Cash Report

To create your own (user) cash report, do the following:

1. Create a new cube, specify For cash reports in its Purpose property. It is also necessary to specify  System Name — the value by which the band in the cube layout will find the desired cube
2. Create a new document: open Documents and Layouts, select the User reports document group, right-click on it and select New document. Fill in the following document properties: Name, Alternative name (optional), Status. Add your cube on the OLAP Cubes tab
3. On the Printing Layouts tab, create a report layout, then add the required sections and fields in the report layout editor
4. Add report layout to a selected printing scheme in a corresponding reference.

Possible Errors

While configuring the cube properties, the following mistake may occur:

In this case, the recalculation filter should be disabled.

In the Filter Type property, Filter and Add Data should be set instead of Filter Data.

The value of the "Filter Type" property is set to "Filter Data", and it should be set to "Filter and Add Data".