main logo of the blog

Database Related - Multidimensional

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).
Relationship between two tables or entites
So from the above diagram we find that Name and Salary is associated with Department (DeptID). So Parijat who is in Department 20 gets a salary of 60000. Now we must understand how to plot this in Multidimensional case.

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
Multidimensional Database Outline
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". 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. Whereas a relational database is typically accessed using a Structured Query Language (SQL) query, a multidimensional database allows a user to ask questions like "How many Colas have been sold in India so far this year?"