Dimensions implement the user interface (UI) in a business intelligence (BI) tool. In a dimensional data warehouse (DW)/BI system, the textual descriptors of all the data warehouse entities like customer, product, location and time reside in dimension tables. My two previous columns carefully described three major types of dimensions according to how the DW/BI system responds to their slowly changing characteristics. But why all this fuss about dimensions? They are the smallest tables in the data warehouse, and the real “meat” is actually the set of numeric measurements in the fact tables. But that argument misses the point that the DW/BI system is always accessed through the dimensions. The dimensions are the gatekeepers, the entry points, the labels, the groupings, the drill-down paths and, ultimately, the texture of the DW/BI system. The actual content of the dimensions determines what is shown on the screen of a BI tool and what UI gestures are possible. That is why we say that the dimensions implement the UI.


This column points out what a good BI tool must be able to do with the dimensions in order to implement an expressive, easy-to-use DW/BI system. I invite you to compare this list against your own BI tools. Almost all of the functions described should be accomplished with single UI gestures, such as dragging an item from a list and dropping it onto a target.



Assemble a BI query or report request by first selecting dimension attributes and then selecting facts to be summarized. This requirement is so basic that it is easy to overlook. One has to be able to see the dimensions and the facts in order to use them. Look for a clean, linear list of all the attributes in your dimensions. Do not accept a normalized snowflaked portrayal, which may appeal to data modelers but is notoriously intimidating to end users. The dimension attributes and the numeric facts should be added to a query or report request with simple UI gestures, as suggested by Figure 1.


Drill down by adding a row header. The most fundamental maneuver in a BI tool is drilling down to a more detailed perspective. In almost all cases, drilling down has nothing to do with declared hierarchies. For instance, in Figure 1, you can drill down by dragging the promotion_type attribute into the results set from the promotion dimension. This would let you see how the individual brands did under different types of promotions. This is drilling down in its most effective form.


Browse a dimension to preview permissible values and set constraints. In Figure 1, you should be able to see a list of all the product categories by double-clicking on the category attribute in the product dimension. This list should serve both as a preview of what the row labels will be when you use the attribute as a row header, and as a place to set constraints, such as category = “Candy.” Make sure you can set multiple picks if you want a short list of simultaneous constraints.


Restrict the results of a dimension browse with other constraints in effect. Sometimes, the list of dimension attribute values is too long to be useful. Make sure that the list can be shortened by applying the constraints you have already set on other attributes in that dimension. An advanced feature would let you shorten this list even further by traversing the fact table and applying constraints that have been set on other dimensions. For instance, maybe you want a list of product categories that were sold in a particular store in January.



Drill across by “accreting” measures under labels defined by conformed dimension attributes.In previous DM Review articles, I described the architecture of drill-across reports that delivered integrated results from multiple separate fact tables (see “Essential Steps for the Integrated Enterprise Data Warehouse,” April and May 2008). Such a report is shown in Figure 2, where we are drilling across three separate fact tables: shipments, inventory and sales.


The first column in Figure 2 is the product name, which comes from a product dimension that must be attached to each fact table separately. The product name is a conformed attribute because it has the same column name and content in all three product dimensions, which could well be located on physically separate machines. The challenge for a BI tool is to allow the end user or report designer to “pin” the product name as a row header and then systematically visit various possible fact tables, dragging separate fact columns into the report.


Stepping back from these BI tool requirements, we can judge the maturity and experience of the BI tool by first asking whether these maneuvers are even possible, and then asking how easy they are to use. I think it is amazing that some BI tools still don’t “get it”; I have talked about these capabilities for 20 years, and we had most of these functions in the Metaphor tool suite starting in 1984.


Judge the ease of use with a simple test: count the clicks. A double-click or a click-and-drag counts as a single click. To my way of thinking, one click counts as outstanding, two clicks is pretty good, three clicks is marginal and more than three clicks is unacceptable.


Modern BI tools have lots more features than those I have described in this column, but what good are advanced features if the BI tool can’t support the basic maneuvers? Put your tool to the test.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access