Periods of Cube Data Analysis
As a rule, the period of cube data and reports analysis consists of the following:
- 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 for optimizing cubes recalculation:
- 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 recalculation periodicity. In case of occasional use, set On Request. If you do not use the cube and its reports at all, disable its recalculation by setting the periodicity 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 periodicity, or set the corresponding value of the Data Waiting Interval property 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 to 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 single-day reports (for example, viewing daily reports for a month), it also makes sense to select a filter for a short period, for example, a month (the current month for the example above).
- If acceptable, you can create a new database (for example, at the beginning of a new year), and leave the previous year's data in the old database, using a separate Report Server to access them.
- If acceptable, you can delete previous periods data 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)
- If you are 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, taking into account date filters and other conditions. To create such a report, select SQL Query in the Report Source field of the Report creation form. Then, use the FastReport 4 editor to edit the layout or save the automatically generated one. To access the report data, the same mechanism is used: 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 periodicity to Never, create a copy of it, and delete unused fields in this copy. 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 is a past period (for example, the entire last year), the periodicity of such a cube should be set to Never. You can start the calculation process of such a cube by selecting Recalculate in the pop-up menu.