Querying Tabular model XML/A with XPath

There can be various reasons to extract metadata from Tabular model, for example to compose documentation or logical model diagram. The next collections are useful from architectural point of view:

• list of dimensions,
• list of measure groups and measures,
• list of calculations with their formulas,
• list of hierarchies.

There are a lot of utilities allowing to browse XML-scripts and efficiently query them. However most likely Tabular developer has SQL Server Management Studio under hand and usage of XQuery looks convenient way to go.

Note: as a preliminary step it’s necessary to crop off the string “xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”” from XML/A, otherwise XQuery doesn’t run.

Placing original XML/A script into a variable:

DECLARE @xml xml =
'<Alter AllowCreate="true" ObjectExpansion="ExpandFull" >
  <Object>
    <DatabaseID>SemanticModel</DatabaseID>
  </Object>
  <ObjectDefinition>
    <Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
		...
		...
		...
    </Database>
  </ObjectDefinition>
</Alter>'

Extracting Measures:

DECLARE @remtxt as varchar(1000)='PowerPivot measures command (do not modify manually)'

SELECT	[MeasureGroupTranslation],
		tbl.value('Annotations[1]/Annotation[1]/Value[1]','varchar(100)') as [CalculationNameWithType],
		tbl.value('Annotations[1]/Annotation[1]/Value[1]','varchar(100)') as [CalculationName],
		REPLACE(REPLACE(REPLACE(REPLACE(tbl.value('Text[1]','varchar(1000)'), @remtxt, ''), '--',''),'CREATE MEASURE ',''), transshns.[MeasureGroupName], [MeasureGroupTranslation])  as [MDXScript]
FROM	@xml.nodes('/Alter/ObjectDefinition/Database/Cubes/Cube/MdxScripts/MdxScript/Commands/Command') as m(tbl)
JOIN	(SELECT
				tbl.value('Name[1]','varchar(100)') as [MeasureName],
				tbl.value('../../Name[1]','varchar(100)') as [MeasureGroupName],
				tbl.value('../../Translations[1]/Translation[1]/Caption[1]','varchar(100)') as [MeasureGroupTranslation]
		FROM	@xml.nodes('/Alter/ObjectDefinition/Database/Cubes/Cube/MeasureGroups/MeasureGroup/Measures/Measure') as m(tbl)
		WHERE	tbl.value('../../Translations[1]/Translation[1]/Caption[1]','varchar(100)') IN ('','','') -- here must be a list of measure groups (can be found while browsing the model)
		) transshns
ON		transshns.[MeasureGroupName] = tbl.value('Annotations[1]/Annotation[2]/Value[1]','varchar(100)')
WHERE	tbl.value('Annotations[1]/Annotation[2]/Value[1]','varchar(100)') IS NOT NULL
ORDER BY 1,2

Extracting Dimensions:

SELECT
		ISNULL(	tbl.value('Translations[1]/Translation[1]/Caption[1]','varchar(100)'),
				tbl.value('Name[1]','varchar(100)')) as [DimensionName],
		attr.value('AttributeID[1]','varchar(100)') as [AttributeName],
		exprns.[Expression]
FROM	@xml.nodes('/Alter/ObjectDefinition/Database/Cubes/Cube/Dimensions/Dimension') as m(tbl)
JOIN	@xml.nodes('/Alter/ObjectDefinition/Database/Cubes/Cube/Dimensions/Dimension/Attributes/Attribute') as n(attr)
ON		attr.value('../../Name[1]','varchar(100)') = tbl.value('Name[1]','varchar(100)')
LEFT JOIN
		(SELECT DISTINCT
			expr.value('../../CubeDimensionID[1]','varchar(100)') as [CubeDimensionID],
			expr.value('AttributeID[1]','varchar(100)') as [AttributeName],
			expr.value('KeyColumns[1]/KeyColumn[1]/Source[1]/Expression[1]','varchar(1000)') as [Expression]
		FROM @xml.nodes('/Alter/ObjectDefinition/Database/Cubes/Cube/MeasureGroups/MeasureGroup/Dimensions/Dimension/Attributes/Attribute') as c(expr)
		WHERE expr.value('KeyColumns[1]/KeyColumn[1]/Source[1]/Expression[1]','varchar(1000)') IS NOT NULL) exprns
ON		exprns.[CubeDimensionID] = tbl.value('DimensionID[1]','varchar(100)')
	AND	exprns.[AttributeName] = attr.value('AttributeID[1]','varchar(100)')
WHERE	tbl.value('Visible[1]','varchar(100)') IS NULL
	AND	attr.value('AttributeHierarchyVisible[1]','varchar(100)') IS NULL
ORDER BY 1,2

Extracting relationships between Measures and Dimensions:

SELECT
		--tbl.value('../../Name[1]','varchar(100)') as [MeasureGroupName],
		msrtransl.[MeasureGroupTranslation] as [MeasureGroupName],
		--tbl.value('CubeDimensionID[1]','varchar(1000)') as [DimensionID],
		dims.[DimensionName]
FROM	@xml.nodes('/Alter/ObjectDefinition/Database/Cubes/Cube/MeasureGroups/MeasureGroup/Dimensions/Dimension') as m(tbl)
JOIN	(
			SELECT	DISTINCT
					tbl.value('ID[1]','varchar(100)') as [DimensionID],
					ISNULL(	tbl.value('Translations[1]/Translation[1]/Caption[1]','varchar(100)'),
							tbl.value('Name[1]','varchar(100)')) as [DimensionName]
			FROM	@xml.nodes('/Alter/ObjectDefinition/Database/Cubes/Cube/Dimensions/Dimension') as m(tbl)
			JOIN	@xml.nodes('/Alter/ObjectDefinition/Database/Cubes/Cube/Dimensions/Dimension/Attributes/Attribute') as n(attr)
			ON		attr.value('../../Name[1]','varchar(100)') = tbl.value('Name[1]','varchar(100)')
			WHERE	tbl.value('Visible[1]','varchar(100)') IS NULL
				AND	attr.value('AttributeHierarchyVisible[1]','varchar(100)') IS NULL
		) dims
ON		dims.[DimensionID] = tbl.value('CubeDimensionID[1]','varchar(1000)')
JOIN	(
			SELECT	tbl.value('../../Name[1]','varchar(100)') as [MeasureGroupName],
					tbl.value('../../Translations[1]/Translation[1]/Caption[1]','varchar(100)') as [MeasureGroupTranslation]
			FROM	@xml.nodes('/Alter/ObjectDefinition/Database/Cubes/Cube/MeasureGroups/MeasureGroup/Measures/Measure') as m(tbl)
			WHERE	tbl.value('../../Translations[1]/Translation[1]/Caption[1]','varchar(100)') IN ('','','') -- list of measure groups (see above)
		) msrtransl
ON		msrtransl.[MeasureGroupName] = tbl.value('../../Name[1]','varchar(100)')
WHERE tbl.value('../../Name[1]','varchar(100)') IN ('','','')
ORDER BY 1