Microsoft Fabric for Businesses in Fresno, California: From Spreadsheets to a Unified Data Backbone

Scenario: A Fresno Operations Team Drowning in Silos

Picture a mid‑size Fresno business with:

• A line‑of‑business app backed by SQL Server on‑prem in a local data center.
• Sensor data from cold‑storage facilities landing as CSVs in Azure Storage.
• Finance tracking budgets and forecasts in Excel on SharePoint.
• Power BI reports built by different departments, each with its own dataset.

The pain points:

• Numbers don’t match: operations throughput, financials, and capacity reports disagree.
• IT is stuck maintaining SSIS jobs and manual CSV uploads.
• The team wants near‑real‑time dashboards, but refresh windows and gateway issues get in the way.

The goal: use Microsoft Fabric to create a single, governed lakehouse that feeds all analytics and reporting, while still working with the existing Fresno infrastructure (on‑prem SQL, Azure resources, and Microsoft 365).

We’ll walk this scenario through Fabric’s main components: OneLake, Lakehouse, Data Warehouse, Data Pipelines, and Power BI in Fabric.

Why Fabric Makes Sense for Fresno Businesses

Most Fresno organizations already sit in the Microsoft stack: Microsoft 365, Microsoft Entra ID (formerly Azure AD), some SQL Server, and Power BI. Fabric’s value in that environment is less about shiny new tools and more about consolidation.

Key advantages that matter locally:

• OneLake as a single logical data lake

  • Centralized storage with open formats (Delta tables) and a unified namespace.
  • One security and governance model across data products.

• Tight integration with existing tools

  • Power BI workspaces become Fabric workspaces when the tenant is enabled for Fabric.
  • Teams can keep using Power BI Desktop, Excel, and SQL while Fabric handles the data backbone.

• Capacity‑based pay‑as‑you‑go model

  • Businesses can start with a small Fabric capacity in a nearby Azure region (e.g., West US) and scale as usage grows.

For Fresno, the practical pattern is: keep your operational systems where they are; build analytics and reporting on top of Fabric so you can retire ad‑hoc integrations and one‑off datasets.

Step 1: Establish a Lakehouse in Fabric

The first move for our Fresno operations team is to create a central Lakehouse that will hold:

• Operational data from on‑prem SQL Server.
• Sensor data from Azure Storage.
• Reference and budgeting tables from Excel.

Lakehouse basics that matter

• A Lakehouse in Fabric stores data in OneLake using Delta tables by default when you create tables through Fabric experiences (such as Dataflows Gen2, Pipelines, or Spark).
• You can work with:
  • The files section (Parquet, CSV, JSON, etc.).
  • The tables section (Delta tables, queryable via SQL and Spark).
• Lakehouses expose both SQL analytics endpoints and Spark endpoints for analysis.

For the Fresno team, we’ll treat the Lakehouse as the single source for analytical data, even though the operational system still lives on‑prem.

Pattern: Organize the Lakehouse by domain

Create folders and tables that mirror business domains:

• /operations/ – orders, shipments, inventory.
• /facilities/ – sensor data, equipment status.
• /finance/ – budgets, forecasts, cost centers.

Within each domain:

• Land raw data in a raw area.
• Transform into curated tables in a curated area.

This simple structure keeps Fresno teams aligned across departments and makes governance easier.

Step 2: Ingest On‑Prem SQL Data with Fabric Data Pipelines

The operations system runs on SQL Server in a local Fresno data center. We want daily and near‑real‑time data in Fabric without rewriting the app.

Fabric Data Pipelines use Azure Data Factory technology under the hood. For SQL Server on‑prem, the key pieces are:

• A Self‑hosted Integration Runtime (SHIR) installed on‑prem or in a VM with network access to SQL Server.
• A linked service to the SQL Server instance.
• Copy activities that move data into the Lakehouse.

Steps to set up the pipeline

1. Install Self‑hosted Integration Runtime

   • Download and install SHIR on a Windows machine that can reach the SQL Server instance.
   • Register it to your Fabric tenant using the keys provided in the Fabric portal.

