While the relationships view of the datamodel provides a very good overview which tables are connected to each other, one cannot see at a glance on which field they are connected to each other.
This is where a pivot table-view of the field-connections can be really helpful:
Pivot-table-view:
Table-Fields-Connections in Pivot-View
Here you see the tables on the many-side in the rows and in the columns are the tables on the one-side (of course you can change that). Add some slicers if your model is very large.
This is the source-data in table-form which can sometimes also be helpful:
Table View:
Translated TMSCHEMA_RELATIONSHIPS
You fetch it via DMVs connected to your PBI-datamodel:
M-Code:
M-Code for Table-Field-Relationships
Code to download: MCodeDMV_TableRelationships.txt
Make sure you have only one PBI-workbook open, making sure that’s the one who this query will connect to.
Want to analyse your Power Pivot model in Excel instead?:
- Use DAX-studio to read the model and export the 3 DMV’s to a txt which you then import or
- Import your xlsx-file into Power BI so that its metadata can be fetched automatically.
Of course, this works for tabular-models in SSAS as well.
Subscribers can download the Excel file: RelationshipAnalysis2.xlsx
Enjoy & stay queryious 🙂
This works very well, thanks for sharing! It’s nice to have a way to communicate to end users which tables have relationships in your model – especially useful for larger analysis services tabular models.
Thx James, that’s what I hoped: I had developed it for me to quicker understand a new customer’s model and thought that it might helps others as well 🙂
Pingback: #Excel Super Links #59– shared by David Hager | Excel For You