Data Warehouse v/s OLAP

'Data warehouse’ and ‘OLAP’ are terms which are often used interchangeably. Actually they refer to two different components of a decision support system. While data in a data warehouse is composed of the historical data of the organization stored for end user analysis, OLAP is a technology that enables a data warehouse to be used effectively for online analysis using complex analytical queries. The differences between OLAP and data warehouse is tabulated below for ease of understanding:

Data Warehouse

Data from different data sources is stored in a relational database for end use analysis Data is organized in summarized, aggregated, subject oriented, non volatile patterns.

Data is a data warehouse is consolidated, flexible collection of data Supports analysis of data but does not support online analysis of data.

Online Analytical Processing

A tool to evaluate and analyze the data in the data warehouse using analytical queries.

A tool which helps organize data in the data warehouse using multidimensional models of data aggregation and summarization.

Supports the data analyst in real time and enables online analysis of data with speed and flexibility.

Clustered v/s Non Clustered Index

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

SQL Server Views v/s Materialized Views

A view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used.All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.

A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition.

A View can also be used to improve security by restricting access to a predetermined set of rows or columns.In addition to operating on base tables, one View can be based on another, a view can also JOIN a view with a table (GROUP BY or UNION).

Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse.

A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data.The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete.

You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.A materialized view can be stored in the same database as its base table(s) or in a different database. 

Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.

MDX Examples

Hi All,
I found some examples of  MDX queries based on Adventure Works cube.Please find the link
Thanks,
Maruthi...