IBM Cognos OLAB & BI

Case 1
Issue
Having 31 cubes in production a company had to dedicate 5 production servers to process and build 27 cubes overnight, which imposed significant cost on IT operation.
Discovery
It was found that 21 nightly processed cubes with the most build time had utilized Product dimension. It turned out that due to market specific conditions and for company to keep its competitive advantage it was necessary to keep Product dimension flexible, so it was modified almost every week. The cubes had 4 years worth of data and were fully processed and rebuilt each night (over 300,000,000 of fact rows and over 4,000,000 categories in total for all cubes) which caused long built times and required significant hardware resources to be dedicated to the process.
Solution
Our consultants devised design changes to the EDW in order to help mitigate the effect of constant changes in Product dimension.
Results
The Company was able to implement incremental cube built (200,000 fact rows nightly) and release 2 production servers to other IT needs. One of the remaining servers was kept on stand-by for the departmental emergency needs.
Case 2
Issue
Cube nightly built time was approaching allocated windows limits
Discovery
Transformer default settings were still in place after 4 yeas in production.
Solution
Our consultants have modified Transformer model to source structural and transactional data from different queries, tuned-up UNIX and Oracle environmental variables, changed Transformer configuration settings to allocate more memory and disk space, and devised maintenance plan to remove unused categories.Some changesto model design (calculated columns,input columns scale, etc.) were also implemented.
Results
Cubes built time was reduced by ~ 30%.

Case 3

Issue

Analysis and reports based on specific cubes were running slow causing business users frustration and often complaints, which required IT to dedicated already scarce Cognos resources to satisfy these complaints.

Discovery

Our analysis clearly indicated that the root of the problem were granularity level of the cubes, which were designed at transactional level to satisfy the needs of diverse user users significantly different by business function, causing cubes over-sizing and .exponential growth.

Solution

Our consultants modified cube design to remove transactional data from the cubes, designed and developed OLAP-Transactional drill-through reports to preserve familiar interface and existing and additional functionality, providing for both Business Unit Management and Operation Management reporting needs (sourced from the same cubes).

Results

Reports performance was significantly improved, reducing load time to acceptable 15 sec, drill-down time to 5 sec. Most important results of this engagement were satisfied business users.
Another benefit – Cognos developers were relieved from maintenance and bug-fixing tasks and assigned to long-standing new development and requests, which previously were understaffed.

Case 4

Issue

Cubes based reports were running slowly, although in Analysis Studio cubes performance was acceptable.

Discovery

We have determined that MDX queries in the report where inefficient, another cause of slow performance was sheer number of similar calculations in the reports.

Solution

Our consultants recommended embedding common calculation routine into ETL. After this move was successfully implemented, reports MDX were redesigned according to IBM Cognos best practice recommendations.

Results

Reports performance was significantly improved. The controversy between IT and various user groups (one group was mostly using Analysis, another - reports) was resolved to mutual satisfaction.