Kết nối SQL Server với Google Sheet

In late September 2018, Google announced that Data Studio had moved out of beta and into to general availability.  Millions of people have used Data Studio since the beta launch in 2016, and there's an even bigger group of people who would use Data Studio, if only they could connect to their SQL Server databases with it.

Update February 2022

Google has released a Native connector to Microsoft SQL Server. Benefits and limitations of it are included below and this article has been updated accordingly.

Connecting to your SQL Server data within Data Studio

Data that exists in your database, files, or other repositories are referred to as data sets within Data Studio. Data sets get into Data Studio through connectors that simply relay the data between the original repository and the report.  

Unlike some other BI tools, the data is not actually imported into Data Studio, though it may be cached for some time.  Instead the data lives in its original repository, and anytime a change is made to a report, such as filtering on date range or pulling in a new dimension, a query is made through the connector to the original data set.  The system holding the original data set performs the query and delivers the resulting table back through the connector to the data source so that the results can be viewed in a Data Studio report. 

To get your SQL Server data into Data Studio, you need to get it from SQL Server and into a data set that can be accessed with a Data Studio Connector. 

Google Data Studio Native Connectors

There are over a dozen connectors to native Google data sets like BigQuery, YouTube and Attribution 360, as well as additional connectors provided by Google including MySQL, Postgres, and Google Cloud SQL. Unfortunately, Google does not provide a native connector for Microsoft SQL Server (which is likely why you’re here!).  

Google now offers a native connector to Microsoft SQL Server, however it has some significant limitations, as do the other Native Connectors (and some of the other Partner Connectors).  They are fine to use in development, but can be risky to use at scale or for commercial purposes (like an agency reporting for their client, or for running dashboards within a company). 

With Native Connectors, interacting with a dashboard will trigger API calls or database connections.  These are are inherently slower, have their own limitations, and come at a cost to the database owner. While there is a caching system in place, it is for static data serving a report.  All bets are off on the first refresh after the cache period and for any interaction on the dashboards. 

For commercial use, Google developed a Partner Connector program and encourages developers to build connectors that scale and serve a wider range of data sources. 

Google Data Studio Partner Connectors

The Community Connectors program enables direct connections from Data Studio to any ‘internet accessible data source’.  Including Analytics Canvas, you can now connect to over 800 different data sources through over 490 connectors!  We were proud to be among the first 10 partners to launch a community connector, enabling Data Studio users to access any data source that Canvas can access, including SQL Server, through the Analytics Canvas Partner Connector.

With the Partner Connector, Canvas now provides 6 ways of getting your data from Microsoft SQL Server into Data Studio with minimal effort.  All you need is a PC and access to a Microsoft SQL Server database. 

Connecting to your SQL Server Data in Data Studio using Analytics Canvas

Analytics Canvas allows anyone, beginners who have never used SQL and those with advanced SQL skills, to connect to data in SQL Server and bring data onto their canvas for processing and analysis. 

If you’re new to ETL tools or Analytics Canvas, check out the video below to see how easy it is to connect to data and publish a data set using Analytics Canvas Desktop.  Connect in a similar way with Analytics Canvas Online (shown above). 

Connecting to your SQL Server data within Analytics Canvas

1. To follow along from this point onwards, you'll need Analytics Canvas. There’s a 30 day free trial and all you need is an email to sign-up.
2. Once installed and activated, go to New Source, select MS SQL, and enter the connection details when prompted. 

1. You can then search for and select tables and views (equivalent to Select *),

Or write a SQL query to retrieve a specific dataset:

Once the data has landed on your canvas, you can either replicate it directly to a data set accessible by Data Studio, or you can blend it with data from other sources and prepare it before publishing.  More on that in the next section.

Having landed data from your SQL Server database, you now have 6 ways to get your data into Data Studio with Analytics Canvas.

1. Analytics Canvas Cloud data set.
2. Google Sheets
3. Google BigQuery 
4. MySQL  
5. PostgreSQL 
6. CSV files 

Connecting to Data Studio through an Analytics Canvas data set and the Partner Connector:

1. Analytics Canvas Cloud Data Set

When using the Analytics Canvas partner connector, your data is published to our secure cloud service where we make your data sets available for your Data Studio reports.

1. Add a Data Studio export block by selecting the Block Library, then drag and drop the Data Studio block on to the Canvas.

1. Connect the database table to the Data Studio Export block by clicking on the output of the database block and dragging to the input of the Data Studio block.
 
2. Click on the centre of the Data Studio block and click the “New Data Set” button in the lower control window.

1. If you haven’t already done so, you will be prompted to sign-up or log-in to the Analytics Canvas Cloud service. This ensures your data is safe, secure, and only accessed by those who have your permission.
 
2. Name your dataset and select a user or users to share it with, then click “OK”

1. Publish the data set by running the Canvas (click the green play button in the top left corner). When complete, the data set will be available in the Analytics Canvas cloud repository.

