Vertipaq Analyzer inside Power BI Desktop DAX Query View

Vertipaq Analyzer inside Power BI Desktop DAX Query View

By in Power BI

The majority of you are likely aware that Power BI utilizes a Vertipaq engine for data and query processing within the platform. To understand and improve the performance of the semantic model, one must delve into the Vertipaq data. Thanks to SQL BI who has introduced the Vertipaq Analyzer, designed to interpret the VertiPaq storage structures of a data model for both Power BI and Analysis Services Tabular.

DAX Studio provides a valuable section for analyzing Vertipaq metrics. It’s particularly beneficial for professional developers aiming to comprehend the model thoroughly and optimize it for enhanced performance.

This article explains how you can use “DAX Query View” in Power BI Desktop to have the Vertipaq analyzer data of your model. Refer Microsoft docs for more about DAX Query View

The secret ingredient for Vertipaq Analyzer information is INFO functions. The INFO functions are nothing but the Dynamic Management Views or DMV for accessing the metadata of the Tabular models. Check the list of functions from Microsoft document.

The below INFO functions are added as part of Power BI Desktop March 2024 update. If you want to use the below functions, then please make sure you have the latest version of Power BI Desktop.

Below are the list INFO functions used,

  • INFO.STORAGETABLES()
  • INFO.STORAGETABLECOLUMNS()
  • INFO.STORAGETABLECOLUMNSEGMENTS()
  • INFO.RELATIONSHIPS()
  • INFO.TABLES()
  • INFO.COLUMNS()

DAX Studio Vertipaq Analyzer has following sections -Tables, Columns, Relationships, Partitions and summary.

I have prepared DAX scripts for all the section except partitions. As we will have only one default partition on the local model.

How to use it?

Option 1: Copy and paste the script.

It is very easy to use it. I have pasted the code in 4 different blocks below. You can copy and paste it in your DAX query view tab of your Power BI Desktop file and run it.

Tables

// Author - Hariharan Rajendran
// Name - VertiPaq Analyser Metrics - Table View in DAX Query View
// Version - 1.0
// Contact - https://www.linkedin.com/in/imhariharanr/
DEFINE
	// Get the table and the cardinality
	VAR _TableCardinality = GROUPBY(
		FILTER(
			INFO.STORAGETABLES(),
			RIGHT(
				LEFT(
					[TABLE_ID],
					2
				),
				1
			) <> "$"
		),
		[DIMENSION_NAME],
		"Cardinality",
		SUMX(
			CURRENTGROUP(),
			[ROWS_COUNT]
		),
		"RI Violations",
		SUMX(
			CURRENTGROUP(),
			[RIVIOLATION_COUNT]
		)
	)
	VAR _DataSize = GROUPBY(
		FILTER(
			INFO.STORAGETABLECOLUMNSEGMENTS(),
			RIGHT(
				LEFT(
					[TABLE_ID],
					2
				),
				1
			) <> "$"
		),
		[DIMENSION_NAME],
		"DataSize",
		SUMX(
			CURRENTGROUP(),
			[USED_SIZE]
		)
	)
	VAR _HierSize = GROUPBY(
		FILTER(
			INFO.STORAGETABLECOLUMNSEGMENTS(),
			LEFT(
				[TABLE_ID],
				2
			) = "H$" && [SEGMENT_NUMBER] = 0
		),
		[DIMENSION_NAME],
		"HierSize",
		SUMX(
			CURRENTGROUP(),
			[USED_SIZE]
		)
	)
	/*
		Added by Ricardo Rincón https://www.linkedin.com/in/nexus150/
		_UserHierSize  = to obtain the size of the user hierarchies
	*/
	VAR _UserHierSize = GROUPBY(
		FILTER(
			INFO.STORAGETABLECOLUMNSEGMENTS(),
			LEFT(
				[TABLE_ID],
				2
			) = "U$" && [SEGMENT_NUMBER] = 0
		),
		[DIMENSION_NAME],
		"UserHierSize",
		SUMX(
			CURRENTGROUP(),
			[USED_SIZE]
		)
	)
	/*
		Added by Ricardo Rincón https://www.linkedin.com/in/nexus150/
		_UserRelSize = to obtain the size of the relationships
	*/
	VAR _RelSize = GROUPBY(
		FILTER(
			INFO.STORAGETABLECOLUMNSEGMENTS(),
			LEFT(
				[TABLE_ID],
				2
			) = "R$" && [SEGMENT_NUMBER] = 0
		),
		[DIMENSION_NAME],
		"RelSize",
		SUMX(
			CURRENTGROUP(),
			[USED_SIZE]
		)
	)
	VAR _DicSize = GROUPBY(
		FILTER(
			INFO.STORAGETABLECOLUMNS(),
			[COLUMN_TYPE] = "BASIC_DATA"
		),
		[DIMENSION_NAME],
		"DicSize",
		SUMX(
			CURRENTGROUP(),
			[DICTIONARY_SIZE]
		)
	)
	VAR _Table = SELECTCOLUMNS(
		NATURALINNERJOIN(
			ADDCOLUMNS(
				NATURALLEFTOUTERJOIN(
					NATURALLEFTOUTERJOIN(
						NATURALINNERJOIN(
							NATURALINNERJOIN(
								_DataSize,
								_HierSize
							),
							_DicSize
						),
						_RelSize
					),
					_UserHierSize
				),
				"Total Size", [DataSize] + [DicSize] + [HierSize] + [RelSize] + [UserHierSize]
			),
			_TableCardinality
		),
		"Table Name",
		[DIMENSION_NAME],
		"Cardinality",
		[Cardinality],
		"Total Size",
		[Total Size],
		"Data",
		[DataSize],
		"Dictionary",
		[DicSize],
		"Hier Size",
		[HierSize],
		"User Hier Size",
		[UserHierSize],
		"Rel Size",
		[RelSize],
		"RI Violations",
		[RI Violations]
	)

