Report Portal

About me

I work as SQL Server DBA / Developer and BI Consultant in Toronto, Canada.Canada Flag More...
Vidas Matelis picture

Search

blank

SSAS 2008 RC0 – New function SYSTEMRESTRICTSCHEMA for restricted schema rowsets – DMVs

June 6th, 2008 by Vidas Matelis

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.

Posted in SSAS, SSAS 2008 - Katmai | Comments Off on SSAS 2008 RC0 – New function SYSTEMRESTRICTSCHEMA for restricted schema rowsets – DMVs

Comments are closed.