| Downloads |
|---|
| 16514.zip |
Exception highlighting, or conditional formatting, lets you apply different display characteristics to various parts of a query result. For example, you might display profitability by product and highlight negative profit numbers (i.e., products that are losing money). Exception highlighting can draw attention to important numbers in a printed report, making the numbers bold or displaying them in a particular color. Highlighting is also important in an online, interactive analysis application. This month, I discuss SQL Server 2000 Analysis Services' cell properties, a powerful feature that lets analysis applications support exception highlighting.
In analysis applications that support them, cell properties let you control the display characteristics of numeric results. You can use three techniques to configure cell properties: calculated cells, calculated members, and custom member options. If you define a cube on the OLAP server, you have access to all three techniques. Defining cell properties on the server is extremely powerful because you define the criteria for displaying numbers only once for the many applications that might draw information from a central OLAP cube. However, a desktop application can use only two of the techniques, calculated cells and calculated members, to configure exception highlighting on a query-by-query basis.
Calculated Cells
The calculated cells technique uses a formula that determines a cube's cell value, a condition that the cell must meet to apply the formula, and a region of the cube in which the calculated cell applies. A calculated cell's definition might also include cell properties information. Like the calculated cells formula, the cell properties information applies to an individual cell only if that cell meets the calculated cell's condition. The cell properties information might include a numeric format string, foreground color, background color, font name, font size, and font flags (i.e., bold, italics, underline, or strikethrough). You can define calculated cells on the server by using Analysis Manager or on the client by using MDX.
Calculated Cells through Analysis Manager. Analysis Manager's Cube Editor includes a Calculated Cells Wizard to help you create calculated cells. For example, let's create a calculated cell that highlights all nonconsumable products with Unit Sales greater than 12,000. Start Analysis Manager, right-click the FoodMart 2000 Sales sample cube, and select Edit to start the Cube Editor. In the Cube Editor window, drop down the Insert menu and select Calculated Cells, which launches the Calculated Cells Wizard.
The first step in the wizard is to define which subcube, or cube region, the calculated cell will affect. To choose the nonconsumable product member, select the Product Dimension from the list on the left, then select the Members Set type A single member on the right. Next, expand the All Products dimension tree to the second level, and select Non-Consumable, as Figure 1 shows. When you select Next, the wizard prompts you for the optional calculated-cell condition. To apply highlighting only when Unit Sales for the nonconsumable product are greater than 12,000, select Meet the following condition and enter the formula
CalculationPassValue((Product.CurrentMember, \[Unit Sales\]), 0 ) > 12000
This condition formula might be more complicated than you expected. The difficulty in writing this formula is that it's self-referencing. The condition formula checks the value of the current cell, which references the calculated-cell formula again. To prevent this formula from endlessly evaluating itself, I used the CalculationPassValue() function to determine the cell's value before I applied the calculated cell. This approach works because MDX formulas require multiple passes to evaluate. Because Analysis Services evaluates calculated cells after pass 0, you can stop evaluating the condition formula at pass 0 to prevent it from referencing itself. The second parameter of CalculationPassValue() is the pass number on which you want to stop evaluating the MDX expression.
After you enter the condition formula and click Next, the wizard prompts you to enter the calculation formula. The calculation formula determines what the cell's value will be after Analysis Services evaluates the calculated cell. For this example, you don't want to change the cell's value, so enter the current cell's address. Again, you must use the CalculationPassValue() function to prevent MDX from being recursive. The calculation formula is
CalculationPassValue(Product.CurrentMember, 0 )
Click Next, name the new calculated cell—I named it NonConsumable Highlight—and click Finish. Now you have a calculated cell without display criteria.
To define the cell properties' display criteria, select the new calculated cell in the Cube Editor from the tree display on the left. Expand the Properties pane in the lower left corner, and select the Advanced tab, as Figure 2 shows. ForeColor and BackColor values are RGB values in which each hue is a value between 0 and 255 (0 is dark, and 255 is bright). The following formula lets you create your own color combinations:
Color Value = (Blue * 256 * 256) + (Green * 256) + (Red)
For example, if you want to create bright red, use a value of 255 for red and 0 for both blue and green, giving you a color value of 255. Bright green has the formula
Color Value = (0 * 256 * 256) + (255 * 256) + (0) = 65280
The possible values for the FontFlags property are 1 for bold, 2 for italic, 4 for underline, and 8 for strikethrough. You can combine flags by adding the numbers together. For example, 3 is 1 + 2, meaning bold plus italic.
The Format String property is more complicated. Its potential values differ for numeric-, string-, and date-type data. For numeric values, you supply one to four different parameters in a semicolon-delimited Format String for positive numbers, zero values, negative numbers, and null values, respectively. If you supply fewer than four parameters, each parameter applies to more than one category of values. For example, if you supply two parameters, the first parameter applies to positive numbers and zero values and the second parameter applies to negative numbers. Nulls receive default treatment, which is to return an empty string. An example of a two-parameter numeric Format String is
$#,##0;($#,##0)
This example uses parentheses to represent negative numbers instead of prefixing the number with a minus sign. For information about all the possible characters in a Format String, see SQL Server Books Online (BOL), "Using Cell Properties."
Calculated Cells through MDX queries. You can also use calculated cells in MDX queries to define cell properties. For example, the following MDX query demonstrates both how to define a calculated cell that has cell properties and how to request that the query return cell properties with the query result.
WITH CELL CALCULATION Sample FOR '(\{\[Product\],\[Product Family\],&\[Food\]\})'
AS 'CalculationPassValue(Product.CurrentMember,
0 )',
CONDITION = 'CalculationPassValue((Product
.CurrentMember,\[Unit Sales\]), 0 ) > 12000',
FORE_COLOR = '255 * 256'
SELECT \[Product\].\[Product Family\].Members on
Columns FROM Sales
CELL PROPERTIES VALUE, FORMATTED_VALUE,
FORE_COLOR, BACK_COLOR
This example displays in green any Food cells with Unit Sales greater than 12,000. The Non-Consumable value is in blue, which you specified when you defined the calculated cell with Analysis Manager. The Drink value will be in red (as I show later).
The last section of the MDX query, starting with the keywords CELL PROPERTIES, specifies which cell properties to return with the query's result. If you use the MDX sample application, or any other OLAP application, you must include the CELL PROPERTIES keyword in your MDX query to have the color and font properties affect the display of your query results. Format String affects the result regardless of whether you include the CELL PROPERTIES keyword because the default cell properties that a query result returns are VALUE, FORMATTED_VALUE, and ORDINAL. VALUE and FORMATTED_VALUE are the numeric and string representations of the cell contents. FORMATTED_VALUE uses Format String regardless of whether the query requested it. ORDINAL is a unique index of the cell within the query result.
Notice one other thing about the MDX query. The FORE_COLOR property is set equal to a numeric expression enclosed in single quotes. Like all cell properties, string values define FORE_COLOR, so quotes always enclose FORE_COLOR when you define it in MDX. For a cell property such as FORE_COLOR, the string must be an MDX expression.
Calculated Members
When using exception highlighting, you might want to define different display characteristics for numbers in different numeric ranges. For example, you might want to display low sales values in bold red and extraordinarily high sales values in green italics. The following code demonstrates how you can use MDX in a cell properties' definition to create multiple display characteristics based on a cell's value; this example also uses a calculated member instead of a calculated cell:
WITH MEMBER \[Measures\].\[Unit Sales Highlight\] as
'\[Unit Sales\]',
FORE_COLOR = 'iif(\[Unit Sales\] > 12000, 255 * 256,
iif(\[Unit Sales\] > 5000, 255, 0))',
FONT_FLAGS = 'iif(\[Unit Sales\] > 12000, 2,
iif(\[Unit Sales\] < 5000, 1, 0))'
SELECT \{\[Measures\].\[Unit Sales Highlight\]\} on
COLUMNS,
\[Drink\],Children on ROWS
FROM Sales
CELL PROPERTIES VALUE, FORMATTED_VALUE,
FORE_COLOR, FONT_FLAGS
You use the calculated member in place of Unit Sales to highlight high and low values. The calculated member displays values less than 5000 in bold red and values greater than 12,000 in green italics. I explored the trade-offs between using calculated cells and calculated members in "The Power of Calculated Cells," November 2000. The most significant point is that only SQL Server 2000 Enterprise Edition supports calculated cells, whereas all SQL Server 2000 editions support calculated members.
You can define calculated members on the server with Analysis Manager or on the client in MDX. One technique is to use Analysis Manager to define a highlighted measure, then change the nonhighlighted measure (Unit Sales) to hidden. Then, client applications can select only the highlighted measure. Or, you can use Analysis Manager to make the new calculated measure the default measure for the cube (this feature is new to Analysis Services).
Custom Member Options
The final technique for defining cell properties, custom member options, lets you define cell properties in a dimension table, then use that table to create a cube. Custom member options are useful if you define the criteria for exception highlighting in the cube's source data. The technique is similar to defining a cell property that retrieves its value from a member property, but custom member options are more flexible.
Using a member property, you specify the value for one cell property. Custom member options let you define multiple cell properties in one dimension-table column. The implication is that different dimension-table members can have different sets of cell properties. For example, you might define one dimension-table member with font and foreground color and another one with a custom format string and a background color.
To use custom member options to create cell properties, take the following steps:
- Turn on Custom Members for a dimension or a level in a dimension.
- Turn on Custom Member Options for the same dimension or level.
- Fill in member options in the dimension table.
- Reprocess the dimension.
- Reprocess the cube.
Only custom members can have custom member options. Custom members are dimension members that use a dimension-table column containing MDX formulas to determine members' values. To enable custom members, start Analysis Manager and expand the treeview to the shared dimensions of the FoodMart 2000 database. Right-click the Product Dimension, and select Edit to start the Dimension Editor. Within the Dimension Editor, select Product Family in the treeview, then expand the Properties pane on the lower left. Click the Advanced tab, and scroll down until you see the property labeled Custom Members. Click the ellipsis next to the value of the Custom Members' property. The Define Custom Member Column dialog box, which Figure 3, page 73, shows, appears and lets you enable custom members and create a new column in the product's dimension table to hold them. Enter FamilyCustomMembers as the column name, and click OK.
After you set up custom members, you can enable custom member options by clicking the ellipsis next to the value of the custom member options' property. The Define Custom Member Options dialog box, which Figure 4 shows, appears and lets you enable custom member options and create another dimension-table column to contain the cell property name and value pairs. I called this column FamilyMemberOptions.
Then, save your changes and start Microsoft Access to view the FoodMart 2000 database (FoodMart 2000.mdb is in \program files\microsoft analysis services\samples). Next, run the following update query to define cell properties for dimension members in the Drink product family:
UPDATE product_class SET product_class.FamilyMemberOptions = "FORE_COLOR='255'", product_class.FamilyCustomMembers = "Aggregate(Product.CurrentMember.Children)" WHERE (((product_class.product_family)="Drink"));
After you run this query, restart Analysis Manager and reprocess the product dimension and the Sales cube. Analysis Manager's data browser shows the Drink product family cell values in red.
A Powerful Tool
If you develop analysis applications, you should add support for cell properties. In other words, when your application generates MDX queries, you should request cell properties and retrieve them with the query results. Web Listing 1 (see the More on the Web box for download information) shows a Visual Basic (VB) program that demonstrates how to request and retrieve cell properties by using ADO. Exception highlighting is a powerful tool because it draws attention to some of an OLAP cube's most relevant information. Business analysis is about finding the trends, patterns, and exceptions in your business. Cell properties are the right tool for communicating the exceptions in your business data to your analysis application users. Put your exception-highlighting knowledge to the test in "March MDX Puzzle," page 72, and check out the answer to last month's puzzle in "February MDX Puzzle Solution Revealed," page 73.