Get metadata from cubes with T-SQL

There is a great way to get metadata from your tabular cubes by using the dynamic management views. With simple T-SQL you can get lots of metadata out of your cubes. I use them to extract the tables/views that are being extracted from the database. Because it is T-SQL, you can easily store the information in your database for future use. All you need is a Linked server to the tabular cube, and you are ready to go.

The cube I am using for this example looks like:

Setting up the Linked server is easy. Below the example for this blog:

The code:

USE [master]
GO

DECLARE
	@ServerScript VARCHAR(50) = 'SSAS_Pokemon'


EXEC master.dbo.sp_addlinkedserver @server = @ServerScript, @srvproduct=N'', @provider=N'msolap', @datasrc=N'.', @catalog=N'Pokemon'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname= @ServerScript,@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

EXEC master.dbo.sp_serveroption @server= @ServerScript, @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server= @ServerScript, @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server= @ServerScript, @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server= @ServerScript, @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server= @ServerScript, @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server= @ServerScript, @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server= @ServerScript, @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server= @ServerScript, @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server= @ServerScript, @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server= @ServerScript, @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server= @ServerScript, @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server= @ServerScript, @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server= @ServerScript, @optname=N'remote proc transaction promotion', @optvalue=N'true'

When you have the linked server in place, you can start querying your cube. In this blog I have some simple examples lined up.

1. $SYSTEM.TMSCHEMA_PARTITIONS – Information about the partitions in the model/perspective…

SELECT * FROM OPENQUERY([SSAS_Pokemon], 'SELECT * FROM $SYSTEM.TMSCHEMA_PARTITIONS')

2. Extract from QueryText(Schema, Table/view)…

3. $SYSTEM.MDSCHEMA_MEASURES – Information about the measures…

SELECT * FROM OPENQUERY([SSAS_Pokemon], 'SELECT
						[CUBE_NAME] AS [CubeName]
						,[CATALOG_NAME] + ''_'' + [CUBE_NAME] + ''_'' + [MEASUREGROUP_NAME] AS [KeyColumn]
						,[MEASURE_CAPTION] AS [Measure]
						,[DESCRIPTION]
						,[EXPRESSION] AS [Definition]
						,[MEASURE_IS_VISIBLE] AS [Measure visible?]
						,[MEASURE_DISPLAY_FOLDER] AS [Display folder]
						,[DEFAULT_FORMAT_STRING] AS [Format]
					FROM $SYSTEM.MDSCHEMA_MEASURES
					WHERE 1 = 1'
			)

4. $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS – Information about the relations…

SELECT
	*
FROM (
	SELECT
		[Cube]
		,REPLACE(REPLACE(CAST(MeasureGroup AS VARCHAR(255)), '[', ''), ']', '') AS [MeasureGroup]
		,REPLACE(REPLACE(CAST(Dimension AS VARCHAR(255)), '[', ''), ']', '') AS [Dimension]
		,[MeasureGroupCardinality]
		,[DimensionCardinality]
	FROM OPENQUERY([SSAS_Pokemon], 'SELECT
					[CUBE_NAME] AS [Cube]
					,[CATALOG_NAME] + ''_'' + [CUBE_NAME] + ''_'' + [MEASUREGROUP_NAME] AS [MeasureGroup]
					,[CATALOG_NAME] + ''_'' + [CUBE_NAME] + ''_'' + [DIMENSION_UNIQUE_NAME] AS [Dimension]
					,[CATALOG_NAME] + ''_'' + [CUBE_NAME] + ''_'' + [MEASUREGROUP_CARDINALITY] AS [MeasureGroupCardinality]
					,[CATALOG_NAME] + ''_'' + [CUBE_NAME] + ''_'' + [DIMENSION_CARDINALITY] AS [DimensionCardinality]
					FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
					WHERE 1 = 1'
			)
	WHERE 1 = 1
	) BASE
WHERE 1 = 1
	AND MeasureGroup <> Dimension

That is it! There are a lot of DMV’s that you can use to get metadata from your cube(s). In this blog I have shown you some simple examples that you can use. If you have any questions/comments/remarks, please let me know by contacting me. Until next time!