Periods of Cube Data Analysis
As a rule, the period of cube data and reports analysis is as follows:
- A short period (daily/weekly/monthly): the speed of such cubes calculation should be reduced by applying a filter to the period of the processed data.
- A fixed past period (last year/quarter/month, all data up to any date): such cubes can be calculated only once (each time the period changes, it must be calculated again).
Optimization Rules
Below are a few rules allowing to optimize the calculation of cubes:
- Analyze your Cubes and corresponding reports frequency of use: if some cubes and reports are used only for viewing or printing reports at the end of the month or week of another period, set them the appropriate frequency of recalculation. In case of occasional use — set On Request. In case the cube and its reports are not used at all, disable its recalculation by setting the frequency to Never.
- If the shifts are not closed at the same time, see if the recalculation time is specified for each cube. Specify the time, for example, set the night hours, when no one uses the server and there are no shift closings. If times of shifts closing diverge (for example, on one server, the shift closes at 18:00, and on the other — at 20:00), either do not set the After the Shift Closes frequency, or set the corresponding value of the Data Waiting Interval property in of the Report Server. In the described case, use the 02:30 value — that is, to wait for data for two and a half hours, and only then start recalculating cubes. This will help to avoid double recalculation of cubes.
- If you need to view cubes for a specific period, and previous periods are rarely viewed, it is recommended to set the filter for the required date to speed up the recalculation process. For example, to view cubes from March 2010, set the following filter: 01.03.2010 - 31.12.2100. If you are only interested in reports for one day (for example, viewing daily reports for a month), it also makes sense to select a filter for a short period, for example, a month (for the above example — the current month).
- If this is acceptable, you can create a new database (for example, at the beginning of a new year), and leave the data for previous years in the old database, using a separate Report Server to access them.
- If this is acceptable, you can delete data for past periods from the cumulative database: either by using the General Shifts reference or by physically deleting data from the database (SQL or check.udb) and uploading the required shifts manually (copying them to the incoming directory on the Reference Server)
- In the case of using an SQL database, there is a way to get reports directly from the database (without building and then using a cube) using an SQL query: this allows you to request only the necessary data, considering date filters and other conditions. To create such a report, select SQL Query in the Report Source field in the Report creation form. Next, use the FastReport 4 editor to change the layout or save the automatically generated one. To access the report data, the same mechanism is used as before: open either a menu in the main form or an item in the pop-up menu when clicking the report in the OLAP Cubes and Reports reference.
- Cubes, that take a long time to be calculated, should be analyzed in more detail: find out which fields are used in reports and remove unused fields from the cube structure. If the cube is pre-installed, it is recommended to set its frequency to Never, create a copy of it and delete unused fields in it. If many cubes are used in one scheme, it makes sense to analyze the possibility of merging the cubes into ones with a larger number of fields, thus reducing the total number of cubes to be calculated.
- If you plan to analyze a closed period (with the specified start and end dates), and this interval is from the past (for example, the entire last year), the periodicity of such a cube should be set to Never. You can start the process of calculating such a cube by selecting Recalculate in the pop-up menu.