I had a problem today whilst trying to calculate RSS (square root of the sum of the squares) in an OLAP cube. I'm the first to admit that I'm no MDX expert (Multi-Dimensional Expressions - the language for querying OLAP cubes). Creating the sum of the squares was okay, but getting the square root was more difficult than expected.
MDX is the de facto standard language for querying cubes, analogous to SQL's ubiquity for querying "two-dimensional" relational data. It emerged from Microsoft's OLD DB for OLAP and was first supported by SAS with the V8 version of OLAP Server. MDX provides powerful functions for handling multi-dimensional collections of values. SAS's support for MDX means that you can connect a wide range of OLAP clients to a SAS OLAP server. OLAP clients that support MDX (and can hence be used with SAS OLAP server) include Excel, Cognos, ProClarity and Business Objects.
RSS is a measure of accuracy (estimated uncertainty) and is calculated by taking the square root of the total sum of each of the observed values squared. More often it is the residual values after comparing a model with observed values that are squared, summed and then rooted. Either way, the underlying calculations consist of a) the sum of the squares of the individual values, and then b) the square root thereof.
Summing-up values is the kind of thing that SAS OLAP server eats for breakfast, and MDX (whilst not directly supporting a square root function) provides an exponentiation operator (^, caret), sometimes also implemented as a function (power). So, I was surprised when I used ^0.5 to request the calculation of a square root and I got an error message.
Investigation showed that SAS V9.1.3 SP4 did not originally provide a "power" operator or function (see the SAS 9.1 OLAP Server MDX Guide). As far as I can see, it still doesn't, but problem note 17440 describes how hot fix E9BA09 introduced a square root function (SQRT).
Having scanned the V9.2 MDX documentation quickly this evening, I see no reference to caret (^), power, or exponentiation, nor sqrt (problem note 17440 says it was fixed in V9.2 TS1M0).
It seems most strange to me that SAS should continue to deviate from a full implementation of MDX. Whilst MDX is not a formally ratified standard, it *is* a stable, de facto standard. It is disappointing to see gaps in SAS's support of MDX. I tried to find some information on further gaps in the SAS implementation of MDX, but none seems to be available.
What's your mileage with SAS MDX? Have you found any further gaps or differences? How do you do exponentiation?
