Browse SQL Server Databases Using Power BI

Problem Statement

The Scenario

Say you have a vast pool of SQL Servers and Databases storing thousands of database objects, including tables, views, stored procs and many more, for the purpose of landing data area or warehousing. On the other hand you have number of user groups, such as, Data Analysts, Business Analysts, Data Engineers, Citizen Developers, BI Developers and many more, who want to explore and consume data available to solve specific business needs.

The Problem

Many users will request access to number of SQL Servers and Databases, just to explore if the the data they are looking is available in any of those. This small paramount problem hides in plain sight and get overlooked in day to day operations. Resulting in, way too many unnecessary permissions to databases. But, there is a simple solution.

The Solution

Download The PBIX File

A Power BI report, which connects using Direct Query to all Servers/Databases, union all the database objects and visualize on a single page. Once published in Power BI Service and shared with users, this report can offer a web based window to explore Schema information across multiple servers and databases.

Merits

• Single search across multiple servers and databases: Since schema information is collected across the servers and databases, you can simply search for an object name using slicers. No need to login into individual server/instances and exploring databases one by one.
• Find duplication of objects: As the object can be searched across servers and databases, any duplication of tables or views can be spotted easily in the report.
• Users don’t need direct access to the underlying SQL Servers/Databases: As the Power BI report uses Direct Query to connect to SQL source via secured Data Gateway, report users don’t need to have direct permissions to the underlying sources, as long as they have Viewer permissions on the report.
• Direct Query connection always pull latest information, so low maintenance: Because Direct Query refreshes the report while rendering, it will always show latest state of the sources, making the maintenance less demanding.

Build The Solution

Step by step guide to develop the report

Step 1

Click on ‘Get Data’ and connect to SQL Server and copy paste following T-SQL script in the query part:

SELECT DISTINCT
o.name AS ObjectName,
C.name AS Column_Name,
o.type_desc AS Type,
s.name AS SchemaName,
@@servername AS ServerName,
DB_NAME() AS DatabaseName
FROM
SYS.objects o
INNER JOIN SYS.schemas s ON s.schema_id = o.schema_id
INNER JOIN sys.columns AS C ON o.object_id = C.object_id
WHERE
o.type_desc IN ('View', 'USER_TABLE')

Step 2

Repeat Step 1 for all the data sources you intend to bring into the report, or as a short cut, duplicate the Query created in Step 1 and edit sources in Advanced Editor:

Step 3

Union all the queries by clicking on ‘Append Queries As New’, to result in one master table with all the data:

Step 4

Visualize: It’s time to create visualization of the data, you can choose to use any chart or layout. My personal favourites are two views below:

View 1: By Server / Database

This view groups tables/views and columns by Server/Database/Schema along with some major slicers at the top:

View 2: By Objects

This view groups by tables/views, which can prove to be useful to spot duplication across servers/databases:

Conclusion

Searching tables or fields across multiple Servers/Databases can be quite cumbersome, specially for Citizen Developers and Business Analysts, who are not technical enough to install SSMS and explore these servers/databases one by one. Not to mention, they would need Read access to all these servers/databases. The solution presented in this blog can provide a web based bridge between vast pool of servers/databases and seasonal data explorers. Once they have identified the Server/Database/Schema, they can raise an access request to explore the underlying data using SSMS.

Download The PBIX File