What is Multi Dimension OLAP CUBE and give example cube with more than 3 dimensions

In the DW world, the word "dimension" is overloaded -- changes meaning depending on context. Here is an example.

• On a certain date, a customer walks into a store and buys a product.

This example has four dimensions (date, customer, store, product) and one fact (sales). So a typical Kimball star would look like:

A dimension (table) is a look-up table for properties of objects that rarely change. Product, customer and store may change some of their properties (attributes), but they rarely do. Fact table captures interactions between these objects. At the intersection of dimensions date, store, product and customer lies a measure SalesAmount. Note how easy is to aggregate (sum) the sales amount by date, by year, by product, by brand, by city, by country, by age group, by whatever -- which was the idea in the first place.

Don't think of a cube as a three-dimensional structure (despite the name). A "dimension" in a data warehouse situation is simply a varying value that you can use to access data in your warehouse. You could think of them as key parts but ones that can be accessed individually, or in combination, quite easily (unlike primary keys in a classical table).

As for an example, you may have the following dimensions in a warehouse for keeping customer and sales data.

• Customer ID.
• State (location).
• Year.
• Month.
• Day of month.

That layout (a five-dimensional "super-hyper-cube") would allow queries to be easily performed for customers that cross state boundaries and that may have different purchasing patterns throughout the year (and even at different times of the month).

All of those key-parts would just point to a single sales figure for the day of month in a specific month in a specific year in a specific location for a specific customer.

An example on how to access that data. Let's say you wanted to see how all customer's buying patterns changed on a monthly basis, averaged over all the years. You would do this to see which customers generated the most revenue for you at specific times of the year so you could, for example, target your advertising at them in the month or so before then.

You would use the customer ID and month to extract information, effectively "collapsing" the state, year and day-of-month dimensions (in other words, sum up the sales figures for those three dimensions to get a two-dimensional result, customers vs. month).