Power BI Model Analyzer

Description

Need insight into your Power BI environment?  Need to know what is consuming your storage space in Power BI? Want to optimize your data models and your Power BI environment? The ability to analyze model, table, column sizing, column usage, and column dependencies is critical to an efficient, optimized Power BI environment.

This solution, Power BI Model Analyzer, will be updated periodically as new feature requests and other thoughts on what could be added to improve optimization practices. As well, if new DMV functionality is added. Make sure you’re running the latest version. The report will have a version displayed and notify you if there is a new version available.  

 

Demo

 

 

Ideaboard

Submit ideas for enhancements of the product: https://vizbp.com/powerdax-ideas/  

 

Download the solution

The solution can be downloaded from your account from vizbp.com after purchase.

1. On vizbp.com, access the Customer Dashboard.
2. On the sidebar menu, select Downloads.
3. You will see all of your purchases and the download links for each.

 

Install the solution

The solution can be downloaded from your account from vizbp.com.

1. For using with Power BI Desktop on your local machine, the custom connector (Power BI Model Analyzer.mez) has to be downloaded and Power BI Desktop configured to allow third-party connectors: https://docs.microsoft.com/en-us/power-bi/desktop-connector-extensibility

Be patient. It takes a few minutes to install and refresh metrics. If the solution shows blank metrics, press F5 to refresh your browser.

 

Monitor data models with the solution

Now that you’ve installed the solution, you can see metrics for the data models in your organization. The solution provides a detailed metrics Report.

 

Dashboard

You can create a customized dashboard that summarizes key metrics for data models for which you are an admin of. This can include any of the metrics already in the Power BI Model Analyzer solution or create your own!

Reports

Reports provide more detailed metrics. To see reports for workspaces for which you are an admin, in Reports, click Power BI Model Analyzer. Or, from the dashboard, click a metric cell to go to the underlying report. At the bottom of the report, there are nine tabs: Cover – Provides metadata about the solution including version information, link to the item, item documentation, and a link to submit ideas for enhancement. Workspaces – Provides a high level view of Power BI workspace sizing and a breakdown by dataset listing within each workspace.  Datasets – Provides a high level view of Power BI dataset sizing and a breakdown by table listing within each dataset. Tables – Provides a high level view of Power BI dataset table sizing and a breakdown by column listing within each table. Columns - Provides a detail analysis of column cardinality and sizing. Measures - Provides detail listing of all measures from each dataset. Relationships - Provides detailed metadata regarding the relationship configuration within each model. Compression – This tab shows information about the compression applied to tables and columns. Encoding – This tab shows information on the datatype and the kind of encoding used for the columns. Object Dependency – This tab shows the dependencies between columns and measures and unused columns (in the data model – not including visuals).  

 

Cover Tab

The Cover tab contains documentation of the solution, a product link contact information, version information, and a link to submit feature requests.

Field	Description
This Version	Currently installed app version.
Latest Version	Latest version available to download.
Last Updated	Date and time the product listing was last updated.
Product Link	Link to the product on vizbp.com.
Feature Request	Link to submit feature requests to the author of the product to review.

 

Workspaces Tab

The Workspaces tab shows an overview of Power BI workspaces and a breakdown by dataset listing.

1. Tiles of the overall count of Workspaces, Datasets, Tables, Table Rows, and the Table Size that you are an admin of.
2. Listing of all Workspaces and their respective count of Tables, number of Table Rows, and the Table Size of all Workspaces and Datasets that you are an admin of.
3. Listing of Datasets showing the number of Table Rows, Table Size, number of Columns, number of Measures, and number of Relationships.

All listings can be cross-filtered to show content of specific Workspaces.  

 

Top Tiles

Field	Description
Workspaces	Number of workspaces of which you are an admin.
Datasets	Number of datasets.
Tables	Number of tables.
Table Rows	Number of rows.
Table Size (Mb)	Size of columns, relationships, and user hierarchies in megabytes.

 

Power BI Workspace filter

Field	Description
Power BI Workspace	Filter to allow selection of workspace(s).

 

Show me information about each workspace…

Field	Description
Power BI Workspace	Name of each workspace.
Datasets	Number of datasets.
Tables	Number of tables.
Table Rows	Number of rows.
Table Size (Mb)	Size of columns, relationships, and user hierarchies in megabytes.

 

Show me information about workspace datasets…