2. Create a linked service to SQL Server

   • In the Fabric Data Pipeline experience, define a new linked service of type SQL Server.
   • Use Windows or SQL authentication as appropriate.

3. Create a Lakehouse destination

   • In the same workspace, create a Lakehouse to hold operations data.

4. Build a Copy activity

   • Source: SQL Server table (e.g., dbo.Orders).
   • Sink: Lakehouse table or file path (e.g., operations/raw/orders).
   • Choose a Delta table sink if you want direct table access.

A typical pattern for the Fresno team:

• Nightly full loads for relatively small dimension tables.
• Incremental loads for large fact tables, using a date or identity column.

Important behavior details

• When copying into a Lakehouse table sink, Fabric creates or updates a Delta table. Schema mapping must be consistent across runs.
• Incremental patterns rely on filters in the source query (e.g., WHERE LastUpdated >= @Watermark). You maintain the watermark in a control table or pipeline parameter.

Step 3: Land Sensor Data from Azure Storage into OneLake

Cold‑storage facilities around Fresno often stream sensor data into Azure Blob or Azure Data Lake Storage as CSV or JSON.

Fabric Data Pipelines can copy this data into the Lakehouse without moving the operational ingestion.

Pattern: File‑based ingestion

1. Create a linked service to Azure Storage

   • Use a managed identity or service principal for authentication.

2. Configure a Copy activity

   • Source: Azure Blob container path (e.g., sensors/incoming/).
   • Sink: Lakehouse files (e.g., facilities/raw/sensors/).

3. Optionally convert to Delta

   • Use Dataflow Gen2 or Spark to convert raw CSV files to Delta tables in facilities/curated/.

This keeps the ingestion simple while giving analysts a consistent, performant format for queries.

Step 4: Bring Excel Finance Data into Fabric Without Breaking Finance

Finance in our Fresno team lives in Excel. Forcing them into a different tool usually fails. Fabric allows you to keep Excel as the authoring surface but move the data into OneLake.

Options that actually work

• Upload Excel files to OneLake and treat them as sources

  • Store files in a Lakehouse’s files area.
  • Use Dataflow Gen2 or Power Query in Fabric to read the tables or ranges and write out Delta tables.

• Use Power BI datasets in Fabric as the finance model

  • Finance maintains Excel tables.
  • Power Query in Power BI Desktop connects to those tables (via SharePoint/OneDrive).
  • Publish to a Fabric workspace; the dataset becomes a Fabric item.

For our scenario, we’ll move finance tables into curated Delta tables so they’re joinable with operations and facilities data.

Example: Power Query to read Excel from OneLake

Assume you’ve stored an Excel file finance_budget.xlsx in the Lakehouse files folder and exposed it via a URL accessible to Power Query.

let
    Source = Excel.Workbook(
        Web.Contents("https://<your-onelake-domain>/finance/raw/finance_budget.xlsx"),
        null,
        true
    ),
    BudgetTable = Source{[Item="Budget",Kind="Table"]}[Data],
    ChangedTypes = Table.TransformColumnTypes(
        BudgetTable,
        {
            {"CostCenter", type text},
            {"Period", type date},
            {"BudgetAmount", type number}
        }
    )
in
    ChangedTypes

This query:

• Reads the Budget table from the Excel workbook.
• Enforces appropriate data types.
• Produces a table you can write to a Lakehouse Delta table via Dataflow Gen2.

Note: In practice, you’ll use the actual OneLake URL provided by Fabric for the file; the pattern above shows the core steps.

Step 5: Build a Fabric Data Warehouse for SQL‑Friendly Consumers

Not every Fresno analyst wants Spark or Delta. Many still prefer T‑SQL and tools like SQL Server Management Studio or Azure Data Studio.

Fabric’s Data Warehouse gives you:

• A relational model on top of OneLake.
• T‑SQL endpoints for querying.
• Integration with Power BI and other tools.

In our scenario, we:

• Use the Lakehouse for storage and transformation.
• Use the Data Warehouse as a curated, relational layer for reporting.

Pattern: Lakehouse → Warehouse

