ROLAP, MOLAP and HOLAP

There are three different types of OLAP technology:

ROLAP stands for Relational-OLAP as it stores all the data in relational database. When we run the MDX queries it retrieves the data from the relational database using a corresponding SQL query. This makes the query processing to be slow but it does provide faster processing time.

MOLAP stands for Multidimensional-OLAP as it stores all the data in the multidimensional data structure of the Analysis Services. It has slow processing time but provides better query performance than ROLAP. MOLAP storage mode is most widely used as OLAP is meant to provide the faster query performance.

HOLAP stands for Hybrid-OLAP as it uses a combination of both the ROLAP and MOLAP. It stores only the indexes and aggregations in the multidimensional form while the rest of the data is stored in the relational database.

Time Dimension in Data Warehouse

In any data warehouse date and time plays a vital role in keeping track of when a particular event has taken place. However, we do not store the time information in the same way as we do in the OLTP database. In a dimensional model a separate time dimension is normally used. This time dimension contains separate attributes for year, month, and day and so on. This time dimension is initially populated with the data of a particular period like ten years. Whenever an event/transaction needs to be recorded in the fact table the time key from this dimension for the corresponding time is obtained from the time dimension.

Surrogate key for time dimension is auto incremented just like any other dimension but it is always helpful to have it the right format like for date: 05-Mar-2010 the time key should be 05032010. This proves to be extremely useful as queries can be done directly based on the time key and thus improves performance.

The following figures show how to create and populate time dimension in Sql Server Analysis Services (SSAS):