Vidas Matelis Analysis Services Blog

In my previous post Katmai Analysis Services 2008 November CTP5 – tests on metadata rowsets I listed examples of new SSAS DMVs. But there were few DMVs that you could not query. For example if you would try to execute following query:

SELECT * FROM $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT

You would get this error message: 

The ‘DATABASE_NAME’ restriction is required but is missing from the request.

In the SQL Server 2008 RC0 when you execute the same query, you will get this error message:

Errors from the SQL query module: The ‘DATABASE_NAME’ restriction is required but is missing from the request.  Consider using SYSTEMRISTRICTSCHEMA to provide restrictions.

There is small spelling mistake (connect) in this message: SYSTEMRISTRICTSCHEMA should be SYSTEMRESTRICTSCHEMA. That is new function that can be used to query restricted schema rowsets.

Different schema rowsets require different restrictions and there also could be different restriction count for each rowset schema. Here are examples of how this new function can be used on different schema rowsets that contain restrictions:

Query for $SYSTEM.DISCOVER_DIMENSION_STAT schema rowset:

SELECT *
  FROM SYSTEMRESTRICTSCHEMA (
  $SYSTEM.DISCOVER_DIMENSION_STAT
, DIMENSION_NAME = ‘Date’
, DATABASE_NAME=’Adventure Works DW’
)

Result will be:

 

DATABASE_ NAME	DIMENSION_ NAME	ATTRIBUTE_ NAME	ATTRIBUTE_ COUNT
Adventure Works DW	Date	Date	1159
Adventure Works DW	Date	Calendar Quarter	14
Adventure Works DW	Date	Fiscal Quarter	14
Adventure Works DW	Date	Calendar Semester	8
Adventure Works DW	Date	Fiscal Semester	8
Adventure Works DW	Date	Day of Week	8
Adventure Works DW	Date	Day Name	8
Adventure Works DW	Date	Day of Month	32
Adventure Works DW	Date	Day of Year	366
Adventure Works DW	Date	Week of Year	54
Adventure Works DW	Date	Month Name	39
Adventure Works DW	Date	Calendar Year	5
Adventure Works DW	Date	Fiscal Semester of Year	3
Adventure Works DW	Date	Calendar Semester of Year	3
Adventure Works DW	Date	Fiscal Quarter of Year	5
Adventure Works DW	Date	Calendar Quarter of Year	5
Adventure Works DW	Date	Month of Year	13

 

Query for $SYSTEM.DISCOVER_INSTANCES schema rowset:

 SELECT *
  FROM SYSTEMRESTRICTSCHEMA(
  $SYSTEM.DISCOVER_INSTANCES
, INSTANCE_NAME=’MSSQLSERVER’
)

Result will be:

Empty result set

Query for $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT schema rowset:

SELECT * FROM SYSTEMRESTRICTSCHEMA(
  $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT
, CUBE_NAME = ‘Adventure Works’
, DATABASE_NAME = ‘Adventure Works DW’
, MEASURE_GROUP_NAME = ‘Sales Orders’
, PARTITION_NAME=’Total_Orders_2004′
)

Result will be:

DATABASE_ NAME	CUBE_ NAME	MEASURE_ GROUP_ NAME	PARTITION_ NAME	DIMENSION_ NAME	ATTRIBUTE_ NAME	ATTRIBUTE_ INDEXED	ATTRIBUTE_ COUNT_MIN	ATTRIBUTE_ COUNT_ MAX
Adventure Works DW	Adventure Works	Sales Orders	Total_Orders_2004	Sales Summary Order Details	(All)	FALSE	1	1
Adventure Works DW	Adventure Works	Sales Orders	Total_Orders_2004	Sales Summary Order Details	Sales Order Number	FALSE	0	0
Adventure Works DW	Adventure Works	Sales Orders	Total_Orders_2004	Sales Summary Order Details	Sales Order	FALSE	0	0
Adventure Works DW	Adventure Works	Sales Orders	Total_Orders_2004	Sales Summary Order Details	Sales Order Line	FALSE	0	0
Adventure Works DW	Adventure Works	Sales Orders	Total_Orders_2004	Sales Summary Order Details	Carrier Tracking Number	FALSE	0	0
Adventure Works DW	Adventure Works	Sales Orders	Total_Orders_2004	Sales Summary Order Details	Customer PO Number	FALSE	0	0
Adventure Works DW	Adventure Works	Sales Orders	Total_Orders_2004	Date	(All)	FALSE	1	1
Adventure Works DW	Adventure Works	Sales Orders	Total_Orders_2004	Date	Fiscal Year	TRUE	4	5
Adventure Works DW	Adventure Works	Sales Orders	Total_Orders_2004	Date	Fiscal Semester	TRUE	5	7
Adventure Works DW	Adventure Works	Sales Orders	Total_Orders_2004	Date	Fiscal Quarter	TRUE	12	14
Adventure Works DW	Adventure Works	Sales Orders	Total_Orders_2004	Date	Month Name	TRUE	32	38
Adventure Works DW	Adventure Works	Sales Orders	Total_Orders_2004	Date	Date	TRUE	916	1128
Adventure Works DW	Adventure Works	Sales Orders	Total_Orders_2004	Date	Calendar Year	TRUE	5	5

Query for $SYSTEM.DISCOVER_PARTITION_STAT schema rowset:

SELECT *
  FROM SYSTEMRESTRICTSCHEMA(
  $SYSTEM.DISCOVER_PARTITION_STAT
, CUBE_NAME=’Adventure Works’
, DATABASE_NAME = ‘Adventure Works DW’
, MEASURE_GROUP_NAME = ‘Sales Orders’
, PARTITION_NAME=’Total_Orders_2004′
)

Result will be:

DATABASE_ NAME	CUBE_ NAME	MEASURE_ GROUP_ NAME	PARTITION_ NAME	AGGREGATION_ NAME	AGGREGATION_ SIZE
Adventure Works DW	Adventure Works	Sales Orders	Total_Orders_2004		45548

Query for $SYSTEM.DISCOVER_PERFORMANCE_COUNTERS schema rowset:

SELECT *
  FROM SYSTEMRESTRICTSCHEMA(
  $SYSTEM.DISCOVER_PERFORMANCE_COUNTERS
, PERF_COUNTER_NAME = ‘\MSAS 2008:Cache\Current KB’
)

Result will be:

PERF_COUNTER_NAME	PERF_COUNTER_VALUE
\MSAS 2008:Cache\Current KB	3606

Special thanks to Edward Melomed to clarifying syntax of this new function.