1. In Data Studio, select “Create New Data Source”, then under Partner Connectors, click “Explore Connectors” and select Analytics Canvas.
2. Authorize the Analytics Canvas connector, then find your data sets under “Data sets you are subscribed to” and click Connect.

The data sets are now available within Data Studio for reporting and analysis!  Learn more about our connector and how it works by visiting this page. 

With Analytics Canvas Version 3.5 or greater, you can now load up to 2 Billion rows per dataset!  Click here to learn more.

Using Native Connectors to connect Data Studio to data from SQL Server

2. Google Sheets

1. Create the Sheets you want to read from in Google Sheets
2. Follow Data Studio’s instructions on how to prepare your Google Sheets data for reporting in Data Studio.
3. If required, use Canvas’ data transformation tools to prepare your data for reporting
4. Add one or more Google Sheets Export blocks to your canvas and connect them to the data sets you want to publish.
5. Publish the data set to Google Sheets by running the Canvas
6. Connect to the Sheet in Data Studio.

This approach works well if your data set contains less than 2 million cells.

3. Google BigQuery

1. Add a BigQuery Upload block to the canvas and connect it to the data set you want to publish
2. In the lower control window, click “Select table”
3. Select the data set and table you want to write into (or define a new table)
4. Publish the data set to BigQuery by running the Canvas
5. Connect to the BigQuery table in Data Studio

** BigQuery is a paid product and you will incur BigQuery usage costs when accessing BigQuery through Data Studio. **

4. and 5. PostgreSQL, SQL Server, and MySQL (or Google Cloud SQL)

1. Add a database export block, click on the centre of it and click “Select Database Table” in the
2. Select MySQL for either a MySQL or Google Cloud SQL database, and PostgreSQL for Postgres.
3. Connect the database export block to the data set(s) you with to publish.
4. Publish the data sets to your database by running the Canvas.
5. Connect to your MySQL, Google Cloud SQL, or PostgreSQL database in Data Studio

** note that if you’re using MySQL or PostgreSQL, you will likely have to whitelist Google’s bank of IP addresses for Data Studio to connect to your database. Instructions outlined in the links above. **

6. CSV Files

1. Add a Text File Export block to your Canvas and connect it to your output
2. Click on the centre of the block, then the “Specify File” button in the lower control window to give your file a name.
3. Ensure that the Text File Type is set to “Delimited” and that Select Delimiter is set to “Comma”
4. Follow Data Studio's instructions on ensuring that your file is ready for upload.
5. If required, use Canvas’ data transformation tools to prepare your data for reporting
6. Publish the file(s) by running the Canvas
7. Use the File Upload connector in Data Studio to upload your CSV files.

Why use Canvas to get SQL Server data into a repository with a Native Connector? 

Replication vs Data Integration and Data Preparation

A lot of the partner connectors replicate data in Data Studio, or allow you to simply choose columns to include in your report.  If your data set is sufficiently small, or sufficiently clean, that approach might be just fine.  However if your raw data requires some prep before publishing, (often called 'data integration' or 'data processing'), Canvas is the way to go.   

Consider the following workflow: 

The workflow shown above is an example of a clearly defined reporting process.  Anyone, from the most junior analyst to the most senior manager, can follow this routine by reading the block labels and clicking on each of the blocks to see how the data is processed for the purpose of generating this report.  What's more, this routine can be run on-demand with the click of a mouse, or on a schedule at any frequency.

Multiple data sets into a single data source

Blending in Data Studio allows you to create charts based on more than 1 data source, however there are limitations.  For example, you can only perform a left-outer join, you can only blend up to 5 data sources in a chart, and blending is only available in reports. Canvas has no such limitations and has the tools to perform the data transformations you need.

Flexibility and Ease of Use

Need to update your report to add a new data set or modify the business logic?  Perhaps you’ve outgrown Sheets or File connectors and need to publish to a database.  Canvas lets you make these updates in minutes without the need for developers or specialized resources.  

Automation

Since there is no live connection between any of the sources listed above and your SQL Server database tables, the workflows you create must be refreshed periodically.  You can open Canvas and manually run your report to update your data sources, or you can use one of our two automation options, Server Automation or Cloud Automation to run your workflows on a schedule.  

Try connecting your SQL Server data to Google Data Studio

Connect to your SQL Server Database with Analytics Canvas today.  There's a risk free, 30 day full featured trial that includes access to the Analytics Canvas cloud and the Data Studio community connector.  Enroll in your trial or book some time on our calendar to go through your data challenge and setup your proof of concept together with one of us.  

Happy Analyzing!

Next Steps

Whenever you’re ready… here are 3 ways we can help you with your Data Studio reporting challenges:

1. Speed up Data Studio reports 
2. Resolve connection and access issues between your DB and Data Studio 
3. Clean and prepare data sets prior to reporting in Data Studio

Ready for the next step?

• Start a 30 day risk-free trial. No credit card or sales call required. 
• Schedule a demo for you and your team.
• Contact us to discuss plans and pricing or activate your subscription 

Wondering if Canvas is right for you? Check out the related articles to learn more about our Data Studio Partner connector.