Transact Sql Server Analysis Services Metadata (Free)
Created by Leandro Tubia. Using Transact SQL for browsing and managing Analysis Services 2005 Metadata.
Managing SSAS 2005 objects when facing scenarios with a large amount of cubes (let's say more than a dozen) and lots of partitions (let's say thousands) is not an easy job to deal with.
When trying to control partition states and cube consistency after large and complex processing batches became a costly job and not allways as exact as process quality control requirements need.
SSAS uses SQL Server relational tables as cube data source but this project proposes to use SQL Server in the opposite way as well: as the ideal tool to browse all data about SSAS metadata objects.
Using T-SQL as the query language can help us to fastly answer, for example, several issues about partition processing:
1) Are there partitions that have not been processed ?
2) are all partitions created after closing period ?
3) How many partitions has aggregation designed ?
4) Which aggregation design are asigned ?
5) List all partitions that has a certain slice.
6) Check if all partitions has the same data source.
7) Want to document all objects and its properties and want to get document updated when an object is changed.
We can query SSAS object using Transact-SQL query language through SQL Server 2005 CLR (Common Language Runtime) new feature.
The idea is create a .NET CLR assembly that access SSAS through AMO; then it will provide rows to a table value function (TVF) that can be queried using a T-Sql SELECT statement.