OLAP Terminology

Developers of OLAP and multidimensional databases speak their own language and use terms such as cubes, dimensions, measures, and members. Here's an example that illustrates these terms.

Let's take a simple sales database and convert it to a multidimensional database. Every time company XYZ makes a sale, the salesperson records the date, the sales amount, the product sold, and the customer that bought it. When we convert this information into a multidimensional database, the numeric values become cells, or measures. In this example, the only measure is sales amount. A multidimensional database organizes the attributes of a sale (e.g., date, product, and customer) into dimensions (e.g., a time dimension, a product dimension, and a customer dimension). The individual items within a dimension are members. For example, split pea soup is a member in the product dimension. A multidimensional database stores measures, dimensions and their members in a cube. A cube is similar to a relational table but can have more than two dimensions. (For an illustration of cube, see Bob Pfeiff's "OLAP: Resistance Is Futile," page 22.)

The members in the dimensions are stored in hierarchies. The original simple sales database has a date entry for each product sold. When we add that date information to a cube, we organize all the individual dates into a hierarchy. We can choose to organize the dates into a standard calendar or a fiscal calendar. A standard calendar combines all the dates in May into a time member called May. We can group May with April and June to form Quarter 2, and we can group Quarter 2 with the other three quarters to form a member called Year 1999.

The terms roll up and drill down describe actions we can perform on members of a dimension. For example, if we drill down on Quarter 3, we see Quarter 3's direct descendents in the hierarchy (i.e., July, August, and September). If we roll up August, we see August's parent in the hierarchy (i.e., Quarter 3) and the siblings of August's parent (i.e., Quarter 1, Quarter 2, and Quarter 4). We can use rolled up as a synonym for aggregated to. For example, July, August, and September are rolled up to Quarter 3.

In a cube, any measure is available for any combination of members. This availability means that we can ask for the sales for product A in Quarter 3 or the sales for product A on May 23. The cube aggregates the sales values to determine the values at higher levels in the hierarchy. Even sales on May 23 is an aggregate because multiple transactions might have occurred on that day. A cube doesn't store the original transactions; it stores only the aggregations. When you create a cube, you choose how many aggregations the cube will have. The number of aggregations affects your MDX queries' performance.