EVALUATE
	ADDCOLUMNS(
		_Table,
		"DB%", FORMAT(
			DIVIDE(
				[Total Size],
				SUMX(
					_Table,
					[Total Size]
				)
			),
			"0.00%"
		)
	)
	ORDER BY [Total Size] DESC

Columns

// Author - Hariharan Rajendran
// Name - VertiPaq Analyser Metrics - Column View in DAX Query View
// Version - 1.0
// Contact - https://www.linkedin.com/in/imhariharanr/
DEFINE
	VAR _Column = SELECTCOLUMNS(
		FILTER(
			INFO.STORAGETABLECOLUMNS(),
			[COLUMN_TYPE] = "BASIC_DATA"
		),
		"Table Name",
		[DIMENSION_NAME],
		"Column Name",
		[ATTRIBUTE_NAME],
		"Data Type",
		SWITCH(
			[DATATYPE],
			"DBTYPE_I8", "Int64",
			"DBTYPE_WSTR", "String",
			"DBTYPE_CY", "Decimal",
			"DBTYPE_BOOL", "Boolean",
			"DBTYPE_DATE", "DateTime",
			"DBTYPE_R8", "Double",
			[DATATYPE]
		),
		"Dictionary Size",
		[DICTIONARY_SIZE],
		"Column Encoding",
		SWITCH(
			[COLUMN_ENCODING],
			1, "HASH",
			2, "VALUE"
		)
	)
	VAR _DataSize = SELECTCOLUMNS(
		FILTER(
			INFO.STORAGETABLECOLUMNSEGMENTS(),
			RIGHT(
				LEFT(
					[TABLE_ID],
					2
				),
				1
			// Exclude the columns
			) <> "$"
		),
		"Table Name",
		[DIMENSION_NAME],
		"Data Size",
		[USED_SIZE],
		"Column Name",
		SUBSTITUTE(
			LEFT(
				[COLUMN_ID],
				SEARCH(
					" (",
					[COLUMN_ID],
					1,
					LEN([COLUMN_ID]) - 1
				) - 1
			),
			"RowNumber ",
			"RowNumber-"
		)
	)
	VAR _HierSize = SELECTCOLUMNS(
		GROUPBY(
			FILTER(
				INFO.STORAGETABLECOLUMNSEGMENTS(),
				LEFT(
					[TABLE_ID],
					2
				) = "H$" && [SEGMENT_NUMBER] = 0
			),
			[DIMENSION_NAME],
			[TABLE_ID],
			"HierSize",
			SUMX(
				CURRENTGROUP(),
				[USED_SIZE]
			)
		),
		"Table Name",
		[DIMENSION_NAME],
		"HierSize",
		[HierSize],
		"Column Name",
		VAR _1 = FIND(
			"$",
			[TABLE_ID],
			1
		)
		VAR _2 = FIND(
			"$",
			[TABLE_ID],
			_1 + 1
		)
		VAR _len = LEN([TABLE_ID])
		VAR _stage1 = MID(
			[TABLE_ID],
			_2 + 1,
			_len - _2 + 1
		)
		VAR _final = LEFT(
			_stage1,
			SEARCH(
				" (",
				_stage1,
				1,
				LEN(_stage1) - 1
			) - 1
		)
		RETURN
			_Final
	)
	VAR _TableCardinality = SELECTCOLUMNS(
		GROUPBY(
			FILTER(
				INFO.STORAGETABLES(),
				RIGHT(
					LEFT(
						[TABLE_ID],
						2
					),
					1
				// Exclude the columns
				) <> "$"
			),
			[DIMENSION_NAME],
			"Cardinality",
			SUMX(
				CURRENTGROUP(),
				[ROWS_COUNT]
			)
		),
		"Table Name",
		[DIMENSION_NAME],
		"Rows",
		[Cardinality]
	)
	VAR _ColumnCardinality = SELECTCOLUMNS(
		FILTER(
			INFO.STORAGETABLES(),
			LEFT(
				[TABLE_ID],
				2
			) = "H$"
		),
		"Table Name",
		[DIMENSION_NAME],
		"Column Name",
		VAR _1 = FIND(
			"$",
			[TABLE_ID],
			1
		)
		VAR _2 = FIND(
			"$",
			[TABLE_ID],
			_1 + 1
		)
		VAR _len = LEN([TABLE_ID])
		VAR _stage1 = MID(
			[TABLE_ID],
			_2 + 1,
			_len - _2 + 1
		)
		VAR _final = LEFT(
			_stage1,
			SEARCH(
				" (",
				_stage1,
				1,
				LEN(_stage1) - 1
			) - 1
		)
		RETURN
			_Final,
		"Column Cardinality",
		[ROWS_COUNT] - 3
	)
	VAR _Final = ADDCOLUMNS(
		ADDCOLUMNS(
			NATURALLEFTOUTERJOIN(
				NATURALLEFTOUTERJOIN(
					NATURALLEFTOUTERJOIN(
						NATURALLEFTOUTERJOIN(
							_Column,
							_DataSize
						),
						_HierSize
					),
					_TableCardinality
				),
				_ColumnCardinality
			),
			"New Data Size", IF(
				ISBLANK([Data Size]) && CONTAINSSTRING(
					[Column Name],
					"RowNumber"
				),
				[Dictionary Size],
				[Data Size]
			)
		),
		"Col Size", [New Data Size] + [HierSize] + [Dictionary Size]
	)

