THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

Random sampling of MDX set using stored procedures with server ADOMD.NET

Analysis Services 2005 comes with true server side stored procedures support. On top of that, Analysis Services 2005 also has server side ADOMD.NET object model. Server ADOMD.NET looks at first similar to client ADOMD.NET, but they serve very different purposes. Client ADOMD.NET is modeled after ADO.NET and ADOMD, and it allows sending MDX queries to the server and exposes nice object hierarchy over cube metadata. Server ADOMD.NET, on the other hand, is there to extend MDX object model itself. It operates in the same type space as MDX, for example itt has classes such as Set, Tuple, Member etc, which are very familiar to any MDX programmer. Today in the newsgroup I saw a question "How to implement random sampling from a set". This is exactly kind of task that server ADOMD.NET and stored procedures are designed for. In order to accomplish it, we will create assembly RandomSample, and implement function Sample(set, k) in it. This function has same signature as Head and Tail MDX functions, only instead of returning first or last k tuples from the set, it returns k random ones. The algorithm used here iterates over the set and probes each tuple with probability (k-j)/(n-i). I didn't prove that it produces unbiased uniform distribution, but it probably does. Anyway, the exact algorithm is not very important, what is more important is to demonstrate how to use server ADOMD.NET. After the code below is compiled into assembly, it needs to be registered in the server, and then the function Sample can be called from MDX, for example like this:
 
SELECT RandomSample.RandomSample.RandomSample.Sample(City.City.MEMBERS, 5) ON COLUMNS FROM Sales
 
The code for the stored procedure follows:
using Microsoft.AnalysisServices.AdomdServer;

namespace RandomSample
{
 public class RandomSample
 {
  public Set Sample(Set set, int k)
  {
   int n = set.Tuples.Count;
   if ( k >= n )
    return set;

   System.Random r = new System.Random();

   SetBuilder sb = new SetBuilder();
   int i = n;
   foreach ( Tuple t in set.Tuples )
   {
    int rnd = r.Next(i);
    if (rnd < k)
    {
     k--;
     sb.Add(t);
    }
    i--;
   }
   return sb.ToSet();
  }
 }
}

Published Thursday, March 31, 2005 6:12 PM by mosha
Filed under:
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement