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:
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:
You fetch it via DMVs connected to your PBI-datamodel:
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 🙂