You'll have to pull the data into Analysis Services to use the MDX function. It can't be used directly against data in SQL Server.

Have you taken a look at Celko's SQL For Smarties? It might have a sample of a query performing a Correlation operation.

-Mike