Connect SQL Server to Google Data Studio: Easy Steps

Are you looking for the best way to connect SQL Server to Google Data Studio? This will help you access and analyze your SQL Server data in real-time, create custom reports and dashboards, and draw insights from various business operations regardless of the volume of data. 

As you can schedule and automate reports, you can focus on analyzing the data rather than creating and distributing the reports. You can combine data from sources other than SQL Server as well, and that will give a broader understanding of business performance.

Connecting SQL Server to Google Data Studio

Let’s look into another way to establish Google Data Studio SQL server connection.

Pre-requisites

• A Google Analytics Account
• Microsoft SQL Server
• Python 3.X

Although Data Studio has been up and running for some time, there are still limited connectors to fetch data from various data sources. For example, there is no connector to help users connect SQL Server to Data Studio. However, there is a workaround to this, which is “Google Sheets”. There is a direct integration between Google Sheets and Data Studio. If you manage to move your data from SQL Server to Google Sheets, you can pull it from there into Data Studio. 

There are two approaches that you can use to pull your data from SQL Server into Sheets:

1. Using a Script written in Python, JavaScript or any other programming language. In this article, we will use Python. 
2. Exporting the data into a CSV file and then importing it into Google sheets. 

Let’s discuss these two approaches. 

Connecting Python to SQL Server

In the next few sections, I will be showing you how to use Python and Google Sheets to connect SQL Server to Data Studio. 

I will divide the process into three steps. 

• First, we will connect Python to SQL Server. 
• Secondly, we will fetch data from SQL Server into Google Sheets. 
• And finally, we will connect the Google Sheets to Data Studio. 

Let’s start…

To connect Python to SQL Server, we can use the pyodbc library. If you have not installed it, use Python’s pip package manager to install it. 

Note that pip comes with Python, so you don’t have to install it if you’ve already installed Python. 

To install the pyodbc library, run the following command on the terminal of your operating system:

pip3 install pyodbc

We have used pip3 because we are using Python 3.X. 

You can then run the following Python code to connect Python to SQL Server:

