DMVs in Analysis Service -- Part 2 (MDSCHEMA)

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.



For other part of this article

DMVs in Analysis Service -- Part 1 (DBSCHEMA)


Information for cubes

SELECT * FROM $SYSTEM.MDSCHEMA_CUBES

SELECT * FROM $SYSTEM.MDSCHEMA_CUBES
WHERE CUBE_SOURCE = 1 -- 1 FOR CUBES
-- 2 FOR DIMENSIONS

We can get column detail from here
http://msdn.microsoft.com/en-us/library/ms126271.aspx


information for dimensions

SELECT * FROM
$SYSTEM.MDSCHEMA_DIMENSIONS

SELECT * FROM
SYSTEMRESTRICTSCHEMA
($SYSTEM.MDSCHEMA_DIMENSIONS ,
DIMENSION_VISIBILITY = '1' -- 1 for visible
-- 2 for invisible

)

SELECT * FROM
SYSTEMRESTRICTSCHEMA
($SYSTEM.MDSCHEMA_DIMENSIONS ,
CUBE_SOURCE= '2' -- 1 for cube
-- 2 for dimension
)



We can get column detail from here
http://msdn.microsoft.com/en-us/library/ms126180.aspx

Information on Hierarchy

SELECT * FROM $SYSTEM.MDSCHEMA_HIERARCHIES

SELECT * FROM $SYSTEM.MDSCHEMA_HIERARCHIES
WHERE [STRUCTURE] = '2'
-- 0 balanced
-- 1 ragged
-- 2 unbalanced
-- 3 network


SELECT * FROM
SYSTEMRESTRICTSCHEMA
($SYSTEM.MDSCHEMA_HIERARCHIES ,
CUBE_SOURCE= '2' -- 1 for cube
-- 2 for dimension
)


We can get column detail from here
http://msdn.microsoft.com/en-us/library/ms126062.aspx

Information on levels



SELECT * FROM $SYSTEM.MDSCHEMA_LEVELS
WHERE [LEVEL_NUMBER] = '1'

SELECT * FROM
SYSTEMRESTRICTSCHEMA
($SYSTEM.MDSCHEMA_LEVELS,
CUBE_NAME ='ADVENTURE WORKS' ,
CUBE_SOURCE = '1',
LEVEL_VISIBILITY= '1'
)


We can get column information from here
http://msdn.microsoft.com/en-us/library/ms126038.aspx