Connect SQL Server to Google Data Studio: Easy Steps

• February 7th, 2022

SQL Server to Google Data Studio

Introduction

Knowing how your SQL Server data affects your business functions can help you grow and also understand your customers better. When you analyze such data, you can make sound decisions for your business. You can connect your SQL Server to Google Data Studio. With Data Studio, you can turn your SQL Server data into reports and dashboards without coding. This way, you unlock the power in your data for smarter business decisions. In this article, you will learn how to connect SQL Server to Google Data Studio. 

You will look at the following aspects:

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. 

  1. To create a new report, just click the “Blank Report” button. 
  1. 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. 
  1. 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. 
Simplify Data Analysis with Hevo’s No-code Data Pipeline

Hevo Data, a No-code Data Pipeline helps to load data from any data source such as Whatsapp, Databases, SaaS applications, Cloud Storage, SDKs, and Streaming Services and simplifies the ETL process. It supports 150+ data sources (including 40+ free data sources) like Asana and is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination. Hevo not only loads the data onto the desired Data Warehouse/destination but also enriches the data and transforms it into an analysis-ready form without having to write a single line of code.

GET STARTED WITH HEVO FOR FREE

Its completely automated pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.

Check out why Hevo is the Best:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
SIGN UP HERE FOR A 14-DAY FREE TRIAL

Connecting SQL Server to Google Data Studio

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. 

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.

No-Code Data Pipeline for SQL Server