How to Export to Azure SQL Database

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 Enterprise plan is required to use Azure SQL exports.

How We Store Data in Azure SQL Exports

Creating a table

Our Azure SQL export feature will create a table with the same name as the Data Explorer report you are exporting, with all spaces replaced by underscores (_). The table has columns corresponding to the fields in the Data Explorer report being exported.

Appending and updating rows

Unlike BigQuery exports, exports to Azure SQL are not partitioned by date. Instead, new data from the export is simply appended to the table, while any changed data will be updated in the affected rows.

Adding columns

When you edit the report in Data Explorer to add another field, this field will be added to the table in Azure SQL as a new column the next time the export runs. This column will be populated with the field's values going forward. Historical rows from before the new column was added will have the new field's value set to null.

Dropping columns

When you edit the report in Data Explorer to remove an existing field, this field's corresponding column will be dropped from the table in Azure SQL the next time the export runs.
Be careful: when dropping a column, historical data for the dropped fields will be deleted.

Prerequisites

Before you begin:

  • Make sure you have an Enterprise plan with Power My Analytics
  • Create the Data Explorer report(s) you want to export
  • Obtain your Azure SQL credentials:
    • Hostname
    • Port (default: 1433)
    • Username
    • Password
    • Database name
  • Whitelist the following IPs in your Azure SQL instance:
    • 35.188.118.242
    • 35.209.185.69

Step 1: Configure Your Data Explorer Report for Initial Backfill

  1. In your PMA hub, go to Reports > Data Explorer and find your report. Click the pencil icon to Edit Report.



  2. Go to Date Range and set it to the entire range of data you want to export (up to 2 years for most PMA connectors).
  3. To backfill from a specific start date up to yesterday, choose Custom start to date and enter a start date.
  4. Click Set Range, then Save to save the report.


Step 2: Connect to Azure SQL

  1. Go to Exports > SQL in the left sidebar.
  2. Click + Create Export.



  3. Click the blue plus button under Destination to add a new destination.



  4. In the Add Account dialog:
    • TySelect Azure SQL as the Type.
    • Enter the host, port, username, and password.
    • Provide a nickname for this destination (e.g., "Azure SQL instance - [your organization]").
    • Click Connect.



  5. Choose the server you just added under Destination.
  6. Under Database, enter your database name. Click Next.


Step 3: Choose Your Report

  1. Under Report or Report Group, select either Single report or Group of reports.



  2. Choose the specific report or report group from the dropdown. Click Next.



  3. For now, select Monthly under Schedule and choose any day and hour (we'll adjust this later). Click Add to create your export, then click Confirm.


Step 4: Run the Initial Backfill

  1. On the Exports to SQL page, find your new export. Click the play icon to run the export manually.



  2. The export will begin running, as indicated by the three dots icon. Wait for the three dots icon to change back to a play button, indicating the export has finished.



  3. Click the paper icon to view the logs and confirm the export was successful.


Step 5: Set Up Rolling Updates

Configure your Data Explorer report

  1. Go back to your report in Data Explorer. Click the pencil icon to Edit Report.
  2. Set the Date Range to a rolling window (e.g., last 30 days for e-commerce to account for returns).
  3. Click Set Range, then click Save.


Configure your Azure SQL export

  1. Return to Exports > SQL. Find your export and click the pencil icon to Edit Export.



  2. Under Schedule, choose your desired frequency: monthly, weekly, daily, or hourly. Select the appropriate day/time for the export to run.
  3. Click Update.


Important: Ensure your report's date range in Data Explorer is at least as long as the refresh period in the Azure SQL export in order to avoid missing data. For example, if your report's date range is one week but your export refreshes monthly, your export will fail to include data from before the last week of the month.
Your export will now run automatically at the scheduled time, keeping your Azure SQL database up-to-date with the latest data from your PMA reports.

Troubleshooting

If you encounter any issues with your exports:

  1. Check the export logs by clicking the paper icon next to your export.
  2. Verify that your Azure SQL credentials are correct and that the PMA IP addresses are whitelisted.
  3. Ensure your report's date range aligns with or exceeds your export schedule to prevent data gaps.


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

      • How to Use the Exports Add-On

        Power My Analytics offers the option to export data from your reports into destinations like BigQuery, MySQL, Azure SQL Database, and FTP. With our easy-to-use Exports Add-On, you can set up a report in PMA's Data Explorer and schedule regular data ...
      • 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 ...
      • Use the GA4 Exports Template in Data Explorer

        Power My Analytics offers pre-made report templates in our Data Explorer for use with your Google Analytics 4 data. By using our GA4 report templates, you can configure your reports in just a few minutes and prepare your data for export to ...
      • 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 Create an FTP Export

        FTP can be a valuable way for Enterprise users to export their Data Explorer reports. In this article, we will walk you through creating an FTP export, including selecting an FTP destination, scheduling data refreshes, and managing your exports to ...