Data Warehouse vs. OLAP Cube?

data-warehouse olap olap-cube
veljasije picture veljasije · Sep 20, 2013 · Viewed 53.3k times · Source

Can anyone explain what is really distinction between Data Warehouse and OLAP Cubes?

Are they different approach for same thing?

Is one of them deprecated in comparison with other?

Are there any performance issues in one of them?

Any explanation is welcomed

Answer

Neil McGuigan picture Neil McGuigan · Sep 20, 2013

A data warehouse is a database with a design that makes analyzing data easier† and faster, often with data from multiple sources. It usually has a dimensional model, meaning fact tables and dimension tables.

OLAP is a set of operations that one can do on a data set, such as pivoting, slicing, dicing, drilling. For example, one can do OLAP operations with Excel PivotTables. There are certain SQL statements which are "for OLAP", such as PIVOT, group by CUBE(), group by ROLLUP(), and group by GROUPING SETS(), as well as the various window functions

An OLAP Server is a type of server software that facilitates OLAP operations, for example with caching and query re-writing. OLAP operations are often expressed in MDX, and your OLAP server might translate MDX into regular SQL for your database. Or it might work against its own binary file format. A dimensional model inside an OLAP server is called an OLAP cube

You can have a data warehouse and not use OLAP at all (you just run reports).

You can also do OLAP operations on something other than a data warehouse, such as a flat file.

Are they different approach for same thing?

No, a data warehouse is a place to store data in an easily analyzable format, and OLAP is a method to analyze data.

Are one of them deprecated in comparison with other?

No, they compliment each other in that a data warehouse makes it easy to analyze data using OLAP, and OLAP can make analyzing a data warehouse more useful.

Is there any performance issues in one of them?

Yes. A data warehouse is meant to store lots and lots of data, and thus it will take time to query. Performance can be improved by using indexes or a columnar db, caching, RAID 10 SSDs, partitioning, and by pre-aggregating some data.

See also: https://dba.stackexchange.com/questions/45655/what-are-measures-and-dimensions-in-cubes

† as opposed to making transactions easier/more integral

Related questions

Benefits of using Staging Database while designing Data Warehouse

I am in process of designing a Data Warehouse Architecture. While exploring various options to Extract data from Production and putting into Data Warehouse, I came across many articles which mainly suggested following two approaches - Production DB ----> …

Read More
What is Multi Dimension OLAP CUBE and give example cube with more than 3 dimensions

As I am new to SSAS, have been reading an article on Multi-Dimension OLAP Cube and struggling to understand Cube concepts, It has been said that Although the term "cube" suggests three dimensions, a cube can have up to 64 dimensions. …

Read More
Should OLAP databases be denormalized for read performance?

I always thought that databases should be denormalized for read performance, as it is done for OLAP database design, and not exaggerated much further 3NF for OLTP design. PerformanceDBA in various posts, for ex., in Performance of different aproaches to …

Read More