azure synapse

In many enterprise scenarios, SharePoint-hosted Excel files serve as the bridge between raw data and business operations. But keeping them up to date, especially when your data lives in Azure Synapse, can be surprisingly difficult due to limitations in native connectors. 

In this guide, you’ll learn a step-by-step method to build a no-code/low-code Azure Synapse to SharePoint Excel automation using Power BI and Power Automate. This method ensures your data is always up-to-date, with zero manual refreshes.

The Business Problem 

Recently, I faced a real-world challenge:

A client needed a solution that automatically updates an Excel workbook on SharePoint with data from an Azure Synapse pipeline, as the Excel file was being used as a data source for Smartsheet reports.

The critical requirement? 

End-to-end automation with no manual intervention ever. 

That meant the Excel workbook needed to be continuously and reliably updated with data from an Azure Synapse view, without anyone having to open or refresh the file manually. 

Key Challenges 

While the problem sounded simple, setting up direct integration between Azure Synapse and Excel on SharePoint revealed several roadblocks:

• No SharePoint Excel connector in Azure Synapse.

  Synapse lacks a native way to push or refresh data directly into an Excel file on SharePoint. 

• SharePoint Excel doesn’t support direct refresh from SQL Server or Synapse.

  You can’t natively connect an Excel file on SharePoint to a SQL-based backend and have it auto-refresh. 

• Even if connected to a Power BI semantic model, Excel doesn’t auto-refresh.

  SharePoint Excel can connect to a Power BI dataset (semantic model), but it won’t pull the latest data unless manually refreshed, a blocker for our automation goal. 

To understand the data layer better, check out this guide on SQL pools in Azure Synapse.

The Low-Code Solution 

To build a robust Azure Synapse to SharePoint Excel automation, I developed a no-code/low-code automation using a combination of: 

• Azure Synapse Analytics (as the data source) 

• Power BI Semantic Model (as the bridge) 

• Power Automate (to refresh Excel connections on SharePoint) 

This approach keeps the SharePoint Excel workbook continuously in sync with Synapse, enabling downstream use in Smartsheet. 

Step-by-Step Implementation 

Here’s how you can replicate this approach:

Create a Power BI Semantic Model

• In Power BI Desktop, create a dataset that pulls data from your Azure Synapse or SQL Server view/table. 

• This model will act as the source for the Excel file. 

Publish the Model to Power BI Service

• Publish the semantic model to your workspace in the Power BI Service. 

Set Up Power BI Semantic Model Refresh

• Configure a Power BI Service to refresh the semantic model on a schedule (e.g., hourly/daily). 
• This ensures the model always reflects the latest data from Synapse. 

Create the Excel File in SharePoint

• In the target SharePoint location, create or upload a new Excel workbook. 
• Inside the workbook, go to Data > Data from Power BI and connect to your semantic model. 

Add an Office Script to Refresh Connections

• In Excel Online, go to Auomate Tab and create a new Office Script with the following code: 
• Name the script something like Refresh All. 

Automate It with Power Automate

• Create a new Power Automate flow.
• Add a Recurrence trigger to define how often it should run. 
• Add the “Run Script” action. 
• Specify the SharePoint file location and the Refresh All script you created. 

Coordinating Refresh Timings and Triggers 

Timing and synchronization are critical to avoid partial or stale data. Here’s how each component should be aligned: 

Azure Synapse: Scheduled View/ETL Triggers 

• Use Azure Synapse Pipelines with scheduled triggers to refresh your views or underlying datasets. 
• If you’re using serverless SQL views, ensure the logic behind them is updated and ready before the Power BI gateway refresh runs. 

Power BI Gateway: Semantic Model Refresh 

• Schedule your Power BI gateway refresh to run after your Synapse views have completed refreshing. 
• This ensures that the semantic model reflects the latest data before Excel attempts to pull it. 

Power Automate: Excel Workbook Refresh 

• Schedule the Power Automate recurrence trigger to run after the Power BI semantic model refresh completes. 
• Important: Always provide a safe buffer time (e.g., 5–10 minutes) between Power BI refresh and the Excel refresh via Power Automate to avoid syncing stale or partially updated data. 

Example Timing Setup: 

• Azure Synapse pipeline runs at 2:00 AM 
• Power BI semantic model refreshes at 2:15 AM 
• Power Automate script runs at 2:45 AM 

This sequencing ensures data consistency across all layers. 

What Happens End to End 

