Data Studio makes great looking reports and dashboards, and at a price point of free for everyone, Google is obviously making it easy to afford. But many organisations find a number of key challenges and "connection does not exist" errors when connecting Google Data Studio to a database. Also, if you're interested, you can read more about all the ways you can get data from SQL to Google Data Studio.
3 serious problems when connecting Google Data Studio to a database
Even if you are able to permit the bank of Google IPs and reach the SQL Server, MySQL, or Postgres databases behind your firewall, you're likely to encounter row limits, connection problems, performance and scalability issues, or security challenges while using the native Data Studio database, API, and file connectors.
Row Limits on Data Studio Database Connectors
There are limits to the Microsoft SQL Server, MySQL, and PostGres connectors in Google Data Studio, and the same limits apply to Google Cloud SQL for MySQL. Each database connector is limited such that you can only query a maximum of 150K rows using those connectors. With the Analytics Canvas Data Studio Partner Data Connector, the limit is 2 billion rows. More on that below.
Data Studio can Overload Your Database and Slow Down your Reports
If you are connecting Google Data Studio directly to your database using native Google Data Studio connectors for Microsoft SQL Server, MySQL or PostGres, you might have noticed it generates a heavy load with a very large number of queries.
Depending on the size and complexity of your Data Studio dashboards, and the performance of your database server, this can be deadly for your DB. To understand why there are so many queries, you need to look at how Data Studio works- it generates one or more queries for every element on a dashboard when its refreshed.
If your dashboard has a filter (like a date range, or one or more filter selectors) every time a user changes the selection, all those queries hit your database all at once. If you have a dashboard with 15 things on it, that might be two dozen queries! If you have multiple users online, slicing and dicing their data, clicking on filters, changing date ranges, you will see a steady stream of queries from Data Studio pounding your database.
Pretty soon Data Studio might be consuming all your database capacity, resulting in errors connecting to SQL Server, MySQL, or PostGres. You may receive the "Connection does not exist" error or have other troubles connecting through.
There have been reports that Data Studio can open a large number of database connections and not release them, potentially even crashing your database completely. Since that database is likely being used by other applications/users in your environment, this is obviously a disaster.
By allowing Data Studio to query your database directly, you give every report viewer and author the same power. Imagine if there are hundreds of users- and even just a few decide to do use the reports at the same time as that critical database job you need to run...
Connectivity and Security Concerns - Don’t Expose your Database!
Chances are you don’t need every table and every row of data in your database available in Data Studio. In fact, its likely the data you do want to report on is in databases that also have highly sensitive data.
But to connect to it you have to provide Data Studio report developers with your database login credentials, and you have to open access to your database through your firewall to Data Studio.
It is likely you will need to create new credentials, and manage their access- adding more administrative work.
Very often, your users will hire third party consultants to build dashboards and reports- by connecting directly from Data Studio to your database, you are providing database credentials to potentially critical systems to these temporary resources.
How to speed up Google Data Studio dashboards
How can you speed up your dashboards, ensure you have complete control over database loading and manage security concerns?
The solution is to provide a buffer between Google Data Studio and the raw data from databases, files, and APIs.
With the Analytics Canvas partner data connector, you load your data into Analytics Canvas Cloud Hosted data sets which can be up to 2 Billion rows each. When in Data Studio, create a data source from your Analytics Canvas data set and continue to setup your chart or table.
After an initial load, you can incrementally load the rest at a frequency and schedule that best suits your database operations.
When Data Studio requires data, it queries it from Analytics Canvas directly, meaning no impact on your database. Whether you have 1 user or thousands of users consuming your Data Studio reports, Canvas will efficiently manage all of the queries and deliver data quickly.
Manage your network security exposure and keep control of data flows
With Analytics Canvas Online, a user with read-only access to specific report tables can ingest tables into the platform and create Data Studio data sets for consumption by multiple users in Data Studio.
Whereas Analytics Canvas Server can be installed on premise, which means you don’t have to grant access to your databases outside your firewall. You provide specific connectivity between the Analytics Canvas server installation and the Analytics Canvas cloud. This is a specific, limited connection- and it’s a process that pushes only the data you want Data Studio to see on the schedule that you want.
This means that rather than wondering when Data Studio report viewers or authors are going to bombard your database, you can schedule regular data refreshes that refresh just once- and you can pick the schedule, so do it in period when your internal databases are most available.
Wrapping Up
If you need to get data from your database into Data Studio, try Analytics Canvas. Your dashboards will load quickly, you will not put unnecessary pressure on your database, and you can eliminate the hassles of setting up separate credentials or maintaining a separate DB to house your reporting tables.
You can enjoy all the benefits of Data Studio and have access to a best-in-class Data Studio Partner Data Connector from as little as $299/mo.
Sign-up to start your free trial, contact us with your questions or book a time to connect with one of us for a demo or discussion about your Google Analytics reporting needs.