business intelligence

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. 

Imagine effortlessly asking your business intelligence dashboard any question and receiving instant, insightful answers. This is not a futuristic concept but a reality unfolding through the power of Large Language Models (LLMs).

Descriptive analytics is at the core of this transformation, turning raw data into comprehensible narratives. When combined with the advanced capabilities of LLMs, Business Intelligence (BI) dashboards evolve from static displays of numbers into dynamic tools that drive strategic decision-making. 

LLMs are changing the way we interact with data. These advanced AI models excel in natural language processing (NLP) and understanding, making them invaluable for enhancing descriptive analytics in Business Intelligence (BI) dashboards.

 

 

In this blog, we will explore the power of LLMs in enhancing descriptive analytics and its impact of business intelligence dashboards.

Understanding Descriptive Analytics

Descriptive analytics is the most basic and common type of analytics that focuses on describing, summarizing, and interpreting historical data.

Companies use descriptive analytics to summarize and highlight patterns in current and historical data, enabling them to make sense of vast amounts of raw data to answer the question, “What happened?” through data aggregation and data visualization techniques.

The Evolution of Dashboards: From Static to LLM

Initially, the dashboards served as simplified visual aids, offering a basic overview of key metrics amidst cumbersome and text-heavy reports.

However, as businesses began to demand real-time insights and more nuanced data analysis, the static nature of these dashboards became a limiting factor forcing them to evolve into dynamic, interactive tools. The dashboards transformed into Self-service BI tools with drag-drop functionalities and increased focus on interactive user-friendly visualization.

This is not it, with the realization of increasing data, Business Intelligence (BI) dashboards shifted to cloud-based mobile platforms, facilitating integration to various data sources, and allowing remote collaboration. Finally, the Business Intelligence (BI) dashboard integration with LLMs has unlocked the wonderful potential of analytics.

 

Explore the Top 5 Marketing Analytics Tools for Success

 

Role of Descriptive Analytics in Business Intelligence Dashboards and its Limitations

Despite of these shifts, the analysis of dashboards before LLMs remained limited in its ability to provide contextual insights and advanced data interpretations, offering a retrospective view of business performance without predictive or prescriptive capabilities. 

The following are the basic capabilities of descriptive analytics:

Defining Visualization

Descriptive analytics explains visualizations like charts, graphs, and tables, helping users quickly grasp key insights. However, this requires manually describing the analyzed insights derived from SQL queries, requiring analytics expertise and knowledge of SQL. 

Trend Analysis

By identifying patterns over time, descriptive analytics helps businesses understand historical performance and predict future trends, making it critical for strategic planning and decision-making.

However, traditional analysis of Business Intelligence (BI) dashboards may struggle to identify intricate patterns within vast datasets, providing inaccurate results that can critically impact business decisions. 

 

Learn to deploy and host predictive models

 

Reporting

Reports developed through descriptive analytics summarize business performance. These reports are essential for documenting and communicating insights across the organization.

However, extracting insights from dashboards and presenting them in an understandable format can take time and is prone to human error, particularly when dealing with large volumes of data.

 

 

LLMs: A Game-Changer for Business Intelligence Dashboards

Advanced Query Handling 

Imagine you would want to know “What were the top-selling products last quarter?” Conventionally, data analysts would write an SQL query, or create a report in a Business Intelligence (BI) tool to find the answer. Wouldn’t it be easier to ask those questions in natural language?  

LLMs enable users to interact with dashboards using natural language queries. This innovation acts as a bridge between natural language and complex SQL queries, enabling users to engage in a dialogue, ask follow-up questions, and delve deeper into specific aspects of the data.

Improved Visualization Descriptions

Advanced Business Intelligence (BI) tools integrated with LLMs offer natural language interaction and automatic summarization of key findings. They can automatically generate narrative summaries, identify trends, and answer questions for complex data sets, offering a comprehensive view of business operations and trends without any hustle and minimal effort.

 

Another interesting read: Fun with Data Visualizations

 

Predictive Insights

With the integration of a domain-specific Large Language Model (LLM), dashboard analysis can be expanded to offer predictive insights enabling organizations to leverage data-driven decision-making, optimize outcomes, and gain a competitive edge.

Dashboards supported by Large Language Mode (LLMs) utilize historical data and statistical methods to forecast future events. Hence, descriptive analytics goes beyond “what happened” to “what happens next.”

Prescriptive Insights

Beyond prediction, descriptive analytics powered by LLMs can also offer prescriptive recommendations, moving from “what happens next” to “what to do next.” By considering numerous factors, preferences, and constraints, LLMs can recommend optimal actions to achieve desired outcomes. 

 

Read more about Data Visualization

 

Example – Power BI

The Copilot integration in Power BI offers advanced Business Intelligence (BI) capabilities, allowing you to ask Copilot for summaries, insights, and questions about visuals in natural language. Power BI has truly paved the way for unparalleled data discovery from uncovering insights to highlighting key metrics with the power of Generative AI.

Here is how you can get started using Power BI with Copilot integration;

Step 1

Open Power BI. Create workspace (To use Copilot, you need to select a workspace that uses a Power BI Premium per capacity, or a paid Microsoft Fabric capacity).

Step 2

Upload your business data from various sources. You may need to clean and transform your data as well to gain better insights. For example, a sample ‘sales data for hotels and resorts’ is used here.

 

 

Step 3

Use Copilot to unleash the potential insights of your data. 

Start by creating reports in the Power BI service/Desktop. Copilot allows the creation of insightful reports for descriptive analytics by just using the requirements that you can provide in natural language.  

For example: Here a report is created by using the following prompt:

 

 

Copilot has created a report for the customer profile that includes the requested charts and slicers and is also fully interactive, providing options to conveniently adjust the outputs as needed. 

 

 

Not only this, but you can also ask analysis questions about the reports as explained below.

 

 

The copilot now responds by adding a new page to the report. It explains the ‘main drivers for repeat customer visits’ by using advanced analysis capabilities to find key influencers for variables in the data. As a result, it can be seen that the ‘Purchased Spa’ service has the biggest influence on customer returns followed ‘Rented Sports Equipment’ service.

 

 

Moreover, you can ask to include, exclude, or summarize any visuals or pages in the generated reports. Other than generating reports, you can even refer to your existing dashboard to question or summarize the insights or to quickly create a narrative for any part of the report using Copilot. 

Below you can see how the Copilot has generated a fully dynamic narrative summary for the report, highlighting the useful insights from data along with proper citation from where within the report the data was taken.

 

 

Microsoft Copilot simplifies Data Analysis Expressions (DAX) formulas by generating and editing these complex formulas. In Power BI, you can easily navigate to the ‘Quick Measure’ button in the calculations section of the Home tab. (if you do not see ‘suggestions with Copilot,’ then you may enable it from settings.

Otherwise, you may need to get it enabled by your Power BI Administrator).

Quick measures are predefined measures, eliminating the need for creating your own DAX syntax. It’s generated automatically according to the input you provide in Natural Language via the dialog box. They execute a series of DAX commands in the background and display the outcomes for utilization in your report.

 

 

In the below example, it can be seen that the copilot gives suggestion for a quick measure based on the data, generating the DAX formula as well. If you find the suggested measure satisfactory, you can simply click the “Add” button to seamlessly incorporate it into your model.

 

 

There can be several other things that you can do with copilot with clear and understandable prompts to questions about your data and generate more insightful reports for your BI dashboards.  

Hence, we can say that Power BI with Copilot has proven to be the transformative force in the landscape of data analytics, reshaping how businesses leverage their data’s potential.

 

 

Embracing the LLM-led Era in Business Intelligence

Descriptive analytics is fundamental to Business Intelligence (BI) dashboards, providing essential insights through data aggregation, visualization, trend analysis, and reporting. 

The integration of Large Language Models enhances these capabilities by enabling advanced query handling, improving visualization descriptions, and reporting, and offering predictive and prescriptive insights.

This new LLM-led era in Business Intelligence (BI) is transforming the dynamic landscape of data analytics, offering a glimpse into a future where data-driven insights empower organizations to make informed decisions and gain a competitive edge.

Data Science Dojo is offering Apache Superset for FREE on Azure Marketplace packaged with pre-installed SQL lab and interactive visualizations to get started.  

  

What is Business Intelligence?  

  

Business Intelligence (BI) depends on the idea of utilizing information to perform activities. It expects to give business pioneers noteworthy bits of knowledge through data handling and analytics. For instance, a business breaks down the KPIs (Key Performance Indicators) to distinguish its benefits and shortcomings. Hence, the decision-makers can conclude in which department the organization can work to increase efficiency.  

