New MDX Functions

Analysis Services 2005 introduces several new MDX functions to facilitate data extraction from the UDM. The following table covers some of the functions and gives a brief description about their behavior for quick reference. Detailed information about these functions is provided in Appendix A. We recommend you learn more about the MDX functions used in MDX scripts with scenarios in MDX Solutions 2nd Edition by George Spofford et al. (Wiley, 2006).

MDX Function


MeasureGroupMeasures (<Measure Group Name>)

This function retrieves all the measures within a specific measure group. Because the UDM can contain several measure groups, this function helps by making the query more specific.

EXISTS (<set>,<filterset> EXISTS (<set>,<filterset>,<measuregroup name>

This function is used to determine if tuples in the set exist with the tuples in the filter set. If yes, those tuples are returned. When the measure group argument is used, the measure group is used to determine the EXISTS operation.

KPI Functions — KPICURRENTTIMEMEMBER ( String Expression ) KPIGOAL ( String Expression KPISTATUS ( String Expression KPITREND ( String Expression ) KPIVALUE ( String Expression ) KPIWEIGHT ( String Expression )

KPI functions help in retrieving the values for the KPI, which help in displaying the status of KPI through KPI graphic icons. Some of these functions return normalized values between -1 and 1. All the KPI functions except KPICurrentTimeMember take the KPI name as an argument and return values that can be matched with the corresponding graphic. These functions are helpful for client-side programming where you retrieve the value and show the corresponding icon that visually represents the status. Analysis Services internally treats these as just calculations. KPICurrentTimeMember retrieves the time member corresponding to the KPI, which can be different from the default member on the Time dimension.

UnOrder (<set>)

MDX usually returns an order set in the results. Often this ordering takes additional overhead on the server. The UnOrder function instructs Analysis Services to return results without ordering. This often helps in improving performance on large results sets, which are typically crossjoins of several hierarchies.

Error (<Error message>) Error

This helps in throwing user-defined errors in cases where certain operations are not possible.

Root (Dimension) Leaves (Dimension)

Root and Leaves functions are used in Scripts for appropriately positioning the coordinate on a dimension for allocations or assignments.

Professional SQL Server Analysis Services 2005 with MDX
Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)
ISBN: 0764579185
EAN: 2147483647
Year: 2004
Pages: 176 © 2008-2017.
If you may any questions please contact us: