Get size of every partition of a SSAS cube

Today, I had to check the sizes of every partitions of an OLAP cube, to ensure size of partitions is homogeneous. I did not find how to do that using a simple xmla query.So I decided to write a small console application in C# (VS2008) to perform it. The quickest way is to use the Management Object facility provided by Microsoft: it is a API which allows to connect to the online cube and browse its structure, read data, alter cube structure, launch process... anything you want !

Create project and reference SSAS DLL

First of all, create a project in Visual Studio (console application for instance).
Then, you have to add a reference in the project to the SSAS management object dll. Could find why Microsoft did it this way, but this dll has a strange component name. Where you expect something like Microsoft.AnalysisServices.xxx, you have to find Analysis Management Objects. So be careful when you link this assembly in Visual Studio.

Link the Analysis Services Management Object assembly to your project

Remember I am a bit lazy ? Yes, I cannot bear to write a long namespace several times... No problem we will use an alias.

using AS = Microsoft.AnalysisServices;

Browse the cube and get partitions size

Now, declare a small structure which will contains the information we need about a partition.

public struct PartitionInfo
{
    public string CubeName;
    public string MeasureGroup;
    public string PartitionName;
    public long EstimatedSize;
}

Here comes the interesting point. We will write the method which lists all the partitions and their size for a given SSAS database. The parameters are a ConnectionString (which does not need Initial Catalog, since it is next parameter), and the name of database you want to connect to.
The method performs the following actions:

1. Connect to AS server using the command line provided as first parameter (line 4 and 6)
2. Open the database which matches the name provided as second parameter (line 7)
3. Browse every cube available in the database (line 8)
4. Browse every measure group available in the current cube (line 10)
5. Browse every partition available in the current measure group (line 12)
6. For each of these partitions, save its definition (name, parent measure group, parent cube) and its size (lines 14 to 19). Even though property name is EstimatedSize, in my experience its value was always quite correct.
7. Return the list of partitions we found to the caller (line 24)

public static IList<partitioninfo> GetListOfPartitions(string ConnectionString, string DatabaseName)
{
    List<partitioninfo> LPI = new List<partitioninfo>();
    using (AS.Server Server = new AS.Server())
    {
        Server.Connect(ConnectionString);
        AS.Database Database = Server.Databases.FindByName(DatabaseName);
        foreach (AS.Cube Cube in Database.Cubes)
        {
            foreach (AS.MeasureGroup MG in Cube.MeasureGroups)
            {
                foreach (AS.Partition P in MG.Partitions)
                {
                    PartitionInfo PI = new PartitionInfo();
                    PI.CubeName = Cube.Name;
                    PI.MeasureGroup = MG.Name;
                    PI.PartitionName = P.Name;
                    PI.EstimatedSize = P.EstimatedSize;
                    LPI.Add(PI);
                }
            }
        }
    }
    return LPI;
}

Pay special attention if you are debugging this code with JIT debugger, I noticed it does not work fine : the Database object does not support to be watched by debugger, this could lead your SSAS objects to be unusable during process execution. For instance I could not find any cube in my database in debug mode, but it works fine in normal execution mode.

Save your data to CSV file

To finish the job, it may be useful to save the loaded data into a CSV file. In my case. I have to check that all partitions have homogeneous sizes, and regroup some small partitions together, so it is of great help to load the results in Excel !

public static void SaveToCsv(IList<partitioninfo> LPI, string Filename)
{
    using (System.IO.StreamWriter SW = new System.IO.StreamWriter(Filename))
    {
        SW.WriteLine("CubeName,MeasureGroup,PartitionName,EstimatedSizeInBytes");
        foreach (PartitionInfo PI in LPI)
        {
            string Row = string.Format("{0},{1},{2},{3}", PI.CubeName, PI.MeasureGroup, PI.PartitionName, PI.EstimatedSize);
            SW.WriteLine(Row);
        }
    }
}