0

I come from a background developing database driven applications. I am now trying to understand Data Warehouse concepts. I have seen plenty of questions here asking about the differences between fact tables; dimensions and measures e.g. here. I understand the differences. I have bought a book on Data Warehouse design using SQL Server and I am working through it.

I do not understand the difference between dimensions in a multidimensional database (SSAS instance) and tables in a relational database. Is it just a difference of terminology? i.e. a dimension is a table?

For example, if you look at this star schema, here; the dimensions contain foreign keys, attributes etc. What is the difference between a dimension and a relational database table?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
w0051977
  • 15,099
  • 32
  • 152
  • 329
  • http://stackoverflow.com/questions/1787883/what-is-multi-dimention-olap-cube-and-give-example-cube-with-more-than-3-dimenti/1797008#1797008 – Damir Sudarevic Oct 28 '14 at 14:40

1 Answers1

1

A dimension can be represented by a table or it might just be a directly created dimension in a cube. In other OLAP systems (i.e. TM1) you can build a dimension inside a cube simply from manually entered data. The dimension never exists in a table.

A dimension is meant to model some part of the business. It might be that the dimension (say a location dimension with states and countries) isn't even represented wholly in any one table or system.

Inside an MOLAP cube, there are no foreign keys or primary keys.

MOLAP stores facts and dimensions in a cube (with aggregations and partitions). ROLAP stores facts and dimensions in a database (with foreign keys etc.)

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • Thanks. Are you saying that a dimension is not always modelled on a database table? Can you post a link to an example of a dimension that is not modelled on a database table?+1. – w0051977 Oct 26 '14 at 11:11
  • Yes. Some tools don't require database tables to build dimensions. They build them from excel files, or interactively populate them by a user typing them in. Here is a link https://www.youtube.com/watch?v=OdL8Ywks-OI to a video that shows a Tm1 dimension being built manually by a user. (Note this is not SSAS). Note that a dimension is really just a tree with alternate hierarchies whose nodes (usually the leaf) join to some kind of fact. This can be represented in a relational table or directly in a cube. – Nick.Mc Oct 26 '14 at 11:30
  • After this Tm1 dimension is built, the cube has to be populated with fact data. You need a recordset (Excel, file, typed in manually) that provides data at the lowest level of detail. Then the dimensions roll it up as required. This is just giving you an alternate view of things (i.e. non SSAS) to help you understand what a dimension really is. – Nick.Mc Oct 26 '14 at 11:32