Data warehouses, a multi-dimensional being (1)

Tom Kyte recently wrote on time travel. This got me thinking... time… the fourth dimension… dimensions… data warehouses.

In the physical world around us we are used to thinking in three dimensions. A carpenter makes a box; it has length, width, and height (we could use other labels such as breadth and depth.) Some people can also visualise the time dimension, that box is here today but gone tomorrow.

In data warehouses we too have dimensions. In our case we are probably not using dimensions to answer questions about the size of an object (but we could be!) but we are using our dimensions to answer other questions. Typically our dimensions are: who, what, where, and when. Most of our questions use a combination of these dimensions to answer a quantity question such as how many or how much. Sometimes, we know the quantity and need to find the dimensional item – “Who bought the most twine last year?” “What were the top five chocolate buying cities in 1997?”

So, if “what, where, when, and who” are dimensions does that mean that there only four possible dimensions in a data warehouse? Not surprisingly the answer to that is “NO.” Apart from the fact that you can also ask questions that don't start with a ‘w’ such as ‘how’ [my wife tells me that the Saxon word was wo and does start with a w!] In one of my data warehouses “how was this sale made, was it in-store or on the web?” is a common question. It is also perfectly possible to use the same basic question multiple times: I have a chain of pizza restaurants, I know who the customer was (they have a loyalty card), I know who the server was and who supplied each ingredient for the meal. Clearly each of these ‘who’ questions are independent of the others. Other questions could be less clear-cut; where a customer lives and where they shop could well share a dimensional domain (for example they both have street addresses and map coordinates, the later is especially useful if you have GIS enabled data warehouse)