85

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

freshbm
  • 5,540
  • 5
  • 46
  • 75
veljasije
  • 6,722
  • 12
  • 48
  • 79
  • I found first ans. useful : https://www.quora.com/What-is-the-difference-between-a-data-warehouse-and-a-cube – Channa Apr 25 '20 at 13:19

3 Answers3

106

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

Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152
  • 1
    A datawarehouse is not "a database with a dimensional design". A DW is a Subject Oriented, Integrated, Nonvolatile and Time Variant collection of data. A data warehouse architecture may or may not include "dimensional" designs - typically only for Marts, not necessarily as part of the DW. – nvogel Sep 26 '13 at 04:35
  • 1
    @sqlvogel I wasn't trying to give a definitive definition of a data warehouse, just contrasting it with OLAP using simple language. I've updated my answer to take into account DWs that don't use dimensional design. – Neil McGuigan Sep 26 '13 at 04:51
  • In my opinion, data warehouse is a preparation for OLAP operations. Is that right? OLAP operations also have the concepts of `dimension` and `fact`. – mingchau Apr 16 '19 at 09:50
  • 1
    @mingchau somewhat. You can do OLAP operations against a flat file, excel file, or binary cube. But a data warehouse is often the source. Both data warehouses and OLAP have the concepts of dimensions and facts – Neil McGuigan Nov 06 '19 at 20:15
11

A data warehouse holds the data you wish to run reports on, analyze, etc.

A cube organize this data by grouping data into defined dimensions. You can have multiple dimensions (think a uber-pivot table in Excel).

For example, in your data warehouse you have all your sales, but running complex SQL queries can be time consuming. So from your data warehouse you create a cube which indexes and precompute the data. In your cube you could have all those precomputed dimensions : sales by months, by week, by salesman, by client, by geographical region, by product color, etc. Then you can run OLAP queries on your cube to have the total, average and maximum sales by (month, salesman, region), or by (color, region), or by (salesman, month). Since all the data is precomputed and indexed, the queries are really fast.

sthiy
  • 115
  • 5
  • 2
    Didn't DW already has measures in Fact tables and dimensions in Dimension tables? – veljasije Sep 20 '13 at 12:38
  • From Wikipedia : "The main source of the data is cleaned, transformed, cataloged and made available for use by managers and other business professionals for data mining, online analytical processing". The data from the transactional database is transformed when transferred in the DW, but isn't indexed as much as in a cube. You have your dimension tables and fact table in your DW, but you can't drill down and aggregate your facts as quickly and easily as in a cube. – sthiy Sep 20 '13 at 14:48
2

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.

No, they really do the same things! OLAP is more precalculate than DWH. OLAP is like aggregates in DWH

user3657020
  • 21
  • 2