Forum Discussion
Advocate IIProcurement analytics from SharePoint, Excel, JSON and on-prem SQL: your Fabric approach?
Hi all,
While working on a procurement analytics project at DataToBiz, we ran into a source and reporting problem that I’d like to compare with how people here would handle it in Fabric.
The data lived in several places: SharePoint files, Excel and JSON flat files, OneDrive files and a legacy on-prem SQL database. In the original build, we landed everything in Azure Blob storage, converted it to a structured format and fed it into a data warehousing layer, with Power BI on top. The dashboards tracked order management, vendor management and free stock vs. out-of-stock items, along with purchase order counts broken down by plant, month and year. They also covered supplier costs and savings across countries, and the change in raw material costs over time. To keep report loading fast, we moved the workspaces to Premium capacity.
That setup predates Fabric, so I’d like to hear how you’d do it today.
For the file-based sources, would you use OneLake shortcuts, Dataflows Gen2 or Pipelines? With SharePoint and OneDrive files that change often, what has worked best for you?
For the on-prem SQL, would you go through the gateway with a pipeline, or is there a better option now? Where does mirroring fit for a legacy database like that?
Would you land everything in a Lakehouse and serve it through a Warehouse, or keep it all in one? We had a structured warehousing layer, and I’m not sure whether the split is worth it now.
For the reporting side, would you use Direct Lake or Import mode? Is capacity sizing less of a worry than it was with Premium workspaces, or does it still take effort to get fast report loads?
And for things like supplier savings and raw material cost trends, how do you handle currency and country differences in the model? Do you convert in the pipeline or in DAX?
Would love to hear how you’d approach it.
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 BII 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 policyThat 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.
4 Replies
- ShivekMaharaj
Community Champion
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 BII 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 policyThat 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.
jubinsoniContinued Contributor
Hi kiara-mehra35,
The ingestion and layering points are well covered above, so I'll take your currency question, because it's the one that tends to cause rework later. My rule of thumb: convert to one reporting currency in the pipeline, keep the local amount too, and only use DAX for "view in another currency" on demand.
Silver: FX rates table (Date, FromCcy, ToCcy, Rate) from your treasury/ERP source Gold fact (PO lines / supplier spend): AmountLocal, LocalCcy, AmountUSD (converted at transaction date), FxRateUsed -> savings and raw-material cost trends compare in USD without DAX at query time -> FxRateUsed makes every number auditablefrom pyspark.sql import functions as F fx = spark.read.table("silver.fx_daily").filter("to_ccy = 'USD'") po = spark.read.table("silver.po_lines") gold = (po.join(fx, (po.order_date == fx.rate_date) & (po.currency == fx.from_ccy), "left") .withColumn("fx_rate_used", F.when(po.currency == "USD", F.lit(1.0)).otherwise(fx.rate)) .withColumn("amount_usd", F.col("amount_local") * F.col("fx_rate_used")) .drop("rate", "rate_date", "from_ccy", "to_ccy")) gold.write.mode("overwrite").saveAsTable("gold.fact_po_lines")Then a light DAX layer only for the cases where users want a different currency (Direct Lake friendly, since it's a simple multiply at the reporting grain):
Spend (Selected Ccy) = VAR ccy = SELECTEDVALUE ( DimCurrency[Code], "USD" ) RETURN IF ( ccy = "USD", SUM ( FactPO[amount_usd] ), SUMX ( VALUES ( DimDate[Month] ), CALCULATE ( SUM ( FactPO[amount_usd] ) ) * [Avg USD->Ccy Rate] ) )Two decisions to settle with finance up front, because they change the numbers: transaction-date vs month-average rates for savings, and whether "raw material cost change" should be shown at constant currency (same FX rate for both periods) so FX swings don't look like price changes.
So my overall approach would be:
FX table in Silver -> convert to reporting currency in Gold (+ keep local + rate used) -> DAX only for optional display currency -> agree FX policy (transaction vs average, constant-currency view) with financeAI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
kiara-mehra35Thanks for breaking down the currency side, especially the point about constant-currency comparisons. Keeping the local amount and FX rate alongside the reporting-currency amount makes sense for auditability.
For raw material cost trends, would you generally show both the actual reported change and the constant-currency change in the same Power BI report, or keep them as separate views?
- tayloramy
Super User
For files, if they are sitting in a place that Fabric has shortcut support for, shortcuts make for an easy bronze layer. I'd avoid dataflows here as they are a transformation engine, not a copy engine. If shortcuts don't work for you, look at a pipeline with a copy activity doing a binary copy to OneLake.
For on prem SQL, the gateway is your only option. If it is less than 1000 tables, and your source supports mirroring, you can use mirroring, however that also depends on the gateway.
I keep each layer in separate items. Some clients want warehouses for gold, some want lakehouses. Keeping bronze, silver, and gold in different items gives the flexibility to add on whatever layer the client wants, and allows for better organization of bronze and silver data.
I've never had issues with DirectLake. All my reporting is in DirectLake mode.
For trends, I would calculate them in PySpark while compuing the gold data.
