Skip to content
  • There are no suggestions because the search field is empty.

Microsoft Azure SQL Integration: Sync SQL View Data into Cascade Overview

Keep Cascade Metrics Updated from Azure SQL

Overview

The Microsoft Azure SQL integration lets you automatically pull KPI / metric data from a customer-owned Azure SQL database (typically from a SQL View) and update the mapped Measures/Fields in Cascade on a schedule.

This integration is designed to be:

  • Read-only (no writes back to your database)
  • Stable (use a curated view with a fixed schema)
  • Secure (least-privilege access + encrypted connections)

What This Integration Supports

Typical use cases:

  • Update Cascade Measures from aggregate KPI values (Revenue, OEE, FPY, On-time delivery, etc.)
  • Update measures from time-series values (daily/weekly/monthly performance)
  • Pull from a single curated View or a small set of curated Views

Recommended Data Approach

Use a Curated View (Strongly Recommended)

A curated view reduces integration breakage and keeps logic controlled on your side.

Your View should:

  • Return a stable schema (don’t rename columns without notice)
  • Return deterministic results (same query → same output)
  • Keep row count reasonable (avoid returning millions of rows)

Authentication 

SQL Authentication (Username + Password)

This method uses a dedicated SQL login created for the integration. No interactive user login is required once credentials are provided.

Use when

  • You want the simplest setup
  • Your security team allows SQL logins for integrations

Azure SQL restricts inbound traffic by default. You will need to allow the integration runtime to reach your database.

Allowlist IP address(es) for the integration runtime in Azure SQL firewall rules. The IP addresses to allowlist can be provided upon request.

Customer Setup Steps

Step 1 — Create a Read-Only Integration User

Create a dedicated SQL user for the integration (do not reuse a personal account).

Step 2 — Grant Least-Privilege Permissions

Grant the integration user read-only access to the curated View(s) only.

Recommended

  • SELECT permissions on specific view(s)
  • Avoid broad permissions like db_owner or SELECT on all tables

Step 3 — Confirm Network Access

Configure your Azure SQL firewall so the integration runtime can connect.

You will confirm IP allowlisting is configured

Step 4 — Provide the View Name and Expected Output

Provide the view name(s) and confirm the expected columns and data types.

View Output Schema (What We Recommend)

To map cleanly into Cascade, your View should return (at minimum):

Minimum fields

  • metric_key (string): stable identifier like revenue_total, oee, cases_shipped
  • metric_value (number): the value to sync into Cascade

Strongly recommended (for time-series)

  • metric_date (date or datetime): the date the value applies to

Optional (only if you need segmented measures)

  • dimension_1, dimension_2 (strings): e.g., plant, region, department

Scheduling & Incremental Logic

Most Azure SQL integrations run on a fixed schedule.

If you want incremental behavior (recommended for time-series or large datasets):

  • Implement filtering inside the view (e.g., last 30/90 days)
  • Or expose only the latest row per metric (if you only need “current”)

Cascade will execute the query exactly as defined. If the view returns extra history, it will be pulled unless filtered.

What You Provide to Cascade

When you’re ready, please provide:

A) Connection Details
  • Azure SQL server name (example: your-server.database.windows.net)
  • Database name
B) Credentials (SQL Auth)
  • SQL username
  • SQL password
C) Data Scope
  • View name(s) to query
  • Expected output schema (column names + meanings)
D) Mapping Rules
  • Which metric_key updates which Cascade Measure/Field
  • Any transformation rules (rounding, unit conversion, aggregation logic)
E) Sync Frequency
  • Desired schedule (hourly/daily/weekly)

Troubleshooting Tips

Authentication errors

  • Confirm username/password are correct
  • Confirm the SQL login is enabled and not locked/expired
  • Confirm the user has SELECT access to the view(s)

Network / firewall issues

  • Confirm Azure SQL firewall rules allow the integration runtime’s IP(s)
  • Confirm your organization isn’t blocking outbound SQL traffic

“Permission denied” / “Cannot access object”

  • Confirm permissions were granted to the view (not just underlying tables)
  • Confirm schema ownership / grants are applied correctly

Data mismatch / mapping failures

  • Confirm the view output schema hasn’t changed (column rename/removal)
  • Confirm data types are consistent (especially for dates and decimals)