Transact Sql Server Analysis Services Metadata

Using Transact SQL for browsing and managing Analysis Services 2005 Metadata



Index:
  1. Introduction
  2. Binary Installation
  3. Deploying from Visual Studio

1. Introduction

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.


Limitations
This first release component has limited functionality as only browse partition objects (SSASPartitions function). Additional functions will be added in future releases


Usage sample 1: knowing all partitions in a certain SSAS Catalog:

Sample01.jpg

Usage sample 2: knowing all partitions of measure group "Internet Sales" that have "molap" storage:

Sample02.jpg

2. Binary Installation

CLR feature must be activated in the SQL Server 2005 instance. Follow these steps to make binary works:

1 Activate SQL Server CLR. Only a user with sysadmin server role can do it.

EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;


2 SQL Server instance must trust database and its content. Only a user with sysadmin server role can do it.

ALTER DATABASE <Your database> SET TRUSTWORTHY ON 


3 Analysis Services Management Object assembly must be registered firstly in SQL Server so as to allow CLR reference it
Must be registered as unsafe because it has methods not supported with SAFE and EXTERNAL_ACCESS permission types.

Use <Your database>
CREATE ASSEMBLY MicrosoftAnalysisServices
FROM '<Path>\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.AnalysisServices.dll'
WITH PERMISSION_SET = UNSAFE


4 Create a new assembly that references TSSASM dll:

CREATE ASSEMBLY TSSASM
FROM '<Path>\TSSASM.dll'
WITH PERMISSION_SET = UNSAFE


5 Create a table function that references the CLR function
Note: table definition cannot be changed, it must be exactly the same as it is hard coded inside dll
CREATE FUNCTION SSASPartitions(@server nvarchar(100),@catalog nvarchar(100))
RETURNS TABLE 
(cubename NVARCHAR(100),measuregroupname NVARCHAR(100), partitionid NVARCHAR(100), partitionname NVARCHAR(100),
createdtimestamp DATETIME,lastprocessed DATETIME,aggregationdesignid NVARCHAR(100),
aggregationdesignname NVARCHAR(100),aggregationdesignaggrscount INT,aggregationdesignestperfgain INT,slice NVARCHAR(100),
estimatedsize BIGINT,estimatedrows BIGINT,storagemode NVARCHAR(100),storagelocation NVARCHAR(100),processingmode NVARCHAR(100),
datasourcename NVARCHAR(100),datasourceconn NVARCHAR(255),datasourceviewname NVARCHAR(100),
remotedatasourcename NVARCHAR(100),remotedatasourceid NVARCHAR(100),description NVARCHAR(100),
errconfkeyerrorlogfile NVARCHAR(100),errconfkeyduplicate NVARCHAR(100),errconfkeyaction NVARCHAR(100),
errconfkeyerrorlimit BIGINT,errconfkeyerrorlimitaction NVARCHAR(100),errconfkeynotfound NVARCHAR(100),
errconfnullkeyconvtounknown NVARCHAR(100),errconfnullkeynotallowed NVARCHAR(100))
AS 
EXTERNAL NAME TSSASM.CLRFunctions.SSASPartitions


6 Try function. Hope this help you.

3. Deploying from Visual Studio

If you've just downloaded source code and you edited project you can deploy it directly from Visual Studio:
1 Follow steps 1, 2, and 3 from Binary Installation section above so as to set security configuration.
2 Open project properties and select the Database Tab.
3 Set the appropiate connection string where assembly will be deployed.
4 Build and deploy project.




***

This project is currently in setup mode and only available to project coordinators and developers. Once you have finished setting up your project you can publish it to make it available to all CodePlex visitors.

There are three requirements before you publish:

- Edit this page to provide information about your project
- Upload the initial source code for your project
- Add your project license

Additional information on starting a new project is available here: Project Startup Guide.

Last edited May 31, 2008 at 6:02 PM by Ltubia, version 20