DMVs in Analysis Service -- Part 1 (DBSCHEMA)

As we all know that AS 2008 has come with many new features
I am using SQL Server since last 8 years and DMVs are great improvement in SQL Server 2005
Now exciting thing is that this DMVs are also in Analysis Service
Till now its not possible to view metadata in Analysis Service , but after 2008 its possible.
We will explore this DMVs in coming articles.



DBSCHEMA_CATALOGS

SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS

Here * in roles means that current user is a server or database administrator
Roles are shown as comma separated.

We can get column information from here

http://msdn.microsoft.com/en-us/library/ms126314.aspx



DBSCHEMA_TABLES

SELECT * FROM $SYSTEM.DBSCHEMA_TABLES

To find dimensions /measuregroups

SELECT * FROM $SYSTEM.DBSCHEMA_TABLES
WHERE TABLE_TYPE = 'TABLE' -- TABLE indicates its a dimension
-- SYSTEM TABLE indicates is a measure group

To get column information
http://msdn.microsoft.com/en-us/library/ms126299.aspx

DBSCHEMA_COLUMNS

SELECT * FROM $SYSTEM.DBSCHEMA_COLUMNS

SELECT * FROM $SYSTEM.DBSCHEMA_COLUMNS
WHERE COLUMN_OLAP_TYPE = 'MEASURE' -- MEASURE for measurgroup columns
-- ATTRIBUTE for dimension attributes


To get column information
http://msdn.microsoft.com/en-us/library/ms126208.aspx


DBSCHEMA_PROVIDER_TYPES

SELECT * FROM $SYSTEM.DBSCHEMA_PROVIDER_TYPES
Provides information for base data types

http://msdn.microsoft.com/en-us/library/ms126200.aspx