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):

Data Warehouse Design

Data Warehouse can be designed in two different ways based on Ralph Kimball and Bill Inmon's model.
In Inmon's architecture data from OLTP databse is stored in a warehouse before it is transferred to the data marts. In Inmon's view the data warehouse is a real database.

According to Kimball's architecture data is transferred directly from OLTP database to the data mart. In Kimball's model the data warehouse is actually a collection of integrated data marts.

SQL Server Replication

Replication is the process of creating and maintaining a separate copy of database in another server or the same server and keeping both synchronized. Replication is useful in a number of scenario:

1. To integrate multiple database to build a central warehouse: Businesses operating on a number of distant locations usually collect data in separate database. However the senior management needs to generate reports showing analysis of data collected from all these sites. Hence the need for central warehouse is increasing and replication proves to be a powerful way to create and maintain the warehouse. It can also be used to create and maintain a separate data mart for OLAP operations.

2. To ensure higher availability and improved performance of web applications: Web applications with the increasing number of users faces huge problem in avoiding the extra load on the database. Replication along with other techniques can offer the right solution by distributing users to a number of replicated database in different servers and thus improving the performance and ensure high availability.

Difference between Data Mining and Data Warehousing

Data Mining provides the Enterprise with intelligence and Data Warehousing provides the Enterprise with a memory.

Data warehousing is the process that is used to integrate and combine data from multiple sources and format into a single unified schema. So it provides the enterprise with a storage mechanism for its huge amount of data. On the other hand, Data mining is the process of extracting interesting patterns and knowledge from huge amount of data. So we can apply data mining techniques on the data warehouse of an enterprise to discover useful patterns.

What is Data Warehousing?

An organization may have a number of different systems like TPS, ERP, Accounting Systems and so on. Each of these systems collect data but the data collected through these systems are not always in the same format. So we need to combine all these data from different sources into a single standard form and this process of combining multiple databases into a single homogenous form is called data warehousing.

A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management’s decision making process.Subject-oriented

The data in the data warehouse is organized so that all the data elements relating to the same real-world event or object are linked together.

Time-variant

The changes to the data in the data warehouse are tracked and recorded so that reports can be produced showing changes over time.

Non-volatile

Data in the data warehouse is never over-written or deleted - once committed, the data is static, read-only, and retained for future reporting.

Integrated

The data warehouse contains data from most or all of an organization's operational systems and this data is made consistent.

A Welcome Note !!!!

Hello and welcome to all of you in this fascinating and rapidly changing world of data mining and warehousing. I being very interested on this topic would like to share all the new things I come across. I have been trying to understand the data mining concepts, techniques and tools for quite some time now but I faced the difficulty of getting useful and desired information on the topic at a single site. I will use this blog to make things easy for all of you interested in data mining with useful posts and to keep note of all the things I will learn.