SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

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
1	5	16	-1		FALSE	Currency
1	5	16	-1		TRUE	Currency
1	5	16	-1		TRUE	Currency
1	5	16	-1		TRUE	Currency
1	5	16	-1		TRUE	Currency
1	5	16	-1		TRUE	Currency
1	5	16	-1		TRUE	Currency
2	3	10	-1		FALSE	#,#
127	12	65535	-1	[Measures].[Unit Price] / [Measures].[Transaction Count]	TRUE	Currency
127	12	65535	-1	[Measures].[Sales Amount] / [Measures].[Order Count]	TRUE	Currency
127	12	65535	-1	[Measures].[Sales Amount] - [Measures].[Total Product Cost]	TRUE	Currency
127	12	65535	-1	( [Measures].[Sales Amount] - [Measures].[Total Product Cost] ) / [Measures].[Sales Amount]	TRUE	Percent
127	12	65535	-1	IIf ( IsEmpty ( [Measures].[Sales Amount] ), Null, ( [Measures].[Amount], [Account].[Accounts].[Account Level 03].&[58] ) / [Measures].[Sales Amount] )	TRUE	Percent
127	12	65535	-1	[Measures].[Sales Amount] / ( Root( [Product] ), [Measures].[Sales Amount] )	TRUE	Percent
127	12	65535	-1	Case When [Product].[Product Model Categories].CurrentMember.Level.Ordinal = 0 Then 1 Else [Measures].[Sales Amount] / ( [Product].[Product Model Categories].CurrentMember.Parent, [Measures].[Sales Amount] ) End	TRUE	Percent
1	3	10	-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_NAME	Aggregate Function	DATA _ TYPE _NAME	COLUMN _SIZE	NUMERIC _PRECISION
Order Quantity	Sum	LONG	4	10
Transaction Count	Count	LONG	4	10
Unit Price	Sum	DOUBLE	8	16
Extended Amount	Sum	DOUBLE	8	16
Standard Product Cost	Sum	DOUBLE	8	16
Total Product Cost	Sum	DOUBLE	8	16
Sales Amount	Sum	DOUBLE	8	16
Tax Amount	Sum	DOUBLE	8	16
Freight Cost	Sum	DOUBLE	8	16
Average Unit Price	Calculated measure	VARIANT	16	65535
Average Sales Amount	Calculated measure	VARIANT	16	65535
Gross Profit	Calculated measure	VARIANT	16	65535
Gross Profit Margin	Calculated measure	VARIANT	16	65535
Expense to Revenue Ratio	Calculated measure	VARIANT	16	65535
Ratio to All Products	Calculated measure	VARIANT	16	65535
Ratio to Parent Product	Calculated measure	VARIANT	16	65535

Done.