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

Hi kiara-mehra35​ 

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.