What Is Dimensional Data?

Traditional relational databases are organized around a list of records. Each record contains related information that is organized into attributes (fields). The customer table of the stores_demo demonstration database, which includes fields for name, company, address, phone, and so forth, is a typical example. While this table has several fields of information, each row in the table pertains to only one customer. If you wanted to create a two-dimensional matrix with customer name and any other field (for example, phone number), you would realize that there is only a one-to-one correspondence. Table 5 shows a table with fields that have only a one-to-one correspondence.

You could put any combination of fields from the preceding customer table in this matrix, but you would always end up with a one-to-one correspondence, which shows that this table is not multidimensional and would not be well suited for a dimensional database.

However, consider a relational table that contains more than a one-to-one correspondence between the fields of the table. Suppose you create a table that contains sales data for products sold in each region of the country. For simplicity, suppose the company has three products that are sold in three regions. Table 6 shows how you might store this data in a relational table.

The table in Table 6 lends itself to multidimensional representation because it has more than one product per region and more than one region per product. Table 7 shows a two-dimensional matrix that better represents the many-to-many relationship of product and region data.

Although this data can be forced into the three-field relational table of Table 6, the data fits more naturally into the two-dimensional matrix of Table 7.

The performance advantages of the dimensional table over the traditional relational table can be great. A dimensional approach simplifies access to the data that you want to summarize or compare. For example, if you use the dimensional table to query the number of products sold in the West, the database server finds the West column and calculates the total for all row values in that column. To perform the same query on the relational table, the database server has to search and retrieve each row where the Region column equals west and then aggregate the data. In queries of this kind, the dimensional table can total all values of the West column in a fraction of the time it takes the relational table to find all the West records.