Database Related – Multidimensional Database

MDBMS – Multidimensional DataBase Management System

To understand Multidimensional Database, you are required to have some knowledge on Relational Database. I’ll explain RDBMS in simpler terms so that everyone can understand. We all know RDBMS, which is nothing but Relational Database Management System.

The name itself says that the database system forms a relation between tables which contains fact. Each and every table has rows and columns which forms 2 dimension. For every corresponding rows and columns of a table there will be one value. It is like a 2 dimensional matrix. So if we assume any matrix like

Table T1

	Name	DeptID
R1	Parijat	20
R2	Sam	40

Where Name and DeptID are two columns and R1 and R2 are two rows.
Now to access the value ‘Parijat’ we need to call R1->Name using Standard SQL (Structured Query Language) which is a database language. Similarly, to access 40 we need to call R2->DeptID.
These columns are known as attribute of the table (table is also known as entity) and rows are known as tuples or values.

Now we can create another table T2 having DeptID as common attribute and is linked to the table T1. Eg, Table T2

	DeptID	Salary
R1	20	60000
R2	40	50000

So this two table are linked by DeptID and hence this type of database system is known as Relational DBMS where there is a relation between tables (or entities).

From the above diagram we find that Name and Salary is associated with Department (DeptID). Parijat who is in Department 20 gets a salary of 60000. Now we must understand how to plot this in Multidimensional case.

Multidimensional Database

Now to understand Multidimensional, you must understand the word Multi which is a short form of multiple. In above table we saw that it is a 2 dimensional (rows and column) architecture. But in case of Multidimensional, we are going to have more than 2 dimension (generally 6/8 dimension) which depends on the application you are building.

It is pretty tough to imagine that kind of situation but I will try my best to explain this scenario.
So here, the multidimensional database structure will be

In this above table you can see that the salary value is recurring and this type of database structure is called denormalized. RDBMS is normalized and it stores only the required data and there will be no repetitive. Generally it is not used in Online Transaction Processing (OLTP) System because of the denormalized form.

It is generally used in analyzing company’s production OLTP data and further budgeting and forecasting of that company. It displays a readymade data for end user ex. as you can see Parijat is working in dept 20 and he gets 60000 as monthly salary.

If you had to view the same thing in OLTP or RDBMS then you would have required running 2-3 SQL queries. The main advantage is that it is faster than RDBMS and it can rapidly aggregate a lot of data in a very short span of time.
Multidimensional structure is defined as “a variation of the relational model that uses multidimensional structures to organize data and express the relationships between data”.

Multidimensional Optimization

A multidimensional database (MDB) is a type of database that is optimized for data warehouse and online analytical processing (OLAP) applications. Multidimensional databases are frequently created using input from existing relational databases. A relational database is typically accessed using a Structured Query Language (SQL) query. A multidimensional database allows a user to ask questions

How many Colas have been sold in India so far this year?

So get in touch with Hyperion Essbase, a true multidimensional database.