0

Just wondering how is data actually stored in a multidimensional database.

All the references that I saw show a cube for 3-dimension, just to imagine its fine, but in actual it has to be 2-D only, having foreign key relationship rite?

Thanks,

Hemal
  • 265
  • 1
  • 7
  • 17

1 Answers1

2

Finally found the answer to my own question, posting so that it might be helpful to others who might have similar doubts or want to know about multi-dimensional database.

Unlike Relational database which uses ER-Model (used for OLTP queries) , multidimensional database (used for OLAP queries) uses Star-Schema or Snowflake-schema for analytical study of large amount of data.

These schema consists of one fact table and multiple dimensional tables. Irrespective of the number of dimensions(i.e. dimensional table), here also data is stored in simple 2-D format with same Primary key - Foreign Key concept.

Little more technical detail: All the dimensional tables have their own primary key. The number of rows in dimensional table are expected to be less (compared to fact table), however the number of columns are expected to be far more giving all the details of that dimension.

The fact table consists of the above discussed primary key of all dimensional tables as foreign key, with all there possible combinations.

Basic Example: Dimensional Table “a”:

Primary Key ……many other columns of this dimension

A1
A2

Dimensional Table “b”:

Primary Key ……many other columns of this dimension

B1
B2

Fact Table: (all combinations of the PK’s from all dimensional tables)

Foreign Key from Table “a” Foreign Key from Table “b”

A1 B1

A1 B2

A2 B1

A2 B2

Hence the number of rows in the fact table is very high(compared to dimensional table), but the number of column are almost same as the number of dimensional tables.

Using this fact table and based on the query, data can be retrieved from the required dimensional table, in different combinations, which can be used for analytical studies.

Hemal
  • 265
  • 1
  • 7
  • 17
  • Doesn't that also mean that large number of dimensions will lead to high number of these combinations in place and will have very high number of records? Less number of dimensions and a very heavy fact can be an ideal situation for OLAP storage with star schema model then. Just wondering while I was also looking at data storage in OLAP systems. – Rupesh Kumar Jul 26 '18 at 14:02
  • A multidimensional DB is a relational DB where the schema has one of certain styles. Tables typical of OLAP are views of a more normalized DB typical of OLTP. There is no difficulty making an ER model for a multidimensional DB schema. Although in general ER modeling is inadequate for describing relational DBs. – philipxy Oct 21 '20 at 03:15