What is a multidimensional database?
A multidimensional database (MDB) is a type of database that is optimized for data warehouse and online analytical processing (OLAP) applications. MDBs are frequently created using input from existing relational databases. An OLAP application that accesses data from a multidimensional database is known as a multidimensional OLAP (MOLAP) application.
A multidimensional database provides the ability to rapidly process data and generate answers quickly. MDBs let users ask questions about businesses operations and trends. Multidimensional database management systems are used to manage these databases. Approaches vary as to how MDB data is stored and how user interfaces work.
Multidimensional databases can be built using a relational database. Relational databases organize data points with defined relationships for easy access. Conceptually, a multidimensional database uses the idea of a data cube to represent the dimensions of data available to a user. MDBs have three or more dimensions to them, labeled as X, Y and Z dimensions. This is opposed to databases with two dimensions, which have rows and columns and only use X and Y labels.
In an MDB, sales could be viewed in the dimensions of the product model, geography, time or some additional dimension. In this case, sales is known as the measure attribute of the data cube and the other dimensions are seen as feature attributes. A database creator can define hierarchies and levels within a dimension, such as state and city levels within a geographical hierarchy.
What are the advantages and disadvantages of a multidimensional database?
MDBs come with a number of benefits and challenges when compared to other types of databases. They include the following:
- Easy maintenance. Because data is stored the same way it is viewed -- by attribute -- it's easy to keep track of and maintain.
- Similar data groupings. Similar data is grouped in single dimensions, which helps with organization and data presentation.
- Performance. MDBs are able to process data and answer queries faster than relational databases.
- Different views of data sets. Users can analyze relationships between data sets and categories in different visual layouts.
- Complexity. MDBs are more complex than relational databases and may require more training and experience.
- Performance issues. Performance can become impaired if the system doesn't cache correctly; for instance, if Google hasn't yet stored a cached view for the page.
OLAP and multidimensional database relationship explained
OLAP is a computing method that enables users to easily and selectively extract and query data and then analyze it from different points of view. OLAP often aids in trend analysis, financial reporting, budget planning and forecasting. Some examples of the leading OLAP vendors include Microsoft, Oracle, IBM and SAP.
Multidimensional databases are important to the functioning of OLAP systems. They consolidate and calculate data and provide for the retrieval and calculation of data subsets. MOLAP is a type of OLAP that indexes directly into a multidimensional database.
Multidimensional database vs. a relational database
The obvious difference between MDBs and relational databases is the former are multidimensional and the latter are two-dimensional. However, there is more to it than that.
Relational databases. A relational database is a collection of data points that are organized to identify defined relationships between key data points.
In the relational database model, various data structures -- such as data tables, indexes and views -- are used. They remain separate from the physical storage structures, enabling database administrators to edit the physical data storage without affecting the logical data structure. Relational databases organize data in two dimensions with rows and columns, and use Structured Query Language (SQL) as their primary language.
Relational databases make it easy to sort and find information, which helps organizations make business decisions efficiently and minimize costs. These databases work well with structured data.
Multidimensional databases. MDBs organize data in three dimensions, using X, Y and Z labels. Dimensions can include data such as location, time, individuals and actions, such as sales or orders.
Multidimensional databases enable a user to ask questions that consider multiple factors. They enable users to uncover the nuances of the data involved and more clearly show how business operations are doing and identify trends.
An example of such a database query would be "How many iMacs have been sold in Nebraska so far this year?" and similar questions related to summarizing business operations and trends.
Learn more about multidimensional databases and the various advantages they can provide in this article.