6 Ways to get data from SQL Server into Google Data Studio

Ameet Wadhwani Google Data Studio Leave a Comment

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.

Connecting to your 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. 

Diagram showing how data sets get through connectors and into data sources to be read by Data Studio reports

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. 

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!).  

Community Connectors

For all other data sets, Google launched the Community Connectors program to enable direct connections from Data Studio to any ‘internet accessible data source’.  Including Canvas, you can now connect to over 500 different data sources through over 120 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 Community Connector.

Diagram showing how data sets get through connectors and into data sources to be read by Data Studio reports

With the Community 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 our quick start guide

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. 
selecting a MS SQL query to import into Analytics Canvas
  1. You can then search for and select tables and views (equivalent to Select *),
Image

Or write a SQL query to retrieve a specific dataset:

Image

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.

6 Ways of connecting data from SQL Server to Data Studio

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 Community Connector:

1. Analytics Canvas Cloud Data Set

When using the Analytics Canvas community 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.
Analytics Canvas Data Studio Export Block
  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.  
  3. Click on the centre of the Data Studio block and click the “New Data Set” button in the lower control window.
Analytics Canvas Data Studio Export Block
  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.  
  3. Name your dataset and select a user or users to share it with, then click “OK”
Analytics Canvas Data Studio Export Block
  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.
Analytics Canvas Data Studio Export Block
  1. In Data Studio, select “Create New Data Source”, then under Community Connectors, click “Explore Connectors” and select Analytics Canvas.
  2.  
  3. Authorize the Analytics Canvas connector, then find your data sets under “Data sets you are subscribed to” and click Connect.
Analytics Canvas Data Studio Export Block

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.

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 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 community 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: 

sample workflow preparing data from SQL server and publishing to Data Studio

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!

 

Leave a Reply

Your email address will not be published. Required fields are marked *