In a nutshell, a multidimensional database simulates a multidimensional space and constructs multiple multidimensional cubes in this multidimensional space. The measurable data will be filled in these cubes, and each cube represents a data collection of a certain business scenario, and we can analyze the business data from various angles and granularity through the axes associated with these multidimensional cubes (that is, the dimensions representing the business perspective).
Before detailing the various concepts of multidimensional databases, let's compare the steps of using a relational database. First create a table, then insert some data into this table, and then you can use DQL SQL to query the data.
We follow a similar step when working with multidimensional databases.
Create dimensions and dimension members, construct cubes, insert metrics into the cubes, and use MDX
What is MDX?
A dimension can be considered as an axis in a multidimensional space, and this dimension contains several dimension members, which can be considered as scales on the axis of this dimension.
Here, we start by creating three dimensions: region, date and product, and the dimension members corresponding to these three dimensions.
Next, we construct one cube with a three-dimensional structure, which is associated with the three dimensions we created before.
Tip: Using a three-dimensional structure does not mean that a multidimensional database can only build three-dimensional cubes, but that three dimensions are the most intuitive; in fact, a multidimensional database can build any number of multidimensional cubes in any number of dimensions.
Finally, fill this cube with some measurable data so that a multidimensional data cube representing a certain business scenario has been constructed.
Multidimensional data structures can provide a variety of different perspectives for data users to perform data analysis.
You can gain insight into the data within a cube through any perspective and granularity you are interested in.
Multidimensional databases also provide the ability to query data at different granularities. In the above example, we have only added month-level metrics, but you can still query quarterly data and the data will be automatically aggregated.
Multidimensional databases can also support more complex queries so that you can gain deeper insight into the valuable information contained within the data.
Compared with relational databases based on two-dimensional table structures, multidimensional databases can provide enhanced and convenient analysis capabilities for data, and certain query schemes for data are complex or even impossible to implement in relational databases.
For example, in a relational database if you want to associate several tables with very large amounts of data for a query, it may take several hours from the beginning to get the query results, but in a multidimensional database you can associate several very large cubes for a query, which will not have a significant impact on the efficiency of the query.