Microsoft SQL Server OLAP Solution - A Survey

Abstract: Microsoft SQL Server 2008 offers technologies for performing On-Line Analytical Processing (OLAP), directly on data stored in data warehouses, instead of moving the data into some offline OLAP tool. This brings certain benefits, such as elimination of data copying and better integration with the DBMS compared with off-line OLAP tools. This report reviews SQL Server support for OLAP, solution architectures, tools and components involved. Standard storage options are discussed but the focus of this report is relational storage of OLAP data. Scalability test is conducted to measure performance of Relational OLAP (ROLAP) storage option. The scalability test shows that when ROLAP storage mode is used, query response time grows linearly with dataset size. A tutorial is appended to demonstrate how to perform OLAP tasks using SQL Server in practice.