Ragged Hierarchies

Here's how SQL Server 2000 handles unbalanced levels of information

For decision support, multidimensional databases beat relational databases in speed, size, and structure. The structure of a multidimensional database, or cube, is superior to a relational table for analysis because the cube's dimensions are hierarchical. These hierarchies mirror the business entities and relationships between entities that you want to analyze. For example, companies usually organize time by fiscal calendar, products by product families, and customers by regions or sales channels. Business analysts can navigate this structure intuitively.

Your goal in building a cube is to model a business accurately. The OLAP engine needs to be flexible enough to handle anomalies in data structure because data is never as clean as you think. In theory, the multidimensional cube neatly and elegantly organizes all business information into balanced hierarchies. In reality, dimensions need multiple hierarchies, and some hierarchies are unbalanced and have gaps. In this issue, I explain how the new dimension features of SQL Server 2000 Analysis Services can help you design cubes to solve these problems.

In SQL Server 7.0 OLAP Services, you had to balance all dimension hierarchies. (In a balanced hierarchy, all leaf members are the same distance from the root member.) Some common OLAP applications don't fit easily into a balanced hierarchy. For example, in financial analysis, developers commonly create a dimension to model an income statement. But income statements have an unbalanced number of income and expense categories, and an income statement dimension has no hierarchy level in which all members are the same type of business entity.

Analysis Services supports unbalanced hierarchies with a new dimension type: parent-child dimensions. You create this type of dimension with a dimension table that specifies the parent-child relationships between dimension members. In OLAP Services, you create all dimensions by identifying a column in the star schema for each dimension level. For example, if you create a geographically organized customer dimension, you might have columns for country, state, city, and customer name. To create parent-child dimensions in Analysis Services, you now identify two columns: a key and a parent key. Each row of the dimension table identifies a member and the member's parent. The sample human resources (HR) cube provided with Analysis Services demonstrates a parent-child dimension called Employees. Figure 1, page 66, illustrates the self-referencing employee table. Each row has a key called employee_id but also has a parent key called supervisor_id. This structure lets you create some branches of the hierarchy that are deeper than others.

Analysis Services also supports ragged hierarchies. (A ragged hierarchy includes dimension members whose parent belongs to a hierarchy more than one level above the child.) You can imagine a ragged hierarchy as one with a gap. For example, let's say a geography dimension has levels for country, state, and city. Washington, D.C., is not in a state, so its direct parent is United States. In OLAP Services, you could emulate ragged hierarchies by creating members with blank names or members with the same name as the parent. Microsoft recommended that front-end application vendors recognize this situation and display the hierarchy as if it were ragged. With Analysis Services, you can make the server hide these placeholder members by using the dimension-level setting Hide Members If. This property determines whether placeholder members will be visible to the front-end application.

The possible settings for Hide Members If are:

You can also skip levels by specifying a Skipped Levels Column in the parent-child dimension table. This column contains the number of levels to skip between parent member and child. You can't find this property in the dimension wizard; instead, choose the Advanced properties tab in the Dimension Editor (or Cube Editor, if the dimension isn't shared), as Figure 2 shows. (More than one cube can use a shared dimension.)

To enable ragged hierarchy support in your application, you need to do more than turn it on. To display ragged hierarchies and let you navigate your front-end application as if the levels were really skipped, the application needs to support ragged hierarchies. When Microsoft added support for ragged and unbalanced hierarchies, the company knew that legacy applications (developed for use with SQL Server 7.0) couldn't handle these new dimension types. Because of this limitation, Analysis Services creates placeholder members by default, regardless of the Hide Members If setting or the Skipped Levels Column.

To support the new dimension structures, an application must set a data source property, which tells Analysis Services that it has the latest MDX compatibility. You need to set this property, called DBPROP _MSMD_MDX_COMPATIBILITY, to a value of 2 to skip the placeholder members. But be sure to consider the following implications when you turn on skipped levels. When the application requests all children of a dimension member, the children can now come from different dimension levels. Also, if the application requests all descendants of a dimension member at a specified level, the results might not equal the total of the ancestor. (For example, if you request all states in the United States and add them, the sum won't equal the value of United States. You should expect this result because Washington, D.C.'s value is reflected in United States, but not in any state.)

Custom Rollups

Financial analysis applications frequently require unbalanced hierarchy support. Besides its inherent imbalance, an income statement provides other analysis challenges. When looking at an income statement, analysts like to see both income and expense amounts as positive numbers, but when OLAP Services aggregates these amounts, you don't want to add income and expense together. To accurately display the data, financial analysis reports should subtract expense from income.

This requirement was difficult to meet with OLAP Services, because you had to make a calculated member with a complicated formula to handle various situations. You can solve the problem more easily with two new custom rollup formulas (included only in the Enterprise Edition of Analysis Services). One formula is the same for all members of a dimension level, and the other varies for each dimension member. Microsoft calls the latter a custom member formula.

To add the custom rollup formulas, you can use the Analysis Manager Dimension Editor (or Cube Editor, for private dimensions—those available only to a single cube), as Figure 3 shows. Either type a simple formula directly into the Advanced Properties pane, or click the ellipsis to use the MDX editor window for more complicated formulas. Microsoft SQL Server Books Online (BOL) has a useful example for a cube with an inventory measure. In the time dimension, you would probably want the inventory measure to equal the last child of the current member, rather than the sum of all children. So the inventory level for the year, for example, should equal the inventory of the last quarter of the year. The inventory level for the quarter should equal the last month of the quarter, and so on.

The method for configuring custom member formulas is different from the method for configuring custom rollups. Figure 3 shows the Custom Members property set to False. Select that property, then click the ellipsis next to False. You'll see the Define Custom Member Column dialog box, as Figure 4 shows, which you can use to define the column of the dimension table used to retrieve member formulas. If you select Create a new column, the Existing column drop-down box changes to an Edit box, in which you can type a name for a new column to be created in the dimension table. Analysis Services will use this column to store the formula associated with each member of the dimension level.

To use Analysis Manager to enter custom member formulas, you must both write-enable the dimension and make it parent-child. Write-enabling a dimension is different from write-enabling a cube. A write-enabled cube lets you change cell values, whereas a write-enabled dimension lets you change dimension members. If you don't want to write-enable the dimension or make it parent-child, you need to either create your own custom application to enter the formulas or use Query Analyzer or Microsoft Access to directly edit the dimension table.

SQL Server's analysis capabilities are evolving quickly. SQL Server 7.0 was very competitive in the OLAP marketplace, but SQL Server 2000's Analysis Services fills many former gaps and raises the bar in performance, capacity, and ease of use. Financial analysis was one of SQL Server 7.0's few weaknesses. By adding unbalanced hierarchies and custom rollup formulas in SQL Server 2000, as well as dimension and cell-level write-back, Analysis Services now offers a powerful tool for financial analysts. I'll focus on the write-back features of Analysis Services in an upcoming issue.