Diagram your Database Tables using Power BI


Ever wanted to create a diagram of your MS SQL Database tables in Power BI?  Here is what I did to make that happen.

The approach is to build a query that can be run against the catalog system views inside each MS SQL database.  The approach is to build a list of “From” and “To” tables using the sys.foreign_keys table as a base.  This works as long as foreign keys have been added to your database between tables.  Not all databases will have these as reasons such as performance will mean foreign keys won’t exist.  I will post an alternative query for generating links later in the blog.

To get started, click the Get Data button on the ribbon in your instance of Power BI Desktop and choose SQL Server as your data source.  Enter the Server and Database in the appropriate fields.

Expand the Advanced option in this dialog and enter the following SQL Query.

Once appropriate authentication has been added, the query will return a dataset useful to  visualise tables and how they are related.  Tables that are returned in the [From Table] column represent child tables, while tables showing in the [To] column represent parents.  When visualised, this data will show multi-generational relationships.  Tables with no foreign keys will exist in this data-set with blank values in the [From Table] column.

It basically tries to suck out a list of To-From pairs of data that will be useful for some of the network visuals in Power BI.

The query works with SQL version 2012 and upwards (including SQL 2017).

The values in the schema columns may be useful in slicers if you would like the ability to show/hide groups of tables if you have a large and complex model.

Now the data is in Power BI, visuals that support a “From/To” dataset can be used to visualise, filter and sort the data.  Two I tried were the Force Directed custom visual.

This provides an animated view of how each table is related to other tables.  I tried it out on the WideWorldImporters sample database from Microsoft, and this is how it can look.

It’s possible to play with colour settings in the format properties of this visual.  You can also turn on direction arrows as well as some other handy refinements.

The other visual I tried was the Sankey diagram.  I like the Sankey because it allows you to click and drag objects around to create a nicer arrangement.  It’s not your traditional ERD box and crows-feet diagram, but it still allows you to see which tables are related to others.

The network navigator was another good visual, and if you have an R instance installed on your local machine, you can play with some of the custom R visuals.

The catalog views could be used in a similar way to generate power bi visuals showing other object dependencies inside an MS SQL Database.  Additional columns could be added to the base query to be used in tool-tips etc.

If your database tables do not have foreign keys, here is another query that can be used to help guess relationships between tables based on column name and datatype.

There will be issues with this approach, but maybe not too many to resolve to help you

Finally, an unrelated tip as a gift for those that read to the end of blogs.  Did you know you can start a non-Store instance of Power BI Desktop on a specific port by using the /diagnosticsport switch?  The example below will start an instance of Power BI Desktop, listening on port 5555.  This will allow you to connect with any Analysis Services client tool (SSMS, Profiler, Excel, Power Shell etc.) and run data, or DMV queries.

This won’t work well with a Store version of Power BI Desktop.  The port number must be available and seems to only work with 4 digits.  Please only use for good and respect the EULA agreements of Power BI Desktop. 🙂

Philip Seamark
Phil is Microsoft Data Platform MVP and an experienced database and business intelligence (BI) professional with a deep knowledge of the Microsoft B.I. stack along with extensive knowledge of data warehouse (DW) methodologies and enterprise data modelling. He has 25+ years experience in this field and an active member of Power BI community.


  • Hi Philipp. Thank you for this very interesting article. I have one question regarding the /diagnosticsport. I don’t understand which use cases are allowed with this option? Can you give me some examples?

    Kindest regards

  • Have you tried the hierarchy chart visual? It might be another neat one to try, you have the option of colour coding again and adding a field beneath what would be table names that could be something like the number of rows.
    I have used the hierarchy chart to plot the execution of ssis packages, code red, amber and green for execution status. It’s not too bad.

Leave a Reply

Your email address will not be published. Required fields are marked *