Forum Discussion
Helper 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.
2 Replies
- 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.
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.
