Azure Synapse Analytics

In this article, we’ll learn in a step-by-step process to connect and query the data warehouse that we created – SQL Pool. This article will share light on the power of the use of different tools Microsoft provides from Azure, Data Warehousing, SQL Server Management Studio, Azure Data Studio and more. Lastly, we’ll also learn to delete the resource we create in order to save ourselves from any charges that may incur.

This article is a part of the Azure Synapse Analytics Articles Series. You can check out other articles in the series from the following links.

Azure Synapse is a limitless enterprise analytics service that enables us to get insight from data analytics and data warehousing. Using dedicated resources or serverless architecture, data can be queried and provides scalability as per the increase in the size of the data. You can learn more about it in the previous article, Azure Synapse Analytics.

Data Warehouse

Data Warehouse can be understood as a core part of business intelligence which enables data analysis and reporting and are basically the major repository which consists of data that are integrated from different disparate sources. The endless possibility of data mining starts with Data Warehousing.

In the previous article, we learn to create data warehouse through the dedicated SQL pool. Today, let us dive deeper into connecting to the data warehouse and querying it.

Step 1

First of all, follow the articles Azure Synapse Analytics - Create Dedicated SQL Pool and Azure Synapse Analytics - Creating Firewall at Server-level and create a dedicated SQL pool with secure firewall enabled.

Connecting to Data Warehouse - SQL Pool

Step 2

Next, we visit the home page of data warehouse we just created, ojash-pool.

Here, copy the server name.

Step 3

Next, we visit the SQL Server Management Studio. Since, Azure Data Studio has the SSMS functionality enabled, we use the Azure Data Studio and add in new connection.

Step 4

Here, fill in the Server name that we copied earlier. Set the Connection type as Microsoft SQL Server.

Next, we set the Authentication type as SQL Login. Once we do this, we are provided with the option to fill in the User name and Password. This is the same login details we setup while creating the SQL Pool.

Once done, click on Connect.

Step 5

Now, the Azure Data Studio will be connected to the server which consists the data warehouse SQL pool we created. Here, we can view ojash-pool with the status ONLINE.

On the left-hand side, as we expand the Tables in ojash-pool we can see numerous tables which is basically the sample data we created initially.

Step 6

Next, Right Click on Tables and click on New Query.

Now, the SQL Query window opens.

Querying the Data Warehouse – SQL Pool

Step 7

Now, we perform some query.

Displaying Databases and Data Warehouse

SELECT * FROM sys.databases

Here, we can obtain the result once we click on Run.

Here we can see, the master and ojash-pool with other details about the database.

Querying Data

Step 8

Now, Choose the ojash-pool in the connection and write a query to display data from the database.

SELECT LastName, FirstName FROM dbo.dimCustomer
WHERE LastName = 'Adams' AND NumberChildrenAtHome = 4;

Here, we selected the First and Last Name from Table and queried in Data with filtering in the lastname and the number of children at home. Here, for LastName Adams and Number of Children at home of 4, we get 5 data with different First Names.

Next, for Last Name of Adams and Number of Children at home of 1, we get 11 data.

SELECT LastName, FirstName FROM dbo.dimCustomer
WHERE LastName = 'Adams' AND NumberChildrenAtHome = 1;

Similarly, with First Names James and Number of Children at Home of 1, we get 18 different data.

SELECT LastName, FirstName FROM dbo.dimCustomer
WHERE FirstName = 'James' AND NumberChildrenAtHome = 1;

In this same way, we can now query in data and use it for our work as per our need.

Deleting Server

Step 9

In order to save ourselves from any charges to incur, we must delete our resources.

This is done by visiting the Azure Portal and Selecting our Server.

Here, we can see ojash-server and ojash-pool running. We choose the ojash-server.

Here, as we are taken to the server page, we can click on Delete.

Now, a next dialog box pops up. Here, we need to type the server name manually. This is done to fail safe any unexpected or undesired error of deletion as this process is irreversible which is highly critical activity.

Now, as the name is filled, click on Delete.

We are popped up with notifications.

Once, we are prompted with the Success of Deletion, visit the home page of Azure.

Here on contrary to the different services that could be seen in recent resources, we can see it is clean now. Our server has been successfully deleted and we made sure about it.

Conclusion

Thus, in this article, we learnt to connect data warehouse that we created using the Azure Synapse Analytics to SSMS. As Azure Data Studio is installed with SSMS itself, we can use the Azure Data Studio to access the data warehouse. Following up on the last article of SQL Pool creation, in this article we connect and queried the Data Warehouse and finally also learnt to delete the server and database we had created.