1. Synapse updates or refreshes the data in the SQL views. 
2. Power BI semantic model (connected to Azure Synapse) is refreshed via scheduled refresh. 
3. SharePoint Excel workbook, connected to that semantic model, is refreshed by a scheduled Power Automate flow running an Office Script. 
4. Smartsheet, connected to the Excel workbook, always sees the most up-to-date data, fully automated. 

Example Use Case: Automating Sales Reporting for Smartsheet Dashboards 

Scenario:

A sales operations team needs daily reports in Smartsheet, which relies on data pulled from an Excel workbook stored in SharePoint. This Excel file should reflect the latest sales transaction data from Azure Synapse Analytics. 

Solution Implementation: 

• Sales data is stored in Synapse view, updated nightly via a Synapse pipeline. 
• A Power BI semantic model is created on top of this view and refreshed every morning. 
• The Excel workbook in SharePoint connects to the Power BI model. 
• A Power Automate flow runs an Office Script daily to refresh all data connections in Excel. 
• The updated Excel file feeds into Smartsheet automatically, keeping dashboards current, no manual work required. 

This use case demonstrates how the automation flow ensures accurate, up-to-date reporting without any manual intervention, even though Synapse cannot natively write to SharePoint Excel. 

Conclusion 

If you’re trying to output Azure Synapse data into an Excel file stored in SharePoint, and need that file to stay in sync automatically, this is your workaround. While there’s no direct connector from Synapse to SharePoint Excel, Power BI + Power Automate fill the gap with a reliable and reusable pattern. 

Azure Synapse provides a unified platform to ingest, explore, prepare, transform, manage, and serve data for BI (Business Intelligence) and machine learning needs.

 

Introduction to SQL pools

Dedicated SQL pools offer fast and reliable data import and analysis, allowing businesses to access accurate insights while optimizing performance and reducing costs. DWUs (Data Warehouse Units) can customize resources and optimize performance and costs. In this blog, we will explore how to optimize performance and reduce costs when using dedicated SQL pools in Azure Synapse Analytics. 

 

Loading data

When loading data, it is best to use PolyBase for substantial amounts of data or when speed is a priority. PolyBase is a feature that allows you to query and load data from different data sources, like Azure Blob Storage. This makes it optimal for handling large amounts of data or when speed is a priority.

Additionally, using a heap table for temporary data can improve loading speed. A heap table is a temporary table that only exists for a session and is useful when loading data to stage it before running more transformations. 

 

Clustered column store index

When loading data to a clustered column store table, creating a clustered column store index is essential for query performance. A clustered column store index is created on a table with a clustered column store architecture.  It is a highly compressed and in-memory storage format that stores each column of data separately, resulting in faster query processing and superior query performance. This helps to improve query performance by allowing the database engine to retrieve the required data pages more quickly. 

 

Managing compute costs

Managing computer costs is also important when working with dedicated SQL pools. One way to do this is by pausing and scaling the dedicated SQL pool. This allows you to only pay for the resources you need and can help you avoid unnecessary expenses. Additionally, using the appropriate resource class can improve query performance.

SQL pools use resource groups to allocate memory to queries. Initially, all users are assigned to the small resource class, which grants 100 MB of memory per distribution. However, more significant memory allocations will benefit certain queries, like large joins or loads to clustered column store tables. 

 

Maintaining statistics and performance tuning

To ensure optimal performance, it is essential to keep statistics updated when using dedicated SQL pools. The quality of the query plans generated by the optimizer depends on the accuracy of the statistics, so it is necessary to make sure statistics on columns used in queries are current. Performance tuning is another crucial aspect of working with dedicated SQL pools.

One way to improve query performance is using materialized views, ordered clustered column store index, and result set caching. Additionally, it is a good practice to group INSERT statements into batches to optimize large amounts of data loading. 

 

Hash distributes large tables and partitioning data

When using dedicated SQL pools, it is recommended to hash-distribute large tables instead of relying on the default Round Robin distribution. It is also important to be mindful when partitioning data, as too many partitions can impact performance negatively. Partitioning can be beneficial for managing data through partition switching or optimizing scans, but it should be done carefully. 

 

Conclusion

In conclusion, working with dedicated SQL pools in Azure Synapse Analytics requires a comprehensive understanding of best practices for loading data, managing compute costs, utilizing PolyBase, maintaining statistics, performance tuning, hash distributing large tables, and partitioning data.

By following these best practices, you can achieve optimal performance and reduce costs with your dedicated SQL pools in Azure Synapse Analytics. It is important to remember that Azure Synapse Analytics is a complex platform. These best practices will help you in your data processing and analytics journey.