As mentioned in the previous topic page, there are two kinds of tuning you can do around OLAP Database. One is the database Tuning (as covered in the previous topic), and one is the Database Structure/model tuning. This tuning has to do with changes in the dimensions, attributes, levels (as in hierarchy paths), the level of aggregations, pre-calculated vs. stored vs. on-line aggregations and the data-cut across OLAP and Data-warehouse (what we call Hybrid OLAP- the combination of MOLAP and ROLAP).
Execution-MiH has maintained that your design has to be flexible and extensible to respond to unpredictable queries and changes to the source systems. This principle has to be applied as we work on building our dimensional (OR relational as there are different schools of thought) design for Data warehouse. However, as we work on our OLAP server, due to data-explosion, storage needs and other issues, one needs to look into smarter ways of aligning your OLAP data model with the Query Profile.
The explanation of these OLAP data-model tuning factors are:
Changes in the dimensions:
An OLAP tool should be able to identify the dimensions (OR the levels within the dimension), which are not being accessed OR used, and should be able to point out to the designers.
For example
While OLAP may contain the sales channel dimension, but it might not be using it, as the analysis may be on the basis of sales location. Another example could be -the sales channel might be used, but the analysis is done mainly at the sales manager and above levels, and lower levels of Sales Team Leader OR Sales Trainee, might not be used in the analysis. This means that you may be able to do away with these lower levels in the short term.
You may also change the attributes, which are not being used by analysis OR end-user tools. When we say the attributes, we mean dimensional attributes (which are not forming the hierarchy paths, but are more of the descriptors). The classification attributes, are what we have mentioned in the levels of dimensional hierarchies, in the above-said example.
Changes in measures-facts:
It is same as that in the dimensions. An OLAP server should be able to point out the measures, which are not being used in the analysis.
The level of OLAP aggregations
An OLAP tool should be able to suggest the give and take between the data storage and query performance for a give query OR set of queries. This will allow you to decide on how much stored vs. on the fly aggregations you want to do.
Stored OLAP aggregations vs. Pre-calculated aggregations vs. on-the-fly aggregations
As we have mentioned in the Data-Explosion management techniques, some OLAP tools have a method of pre-calculating the aggregations before the query is fired and keep them ready. This is different from aggregations in the cube and also the ones which you do on dynamic basis (after the query is fired).
For example you may have a stored aggregation on sales revenue at sales manager level, but you may decide to do the pre-calculation for revenue at sales team-leader level, and finally decide to the do the online aggregation at the sales executive level (the lowest level), for on the fly real-time basis. The reason for this could be that:
- Most of your queries could be at sales manager level (in the sales channel dimension), whereas
- Some, but regular queries could be at sales team-leader level (one level below the sales manager) and
- May have least number of and un-predicted queries around the sales executive level.
Typically more predictable and heavy load queries go for cube storage.
A smart OLAP is the one which allows you to identify various options on how you want maintain the cuts on the aggregations which you want to store in the cube vs. the aggregations you want to pre-calculate before the queries vs. real-time aggregations.
Data-cut across OLAP and Data-warehouse (what we call Hybrid OLAP- the combination of MOLAP and ROLAP).
A Smart OLAP solution identifies opportunities on which data should reside in OLAP and which should be maintained in Data-ware house for further drill down and querying. This is more from the perspective of HOLAP. An OLAP solution should allow user to decide on the cut they want to maintain between Data warehouse and OLAP. |
