MSAS - Implementing Calculations Using MDX Part 1 Page - 2

Using Function Libraries to Build Calculated Members

Microsoft SQL Server 2000 Analysis Services provides an extensive library of functions for use while creating calculated members. However it also supports other function libraries. The libraries used by Microsoft Visual Basic for Application Expression services is automatically registered in Analysis services. Microsoft Excel worksheet library is also automatically registered in Analysis services if Microsoft Excel is installed on the system having Analysis services. Other libraries that are separately installed on the machine can be registered by clicking Register in the Calculated Member Builder.

However, functions in these libraries can be used only if the preconditions for their use are satisfied. The conditions being that the function should accept as arguments only string or numeric data types or array or a variant data type containing string or numeric values. The functions should return only a string or a numeric data type or a variant data type containing string or numeric values. Therefore, it is important that every function should be tested on the above conditions before being used in the creation of calculated members.

If multiple libraries use the same function, the Analysis services library takes precedence over the other libraries. Thereafter precedence is decided on the basis of the order of registration.

It must be noted that if end users are to see the values returned by the functions correctly the relevant library must be installed in the system and registered.

Calculated Member Builder

On the Insert menu of the Cube Editor or Virtual Cube editor click Calculated member. The calculated member builder dialog box opens. The first three boxes determine the dimension's characteristics of the calculated member--Parent dimension (the dimensions to which it belongs), Parent member (the parent under which it is attached), and Member name.

Let us leave Parent dimension set to Measures. The parent member determines the location of the calculated member in the dimension structure. This option is enabled if you select a parent dimension (other than the Measures dimension) that has more than one level. Clicking change will display the Select the Parent Member dialog box. This option is enabled if the user selects a parent dimension other than Measures that has more than one level.

Note that the Parent Member box is unavailable because the measure dimension does not support hierarchies. In the Member name box, let us enter Average price as the member name.

The lower part of Calculated Member Builder provides all the components necessary for building the calculated member expression. Under Data, expand the Measures dimension, and then expand MeasuresLevel. The list of measures appears.

The value expression box allows the user enter values that determine the values of the calculated member. To add a function in the Value expression box, the user has to click in the box, select a function from the functions box, double click the function or click Insert button to insert the function. The function syntax is then displayed in the Value expression box. The arguments and the delimiters can then be replaced with appropriate values.

The user can also type the value expression directly into the box. This method is useful when users want to add functions from libraries other than the native library of Microsoft SQL Server 2000 Analysis Services.

The user can validate the expression entered in the Value expression box by clicking on the Check button.

The Data pane of the Calculated member builder displays the cube’s dimensions, measures, and existing calculated members. The user can select an object from this pane and click insert button or drag the object to the Value expression box. This will add objects to the pane.