OLAP based analysis


OLAP Analysis

Online analytical processing (OLAP) allows the user to access aggregated and organized data from business data sources, such as data warehouses, in a multidimensional structure called a cube. Microsoft provides tools and features for OLAP that user can use to design, deploy, and maintain cubes and other supporting objects.
Cubes in a data warehouse are stored in three different modes. A relational storage model is called Relational Online Analytical Processing mode or ROLAP, while a Multidimensional Online Analytical processing mode is called MOLAP. When dimensions are stored in a combination of the two modes then it is known as Hybrid Online Analytical Processing mode or HOLAP.


MOLAP (multidimensional online analytical processing) is online analytical processing (OLAP) that indexes directly into a multidimensional database. In general, an OLAP application treats data multi dimensionally; the user is able to view different aspects or facets of data aggregates such as sales by time, geography, and product model. If the data is stored in a relational data base, it can be viewed multi dimensionally, but only by successively accessing and processing a table for each dimension or aspect of a data aggregate. MOLAP processes data that is already stored in a multidimensional array in which all possible combinations of data are reflected, each in a cell that can be accessed directly. For this reason, MOLAP is, for most uses, faster and more user-responsive than relational online analytical processing (ROLAP), the main alternative to MOLAP.


Relational online analytical processing (ROLAP) is a form of online analytical processing (OLAP) that performs dynamic multidimensional analysis of data stored in a relational database rather than in a multidimensional database (which is usually considered the OLAP standard).
Data processing may take place within the database system, a mid-tier server, or the client. In a two-tiered architecture, the user submits a Structure Query Language (SQL) query to the database and receives back the requested data. In a three-tiered architecture, the user submits a request for multidimensional analysis and the ROLAP engine converts the request to SQL for submission to the database. Then the operation is performed in reverse: the engine converts the resulting data from SQL to a multidimensional format before it is returned to the client for viewing. Since ROLAP uses a relational database, it requires more processing time and/or disk space to perform some of the tasks that multidimensional databases are designed for. However, ROLAP supports larger user groups and greater amounts of data and is often used when these capacities are crucial, such as in a large and complex department of an enterprise.


Hybrid online analytical processing (HOLAP) is a combination of relational OLAP (ROLAP) and multidimensional OLAP (usually referred to simply as OLAP). HOLAP was developed to combine the greater data capacity of ROLAP with the superior processing capability of OLAP.
HOLAP can use varying combinations of ROLAP and OLAP technology. Typically it stores data in a both a relational database (RDB) and a multidimensional database (MDDB) and uses whichever one is best suited to the type of processing desired. The databases are used to store data in the most functional way. For data-heavy processing, the data is more efficiently stored in a RDB. For speculative processing, the data is more effectively stored in an MDDB.
HOLAP users can choose to store the results of queries to the MDDB to save the effort of looking for the same data over and over which saves time. Although this technique – called “materializing cells” – improves performance, it takes a toll on storage. The user has to strike a balance between performance and storage demand to get the most out of HOLAP. Nevertheless, because it offers the best features of both OLAP and ROLAP, HOLAP is increasingly preferred.

Data Mining

Data mining gives the user access to the information that is needed to make intelligent decisions about difficult business problems. Microsoft provides tools for data mining with which user can identify rules and patterns in the data, so that the user can determine why things happen and predict what will happen in the future. While creating a data mining solution in Analysis Services, user first creates a model that describes the business problem, and then user runs the data through an algorithm that generates a mathematical model of the data. User can then either visually explore the mining model or create prediction queries against it. Analysis Services can use datasets from both relational and OLAP databases, and includes a variety of algorithms that can be used to investigate that data.