TSSASM

UPDATED WIKI: Home

Ltubia — Sat, 31 May 2008 18:02:35 GMT

Transact Sql Server Analysis Services Metadata

Using Transact SQL for browsing and managing Analysis Services 2005 Metadata

Index:

Introduction
Binary Installation
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:

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

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.

CREATED ISSUE: Resultset of function growth with every call

kjh24 — Tue, 06 May 2008 13:55:12 GMT

In my environment the resultset of SELECT * FROM SSASPartitions('Server','Database') growth every time.
The function never forgets the last result set and adds every new resultset at the end.
W2003 Ent Eng., 64-Bit

UPDATED WIKI: Home

Ltubia — Mon, 10 Mar 2008 17:36:13 GMT

Transact Sql Server Analysis Services Metadata

Using Transact SQL for browsing and managing Analysis Services Metadata

Index:

Introduction
Binary Installation
Deploying from Visual Studio

1. Introduction

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

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

UPDATED RELEASE: TSSASM v0.1 beta (mar 10, 2008)

Mon, 10 Mar 2008 17:35:21 GMT

This first release is intended to be proof of concept and it has only one SSAS 2005 metadata retrieving function: SSASPartitions.
See Home tab to get detailed steps for installation and using.
Other SSAS 2005 metadata object will be able to be browsed in future releases.

RELEASED: TSSASM v0.1 beta (Mar 10, 2008)

Mon, 10 Mar 2008 17:35:21 GMT

UPDATED WIKI: Home

Ltubia — Mon, 10 Mar 2008 17:19:30 GMT

Transact Sql Server Analysis Services Metadata

Using Transact SQL for browsing and managing Analysis Services Metadata

Index:

Introduction
Binary Installation
Deploying from Visual Studio

1. Introduction

Managing SSAS 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:

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

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

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

UPDATED WIKI: Home

Ltubia — Mon, 10 Mar 2008 17:17:09 GMT

Transact Sql Server Analysis Services Metadata

Using Transact SQL for browsing and managing Analysis Services Metadata

Index:

Introduction
Binary Installation
Deploying from Visual Studio

1. Introduction

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

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

UPDATED WIKI: Home

Ltubia — Mon, 10 Mar 2008 17:16:13 GMT

Transact Sql Server Analysis Services Metadata

Using Transact SQL for browsing and managing Analysis Services Metadata

Index

Introduction
Binary Installation
Deploying from Visual Studio

1. Introduction

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

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 the project 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.

UPDATED WIKI: Home

Ltubia — Mon, 10 Mar 2008 17:15:17 GMT

Transact Sql Server Analysis Services Metadata

Using Transact SQL for browsing and managing Analysis Services Metadata

Index

Introduction
Binary Installation
Deploying from Visual Studio

Introduction

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

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.

Deploying from Visual Studio

UPDATED WIKI: Home

Ltubia — Mon, 10 Mar 2008 17:13:07 GMT

Transact Sql Server Analysis Services Metadata

Using Transact SQL for browsing and managing Analysis Services Metadata

Introduction

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

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.

Deploying from Visual Studio

If you've just downloaded source code and you edited project you can deploy the project 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.

***

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.

UPDATED RELEASE: TSSASM v0.1 beta (mar 10, 2008)

Mon, 10 Mar 2008 17:03:43 GMT

This first release is intended to be proof of concept and it has only one SSAS metadata retrieving function: SSASPartitions.
See Home tab to get detailed steps for installation and using.
Other SSAS metadata object will be able to be browsed in future releases.

UPDATED RELEASE: TSSASM v0.1 beta (mar 10, 2008)

Mon, 10 Mar 2008 16:57:30 GMT

This first release has only one TSSASM function: SSASPartitions.
See Home tab to get detailed steps for installation and using.
Other SSAS metadata object will be able to be browsed in future releases.

UPDATED WIKI: Home

Ltubia — Mon, 10 Mar 2008 16:54:31 GMT

Transact Sql Server Analysis Services Metadata

Using Transact SQL for browsing and managing Analysis Services Metadata

Introduction

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

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.

***

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.

UPDATED WIKI: Home

Ltubia — Sat, 08 Mar 2008 23:54:38 GMT

Transact Sql Server Analysis Services Metadata

Using Transact SQL for browsing and managing Analysis Services Metadata

Introduction

Managing SSAS objects when facing scenarios with lots 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 thsi 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
Component has limited functionality as only browse partition objects (SSASPartitions function). Aditional functions will be added in future releases

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

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

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 so as to allow CLR can reference it
Must be registered as unsafe because it has method not supported in 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.

***

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.

UPDATED WIKI: Home

Ltubia — Sat, 08 Mar 2008 23:51:35 GMT

Transact Sql Server Analysis Services Metadata

Transact SQL for browsing and managing Analysis Services Metadata

***
Managing SSAS objects when facing scenarios with lots 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 thsi 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
Component has limited functionality as only browse partition objects (SSASPartitions function). Aditional functions will be added in future releases

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

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

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

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

UPDATED WIKI: Home

Ltubia — Sat, 08 Mar 2008 23:48:35 GMT

Heading 1

Transact Sql Server Analysis Services Metadata
Transact SQL for browsing and managing Analysis Services Metadata

***
Managing SSAS objects when facing scenarios with lots 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 thsi 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
Component has limited functionality as only browse partition objects (SSASPartitions function). Aditional functions will be added in future releases

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

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

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

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

UPDATED WIKI: Home

Ltubia — Sat, 08 Mar 2008 23:44:46 GMT

Heading 1 Transact Sql Server Analysis Services Metadata ! Heading 1

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

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

UPDATED WIKI: Home

Ltubia — Sat, 08 Mar 2008 23:43:25 GMT

Heading 1Transact Sql Server Analysis Services Metadata

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

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

UPDATED WIKI: Home

Ltubia — Sat, 08 Mar 2008 23:37:42 GMT

Transact Sql Server Analysis Services Metadata
Transact SQL for browsing and managing Analysis Services Metadata

***
Managing SSAS objects when facing scenarios with lots 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 thsi 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
Component has limited functionality as only browse partition objects (SSASPartitions function). Aditional functions will be added in future releases

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

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

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

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

UPDATED WIKI: Home

Ltubia — Sat, 08 Mar 2008 23:30:47 GMT

Transact Sql Server Analysis Services Metadata
Transact SQL for browsing and managing Analysis Services Metadata

***
Managing SSAS objects when facing scenarios with lots 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 processing large and complex 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 I propose to use also SQL Server in the opposite way: as the ideal tool to browse all data about SSAS metadata objects.
Using T-SQL as the query language can help us answer, for example, the some 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 functionality.
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
Component has limited functionality as only browse partition objects (SSASPartitions function). Aditional functions will be added in future releases

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

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

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 so as to allow CLR can reference it
Must be registered as unsafe because it has method not supported in 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