Forum Discussion

kiara-mehra35's avatar
kiara-mehra35
Icon for Advocate II rankAdvocate II
3 days ago
Solved

Procurement 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 l...
data engineering
data pipeline
lakehouse
  • ShivekMaharaj's avatar
    ShivekMaharaj
    1 day ago

    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.

jubinsoni's avatar
jubinsoni
Icon for Continued Contributor rankContinued Contributor
2 days ago

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 auditable
from 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 finance

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

kiara-mehra35's avatar
kiara-mehra35
Icon for Advocate II rankAdvocate II
1 day ago

Thanks 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?