OLAP Database tuning and Data model tuning are two different subjects. However, they are closely linked to deliver an adequate response time and performance, while maintaining an optimum database size. In this page, we are going to talk about the Database Tuning.
OLAP Database Tuning:
Database tuning is to optimize the data-base setting given the same database model. This means that the multi-dimensional OR/and relational model of OLAP, is assumed to be a constant in the equation. Same Database structure OR model means that your distribution of data across OLAP and Data-warehouse and the level of aggregations and attributes remain same. Within that boundary condition, the database tuning is governed by the following factors. Please note that we are not going to be covering the typical database tuning concepts which are inherent in any data-base (OLTP OR OLAP OR Data warehouse..).
OLAP Density vs. Sparsity:
You are aware of the Database sparsity in relation to the Database explosion issue in OLAP servers. Database density is the antonym to Database sparsity. An OLAP should be able to provide:
- Options on the level of density you want to maintain. The options can be in terms of %age of density and also on the High, Medium, Low levels.
- Recommendations on the areas where density can be improved.
NOTE: Absolute density may not be desirable, as a higher level of density provides diminishing returns.
OLAP Cache settings:
We have already mentioned the cache management at Database layer level. The Database tuning should be able to manage the level of cache setting on the items which need to be cached (data tables, Data files, Indexes..), and the space allocations. It should also be able to maintain the version check (checking the cache version with the database version) which is assumed to be inherent with Cache management.
OLAP Compression algorithms
Compression algorithms are used to compress the OLAP data, so that the data-size can be manageable. Given the issue of Data-explosion and the typical large size data needs of OLAP, this is a big benefit. There are many compression algorithms available and almost all OLAP servers do use the compression algorithms. An OLAP server, should be able to use different compression algorithms either smartly OR/AND allow users to select the algorithm OR turn on and off the compression.
OLAP Indexing
This is a basic concept, whereby you can build indexes on various tables around the database. This allows you for faster search. The concept remains the same in all kinds of Databases (OLTP, OLAP, Data warehouse). This is being mentioned here as there is a difference in the OLTP vs. OLAP indexing. In OLAP, most of the indexing is done during the end of the day jobs (as the Data-Warehouse and OLAP is refreshed), where as in OLTP, the indexing is done on the online basis as new records are added.
Does this mean that we can be more liberal in indexing (in OLTP the indexing needed to be optimum as over-indexing reduces the response time of the systems)? The answer is a cautious yes, because indexing does add to the storage requirements. |
