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 for forming cube fields is displayed below:

The main properties of cubes that you should pay attention to:

• Purpose
• Periodicity - the periodicity of cube generation
• Source type
• Timeout for building cubes (in the properties of the report server)

Before viewing a report based on a cube, you need to recalculate the cube! Building a report after recalculating the cube is only possible if you have a license for the report server 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 stored in the SQL database as a view, grouping and sorting are not allowed in views
• the request must return at least one numeric value (int, float) field - this restriction is imposed by the cube engine in the manager. The cube requires the presence of at least one fact and must count the totals by the fact, it is impossible to count the totals by a string field
• nested queries instead of specifying a table field are incorrectly processed when parsing a cube query, the solution is to collect data from tables via join and specify the necessary 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 reference to the reference directory (OBJECTREFNO) and the property ID (RKTYPEIDENT) in the request.)

Creating a Cash Report

To create your own (custom) cash report, perform the following operations:

1. Create a new cube, in the property "Purpose" of which specify "For cash reports". Make sure that the cube property has "System Name" specified (the value by which the band in the cube layout will find the desired cube);
2. Create a new document: reference "Documents and Layouts", select the document group "Custom reports", open the pop-up menu from it, select command "New document". Fill document properties: Name, Alternative name(optional), Status. On the OLAP Cubes report document page, add your cube;
3. On the "Print templates" page of the document create a report template, then add the necessary sections and fields to the report in the report template editor;
4. Add report template to a selected printing scheme in a corresponding directory.

Note for versions older than 7.4.21. XX! To display a report from the report server on Yandex. Checkout, make a note about it in the layout properties.

'Exception Field 'SHIFTDATE' not found'
ProcessErrorException:UCSERR(5004):Exception "Exception Field 'SHIFTDATE' not found" during cube loading.

It appeared after I set up the filter in the cube properties to recalculate the last two months.
I removed the filter-the error disappeared.

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