Q: How can I get a list of measures in one measure group from SSAS DMVs?
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.