EVALUATE
	SELECTCOLUMNS(
		_Final,
		"Table Name",
		[Table Name],
		"Column Name",
		[Column Name],
		"Rows",
		[Rows],
		"Cardinality",
		[Column Cardinality] + 0,
		"Col Size",
		[Col Size],
		"Data",
		[New Data Size],
		"Dictionary",
		[Dictionary Size],
		"Hier Size",
		[HierSize] + 0,
		"Encoding",
		[Column Encoding],
		"Data Type",
		[Data Type],
		"% DB",
		DIVIDE(
			[Col Size],
			SUMX(
				_Final,
				[Col Size]
			)
		) * 100
	)
	ORDER BY [Col Size] DESC

Relationships

// Author - Hariharan Rajendran
// Name - VertiPaq Analyser Metrics - Relationship View in DAX Query View
// Version - 1.0
// Contact - https://www.linkedin.com/in/imhariharanr/
DEFINE
	VAR _RelationshipSize = SELECTCOLUMNS(
		FILTER(
			INFO.STORAGETABLECOLUMNSEGMENTS(),
			LEFT(
				[TABLE_ID],
				2
			) = "R$"
		),
		"Table Name",
		[DIMENSION_NAME],
		"Used Size",
		[USED_SIZE],
		"Relationship ID",
		VAR _1 = FIND(
			"(",
			[TABLE_ID],
			1
		)
		VAR _2 = FIND(
			"(",
			[TABLE_ID],
			_1 + 1
		)
		RETURN
			SUBSTITUTE(
				MID(
					[TABLE_ID],
					_2 + 1,
					_2
				),
				")",
				""
			)
	)
	VAR _FromColumnCardinality = SELECTCOLUMNS(
		FILTER(
			INFO.STORAGETABLES(),
			LEFT(
				[TABLE_ID],
				2
			) = "H$"
		),
		"From Column ID",
		VAR _1 = FIND(
			"(",
			[TABLE_ID],
			1
		)
		VAR _2 = FIND(
			"(",
			[TABLE_ID],
			_1 + 1
		)
		RETURN
			SUBSTITUTE(
				MID(
					[TABLE_ID],
					_2 + 1,
					_2
				),
				")",
				""
			),
		"From Column Cardinality",
		[ROWS_COUNT] - 3
	)
	VAR _ToColumnCardinality = SELECTCOLUMNS(
		FILTER(
			INFO.STORAGETABLES(),
			LEFT(
				[TABLE_ID],
				2
			) = "H$"
		),
		"To Column ID",
		VAR _1 = FIND(
			"(",
			[TABLE_ID],
			1
		)
		VAR _2 = FIND(
			"(",
			[TABLE_ID],
			_1 + 1
		)
		RETURN
			SUBSTITUTE(
				MID(
					[TABLE_ID],
					_2 + 1,
					_2
				),
				")",
				""
			),
		"To Column Cardinality",
		[ROWS_COUNT] - 3
	)
	VAR _Relationship = SELECTCOLUMNS(
		INFO.RELATIONSHIPS(),
		"Relationship ID",
		FORMAT(
			[ID],
			"###"
		),
		"From Table ID",
		[FromTableID],
		"From Column ID",
		FORMAT(
			[FromColumnID],
			"####"
		),
		"To Table ID",
		[ToTableID],
		"To Column ID",
		FORMAT(
			[ToColumnID],
			"###"
		),
		"Cross Filter Behavior From",
		SWITCH(
			[CrossFilteringBehavior],
			1, "One",
			2, "Both",
			"Something else"
		),
		"From Cardinality",
		SWITCH(
			[FromCardinality],
			1, "One",
			2, "Many",
			"Something else"
		),
		"Cross Filter Behavior (To)",
		SWITCH(
			[CrossFilteringBehavior],
			1, "One",
			2, "Both",
			"Something else"
		),
		"To Cardinality",
		SWITCH(
			[ToCardinality],
			1, "One",
			2, "Many",
			"Something else"
		)
	)
	VAR _FromTable = SELECTCOLUMNS(
		INFO.TABLES(),
		"From Table ID",
		[ID],
		"From TableName",
		[Name]
	)
	VAR _ToTable = SELECTCOLUMNS(
		INFO.TABLES(),
		"To Table ID",
		[ID],
		"To TableName",
		[Name]
	)
	VAR _FromCol = SELECTCOLUMNS(
		INFO.COLUMNS(),
		"From Column ID",
		FORMAT(
			[ID],
			"####"
		),
		"From ColumnName",
		IF(
			ISBLANK([ExplicitName]),
			[InferredName],
			[ExplicitName]
		)
	)
	VAR _ToCol = SELECTCOLUMNS(
		INFO.COLUMNS(),
		"To Column ID",
		FORMAT(
			[ID],
			"####"
		),
		"To ColumnName",
		IF(
			ISBLANK([ExplicitName]),
			[InferredName],
			[ExplicitName]
		)
	)

