NOTE: Spicing-Up Your Enterprise Guide With Custom Tasks - Checking Cardinality

Last week I wrote about a couple of neat custom tasks from Metacoda that would allow you to search metadata from within Enterprise Guide (EG) and the Add-In for Microsoft Office (AMO). Custom tasks are a great way to augment the functionality you get in EG and AMO.

I recently saw Chris Hemedinger write in The SAS Dummy blog about a custom task to check cardinality that he'd written. If you're into hierarchies, dimensions or forms of analytics, cardinality is doubtless a frequently used part of your vocabulary. In essence, cardinality of a variable specifies the number of distinct values of that variable.

Back in May, Chris published some good advice about installing custom tasks, taking into account changes made in EG 5.1 and later. Worth a read.

If you're new to the idea of custom tasks, the SAS Talks archive contains a one hour Introduction to Custom Tasks video from earlier this year.

If you would like to broaden your knowledge of custom tasks and start to write some for yourself, I highly recommend Chris's book Custom Tasks for SAS Enterprise Guide using Microsoft .NET. Examples from the book (with source) are available too.

All-in-all, if you haven't already given attention to custom tasks, you've no longer got any excuse to ignore them!

NOTE: Clean Your Cubes

It's not spring-time, but it's still worth giving a thought to the cleanliness of your environment, for the benefit of reducing complexity and of reducing space usage. Angela Hall posted a great article about Cleaner OLAP Cube Physical Folder Structures earlier this month on SAS's Real BI for Real Users blog.

In her article, Angela gave a "below the water line" view of OLAP cubes and how they are maintained and stored. Her tips for cube rationalisation will reduce disk space usage and improve performance. Well worth a look.

NOTE: Roots and Powers in SAS MDX

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?

NOTE: OLAP Security and the SAS-BI Blog

Whilst advising a client on the infamous "The cube has too few dimensions" OLAP error message, I was minded to re-research what information was available. I'm glad I did because I not only found the (expected) SAS usage note 14626 I also found a hit on the SAS-BI blog run by Angela Hall. This blog is a real gem and I was glad to be reminded of it.

The "too few dimensions" message is one of those mis-leading messages that can have you scratching your head for a long time. You can read the details in the two sources of information that I mentioned, but I'll summarise by saying that it's a security-related issue and not a problem with the cube - despite the implications of the message.

The SAS-BI hit provides a link to one of Angela's earlier postings that gives valuable advice on the many ways to refresh a cube. Deleting and rebuilding your cubes every evening isn't always the best approach!

The SAS-BI blog has been running since August 2005 (the very first post was talking about OLAP) and has to-date accumulated 134 posts. It represents an excellent collection of SAS Business Intelligence hints, tips and experience. Many of the posts relate to V9.2, so it's contemporary too. A visit to sas-bi.blogspot.com is highly recommended. For news of the latest blog updates (and Angela's progress with her 30-day challenge - #30dchallenge), follow SASBI on Twitter.