Power My Analytics' Data Warehouse Solution

Power My Analytics' Data Warehouse Solution

Power My Analytics offers a Hosted MySQL solution with your Enterprise plan. This article will help you learn about its uses and how to get started. With our Hosted MySQL solution, you can achieve GDPR compliance by setting up your data warehouse within the EU.

Alert
Currently, we only support MySQL. PostgreSQL and Microsoft SQL Server may become available in the future.

Uses and Benefits

  1. Own Your Data:  The data will be stored in your MySQL database and can be used in any of our data destinations (Looker Studio, Google Sheets, Excel, BigQuery, etc.)
  2. Have Your Data Stored In Your Country:  For those EU-based clients that require their data must be housed in the EU or their country, this solution satisfies GDPR requirements. The Hub owner must set up the database in an EU country in order for the data warehouse to be GDPR-compliant.
  3. Use Your Data In BI Tools like Tableau, Looker, and Power BI:  These BI tools all accept MySQL as a data source.
  4. 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 append future data into your data warehouse

Hosted MySQL vs. BigQuery Exports

Hosted MySQL is a more optimal solution for data warehousing than BigQuery for several reasons:
  1. The entire schema can be automatically exported into the Hosted MySQL data warehouse with no coding or report setup.  For BigQuery, a report must first be created in the Data Explorer before it can be exported to BigQuery, so it does take more effort to set up BigQuery exports.
  2. 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, Google Sheets add-on and export to BigQuery from your MySQL data warehouse just like 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

  1. Just like PMA's own data warehouse, our hosted MySQL data warehouse solution will not store the values of live fields.
  2. If you need to store live field values in MySQL, our MySQL export feature will retrieve the live fields in a Data Explorer 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:
  1. 3.75 GB or higher memory
  2. SSD
  3. 100 GB in storage capacity is usually enough. Agencies or Enterprise subscriptions with many accounts may require more.
  4. 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:
  1. 35.188.118.242/32
  2. 35.209.185.69/32

Connect your database to your Hub

Select SQL Warehouse under Exports in the left navigation pane then click SQL Settings to begin setup.



Provide the IP address or domain name of your server, the username, and the password to connect.



Create a report

Use Looker Studio, our Sheets Add-on or Data Explorer to create a report and begin the flow of data through your MySQL database. 



Select all accounts that you wish to have in your database. Only accounts that have reports will show data in your MySQL database. 


Backfill

The MySQL solution is unique and only automatically backfills 7 days of data for most connectors. Run a manual backfill to retrieve up to 2 years of data.



Migration from PMA Data Warehouse to Self-Hosted SQL Warehouse

Info
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.
  1. Requirements: Before requesting migration, you must have already set up your MySQL warehouse and confirmed that new data is flowing correctly.
  2. Contact Support: To initiate the migration process, you need to submit a support ticket.

Migration Details and Process

  1. Data Transfer: All data currently stored in the PMA warehouse will be transferred to your MySQL warehouse.
  2. Query Redirection: After migration, queries from connected reporting services (such as Looker Studio) will hit your MySQL database directly.
  3. PMA Warehouse Status After Migration: Following the data migration to your MySQL database, your data will no longer reside in the PMA data warehouse.
Alert
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.

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.


    Still having issues?
    Send us a ticket and we will get back to you.
    Submit a ticket

      • Related Articles

      • 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 Explorer, you're ready to export your data to your MySQL server. With ...

      • Enterprise Plan Guide

        Our Enterprise plan is a unique offering specially tailored for clients who require more tools and access. This article will explain the benefits of the Enterprise Plan. Exports Exports to SQL Warehouse, BigQuery, FTP, and our API are available only ...

      • 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 Azure SQL Database

        Exporting your marketing data to Azure SQL allows you to integrate it with various applications and run complex data queries. This guide will walk you through the process of setting up and managing your Azure SQL exports using Power My Analytics. An ...

      • Error: Missing BigQuery Scopes

        When setting up BigQuery exports in Power My Analytics, you may encounter an error message stating "Missing BigQuery Scopes." This guide will help you understand the cause of this error and provide step-by-step instructions to resolve it. Error ...