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.

logo_xquery_xpath

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
Advertisements

About fdtki

Sr. BI Developer | An accomplished, quality-driven IT professional with over 16 years of experience in design, development and implementation of business requirements as a Microsoft SQL Server 6.5-2014 | Tabular/DAX | SSAS/MDX | Certified Tableau designer
This entry was posted in programming, Uncategorized and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s