Field	Description
Power BI Workspace	Name of each workspace.
Power BI Dataset	Name of each dataset in each workspace.
Table Rows	Number of rows.
Table Size (Mb)	Size of columns, relationships, and user hierarchies in megabytes.
Columns	Number of columns.
Measures	Number of measures.
Relationships	Number of relationships.

back to top  

 

Datasets Tab

The Datasets tab shows an overview of Power BI workspaces and a breakdown by dataset listing.  As well, it shows a breakdown of dataset tables under each dataset.

1. Tiles of the overall count of Workspaces, Datasets, Tables, Table Rows, and the Table Size that you are an admin of.
2. Listing of all Workspaces with a breakdown of Datasets and their respective count of Tables, number of Table Rows, and the Table Size of all Workspaces and Datasets.
3. Listing of Datasets and Tables in each dataset showing the number of Table Rows, breakdown of Table Size, number of Columns, number of Measures, and number of Relationships.

All listings can be cross-filtered to show content of specific Workspaces.  

 

Top Tiles

Field	Description
Workspaces	Number of workspaces of which you are an admin.
Datasets	Number of datasets.
Tables	Number of tables.
Table Rows	Number of rows.
Table Size (Mb)	Size of columns, relationships, and user hierarchies in megabytes.

 

Power BI Workspace filter

Field	Description
Power BI Workspace	Filter to allow selection of workspace(s).

 

Show me information about each dataset…

Field	Description
Power BI Workspace	Name of each workspace.
Power BI Dataset	Name of each dataset.
Datasets	Number of datasets.
Tables	Number of tables.
Table Rows	Number of rows.
Table Size (Mb)	Size of columns, relationships, and user hierarchies in megabytes.

 

Show me information about dataset tables…

Field	Description
Power BI Workspace	Name of each workspace of which you are an admin.
Power BI Dataset	Name of each dataset.
Table Rows	Number of rows.
Table Size (Mb)	Size of columns, relationships, and user hierarchies in megabytes.
Columns	Number of columns.
Measures	Number of measures.
Relationships	Number of relationships.

back to top  

Tables Tab

The Tables tab shows an overview of Power BI datasets and a breakdown by table listing.  As well, it shows a breakdown of columns under each table.

1. Tiles of the overall count of Tables, Columns, Table Rows, Table Size, and the Column Size (Total).
2. Listing of all Datasets with a breakdown of Tables and their respective number of Table Rows, the Table Size, number of Columns, and total number of Tables.
3. Listing of Tables in each dataset and the Columns in each showing the number of Column Size (Total), Column Cardinality, Column Size (Avg), and number of Columns.

All listings can be cross-filtered to show content of specific Workspaces.  

 

Top Tiles

Field	Description
Tables	Number of tables.
Columns	Number of columns.
Table Rows	Number of rows.
Table Size (Mb)	Size of columns, relationships, and user hierarchies in megabytes.
Column Size (Mb)	Size of columns including data, dictionary, and hierarchy sizes in megabytes.

 

Power BI Workspace filter

Field	Description
Power BI Workspace	Filter to allow selection of workspace(s).

  Power BI Dataset filter

Field	Description
Power BI Dataset	Filter to allow selection of dataset(s).

 

Show me information about each table…

Field	Description
Power BI Dataset	Name of each dataset.
Table Name	Name of each table.
Table Rows	Number of rows.
Table Size (Mb)	Size of columns, relationships, and user hierarchies in megabytes.
Columns	Number of columns.
Tables	Number of tables.

 

Show me information about each column…

Field	Description
Table Name	Name of each table.
Column Name	Name of each column.
Column Size (Mb)	Size of columns including data, dictionary, and hierarchy sizes in megabytes.
Column Cardinality	Number of distinct values in each column.
Column Size (Avg per Row bytes)	Average size of each record in a column in bytes.
Columns	Number of columns.

back to top  

 

Columns Tab

The Columns tab shows detailed information for all columns for tables in datasets of the workspaces of which you are an admin.

1. Count of Columns, number of Table Rows, and total Column Size (Mb).
2. A donut chart showing components of total column size broken out by Column Size (Dictionary Mb), Column Size (Data Mb), and Column Size (Hierarchy).
3. Filters for Power BI Workspace, Power BI Dataset, and Table Name.
4. Listing of Power BI Workspaces and Power BI Datasets along with the Table Size (Mb).
5. Listing of Table Name and Column Name with their respective Column Size (Mb), Column Cardinality, and count of Columns.
6. Listing of Power BI Dataset, Table Name, Column Name, Data Type of the column, Column Size (Mb), Column Size (Data Mb), Column Size (Dictionary Mb), Column Cardinality, number of Table Rows, Column Size (Avg per Row bytes), and number of Columns.

