 |
|
| MDX Queries and Syntax |
SAS Functions
When building MDX expressions, you can use all SAS functions, functions included in other SAS products, and user-written functions created with the SAS/TOOLKIT. Here is an MDX query that uses a SAS function called MDY:
WITH MEMBER measures.mdy AS 'SAS!mdy(2,9,2003)' SELECT {cars.members} ON 0 FROM mddbcars WHERE (measures.mdy)
Note: If you use a SAS function that has the same name as an MDX function
(for example: SUM()), you must prefix it with SAS!
. ![[cautionend]](../../../../common/62850/HTML/default/images/cautend.gif)
| SetToList MDX Function |
For SAS functions that take variable-length parameter lists, the SetToList MDX function can be useful as it enables you to convert MDX sets into variable-length parameter lists. See the SetToList function description for further information.
| Function Arguments and Return Types |
Currently only floating-point (double) arguments, character string arguments, and return values are supported. There is no limit to the number of arguments. The promotion of arguments from MDX types to SAS data types is automatically performed when there is a difference between the two types.
| Numeric Precision |
To store numbers of large magnitude and to perform computations that require many digits of precision to the right of the decimal point, the SAS OLAP Server stores all numeric values as floating-point representation. Floating-point representation is an implementation of scientific notation, in which numbers are represented as numbers between 0 and 1 times a power of 10.
In most situations, the way the SAS OLAP Server stores numeric values does not affect you as a user. However, floating-point representation can account for anomalies that you might notice in MDX numeric expressions. This section identifies the types of problems that can occur and how you can anticipate and avoid them.
Magnitude versus Precision
Floating-point representation allows for numbers of very large magnitude (such as 230) and high degrees of precision (many digits to the right of the decimal place). However, operating systems differ on how much precision and how much magnitude to allow.
Whether magnitude or precision is more important depends on the characteristics of your data. For example, if you are working with engineering data, very large numbers might be needed and magnitude will probably be more important. However, if you are working with financial data where every digit is important, but the number of digits is not great, then precision is more important. Most often, applications that are created with the SAS OLAP Server need a moderate amount of both magnitude and precision, which is handled well by floating-point representation.
Computational Considerations of Fractions
Regardless of how much precision is available, there is still the problem that some numbers cannot be represented exactly. For example, the fraction 1/3 cannot be rendered exactly in floating-point representation. Likewise, .1 cannot be rendered exactly in a base 2 or base 16 representation, so it also cannot be accurately rendered in floating-point representation. This lack of precision is aggravated by arithmetic operations. Consider the following example:
((10 * .1) = 1)
This expression might not always return TRUE due to differences in numeric precision. However, the following expression uses the ROUND function to compensate for numeric precision and therefore will always return TRUE:
(round((10 * .1), .001) = 1)
Usually, if you are doing comparisons with fractional values, it is good practice to use the ROUND function.
Using the TRUNC Function
The TRUNC function truncates a number to a requested length and then expands the number back to full precision. The truncation and subsequent expansion duplicate the effect of storing numbers in less than full precision. So in the following example, the first expression would return FALSE and the second would return TRUE:
((1/3) = .333)
(TRUNC((1/3), 3) = .333)
When you compare the result of a numeric expression to be equal to a specific value, such as 0, it is important that you use the TRUNC and ROUND functions to ensure that the comparison evaluates as intended.
| Differences with Microsoft Analysis Services 2000 |
Microsoft Analysis Services 2000 (AS2K) labels external functions as user-defined functions (UDFs). Because AS2K runs only on Windows, it supports calling COM libraries (usually written in Visual Basic). Because MDX evaluation can occur on either the client or the server, Microsoft provides a means to install and use libraries on either location (due to a dual-mode OLE DB for OLAP provider, MSOLAP).
If you use a client-side function, then all the execution is on the client. The SAS OLAP Server is a thin-client system that is designed for high volume and scalability, with all evaluation done on the server. Therefore, external function libraries such as SAS functions can only be installed on the server. Additionally, with the proper license, you can run a server on your own computer and install any libraries that you need.
| SAS MDX Reserved Keywords |
A reserved keyword should not be used to reference a dimension, hierarchy, level, or member name unless the reference is enclosed in square brackets [ ]. Otherwise, the keyword might be interpreted incorrectly.
Note: The SAS OLAP Server currently
does not support the use of square brackets in cube, dimension, hierarchy,
level, or member names or captions.
|
|
Copyright © 2009 by SAS Institute Inc., Cary, NC, USA. All rights reserved.