Inzata Announced as a 2020 Gartner Cool Vendor in Data Management!

Big DataBusiness IntelligenceData AnalyticsData Science

Relational vs. Multidimensional Databases: Why SQL Can Impair Your Analytics

By October 26, 2020 No Comments

What is a Relational Database?

A relational database is a type of database that is based on the relational model. The data within a relational database is organized through rows and columns in a two-dimensional format.

The relational database has been used since the early 1970s, and is the most widely used database type due to its ability to maintain data consistency across multiple applications and instances. Relational databases make it easy to be ACID (Atomicity, Consistency, Isolation, Durability) compliant, because of the way that they handle data at a granular level, and the fact that any changes made to the database will be permanent. SQL is the primary language used to communicate with relational databases.

Below is an example of a two dimensional data array. Each axis in the array is a dimension, and each entry within the dimensions is called a position.

Store Location Product 1 Product 2
New York 83 68
London 76 97
As you can see we have an X and a Y axis, with each position corresponding to a Product and a Store Location.

What is a Multidimensional Database?

A multidimensional database is another type of database that is optimized for online analytical processing (OLAP) applications and data warehouses. It is not uncommon to use a relational database to create a multidimensional database.

As the name suggests, multidimensional databases contain arrays of 3 or more dimensions. In a two dimensional database you have rows and columns, represented by X and Y. In a multidimensional database, you have X, Y, Z, etc. depending on the number of dimensions in your data. Below is an example of a 3-Dimensional Data Array represented in a relational table and in 3-D.

Item Store Location Customer Type Quantity
Product 1 New York Public 47
Product 2 New York Private 20
Product 1 London Public 36
Product 2 London Public 69
Product 1 New York Private 36
Product 2 New York Public 48
Product 1 London Private 40
Product 2 London Private 28

The third dimension we incorporated into our data is “Customer Type” which tells us whether our customer was public or private.

We can then add a fourth dimension to our data, which in this example is time. This allows us to keep track of our sales, giving us the ability to see how each product is selling in relation to each store location, customer type, and time.

What are the Advantages and Disadvantages of Relational Databases?

Advantages: 

Single Data Locations: A key benefit to using relational databases is that data is only stored in one location. This means that each department will pull the data from a single collective source, rather than each department having their own record of the same information. This also means that when data is updated by one department, that change is reflected across the entire system, so that everybody’s data is always updated.

Security: Certain tables can be made available only to who needs it, which means more security for sensitive information. For example, it is possible for only the shipping department to have access to client addresses, rather than making that information available tclient addresses, rather than making that information available to all departments.

Disadvantages:

Running queries: When it comes to running queries, the simplicity of relational databases comes to an end. In order to access data, complex joins of many tables may need to be made, and even simple queries may need to be structured in SQL by a professional.

Live System Environments: Running a new query, especially ones that use DELETE, ALTER TABLE, and INSERT, can be incredibly risky when using a live system environment. The slightest error can mess everything up across the entire system, leading to loss of time and productivity.

What are the Advantages and Disadvantages of Multidimensional Databases?

Advantages:

Similar Information is Grouped: All similar information is grouped into a single dimension, keeping things organized and making it easy to view or compare your data.

Speed: Overall, using a multidimensional database will be faster than using a relational database. It may take longer to set up your multidimensional database, but in the long run, it will process data and answer queries faster.

Easy Maintenance: Multidimensional databases are incredibly easy to maintain, due to the fact that data is stored the same way it is viewed: by attribute. 

Better Performance: A multidimensional database will achieve better performance than a relational database with the same data storage requirements. Database tuning allows for further increased performance. Although the database cannot be tuned for every single query, it is significantly easier and cheaper than tuning a relational database.

Disadvantages:

Complexity: Multidimensional databases are more complex, and may require experienced professionals to understand and analyze the data to the fullest extent.