Microsoft OLAP by Mosha Pasumansky

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
                
                    

                        posted on Thursday, March 31, 2005 6:12 PM
						by
						mosha