All visuals can be cross-filtered.  

 

Top Tiles

Field	Description
Columns	Number of columns.
Table Rows	Number of rows.
Column Size (Mb)	Size of columns including data, dictionary, and hierarchy sizes in megabytes.

 

Donut chart

Field	Description
Column Size (Dictionary Mb)	The dictionary structure size in Mb of the column(s).
Column Size (Data Mb)	The compressed data size in Mb of the column(s).
Column Size (Hierarchy Mb)	The size in Mb of the auto-generated hierarchies for column(s).

 

Power BI Workspace filter

Field	Description
Power BI Workspace	Filter to allow selection of workspace(s).

 

Power BI Dataset filter

Field	Description
Power BI Dataset	Filter to allow selection of dataset(s).

 

Table Name filter

Field	Description
Table Name	Filter to allow selection of table name(s).

  Show me information about each dataset…

Field	Description
Power BI Workspace	Name of each workspace of which you are an admin.
Power BI Dataset	Name of each dataset.
Table Size (Mb)	Size of columns, relationships, and user hierarchies in megabytes.

 

Show me information about each table…

Field	Description
Table Name	Name of each table.
Column Name	Name of each column.
Column Size (Mb)	Size of columns including data, dictionary, and hierarchy sizes in megabytes.
Column Cardinality	Number of distinct values in each column.
Columns	Number of columns.

 

Show me information about each column…

Field	Description
Power BI Dataset	Name of each dataset.
Table Name	Name of each table.
Column Name	Name of each column.
Data Type	Data type of each column.
Column Size (Mb)	Size of columns including data, dictionary, and hierarchy sizes in megabytes.
Column Size (Data Mb)	The compressed data size in Mb of the column(s).
Column Size (Dictionary Mb)	The dictionary structure size in Mb of the column(s).
Column Cardinality	Number of distinct values in each column.
Table Rows	Number of rows.
Column Size (Avg per Row bytes)	Average size of each record in a column in bytes.
Columns	Number of columns.

back to top  

 

Measures Tab

The Measures tab shows detailed information for all measures in datasets of the workspaces of which you are an admin.

1. Count of Measures, number of Dependencies for those measures, and number of Measures w/ Dependencies.
2. Filters for Power BI Workspace and Power BI Dataset.
3. Listing of Power BI Workspaces and Power BI Datasets along with the Table Size (Mb) and number of Measures.
4. Listing of Measure Name and Referenced Name showing their respective level in which they are referenced.
5. Listing of Power BI Dataset, Table Name, Measure Name, IsHidden flag showing if the measure is hidden, Display Folder of the measure, the Expression of the measure, count of Measures, count of Measures w/ Dependencies, and number of Dependencies.

All visuals can be cross-filtered.  

 

Top Tiles

Field	Description
Measures	Count of measures.
Dependencies	Count of instances in which a measure is referenced by another measure.
Measures w/ Dependencies	Count of measures that have dependencies on other measures.

  Power BI Workspace filter

Field	Description
Power BI Workspace	Filter to allow selection of workspace(s).

 

Power BI Dataset filter

Field	Description
Power BI Dataset	Filter to allow selection of dataset(s).

 

Show me information about each dataset…

Field	Description
Power BI Workspace	Name of each workspace of which you are an admin.
Power BI Dataset	Name of each dataset.
Table Size (Mb)	Size of columns, relationships, and user hierarchies in megabytes.
Measures	Number of measures.

 

What measures have dependencies?

Field	Description
Measure Name	Name of each measure.
Referenced Measure	Name of each measure referenced.
Level 1	Count of measures directly referenced by the original measure.
Level 2	Count of measures referenced two levels deep by the original measure.
Level 3	Count of measures referenced three levels deep by the original measure.
Level 4	Count of measures referenced four levels deep by the original measure.
Level 5	Count of measures referenced five levels deep by the original measure.
Level 6+	Count of measures referenced six or more levels deep by the original measure.

 

Show me information about each measure…

