Procurement analytics from SharePoint, Excel, JSON and on-prem SQL: your Fabric approach?

Hi kiara-mehra35​,

I would handle this source by source rather than force everything through one ingestion pattern.

For SharePoint / OneDrive files, I would first ask whether you actually need to copy them.

Microsoft now supports OneDrive and SharePoint shortcuts, so if the files can stay in place and you mainly want them available through OneLake, a shortcut is a clean option.

If you need cleansing, combining, reshaping or business-rule transformations, then I would use Dataflow Gen2 or a pipeline depending on the connector. One nuance is that Microsoft currently documents the SharePoint Folder connector for Dataflow Gen2, while SharePoint Online File support in pipelines is still Preview.

For the on-prem SQL source, I would not automatically default to a scheduled pipeline anymore.

Microsoft now supports SQL Server Mirroring in Fabric, including on-premises SQL Server through an on-premises or virtual network gateway.

If the source is mainly regular tables and you want continuous/near-real-time replication into OneLake, I would evaluate Mirroring first.

If you depend on unsupported objects such as views, or you need substantial transformations during ingestion, then a pipeline/copy pattern is still the better fit.

For the overall architecture, I would probably use:

SharePoint / OneDrive / JSON / SQL
→ OneLake / Lakehouse as the landing and curated layer
→ Warehouse only where you want a relational serving layer for facts, dimensions and SQL-heavy reporting
→ semantic model / Direct Lake for Power BI

I would not create a Warehouse just because Fabric has one. If your curated Delta tables already satisfy the reporting model well, Direct Lake can sit directly on top of them.

For procurement/finance logic such as supplier savings and raw-material cost trends, I would keep currency conversion governed upstream.

For example:

local amount
FX rate used
reporting-currency amount
transaction date / FX policy

That logic belongs in Silver/Gold so every report uses the same finance definition.

Then, if users want to switch display currency interactively, add a light DAX layer on top rather than putting the core conversion logic into measures.

So my rule of thumb would be:

• files already in SharePoint/OneDrive and mostly read-as-is → shortcut
• files needing shaping/combining → Dataflow Gen2 / supported pipeline connector
• on-prem SQL with regular tables and CDC need → Mirroring
• complex SQL ingestion / unsupported objects → pipeline
• structured BI serving → Warehouse if useful
• open Delta + Direct Lake works well → no need to add Warehouse just for the sake of it
  
  

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.