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.
Be the first to comment on this post using the section below.