Power My Analytics' Sovereign SQL Warehouse Solution
Power My Analytics offers a Sovereign SQL Warehouse solution with the Custom plan. This article will help you learn about its uses and how to get started. The Sovereign SQL Warehouse gives you complete ownership of your data by storing it in your own self-hosted MySQL database — on infrastructure you control.
Currently, we only support MySQL. PostgreSQL and Microsoft SQL Server may become available in the future.
Sovereign SQL Warehouse vs. EU Google Cloud Data Center
If your interest in the Sovereign SQL Warehouse is primarily about storing data within the European Union for GDPR compliance, it's worth knowing that a Custom plan is not required for EU data residency alone. All PMA subscribers on any plan can now choose to have their data warehoused in PMA's EU Data Center (Google Cloud Europe-West3, Frankfurt, Germany) during onboarding — no additional setup required.
The Sovereign SQL Warehouse is a distinct and more advanced option for organizations that require full data ownership and self-hosted control, beyond what PMA's managed Google Cloud warehouse provides. Here's how the two options compare:
| Feature | EU Data Center (Google Cloud) | Sovereign SQL Warehouse |
| Data stored within the EU | Yes (Frankfurt, Germany) | Yes, when database is hosted in an EU country |
| Available on all plans | Yes | Custom plan only |
| Data ownership | PMA-managed | You own your data and database |
| Choose your own hosting infrastructure | No — hosted by PMA in Google Cloud | Yes — any MySQL-compatible hosting |
| Take your data if you leave PMA | No | Yes |
| Use in BI tools (Tableau, Looker, Power BI) | Via PMA destinations only | Yes — directly, as a MySQL data source |
| Setup required | Selected during onboarding — no additional setup | Requires creating and connecting your own MySQL database |
For more information on selecting your Google Cloud data center region during onboarding, see Choosing a US or EU Data Warehouse Region.
Uses and Benefits of the Sovereign SQL Warehouse
The Sovereign SQL Warehouse is the right choice when your organization needs more than EU data residency alone — specifically, when you need full ownership, portability, and direct control of your data. Key benefits include:
- Own Your Data: Your data is stored in your own MySQL database and can be used in any of PMA's data destinations (Looker Studio, Google Sheets, Excel, BigQuery, etc.), as well as directly in BI tools like Tableau, Looker, and Power BI that accept MySQL as a data source.
- Choose Your Own Infrastructure: Host your database on any MySQL-compatible infrastructure — including within a specific EU country or region of your choosing to meet data residency or sovereignty requirements. The hub owner is responsible for setting up the database in the appropriate location.
- Take Your Data With You: If you choose to stop using Power My Analytics, your data warehouse will be populated up to the day you stop your service. You can then use another service or method to continue appending data to your warehouse.
- Use Your Data in BI Tools: Tableau, Looker, Power BI, and other BI tools all accept MySQL as a direct data source, giving you maximum flexibility in how you work with your data.
Sovereign SQL Warehouse vs. BigQuery Exports
A hosted MySQL sovereign warehouse is a more optimal solution for data warehousing than BigQuery for several reasons:
- The entire schema can be automatically exported into the Sovereign SQL data warehouse with no coding or report setup. For BigQuery, a report must first be created in the Data Builder with the desired fields before it can be exported to BigQuery, so it does take more effort to set up BigQuery exports.
- Our Hosted MySQL data warehouse solution works with our data destinations (Looker Studio, Google Sheets, Excel, Big Query, etc.) seamlessly. You can still use our Looker Studio connectors, spreadsheets add-on, and export to BigQuery from your MySQL data warehouse, just as if you are using our normal plans. The benefit is you own your data and you can take your data with you at any time.
Hosted MySQL vs. MySQL Exports
- Just like PMA's own data warehouse, our hosted MySQL data warehouse solution will not store the values of live fields.
- If you need to store live field values in MySQL, our MySQL export feature will retrieve the live fields in a Data Builder report and export them to your MySQL database at the scheduled time. For more information on setting up MySQL exports, please see How to Export to MySQL.
Getting Started
Create a MySQL database
Recommended specs:
- 3.75 GB or higher memory
- SSD
- 100 GB in storage capacity is usually enough. Agencies or Custom subscriptions with many accounts may require more.
- If in the European Union: Select a European region for your Google Cloud MySQL instance in order to meet GDPR requirements.
Example of recommended settings:
Under Connections, check Public IP and add PMA's IP ranges as authorized networks:
- 35.188.118.242/32
- 35.209.185.69/32
Connect your database to your Hub
- Select Warehouse in the left sidebar, then click the Warehouse Settings button in the upper right.
- Under Storage Options, toggle the selection from Google Cloud (default) to SQL Warehouse.
- Click the SQL Connection Settings button. Enter your server's hostname or IP address, port, username, and password, then click Connect Database.
Create a report
Once you have connected a data source, you must create a report using this data source and select the desired sub-accounts within the data source, before the data source's data will be warehoused in your database.
The sub-accounts selected when the report is created will have all the data from their entire schema warehoused in your MySQL database - it is not necessary to select specific fields when creating the report, as all fields available for the connector will be warehoused.
Use Looker Studio, our Sheets Add-on, or Data Builder to create a report:
Select all sub-accounts within the connected data source that you wish to have in your sovereign SQL warehouse database. For example, in Data Builder with Facebook Ads:
Backfill
The MySQL solution is unique and only automatically backfills 7 days of data for most connectors. Find your connected data source in the Sources section and run a manual backfill to retrieve up to 2 years of data.
Migration from PMA Data Warehouse to Self-Hosted SQL Warehouse
If you've been using PMA's data warehouse and want to migrate your existing data to your own self-hosted SQL warehouse, we offer a migration service to ensure a smooth transition.
Warehouse Migration Service Overview
Power My Analytics provides a migration service for customers who want to move their data from PMA's warehouse to their self-hosted SQL warehouse.
- Requirements: Before requesting migration, you must have already set up your MySQL warehouse and confirmed that new data is flowing correctly.
- Contact Support: To initiate the migration process, you need to submit a support ticket.
Migration Details and Process
- Data Transfer: All data currently stored in the PMA warehouse will be transferred to your MySQL warehouse.
- Query Redirection: After migration, queries from connected reporting services (such as Looker Studio) will hit your MySQL database directly.
- PMA Warehouse Status After Migration: Following the data migration to your MySQL database, your data will no longer reside in the PMA data warehouse.
To request migration of your data from PMA's warehouse to your self-hosted SQL warehouse, please submit a support ticket. This is the official channel for initiating and managing your migration request.
Reverting from Self-Hosted SQL Warehouse to PMA Data Warehouse
Your data will begin to be stored in the PMA warehouse after switching back from the self-hosted SQL solution to PMA's warehouse.
If you choose to revert from using the sovereign SQL warehouse solution to PMA's data warehouse, the data stored in your warehouse will not be imported into PMA's warehouse.
Troubleshooting
Error: Could not set global database flag
When connecting to your SQL data warehouse, you may receive the following error:
Could not set global database flag. Please elevate this user's permissions OR set 'max_allowed_packet = 536870912'
If this error appears, go to your Google Cloud console and choose the project containing your Cloud SQL instance. Open your instance and click Edit, then scroll to Flags.
For Choose a flag, select max_allowed_packet. Under Value, enter 536870912. Then click Done.
Related Articles
Custom Plan Guide
Our Custom plan is a premium offering specially tailored for clients who require advanced tools, flexible data options, and personalized support. This article will explain the features and benefits of the Custom plan. Exports Exports to BigQuery, SQL ...Power My Analytics Data Connector User Guide
The Power My Analytics (PMA) Data Connector is your gateway from Data Builder datasets to your reporting destinations. Design your datasets in Data Builder, then report your data in Looker Studio or Sheets or export to destinations like BigQuery and ...How to Use the SQL Connector
Power My Analytics now offers an SQL connector in addition to our Hosted SQL solution. This article will help you get started with using our SQL connector and set up a MSSQL or MySQL query. Connect to your server in the Hub Select Sources in the left ...How to Export to MySQL
Our MySQL export feature enables flexible access to your data in the form of tables in a MySQL database hosted on the server of your choice. Once you've prepared a report in Data Builder, you're ready to export your data to your MySQL server. With ...Get Started with Power My Analytics API
Enterprise clients can use the Power My Analytics API for easy, flexible direct access to their report data. Learn how to use this powerful tool to design your custom solutions. Who Can Use the API? Access to the API is available only to customers on ...