EVALUATE
	SELECTCOLUMNS(
		NATURALLEFTOUTERJOIN(
			NATURALLEFTOUTERJOIN(
				NATURALLEFTOUTERJOIN(
					NATURALINNERJOIN(
						NATURALLEFTOUTERJOIN(
							_RelationshipSize,
							NATURALLEFTOUTERJOIN(
								NATURALLEFTOUTERJOIN(
									_Relationship,
									_FromColumnCardinality
								),
								_ToColumnCardinality
							)
						),
						_FromTable
					),
					_ToTable
				),
				_FromCol
			),
			_ToCol
		),
		"From Table Name",
		[From TableName],
		"From Column Name",
		[From ColumnName],
		"To Table Name",
		[To TableName],
		"To Column Name",
		[To ColumnName],
		"From",
		[From Cardinality],
		"To",
		[To Cardinality],
		"Cross Filter Behaviour (From]",
		[Cross Filter Behavior From],
		"Size",
		[Used Size],
		"Max from Cardinality",
		[From Column Cardinality],
		"Max To Cardinality",
		[To Column Cardinality]
	)
	ORDER BY [From Table Name], [From Column Name]

Summary

// Author - Hariharan Rajendran
// Name - VertiPaq Analyser Metrics - Summary View in DAX Query View
// Version - 1.0
// Contact - https://www.linkedin.com/in/imhariharanr/
DEFINE
	// Get the table and the cardinality
	VAR _TableCardinality = GROUPBY(
		FILTER(
			INFO.STORAGETABLES(),
			RIGHT(
				LEFT(
					[TABLE_ID],
					2
				),
				1
			// Exclude the columns
			) <> "$"
		),
		[DIMENSION_NAME],
		"Cardinality",
		SUMX(
			CURRENTGROUP(),
			[ROWS_COUNT]
		),
		"RIViolocation",
		SUMX(
			CURRENTGROUP(),
			[RIVIOLATION_COUNT]
		)
	)
	// Get the data size for each table
	VAR _DataSize = GROUPBY(
		FILTER(
			INFO.STORAGETABLECOLUMNSEGMENTS(),
			RIGHT(
				LEFT(
					[TABLE_ID],
					2
				),
				1
			// Exclude the columns
			) <> "$"
		),
		[DIMENSION_NAME],
		"DataSize",
		SUMX(
			CURRENTGROUP(),
			[USED_SIZE]
		)
	)
	// Get the hierarchy size of each table
	VAR _HierSize = GROUPBY(
		FILTER(
			INFO.STORAGETABLECOLUMNSEGMENTS(),
			LEFT(
				[TABLE_ID],
				2
			) = "H$" && [SEGMENT_NUMBER] = 0
		),
		[DIMENSION_NAME],
		"HierSize",
		SUMX(
			CURRENTGROUP(),
			[USED_SIZE]
		)
	)
	/*
		Added by Ricardo Rincón https://www.linkedin.com/in/nexus150/
		_UserHierSize  = to obtain the size of the user hierarchies
	*/
	VAR _UserHierSize = GROUPBY(
		FILTER(
			INFO.STORAGETABLECOLUMNSEGMENTS(),
			LEFT(
				[TABLE_ID],
				2
			) = "U$" && [SEGMENT_NUMBER] = 0
		),
		[DIMENSION_NAME],
		"UserHierSize",
		SUMX(
			CURRENTGROUP(),
			[USED_SIZE]
		)
	)
	/*
		Added by Ricardo Rincón https://www.linkedin.com/in/nexus150/
		_UserRelSize = to obtain the size of the relationships
	*/
	VAR _RelSize = GROUPBY(
		FILTER(
			INFO.STORAGETABLECOLUMNSEGMENTS(),
			LEFT(
				[TABLE_ID],
				2
			) = "R$" && [SEGMENT_NUMBER] = 0
		),
		[DIMENSION_NAME],
		"RelSize",
		SUMX(
			CURRENTGROUP(),
			[USED_SIZE]
		)
	)
	// Get dictionary size of each table
	VAR _DicSize = GROUPBY(
		FILTER(
			INFO.STORAGETABLECOLUMNS(),
			[COLUMN_TYPE] = "BASIC_DATA"
		),
		[DIMENSION_NAME],
		"DicSize",
		SUMX(
			CURRENTGROUP(),
			[DICTIONARY_SIZE]
		)
	)
	VAR _Table = SELECTCOLUMNS(
		NATURALINNERJOIN(
			ADDCOLUMNS(
				NATURALLEFTOUTERJOIN(
					NATURALLEFTOUTERJOIN(
						NATURALINNERJOIN(
							NATURALINNERJOIN(
								_DataSize,
								_HierSize
							),
							_DicSize
						),
						_RelSize
					),
					_UserHierSize
				),
				"Total Size", [DataSize] + [DicSize] + [HierSize] + [RelSize] + [UserHierSize]
			),
			_TableCardinality
		),
		"Table Name",
		[DIMENSION_NAME],
		"Cardinality",
		[Cardinality],
		"Total Size",
		[Total Size],
		"Data",
		[DataSize],
		"Dictionary",
		[DicSize],
		"Hier Size",
		[HierSize],
		"RIViolation",
		[RIViolocation]
	)

EVALUATE
	SUMMARIZE(
		_Table,
		"Size in MB",
		DIVIDE(
			DIVIDE(
				SUMX(
					_Table,
					[Total Size]
				),
				1024
			),
			1024
		)
	)

Option 2: Download the files and add it in PBIP folder

I have placed the below .dax files in my Github repo. You can download and add it in your PBIP folder.

Considerations

[Updated] This version has most of the information and I am planning to enhance it more to include all other columns. Let me know your feedback.

Thanks to Ricardo Rincón and James Bartlett for supporting to fix and format the queries.

To get the latest scripts, use GitHub Link – Power-BI/Vertipaq Analyzer in DAX Query View at master · rhariharaneee/Power-BI (github.com)

Post Views: 341
Hariharan

My name is Hariharan Rajendran. I am a Microsoft MVP and I have more than 14 years of experience in Data and BI technologies. This site help me to share some of my BI experience and Information about the Microsoft products, how to access the tools, how to improve your productivity with using the Microsoft tools. I am also running a Youtube Channel. If you are interested, then check at Hari’s BI - https://www.youtube.com/@HariBI/videos

Leave a Reply