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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: