{"id":67,"date":"2008-06-06T22:42:45","date_gmt":"2008-06-07T03:42:45","guid":{"rendered":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/?p=67"},"modified":"2008-06-12T22:01:41","modified_gmt":"2008-06-13T03:01:41","slug":"ssas-2008-rc0-new-function-systemrestrictschema-for-restricted-schema-rowsets-dmvs","status":"publish","type":"post","link":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/67_ssas-2008-rc0-new-function-systemrestrictschema-for-restricted-schema-rowsets-dmvs","title":{"rendered":"SSAS 2008 RC0 – New function SYSTEMRESTRICTSCHEMA for restricted schema rowsets – DMVs"},"content":{"rendered":"

In my previous post Katmai Analysis Services 2008 November CTP5 – tests on metadata rowsets<\/a>\u00a0I 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\u00a0query:<\/p>\n

SELECT * FROM $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT<\/p><\/blockquote>\n

You would get this error message:\u00a0<\/p>\n

The ‘DATABASE_NAME’ restriction is required but is missing from the request.<\/p><\/blockquote>\n

<\/p>\n

In the SQL Server 2008 RC0 when you execute the same query, you will get this error message:<\/p>\n

Errors from the SQL query module: The ‘DATABASE_NAME’ restriction is required but is missing from the request.\u00a0 Consider using SYSTEMRISTRICTSCHEMA to provide restrictions.<\/p><\/blockquote>\n

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

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:<\/p>\n

Query for $SYSTEM.DISCOVER_DIMENSION_STAT schema rowset:<\/p>\n

SELECT *
\n\u00a0 FROM SYSTEMRESTRICTSCHEMA (
\n\u00a0 $SYSTEM.DISCOVER_DIMENSION_STAT
\n, DIMENSION_NAME = ‘Date’
\n, DATABASE_NAME=’Adventure Works DW’
\n)<\/p><\/blockquote>\n

Result will be:<\/p>\n

\u00a0<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
DATABASE_ NAME<\/td>\nDIMENSION_ NAME<\/td>\nATTRIBUTE_ NAME<\/td>\nATTRIBUTE_ COUNT<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nDate<\/td>\nDate<\/td>\n1159<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nDate<\/td>\nCalendar Quarter<\/td>\n14<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nDate<\/td>\nFiscal Quarter<\/td>\n14<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nDate<\/td>\nCalendar Semester<\/td>\n8<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nDate<\/td>\nFiscal Semester<\/td>\n8<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nDate<\/td>\nDay of Week<\/td>\n8<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nDate<\/td>\nDay Name<\/td>\n8<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nDate<\/td>\nDay of Month<\/td>\n32<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nDate<\/td>\nDay of Year<\/td>\n366<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nDate<\/td>\nWeek of Year<\/td>\n54<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nDate<\/td>\nMonth Name<\/td>\n39<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nDate<\/td>\nCalendar Year<\/td>\n5<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nDate<\/td>\nFiscal Semester of Year<\/td>\n3<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nDate<\/td>\nCalendar Semester of Year<\/td>\n3<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nDate<\/td>\nFiscal Quarter of Year<\/td>\n5<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nDate<\/td>\nCalendar Quarter of Year<\/td>\n5<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nDate<\/td>\nMonth of Year<\/td>\n13<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u00a0<\/p><\/blockquote>\n

Query for $SYSTEM.DISCOVER_INSTANCES schema rowset:<\/p>\n

\u00a0SELECT *
\n\u00a0 FROM SYSTEMRESTRICTSCHEMA(
\n\u00a0 $SYSTEM.DISCOVER_INSTANCES
\n, INSTANCE_NAME=’MSSQLSERVER’
\n)<\/p><\/blockquote>\n

Result will be:<\/p>\n

Empty result set<\/p><\/blockquote>\n

Query for $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT schema rowset:<\/p>\n

SELECT * FROM SYSTEMRESTRICTSCHEMA(
\n\u00a0 $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT
\n, CUBE_NAME = ‘Adventure Works’
\n, DATABASE_NAME = ‘Adventure Works DW’
\n, MEASURE_GROUP_NAME = ‘Sales Orders’
\n, PARTITION_NAME=’Total_Orders_2004′
\n)<\/p><\/blockquote>\n

Result will be:<\/p>\n

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
DATABASE_ NAME<\/td>\nCUBE_ NAME<\/td>\nMEASURE_ GROUP_ NAME<\/td>\nPARTITION_ NAME<\/td>\nDIMENSION_ NAME<\/td>\nATTRIBUTE_ NAME<\/td>\nATTRIBUTE_ INDEXED<\/td>\nATTRIBUTE_ COUNT_MIN<\/td>\nATTRIBUTE_ COUNT_ MAX<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nAdventure Works<\/td>\nSales Orders<\/td>\nTotal_Orders_2004<\/td>\nSales Summary Order Details<\/td>\n(All)<\/td>\nFALSE<\/td>\n1<\/td>\n1<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nAdventure Works<\/td>\nSales Orders<\/td>\nTotal_Orders_2004<\/td>\nSales Summary Order Details<\/td>\nSales Order Number<\/td>\nFALSE<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nAdventure Works<\/td>\nSales Orders<\/td>\nTotal_Orders_2004<\/td>\nSales Summary Order Details<\/td>\nSales Order<\/td>\nFALSE<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nAdventure Works<\/td>\nSales Orders<\/td>\nTotal_Orders_2004<\/td>\nSales Summary Order Details<\/td>\nSales Order Line<\/td>\nFALSE<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nAdventure Works<\/td>\nSales Orders<\/td>\nTotal_Orders_2004<\/td>\nSales Summary Order Details<\/td>\nCarrier Tracking Number<\/td>\nFALSE<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nAdventure Works<\/td>\nSales Orders<\/td>\nTotal_Orders_2004<\/td>\nSales Summary Order Details<\/td>\nCustomer PO Number<\/td>\nFALSE<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nAdventure Works<\/td>\nSales Orders<\/td>\nTotal_Orders_2004<\/td>\nDate<\/td>\n(All)<\/td>\nFALSE<\/td>\n1<\/td>\n1<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nAdventure Works<\/td>\nSales Orders<\/td>\nTotal_Orders_2004<\/td>\nDate<\/td>\nFiscal Year<\/td>\nTRUE<\/td>\n4<\/td>\n5<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nAdventure Works<\/td>\nSales Orders<\/td>\nTotal_Orders_2004<\/td>\nDate<\/td>\nFiscal Semester<\/td>\nTRUE<\/td>\n5<\/td>\n7<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nAdventure Works<\/td>\nSales Orders<\/td>\nTotal_Orders_2004<\/td>\nDate<\/td>\nFiscal Quarter<\/td>\nTRUE<\/td>\n12<\/td>\n14<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nAdventure Works<\/td>\nSales Orders<\/td>\nTotal_Orders_2004<\/td>\nDate<\/td>\nMonth Name<\/td>\nTRUE<\/td>\n32<\/td>\n38<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nAdventure Works<\/td>\nSales Orders<\/td>\nTotal_Orders_2004<\/td>\nDate<\/td>\nDate<\/td>\nTRUE<\/td>\n916<\/td>\n1128<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nAdventure Works<\/td>\nSales Orders<\/td>\nTotal_Orders_2004<\/td>\nDate<\/td>\nCalendar Year<\/td>\nTRUE<\/td>\n5<\/td>\n5<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/blockquote>\n

Query for $SYSTEM.DISCOVER_PARTITION_STAT schema rowset:<\/p>\n

SELECT *
\n\u00a0 FROM SYSTEMRESTRICTSCHEMA(
\n\u00a0 $SYSTEM.DISCOVER_PARTITION_STAT
\n, CUBE_NAME=’Adventure Works’
\n, DATABASE_NAME = ‘Adventure Works DW’
\n, MEASURE_GROUP_NAME = ‘Sales Orders’
\n, PARTITION_NAME=’Total_Orders_2004′
\n)<\/p><\/blockquote>\n

Result will be:<\/p>\n

\n\n\n\n\n
DATABASE_ NAME<\/td>\nCUBE_ NAME<\/td>\nMEASURE_ GROUP_ NAME<\/td>\nPARTITION_ NAME<\/td>\nAGGREGATION_ NAME<\/td>\nAGGREGATION_ SIZE<\/td>\n<\/tr>\n
Adventure Works DW<\/td>\nAdventure Works<\/td>\nSales Orders<\/td>\nTotal_Orders_2004<\/td>\n\u00a0<\/td>\n45548<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/blockquote>\n

Query for $SYSTEM.DISCOVER_PERFORMANCE_COUNTERS schema rowset:<\/p>\n

SELECT *
\n\u00a0 FROM SYSTEMRESTRICTSCHEMA(
\n\u00a0 $SYSTEM.DISCOVER_PERFORMANCE_COUNTERS
\n, PERF_COUNTER_NAME = ‘\\MSAS 2008:Cache\\Current KB’
\n)<\/p><\/blockquote>\n

Result will be:<\/p>\n

\n\n\n\n\n
PERF_COUNTER_NAME<\/td>\nPERF_COUNTER_VALUE<\/td>\n<\/tr>\n
\\MSAS 2008:Cache\\Current KB<\/td>\n3606<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/blockquote>\n

Special thanks to Edward Melomed to clarifying syntax of this new function.<\/p>\n","protected":false},"excerpt":{"rendered":"

In my previous post Katmai Analysis Services 2008 November CTP5 – tests on metadata rowsets\u00a0I 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\u00a0query: SELECT * FROM $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT You would get this error message:\u00a0 The ‘DATABASE_NAME’ restriction is required […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4,6],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/67"}],"collection":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/comments?post=67"}],"version-history":[{"count":0,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/67\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/media?parent=67"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/categories?post=67"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/tags?post=67"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}