Recently two elements in BI have resulted in sensational enhancements in metrics like speed and proficiency. The two elements include:  

 

• Automation  
• Data Visualization  

 

Apache Superset widely focuses on the latter model which has changed the course of business insights.  

 

But what were the challenges faced by analysts before there were popular exploratory tools like Superset?  

  

Pro Tip: Join our 6-months instructor-led Data Science Bootcamp to master data science.  

  

Challenges of Data Analysts

  

Scalability, framework compatibility, and absence of business-explicit customization were a few challenges faced by data analysts. Apart from that exploring petabytes of data and visualizing it would cause the system to collapse or hang at times.  

In these circumstances, a tool having the ability to query data as per business needs and envision it in various diagrams and plots was required. Additionally, a system scalable and elastic enough to handle and explore large volumes of data would be an ideal solution.  

 

Data Analytics with Superset  

  

Apache Superset is an open-source tool that equips you with a web-based environment for interactive data analytics, visualization, and exploration. It provides a vast collection of different types of vibrant and interactive visualizations, charts, and tables. It can customize the layouts and the dynamic dashboard elements along with quick filtering, making it flexible and user-friendly. Apache Superset is extremely beneficial for businesses and researchers who want to identify key trends and patterns from raw data to aid in the decision-making process.  

 

 

  

It is a powerhouse of SQL as it not only allows connection to several databases but also provides an in-browser SQL editor by the name SQL Lab  

 

Key attributes  

  

• Superset delivers an interactive UI that enriches the plots, charts, and other diagrams. You can customize your dashboard and canvas as per requirement. The hover feature and side-by-side layout make it coherent  
• An open-source easy-to-use tool with a no-code environment. Drag and drop and one-click alterations make it more user-friendly  

• Contains a powerful built-in SQL editor to query data from any database quickly  
• The choice to select from various databases like Druid, Hive, MySQL, SparkSQL, etc., and the ability to connect additional databases makes Superset flexible and adaptable  
• In-built functionality to create alerts and notifications by setting specific conditions at a particular schedule  
• Superset provides a section about managing different users and their roles and permissions. It also has a tab for logging the ongoing events  

 

What does Data Science Dojo have for you  

  

Superset instance packaged by Data Science Dojo serves as a web-accessible no-code environment with miscellaneous analysis capabilities without the burden of installation. It has many samples of chart and dataset projects to get started. In our service users can customize dashboards and canvas as per business needs.

It comes with drag-and-drop feasibility which makes it user-friendly and easy to use. Users can create different visualizations to detect key trends in any volume of data.  

 

What is included in this offer:  

 

• A VM configured with a web-accessible Superset application  
• Many sample charts and datasets to get started  
• In-browser optimized SQL editor called SQL Lab  
• User access and roles manager  
• Alert and report feature  
• Feasibility of drag and drop  
• In-build functionality of event logging  

 

Our instance supports the following major databases:  

 

• Druid  
• Hive  
• SparkSQL  
• MySQL  
• PostgreSQL  
• Presto  
• Oracle  
• SQLite  
• Trino  
• Apart from these any data engine that has Python DB-API driver and a SQL Alchemy dialect can be connected  

 

Conclusion  

  

Efficient resource requirement for exploring and visualizing large volumes of data was one of the areas of concern when working on traditional desktop environments. The other area of concern includes the ad-hoc SQL querying of data from different database connections. With our Superset instance, both concerns are put to rest.

When coupled with Microsoft cloud services and processing speed, it outperforms its traditional counterparts since data-intensive computations aren’t performed locally but in the cloud. It has a lightweight semantic layer and is designed as a cloud-native architecture.  

At Data Science Dojo, we deliver data science education, consulting, and technical services to increase the power of data. We are therefore adding a free Superset instance dedicated specifically to Data Science & Analytics on Azure Marketplace. Now hurry up and avail this offer by Data Science Dojo, your ideal companion in your journey to learn data science!  

  

Click on the button below to head over to the Azure Marketplace and deploy Apache Superset for FREE by clicking on “Get it now”.  

 

 

Note: You’ll have to sign up to Azure, for free, if you do not have an existing account.