Field	Description
Power BI Dataset	Name of each dataset.
Table Name	Name of each table.
Measure Name	Name of each measure.
IsHidden	Flag indicating if a measure is hidden or not.
DisplayFolder	Display Folder the measure is contained within.
Expression	DAX expression used to build measure.
Measures	Number of measures.
Measures w/ Dependencies	Count of measures that have dependencies on other measures.
Dependencies	Count of instances in which a measure is referenced by another measure.

back to top  

 

Relationships Tab

The Relationships tab shows information about relationships from each dataset from workspaces of which you are an admin.

1. Count of Relationships, Tables, and Relationship Size (Mb) of those relationships.
2. Filters for Power BI Workspace and Power BI Dataset.
3. Listing of Power BI Workspaces and Power BI Datasets along with the Relationships and their Relationship Size (Mb).
4. Listing of relationships showing the From Table Name, From Column Name, To Table Name, and To Table Column showing their Active status, if Single or Both Cross Filter direction, and Relationship Size (Mb).
5. Listing of From Table Name, From Column Name, From Cardinality of the from column, To Table Name, and To Table Column, To Cardinality of the to column showing their Active status, if Single or Both Cross Filter direction, FromCardinalityType, ToCardinalityType, SecurityFilteringBehavior, RelyOnReferencialIntegrity, and Relationship Size (Mb)

All visuals can be cross-filtered.  

 

Top Tiles

Field	Description
Relationships	Count of relationships.
Tables	Count of tables.
Relationship Size (Mb)	Size of relationships between tables.

 

Active filter

Field	Description
Active	Active status of relationships between tables.

 

Cross Filter filter

Field	Description
Cross Filter	Cross filter direction of relationships between tables.

 

Power BI Workspace filter

Field	Description
Power BI Workspace	Filter to allow selection of workspace(s).

 

Power BI Dataset filter

Field	Description
Power BI Dataset	Filter to allow selection of dataset(s).

  Show me information about each dataset…

Field	Description
Power BI Workspace	Name of each workspace of which you are an admin.
Power BI Dataset	Name of each dataset.
Relationships	Count of relationships.
Relationship Size (Mb)	Size of relationships between tables.

 

Show me each relationship…

Field	Description
From Table Name	Originating table of a relationship between tables.
From Column Name	Originating column of a relationship between tables.
To Table Name	To table of a relationship between tables.
To Column Name	To column of a relationship between tables.
Active	Active status of relationships between tables.
Cross Filter Behavior	Cross filter direction of relationships between tables.
Relationship Size (Mb)	Size of relationships between tables.

 

Show me information about each relationship…

Field	Description
From Table Name	Originating table of a relationship between tables.
From Column Name	Originating column of a relationship between tables.
From Cardinality	Number of distinct values in each originating column.
To Table Name	To table of a relationship between tables.
To Column Name	To column of a relationship between tables.
To Cardinality	Number of distinct values in each to column.
Active	Active status of relationships between tables.
Cross Filter Behavior	Cross filter direction of relationships between tables.
Cardinality Type	The cardinality type of each relationship.
SecurityFilteringBehavior	Apply security filter in both directions flag.
RelyOnReferentialIntegrity	Assume referential integrity flag (DirectQuery).
Relationship Size (Mb)	Size of relationships between tables.

back to top  

 

Compression Tab

The Compression tab shows information about compression applied to tables and columns.

1. Count of Columns, count of Column Cardinality, Column Size (Mb), Table Size (Mb), and number of Table Rows.
2. Filters for Power BI Workspace and Power BI Dataset.
3. Listing of Compression Type and Bits Count of all columns along with the count of Columns, Column Size (Mb), overall % Column Size, number of Segments, and number of Partitions.
4. Listing of Table Name, Column Name, Data Type of the column, Column Cardinality, Column Size (Mb), % Column Size, and the number of Table Rows.

All visuals can be cross-filtered.  

 

Top Tiles

Field	Description
Columns	Count of columns.
Column Cardinality	Number of distinct values in each column.
Column Size (Mb)	Size of relationships between tables.
Table Size (Mb)	Size of columns, relationships, and user hierarchies in megabytes.
Table Rows	Number of rows.

 

Power BI Workspace filter

Field	Description
Power BI Workspace	Filter to allow selection of workspace(s).

 

Power BI Dataset filter

Field	Description
Power BI Dataset	Filter to allow selection of dataset(s).

 

What compression type was used on the columns?

Field	Description
Compression Type	Type of compression applied.
Bits Count	Encoding bits applied.
Columns	Number of columns.
Column Size (Mb)	Size of columns including data, dictionary, and hierarchy sizes in megabytes.
% Column Size	% of total column size.
Segments	Number of segments.
Partitions	Number of partitions.

  Show me information about each column…