1. Create a Data Warehouse in the same workspace.
2. Use Dataflows Gen2 or Pipelines to load curated Delta tables into warehouse tables.
3. Expose warehouse tables to Power BI and external tools via the SQL endpoint.

This gives Fresno teams a stable schema with clear semantics:

• dw.FactOperations – operations fact table.
• dw.FactSensorReadings – sensor fact table.
• dw.DimCostCenter – finance dimension.

You can control schema evolution in the warehouse, while the Lakehouse holds the raw and curated data.

Step 6: Connect Power BI to Fabric Without Rebuilding Everything

Many Fresno teams already have Power BI reports. The concern is: will moving to Fabric break them?

Key behaviors:

• Existing Power BI workspaces can be used as Fabric workspaces once Fabric is enabled for the tenant; you don’t need to recreate them.
• Datasets in those workspaces become Fabric items, but continue to function.
• You can create new datasets that connect directly to Lakehouse tables, Data Warehouse tables, or existing Power BI models.

Recommended pattern in this scenario

1. Leave existing critical reports alone initially.

   • Keep their refresh patterns, data sources, and gateways.

2. Build new or refactored reports on top of Fabric data.

   • Use Direct Lake mode where appropriate for Lakehouse tables.
   • Use Import mode for complex models or when you need advanced transformations.

3. Gradually retire legacy datasets.

   • Once a report is validated against the Fabric warehouse or Lakehouse, decommission the old dataset.

Example: DAX measure on Fabric warehouse table

Assume FactOperations is a warehouse table with columns Quantity, UnitCost, and Date.

TotalCost := SUMX(
    FactOperations,
    FactOperations[Quantity] * FactOperations[UnitCost]
)
CurrentMonthCost := CALCULATE(
[TotalCost],
DATESMTD('Date'[Date])
)

These measures behave the same whether the source is a Fabric Data Warehouse or a traditional Power BI dataset, as long as the model is set up correctly.

Governance and Local Constraints: What Fresno Teams Need to Watch

Fabric’s unified environment is powerful, but it can create problems if governance is ignored.

Identity and access

• Fabric uses Microsoft Entra ID (formerly Azure AD) for identity.
• Workspace access controls manage who can:
  • Create and modify Lakehouses and Warehouses.
  • Run pipelines and Dataflows.
  • Publish and edit reports.

For a Fresno business:

• Map existing Entra ID security groups (operations, facilities, finance) to Fabric workspace roles.
• Use least privilege: analysts get build rights, engineers get contributor rights, admins stay small.

Data locality and compliance

• Fabric data is stored in an Azure region associated with your tenant’s configuration.
• If Fresno operations have data residency requirements, coordinate with IT to choose appropriate regions and understand cross‑region behavior.

Performance and capacity

• Fabric performance depends on the capacity SKU and load.
• Heavy Spark workloads, concurrent refreshes, and interactive BI can compete for resources.

Practical tips:

• Start with a modest capacity and monitor usage.
• Separate noisy dev/test workloads into their own workspaces or capacities.
• Use scheduling to avoid heavy pipeline runs during peak report usage.

Before/After: What Changes for the Fresno Operations Team

Before Fabric:

• Operations data pulled nightly into Excel via ODBC.
• Sensor CSVs manually uploaded into Power BI.
• Finance numbers pasted into Power BI datasets.
• IT maintains SSIS packages and a fragile gateway.

After Fabric:

• Lakehouse holds unified operations, facilities, and finance data in OneLake.
• Pipelines bring SQL and sensor data in automatically.
• Finance continues in Excel, but data lands in curated Delta tables.
• Data Warehouse exposes a clean schema to Power BI and SQL tools.
• Reports across departments share the same definitions and measures.

The Fresno team doesn’t have to become data engineers overnight. They get a consistent, governed platform that fits into their existing Microsoft stack.

Practical Takeaway for Fresno Businesses

If you’re in Fresno and already rely on SQL Server, Excel, and Power BI, the most effective way to adopt Microsoft Fabric is to pick one cross‑department process—like operations and finance reconciliation—build a Lakehouse and Warehouse for that process, and route new reports through Fabric while leaving legacy ones untouched until they’re replaced.