import pyodbc 
conn=pyodbc.connect('DRIVER={SQL Server};SERVER=server_name;DATABASE='+DB_name;UID=user_name;PWD=user_password')
cursor = conn.cursor()
cursor.execute("SELECT * FROM table_name limit 5")
result = cursor.fetchall()
conn.close()

The above code helps us connect Python to SQL Server.

• First, we imported the pyodbc library using Python’s import statement. 
• We then created the conn object to help us connect to the database. 
• The DRIVER parameter states that we are connecting to SQL Server database. 
• The name of the server on which the SQL SERVER database is running should be specified using the SERVER parameter. 
• The DATABASE parameter is for specifying the name of the SQL Server database to connect to. 
• The UID (User ID) is for specifying the name of the database user, while the PWD parameter is for specifying the password for the database user. 
  Note that you MUST change the values of the above parameters to match what you’ve in your system. 
• Next, we have used the cursor() function to create a database cursor. This cursor will help us to execute queries against the database. 
• We have then called the execute() function to run a SELECT query on a table.
• The use of the limit 5 option means that only the first 5 rows of the table will be returned. 
• We have then used the fetchall() function to fetch all the data. 
• Finally, the close() function was invoked on the database connection object, that is, conn, to close the connection to the database. 

Write Data to Google Spreadsheet

Now that you’ve established a connection to your SQL Server database using Python, you can write another Python script to write the database data into a Google Spreadsheet. To do this, you should have the gspread and oauth2client libraries. 

You can install them by running the following commands on the terminal of your operating system:

pip3 install gspread
pip3 install --upgrade oauth2client

Google Spreadsheet also requires you to have a client key to be able to write to it. See the client key as a substitute for your email password. You can get the client key from the following URL:

https://gspread.readthedocs.io/en/latest/oauth2.html

Now, here is the Python code to help you write to the Google Spreadsheet:

import gspread
from oauth2client.service_account import ServiceAccountCredentials
Scopes=['https://spreadsheet.google.som/feeds']
credentials=ServiceAccountCredentials.from_json_keyfile_name(key_path,Scopes)
con=gspread.authorize(credentials)
ss = con.open(mygooglesheet)
ws=ss.worksheet(mysheet)
cell_range='A1:B3'
cell_list=ws.range(cell_range)
for x in range(0,6)
  cell_list[x]=x*2
ws.update_cells(cell_list)

We first imported the two libraries, that is, gspread and oauth2client. We have then used a scope to request for permission to access the Google Spreadsheet. The path to the key was also passed for authentication purposes. 

• The gspread authorize() function has helped us perform authorization based on the specified credentials and establish a connection to the Google Spreadsheet. 
• The open() function has then been called to open a Google Spreadsheet named mygooglesheet. 
• The worksheet() function has helped us select a worksheet named mysheet. 

The writing of the data will be done within the cells A1 to B3 of the worksheet. We have used the for loop and variable x to iterate over the cells of the spreadsheet and update them with data. 

Exporting SQL Server Data to CSV

The SQL Server Management Studio (SSMS) has the Import and Export wizard that we can use to export SQL Server data to CSV. 

Follow the steps given below:

Step 1: Open SSMS and establish a connection to the database. 

Step 2: Identify the name of the database whose data is to be exported from the Object Explorer window and right-click on it. Choose “Tasks” then “Export data”. The SQL Server Import and Export window will pop up. 

Step 3: Select the data source that you want to copy. Also, select the name of the server instance. Choose the mode of authentication and the database from which the data is to be copied. Click “Next”. 

Step 4: You will be redirected to a “Choose a Destination” window.  In the Destination box, choose “Flat File Destination“. Specify the name of the CSV file where the data will be exported to. Ensure that the name of the file ends with a .csv extension. Click “Next“. 

Step 5: You will be taken to the “Specify Table Copy or Query” screen. Select “Copy data from one or more tables or views” and then click “Next”. 

Step 6: You will be taken to the “Configure Flat File Destination” window. Select the table that you want to export from the “Source table or view” option and select Comma {,} as the column delimiter. 

Step 7: In the “Save and Run” window, you must click on “Next”. 

Step 8: In the next window, click the “Finish” button. You will get the “Success” window, meaning that the data was exported successfully. 

How to Import CSV Data into a Google Spreadsheet

Now that we have our data in a CSV file, we need to move it into Google Sheets. 

Follow the steps given below:

Step 1: Log into your Google Spreadsheets account. You can use your Google/Gmail account. 

Step 2:  Click “File” and choose “Import”. A new window will pop up. Click on the “Upload” button and choose the “Select a file from your device” option. 

Step 3: Navigate to where you have stored your CSV file and choose it. 

Step 4: A new window will pop up. Click “Import data”. 

The data will then be filled in the currently opened worksheet. 

Connecting Google Spreadsheet to Data Studio

You now have your SQL Server data in the Google Spreadsheets. It’s time to move it to Data Studio for analysis. The good news is that Data Studio has a direct connector that can help you connect to Google Spreadsheets. 

Just follow the steps given below:

Step 1: Sign in to your Data Studio account. 

Step 2: Click the “CREATE” button. A small window will pop up. 

Step 3: Click “Data source”. You will be taken to a page that shows all the available Google Connectors. 

Step 4: Click “Google Sheets”. A page showing all the Google Sheets that you have created in your account will be shown. 

Step 5: Add the Google Sheet that you created as the data source. You will also be prompted to select the sheet name that you want to work with.  Click the “CONNECT” button located on the top right corner of the window. 

Congratulations! It’s that simple. 

Limitations of this Approach

There are many reasons as to why most users will not like the above approach. 

First, you MUST have Python programming knowledge. Python is not a simple programming language, especially when you are a beginner to computer programming. This is not easy for a user who doesn’t have a computer science background. 

Again, this approach results in a lengthy process. You have to install Python, install the libraries, generate the client key and write the code. This will take a longer time for you to accomplish your goal. 

Finally, you may encounter many errors when connecting the Google Spreadsheet to Data Studio. This is normally caused by the NULLs added to rows and columns when gspread writes to the Google Spreadsheet. 

Best Practices for Using Google Data Studio with SQL Server

To optimize your results when using Google Data Studio with SQL Server, you can keep the following best practices in mind:

• Always establish a secure connection between Google Data Studio and SQL Server
• Use meaningful names for fields and tables. This helps in easy data identification and management
• To protect sensitive data implement proper data security measures
• You should regularly monitor your Google Data Studio and SQL Server connection for issues
• To minimize data errors in queries, optimize them so that they run efficiently.

Importance of Regularly Monitoring Your Google Data Studio and SQL Server Connection

As emphasized earlier, to ensure that your Google Data Studio and SQL Server connection remains functional, it is important to monitor it regularly. This helps in providing accurate insights without interruptions. Monitoring helps to identify and address issues that might arise and optimize the connection for optimal performance.

Use Cases of Connecting SQL Server to Google Data Studio

• Access and analysis of SQL Server data in real-time along with ability to create custom reports and dashboards which helps to gain deeper insights into their business operations
• Connecting Google Data Studio and SQL Server facilitates easy collaboration and sharing of reports with team members and stakeholders. It also provides you ability to schedule and automate report generation by which you can save time and focus on analyzing the data rather than manually creating and distributing reports.
• This integration enables you to combine data from multiple sources, providing a comprehensive view of their business performance.

Before wrapping up, let’s cover some basics.

What is Data Studio?

Data Studio is Google’s reporting tool for power users who want more than just the data and dashboards provided by Google Analytics. It gives its users everything that they need to turn their data into easy-to-understand and informational reports through data visualization. The Data Studio’s reports are easy to share, read and customize. It gives you the opportunity to choose the way you want to present your data. You can use line graphs, bar graphs, charts, etc. 

Data Studio also allows you to change the colours and fonts of your reports and brand them with your logo. The good thing with Data Studio is that you can pull in data from different sources other than just Google Analytics. Its reports are so dynamic that when an update is made to the data source, the changes are reflected in the reports. You can also share reports with other people, allow them to view and make changes to them. 

Accessing Data Studio

You can access Google Data Studio from the following URL: https://datastudio.google.com/

1. Simply login using your Google Analytics account. You will be taken to the “Reports” page where you will see sample Data Studio reports as shown below:

The sample reports give you an idea of how Data Studio reports look like and the types of information that you can pull into your custom reports once you get started. 

2. To create a new report, just click the “Blank Report” button. 

3. When you click the “Data Sources” button, you will be taken to a page that shows all the data sources that you have connected to your Data Studio account.
   
   If no single data source has been connected, you will be asked to add one by clicking the “CREATE” button.
   
   This is the same case when you click the “Explorer” button. 

4. To see any reports that have been shared with you, click the “Shared with me” button from the vertical navigation page on the left of the window. 
   
   To see all the reports that you own, click the “Owned by me” button just below the above button. 
   
   Below the above button is the “Trash” button that lets you see all the trashed reports. 

Conclusion

In this article, you connected Python to SQL Server. You wrote your SQL Server data to Google Spreadsheet and also connected Google Spreadsheet to Data Studio. You also learnt how to export your SQL Server data to CSV and then move it to Data Studio. However, this process is very lengthy.

While SQL server to Google Data Studio Integration is insightful, it is a hectic task to Set Up the proper environment. To make things easier, Hevo comes into the picture. Hevo Data is a No-code Data Pipeline and has awesome 150+ pre-built Integrations that you can choose from.

visit our website to explore hevo

Hevo can help you Integrate your data from numerous sources like Datadogs and load them into a destination to Analyze real-time data with a BI tool such as Tableau. It will make your life easier and data migration hassle-free. It is user-friendly, reliable, and secure.

SIGN UP for a 14-day free trial and see the difference!

Share your experience of learning about SQL server to Google Data Studio in the comments section below.