Field	Description
Table Name	Name of each table.
Column Name	Name of each column.
Data Type	Data type of each column.
Column Cardinality	Number of distinct values in each column.
Column Size (Mb)	Size of columns including data, dictionary, and hierarchy sizes in megabytes.
% Column Size	% of total column size.
Table Rows	Number of rows.

 

Encoding Tab

The Encoding tab shows information about encoding applied to tables and columns.

1. Count of Columns, count of Column Cardinality, Column Size (Mb), Table Size (Mb), and number of Table Rows.
2. Filters for Power BI Workspace and Power BI Dataset.
3. Listing of Column Encoding and Data Type of all columns along with the count of Columns, Column Size (Mb), and Column Cardinality.
4. Listing of Table Name, Column Name, Column Cardinality, Column Size (Mb), % Column Size, and the number of Table Rows.

All visuals can be cross-filtered.  

 

Top Tiles

Field	Description
Columns	Count of columns.
Column Cardinality	Number of distinct values in each column.
Column Size (Mb)	Size of columns including data, dictionary, and hierarchy sizes in megabytes.
Table Size	Size of columns, relationships, and user hierarchies in megabytes.
Table Rows	Number of rows.

 

Power BI Workspace filter

Field	Description
Power BI Workspace	Filter to allow selection of workspace(s).

 

Power BI Dataset filter

Field	Description
Power BI Dataset	Filter to allow selection of dataset(s).

 

How are columns encoded?

Field	Description
Column Encoding	Encoding type used.
Data Type	Data type of each column.
Columns	Number of columns.
Column Size (Mb)	Size of columns including data, dictionary, and hierarchy sizes in megabytes.
Column Cardinality	Number of distinct values in each column.

 

Show me information about each column…

Field	Description
Table Name	Name of each table.
Column Name	Name of each column.
Column Cardinality	Number of distinct values in each column.
Column Size (Mb)	Size of columns including data, dictionary, and hierarchy sizes in megabytes.
% of Column Size	% of total column size.
Table Rows	Number of rows.

back to top  

 

Object Dependency Tab

The Object Dependency tab shows information about dependencies between all reporting artifacts from each dataset from workspaces of which you are an admin.

1. Count of Columns, count of Columns Referenced, and a count of Columns not Referenced.
2. Filters for Power BI Workspace and Power BI Dataset.
3. Listing of Object Type and the number of Column References for each type.
4. Listing of Power BI Dataset, Table Name, Column Name, Data Type of the column, count of Columns, count of Column References, the Column Cardinality, the Column Size (Mb), the number of Columns not Referenced, and the number of Columns Referenced.

It is noted that this does not include if the column is referenced in Power Query transformations or utilized in visuals. All visuals can be cross-filtered.  

 

Top Tiles

Field	Description
Columns	Count of columns.
Columns Referenced	Count of columns referenced.
Columns not Referenced	Count of columns not referenced.

 

Power BI Workspace filter

Field	Description
Power BI Workspace	Filter to allow selection of workspace(s).

 

Power BI Dataset filter

Field	Description
Power BI Dataset	Filter to allow selection of dataset(s).

 

How are columns encoded?

Field	Description
Object Type	Reference type.
Column References	Count of columns referenced by each type.

  What content is in each workspace?

Field	Description
Power BI Dataset	Name of each dataset.
Table Name	Name of each table.
Column Name	Name of each column.
Data Type	Data type of each column.
Columns	Count of columns.
Column References	Count of columns referenced.
Column Cardinality	Number of distinct values in each column.
Column Size (Mb)	Size of columns including data, dictionary, and hierarchy sizes in megabytes.
Columns not Referenced	Count of columns not referenced.

  back to top  

 

Want to build it yourself?

https://powerdax.com/how-to-analyze-your-power-bi-data-model-size/

Take a look at the product and see if it would be valuable for you or your organization. It is available on vizbp.com:

https://vizbp.com/downloads/power-bi-model-analyzer/

While there, take a look at other solutions people like me are making available for free or for purchase. Take a look at becoming a seller yourself and offering your solutions in the marketplace.

https://vizbp.com/sell/

While here, take a look at my other articles related to vizbp.com:

• Why I started vizbp…..
• Why buy from vizbp?
• Why sell with vizbp?