Multidimensionality
The structure of a multidimensional model is not table-based as in a relational database, but cube-based. Cubes modeled in a multidimensional database extend a key concept of spreadsheets. Just as a cell in a spreadsheet represents the intersection of two dimensions, such as product sales and sales region, a cell in an OLAP cube represents the intersection of up to 30 dimensions. For example, Products, Customers, Regions, Months, and so on. As in a spreadsheet, a cell might be calculated by formulas involving other cells.
OLAP enables users to add dimensions rather than additional tables, as in a relational model. The MOLAP cube structure allows for fast, flexible data modeling and calculation. One advantage over a relational model is the simplicity of locating cells. An application can identify a cell location by name, at the intersection of dimension elements, rather than by searching an index or the entire model. OLAP also incorporates advanced array-processing techniques and algorithms for managing data and calculations. As a result, unlike relational databases, OLAP does not store individual transaction records in two-dimensional, row-by-column format, like a spreadsheet. OLAP uses cubes to calculate arrays of consolidated information. The data and formulas are stored in an optimized multidimensional database, while queries against the data are created on demand.
Business data is multidimensional by nature and understanding the concept of multidimensionality informs the ways in which you collect, analyze, consolidate, and report business data.
For example, imagine you start your own retail computer business. You decide to prepare a financial forecast or business plan showing your expected sales, costs, and expenses. You are planning to sell three types of equipment: laptops, tablets, and phones. One way to structure the data in a spreadsheet is to track accounts down the rows and the products across the columns.
| Phones | Tablets | Laptops | |
|---|---|---|---|
| Sales | 10,000 | 20,000 | 30,000 |
| Cost of goods | 6,500 | 13,000 | 19,500 |
| Gross margin | 3,500 | 7,000 | 10,500 |
As time passes, you want to calculate total product figures. You want to see the actual numbers next to the forecast numbers. Then you think how useful it would be to see this information month-by-month, and year-to-date, as well. You also open five more stores and want to see the same figures displayed for each store and the total for all your stores. This is an example of multidimensional data.
A dimension in a multidimensional data set is a group of similar items that can be displayed as row or column headers in a report. With the example given, you can display Products across Time, Accounts across Time, Accounts by Store, or Products by Store.
- Products
- Stores
- Time (months and years)
- Accounts (sales, costs of goods, gross margin and so on)
- Version (actual, budget, forecast)
These are the five dimensions of the model.
Multidimensionality provides the ability to rearrange these dimensions. You can choose which dimensions to display as column headings and which as row headings. With a multidimensional database such as OLAP, any of the above views of the data is available at the click of a button.