Customize Azure Data Studio with Dashboard Widgets

Introduction

Azure Data Studio is a free multi-platform database tool with built-in support for both SQL Server on-premises and Azure SQL Databases. These databases can be accessed in Azure Data Studio for numerous tasks like query editing, data development, built-in charting of T-SQL queries, etc. Database developers or Database Administrators often have a need to check large datasets on a regular basis. For this, accessing this information in a convenient method rather than executing some scripts or reports repeatedly will be a better choice for data professionals.

Azure Data Studio users can add customized widgets to the default dashboards which can be used to track and access regular usage of databases. We can turn the T-SQL query result set into interesting charts and graphs like donut, pie, table, etc. using the Chart tab in Azure Data Studio. With basic JSON configuration of these visualizations, an effective custom insight widget can be created. Azure Data Studio offers building customizations without making use of any other language other than T-SQL and some basic JSON configuration, which is generated for the users by the tool itself.

View the result in a chart view

Open Azure Data Studio and run a SQL script to query the current active sessions.

• To open a new editor, press Ctrl+N.
• Change the connection context to in which DB we need to create the Widget.
• Write our T-SQL Statement and Execute it.

SELECT
      AddressLine2,
	  COUNT(*) AS TotalMembers
  FROM [PracticalWorks].[Details].[StudentPersonalDetails]
  GROUP BY AddressLine2

• Save the query in the editor to a *.sql file.
• Again execute the same query
• After the query results are displayed, click Chart Icon from the Result Window.

Add the custom insight to the database dashboard

• To open the insight widget configuration, click Create Insight on Chart Viewer

Fig.1 Click the Chart Icon

Fig.2 Choose the Chart Type

• Copy the insight configuration (the JSON data).
• Press Ctrl+Comma to open Settings
• Type dashboard in Search Settings
• Click Edit for dashboard.database.widgets.
• Paste the insight configuration JSON into dashboard.database.widgets. Database dashboard settings looks like the following

{
    "datasource.connectionGroups": [
        {
            "name": "ROOT",
            "id": "C777F06B-202E-4480-B475-FA416154D458"
        }
    ],
    "datasource.connections": [
        {
            "options": {
                "connectionName": "",
                "server": "DESKTOP-8K4JUIJ\\MSSQLSERVER01",
                "database": "",
                "authenticationType": "Integrated",
                "user": "",
                "password": "",
                "applicationName": "azdata",
                "groupId": "C777F06B-202E-4480-B475-FA416154D458",
                "databaseDisplayName": ""
            },
            "groupId": "C777F06B-202E-4480-B475-FA416154D458",
            "providerName": "MSSQL",
            "savePassword": true,
            "id": "a576e6f7-ff0a-4661-b690-14231684d3cc"
        }
    ],
    "workbench.enablePreviewFeatures": true,
    "workbench.startupEditor": "welcomePage",
    "window.zoomLevel": 0,
    "dashboard.database.widgets": [
        {
            "name": "State Wise Count",
            "gridItemConfig": {
                "sizex": 2,
                "sizey": 1
            },
            "widget": {
                "insights-widget": {
                    "type": {
                        "doughnut": {
                            "dataDirection": "vertical",
                            "columnsAsLabels": true,
                            "labelFirstColumn": false,
                            "legendPosition": "top",
                            "encoding": "hex",
                            "imageFormat": "jpeg"
                        }
                    },
                    "queryFile": "c:\\Users\\user\\TotalCityCount.SQL"
                }
            }
        }
    ],
    "dashboard.database.properties": "collapsed"
}

• Refresh the Database and our Widget is created,

Fig. 3 Dashboard with Insight Widget

Demo

Fig 4 Demo

Conclusion

In this article we have discussed how to implement the Dashboard Widgets in Azure Data Studio. I hope you all found useful and please share your feedbacks in the comment section.

Consider reading other SQL articles of Mine

• Cycle Clipboard Ring In SSMS – Reuse Copied Items
• Set custom colors to differentiate between environments in SSMS
• Recover unsaved SQL queries in SSMS
• Multiple Backup Files of the SQL Server database with SSMS and T-SQL
• Difference between CURRENT_TIMESTAMP vs GETDATE() vs SYSDATETIME() vs GETUTCDATE() in SQL Server
• Change Schema Name of Table in MS SQL Server
• Identifying object dependencies in SQL Server using SP_DEPENDS
• Select Names Starting With Vowels in MS SQL Server
• Display Line Numbers in a SQL Server Management Studio Query Window (SSMS)