Q: How can I get a list of measures in one measure group from SSAS DMVs?
User Rating: / 3
PoorBest 
Written by Vidas Matelis   
Wednesday, 21 January 2009 03:25

Q: How can I get a list of measures in one measure group from SSAS DMVs?

A: In SSAS 2008 there is a DMV '$SYSTEM.MDSCHEMA_MEASURES' that contain a list of measures and basic information about each measure. Here is the sample query:

SELECT [MEASURE_NAME],[MEASURE_AGGREGATOR],[DATA_TYPE]
, [NUMERIC_PRECISION],[NUMERIC_SCALE],[EXPRESSION]
 , [MEASURE_IS_VISIBLE],[DEFAULT_FORMAT_STRING]
FROM $SYSTEM.MDSCHEMA_MEASURES
 WHERE CUBE_NAME = 'Adventure Works'
   AND MEASUREGROUP_NAME = 'Sales Summary'
ORDER BY [MEASUREGROUP_NAME]

And here are results of this query:

MEAS URE _AGGRE  GATOR

DATA _TYPE

NUME RIC _PRECI SION

NUM ERIC _SCALE

EXPRESSION

MEASURE _IS _VISIBLE

DEFAULT _FORMAT _STRING

1516-1 FALSECurrency
1516-1 TRUECurrency
1516-1 TRUECurrency
1516-1 TRUECurrency
1516-1 TRUECurrency
1516-1 TRUECurrency
1516-1 TRUECurrency
2310-1 FALSE#,#
1271265535-1[Measures].[Unit Price] / [Measures].[Transaction Count]TRUECurrency
1271265535-1[Measures].[Sales Amount] / [Measures].[Order Count]TRUECurrency
1271265535-1[Measures].[Sales Amount] - [Measures].[Total Product Cost]TRUECurrency
1271265535-1( [Measures].[Sales Amount] - [Measures].[Total Product Cost] ) / [Measures].[Sales Amount]TRUEPercent
1271265535-1IIf ( IsEmpty ( [Measures].[Sales Amount] ), Null, ( [Measures].[Amount], [Account].[Accounts].[Account Level 03].&[58] ) / [Measures].[Sales Amount] )TRUEPercent
1271265535-1[Measures].[Sales Amount] / ( Root( [Product] ), [Measures].[Sales Amount] )TRUEPercent
1271265535-1Case When [Product].[Product Model Categories].CurrentMember.Level.Ordinal = 0 Then 1 Else [Measures].[Sales Amount] / ( [Product].[Product Model Categories].CurrentMember.Parent, [Measures].[Sales Amount] ) EndTRUEPercent
1310-1 TRUE#,#

Pleae note that field [EXPRESSION] contains formula for calculated measures.

If you would like to get textual representation of DATA_TYPE field, you will need to execute same query from the SQL Server and join result to the $SYSTEM.DBSCHEMA_PROVIDER_TYPE DMV. Here is sample query:

SELECT M.[MEASURE_NAME]
 , CASE M.[MEASURE_AGGREGATOR]
  WHEN 1 THEN 'Sum'
  WHEN 2 THEN 'Count'
  WHEN 3 THEN 'Min'
  WHEN 4 THEN 'Max'
  WHEN 8 THEN 'Distinct Count'
  WHEN 9 THEN 'None'
  WHEN 10 THEN 'AverageOfChildren'
  WHEN 11 THEN 'FirstChild'
  WHEN 12 THEN 'LastChild'
  WHEN 13 THEN 'FirstNonEmpty'
  WHEN 14 THEN 'LastNonEmpty'
  WHEN 15 THEN 'ByAccount'
  WHEN 127 THEN 'Calculated measure'
  ELSE 'N/A'
   END AS AggregateFunction
 , D.[TYPE_NAME] AS DATA_TYPE_NAME
 , D.[COLUMN_SIZE]
 , M.[NUMERIC_PRECISION]
-- , M.[NUMERIC_SCALE]
-- , M.[EXPRESSION]
-- , M.[MEASURE_IS_VISIBLE]
-- , M.[DEFAULT_FORMAT_STRING]
 FROM OPENQUERY(SSAS2008Test,
  '
SELECT *
FROM $SYSTEM.MDSCHEMA_MEASURES
 WHERE CUBE_NAME = ''Adventure Works''
   AND MEASUREGROUP_NAME = ''Sales Summary''
   '
   ) M
   JOIN OPENQUERY(SSAS2008Test,
  '
SELECT DATA_TYPE, TYPE_NAME, COLUMN_SIZE, IS_FIXEDLENGTH
 FROM $SYSTEM.DBSCHEMA_PROVIDER_TYPES -- Data types here
') D ON D.DATA_TYPE = M.DATA_TYPE

Result of this query will be:

MEASURE_NAMEAggregate FunctionDATA _ TYPE _NAMECOLUMN _SIZENUMERIC _PRECISION
Order QuantitySumLONG410
Transaction CountCountLONG410
Unit PriceSumDOUBLE816
Extended AmountSumDOUBLE816
Standard Product CostSumDOUBLE816
Total Product CostSumDOUBLE816
Sales AmountSumDOUBLE816
Tax AmountSumDOUBLE816
Freight CostSumDOUBLE816
Average Unit PriceCalculated measureVARIANT1665535
Average Sales AmountCalculated measureVARIANT1665535
Gross ProfitCalculated measureVARIANT1665535
Gross Profit MarginCalculated measureVARIANT1665535
Expense to Revenue RatioCalculated measureVARIANT1665535
Ratio to All ProductsCalculated measureVARIANT1665535
Ratio to Parent ProductCalculated measureVARIANT1665535

Done.
  

 

 

Tags: dmv, faq
 

Pyramid Analytics