This will allow you to connect with any Analysis Services client tool (SSMS, Profiler, Excel, Power Shell etc.) and run data, or DMV queries. 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. There will be issues with this approach, but maybe not too many to resolve to help youДЇinally, an unrelated tip as a gift for those that read to the end of blogs. ON OtherTable.object_id = OtherTable_Columns.object_id ON OtherTable_Columns.Name = ChildTable_Columns.NameДЄND OtherTable_Columns.object_id != ChildTable_Columns.object_idДЄND OtherTable_Columns.system_type_id = ChildTable_Columns.system_type_id INNER JOIN sys.columns AS OtherTable_Columns ON ChildTable_Columns.object_id = ChildTable.object_id INNER JOIN sys.columns AS ChildTable_Columns

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. Additional columns could be added to the base query to be used in tool-tips etc. The catalog views could be used in a similar way to generate power bi visuals showing other object dependencies inside an MS SQL Database. 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. ItвЂ™s not your traditional ERD box and crows-feet diagram, but it still allows you to see which tables are related to others. I like the Sankey because it allows you to click and drag objects around to create a nicer arrangement. The other visual I tried was the Sankey diagram. You can also turn on direction arrows as well as some other handy refinements. ItвЂ™s possible to play with colour settings in the format properties of this visual. I tried it out on the WideWorldImporters sample database from Microsoft, and this is how it can look. This provides an animated view of how each table is related to other tables. Two I tried were the Force Directed custom visual. Now the data is in Power BI, visuals that support a вЂњFrom/ToвЂќ dataset can be used to visualise, filter and sort the data.

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. The query works with SQL version 2012 and upwards (including SQL 2017). 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. Tables with no foreign keys will exist in this data-set with blank values in the column. When visualised, this data will show multi-generational relationships. Tables that are returned in theВ column represent child tables, while tables showing in the column represent parents. Once appropriate authentication has been added, the query will return a dataset useful to visualise tables and how they are related. ON Tables_With_No_FKeys_Schema.schema_id = Tables_With_No_FKeys.schema_id RIGHT JOIN sys.schemas AS Tables_With_No_FKeys_Schema ON Tables_With_No_FKeys.Name = SO_To_Table.NameДЄND SO_To_Table.schema_id = SO_To_Table.schema_id

RIGHT JOIN sys.Tables AS Tables_With_No_FKeys ON SO_To_Schema.schema_id = SO_To_Table.schema_id ON SO_To_Table.object_id = FC.referenced_object_id ON SO_From_Schema.schema_id = SO_From_Table.schema_id ON SO_From_Table.object_id = FC.parent_object_id Enter the Server and Database in the appropriate fields.Д®xpand the Advanced option in this dialog and enter the following SQL Query. 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. I will post an alternative query for generating links later in the blog. Not all databases will have these as reasons such as performance will mean foreign keys wonвЂ™t exist. This works as long as foreign keys have been added to your database between tables. The approach is to build a list of вЂњFromвЂќ and вЂњToвЂќ tables using the sys.foreign_keys table as a base. The approach is to build a query that can be run against the catalog system views inside each MS SQL database. Ever wanted to create a diagram of your MS SQL Database tables in Power BI? Here is what I did to make that happen.

Power bi desktop sql query