The Solution
Format String differs from other cell properties because it can't have an MDX formula as a value (e.g., ForeColor lets you use an MDX conditional formula to change the font color, depending on the cube cell's contents or location). Format String allows only one format per calculated member or calculated cell. Therefore, you need four calculated cell definitions, each with its own Format String, to display a value that has the correct number of decimal places. You need four definitions because the World Bank data can have one of four possible levels of accuracy: 0, 1, 2, or 3 decimal places. If the data required more levels of accuracy, you would need more definitions.
This calculated cell solution has two parts. First, the loaded measure from the fact table, Measures.[Scale], specifies scaling and accuracy. This measure has values such as 0.2, 0.3, 6.1, 6.2, 9.1, 9.3 (where the integers before the decimal point specify what power of 10 to apply to the cell's value, and the number after the decimal point specifies how many decimal places to display). The number's decimal portion can have only values 0 through 3 (i.e., up to three decimal places). Second, you use a calculated member (part of the Measures dimension) to perform the scaling calculation and display the result. This value, Measures.[Display Value], has the following MDX formula:
Measures.[Value]/10^INT(Measures.[Scale])
Note that the calculated cells reference this calculated member. The calculated cells don't reference a loaded measure directly because the fact table doesn't include only one value for each fact record.
A fact record consists of five values—Yval, Qval, Mval, Wval, and Dval—the combination of which is one measure from the user's point of view. Analysis Services loads each of these values into a different measure, and the calculated measure—Measures.[Value]—picks the appropriate value depending on the current member of the Time dimension. The following MDX formula calculates Measures.[Value]:
IIF(Time.CurrentMember.Level.Ordinal = 1, Measures.Yval,
IIF(Time.CurrentMember.Level.Ordinal = 2, Measures.Qval,
IIF(Time.CurrentMember.Level.Ordinal = 3, Measures.Mval,
IIF(Time.CurrentMember.Level.Ordinal = 4, Measures.Wval,
IIF(Time.CurrentMember.Level.Ordinal = 5, Measures.Dval,NULL)))))
The four calculated cell definitions are ScaledVal0Decimal, ScaledVal1Decimal, ScaledVal2Decimal, and ScaledVal3Decimal; the names reflect the number of decimal places that each calculated cell handles. Each definition has the same Calculation Subcube and Calculation Value. However, Calculation Condition and Format String differ from one calculated cell definition to the next.
Creating calculated cells is a simple process. The Analysis Manager Calculated Cells Wizard is easy to use, even though the calculated cell concept can be difficult to understand. SQL Server Books Online (BOL) has a calculated cell overview that contains several complicated cube diagrams. Calculated cells, like calculated members, are parts of a cube, and you use the cube editor to add or modify them. Within the cube editor, the left pane shows the folders for Dimensions, Measures, Calculated Members, Calculated Cells (available only in Analysis Services Enterprise Edition), Actions, and Named Sets. Select the Calculated Cells folder, right-click, and select New Calculated Cells to launch the wizard.
To define the calculation Subcube, you use the calculated measure [Measures].[Display Value], but Analysis Manager can't display calculated measures—only loaded measures, as Figure 3 shows. So, you need to choose a loaded measure to use as a placeholder. When you finish using the wizard, you need to edit the Subcube property directly to designate Measures.[Display Value].
Prev. page
1
[2]
3
next page 
