Steve Novoselac's Blog

I manage some servers that have many cubes. OK, a lot of cubes (60+ on one). I needed some way to output a report of last processed time, last schema update, etc. Now, there are about 5 different ways to do this (one being the SSAS Stored Procedure Project), but this is what I came up with. I wrote a 100 line C# app to take a server name, loop through the SSAS DB’s, cubes, measures, partitions, and dimensions and log info about them.

Here is a c# code snippet of a function that just outputs to the console, the app I have actually logs the info to a SQL Server database and then I can write reports off that.

     private static void LogSSASInfo(string serverName)
        {
            var server = new Server();
            server.Connect(serverName);

            foreach (Database database in server.Databases)
            {
                Console.WriteLine(database.Name + " " + database.LastUpdate + " " + database.EstimatedSize / 1024 + " " + database.CreatedTimestamp);

                foreach (Cube cube in database.Cubes)
                {
                    Console.WriteLine("     Cube: " + cube.Name + " " + cube.LastProcessed + " " + cube.LastSchemaUpdate);

                    foreach (MeasureGroup measureGroup in cube.MeasureGroups)
                    {
                        Console.WriteLine("         Measure Group: " + measureGroup.Name + " " + measureGroup.LastProcessed);

                        foreach (Partition partition in measureGroup.Partitions)
                        {
                            Console.WriteLine("             Partition: " + partition.Name + " " + partition.LastProcessed);
                        }
                    }
                }

                foreach (Dimension dimension in database.Dimensions)
                {
                    Console.WriteLine(" Dimension: " + dimension.Name + " " + dimension.LastProcessed);
                }

                Console.WriteLine("");
                Console.WriteLine("------------------------------------------------");
                Console.WriteLine("");
            }

            server.Disconnect();
        }

As you can see, it isn’t the most elegant code in the world, but it works. In order to get this to work in your project, you need to reference the Microsoft.AnalysisServices assembly.

Use your imagination, you could make an app wrap that function above and log info for all the SSAS instances on your network. There have been a few times already in the last year where I have found some cube or measure group not updating correctly and a report like the one I can get now will help dealing with that challenge.