Quite often OLAP users report performance problems from the past like “last Wednesday between 14:00 and 17:00”.
For retrospective OLAP performance analysis we need not only the monitoring information and query traces but also the complete picture of events that could have an influence on the system performance.
That is why we strongly recommend to have a logging of such events as a part of production system policies.
In a simple case it can be just a plain Excel file placed on a network share. But the best way is to have this information integrated with your monitoring system. This can dramatically increase the performance and easiness of your analysis.
You can start logging following events while adding other categories that are relevant:
- Change of aggregation design
- Inactive aggregations detected (unassigned or empty)
- Change of the configuration or version of the OLAP-Engine
- Change in the software (not OLAP-Engine) or hardware configuration
- Deployment new cube version
- System maintenance
- Unplanned cube processing
- Any non-regular OLAP activities (performance tests, heavy one time queries)
It is also recommended to have at least following attributes in your logging:
- Time or time span
- Relevant object (system, instance, OLAP object, etc.)
Ideally the users should also have a channel to easily report performance issues. Technically it can be a simple web frontend referenced directly from OLAP client using cube actions. At least the following info should be logged:
- User reporting the issue
- Time or time span of issue observed
- Report, cube or other info localizing the issue
- Severity or priority
We recommend to have this part of OLAP infrastructure starting with first system deployment in order to have a complete history for analysis.