Forum Discussion
Data Environment Options Follow-Up
Hi icassiem ,
1. ERD / Architecture Example.
AWS (API / S3 / RDS / Mongo)
|
Fabric Notebook (Python ingest + transform, remove PII)
|
Lakehouse Tables (Silver Layer only)
|
SQL Views (Semantic Layer)
|
Power BI (Direct Lake / DirectQuery)Ex: fact_usage_metrics, dim_client, dim_product and dim_date
2. Motivation + Risks: Replace Azure Function ($150) and Potential DB / ETL tools with single Fabric capacity.
Note: Reusable data for reports, Centralized semantic layer and Shared across consulting + clients. Risks you must call out, Capacity limits (F2) as Small compute (2 CUs) Can slow if too many users and large joins
Solution: Pre-aggregate data and Small models
3. Medallion — Do you need it?
Try below architecture.
API --> Python (clean + aggregate + remove PII) --> Silver tables
|
SQL Views (Gold)Note: No Bronze (save cost), Silver = your storage and Gold = SQL views (no extra storage)
4. Orchestration: Best option is Fabric Pipelines, You can Schedule notebook runs, Retry handling and No extra cost (included in F2).
5. Query using SSMS: YES , this is supported, Please refer below steps.
Go to Lakehouse
Open SQL Endpoint
Copy connection string
Connect via SSMS (SQL Auth / AAD)Note: Fabric exposes T-SQL endpoint over Lakehouse
6. Dev Access + Power BI Connectivity:
Development: You can use Fabric web portal (primary), VS Code (optional for notebooks) and Git integration available.
Power BI: Direct Lake Connect to Lakehouse tables and No gateway needed.
Note: Gateway only needed if you connect to on-prem data.
7. Why NOT AWS RDS:
AWS RDS --> Operational DB not analytics, Power BI , RDS --> Security risk (prod exposure), Scaling --> Expensive + manual, Transformation -->No built-in ETL.
Note: Fabric contains Separate analytics layer, No impact on prod, Built-in ETL + BI and Lower total cost.
8. Copilot usage: Works in Power BI (narratives, DAX, Q&A) and Fabric notebooks (code assist)
Note: It is not full pipeline automation and Not replacing ETL. It is a“Enhancement layer, not core architecture”.
9. Python vs SQL roles: Python Ingest APIs, Transform, Aggregate and Remove PII. SQL contains Views, Joins and Semantic layer.
10. Ingestion Pattern (overwrite vs CDC): Simple approach (recommended for F2) Overwrite or append and Use timestamps. Better approach: Incremental loads (by date) and Partition tables.
Note: CDC Overkill for your case and Requires more complexity. You do NOT need Azure Data Factory. No extra infra needed Everything is SaaS and no hidden infra costs.
11. Backup Plan (Power BI only approach): Yes you can, but No central data store, Repeated API calls, Not reusable and Not scalable.
Note: It acceptable only for Very small MVP only. Power BI-only creates datasets and is not a reusable data platform.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
I don't think it's a big ask, motivating from 170 USD to 263 USD due to storage and doing internal reporing with F2 pluss the client essay narratives from the Ask to be distrubuted and summaries are good bonuses - so this is my first prize pitch
But wrost case could i do this in powerbi, i know i can schedule refresh python in the services but could powerquery loop per clinet api do the ingest and publish the extracts as datasets for me then to do the repoeting from the datasets almost like dtamrts as powerbi store?
"just thinking out loud"
Please Help
Hi icassiem ,
1. ERD / Architecture Example.
AWS (API / S3 / RDS / Mongo)
|
Fabric Notebook (Python ingest + transform, remove PII)
|
Lakehouse Tables (Silver Layer only)
|
SQL Views (Semantic Layer)
|
Power BI (Direct Lake / DirectQuery)
Ex: fact_usage_metrics, dim_client, dim_product and dim_date
2. Motivation + Risks: Replace Azure Function ($150) and Potential DB / ETL tools with single Fabric capacity.
Note: Reusable data for reports, Centralized semantic layer and Shared across consulting + clients. Risks you must call out, Capacity limits (F2) as Small compute (2 CUs) Can slow if too many users and large joins
Solution: Pre-aggregate data and Small models
3. Medallion — Do you need it?
Try below architecture.
API --> Python (clean + aggregate + remove PII) --> Silver tables
|
SQL Views (Gold)
Note: No Bronze (save cost), Silver = your storage and Gold = SQL views (no extra storage)
4. Orchestration: Best option is Fabric Pipelines, You can Schedule notebook runs, Retry handling and No extra cost (included in F2).
5. Query using SSMS: YES , this is supported, Please refer below steps.
Go to Lakehouse
Open SQL Endpoint
Copy connection string
Connect via SSMS (SQL Auth / AAD)
Note: Fabric exposes T-SQL endpoint over Lakehouse
6. Dev Access + Power BI Connectivity:
Development: You can use Fabric web portal (primary), VS Code (optional for notebooks) and Git integration available.
Power BI: Direct Lake Connect to Lakehouse tables and No gateway needed.
Note: Gateway only needed if you connect to on-prem data.
7. Why NOT AWS RDS:
AWS RDS --> Operational DB not analytics, Power BI , RDS --> Security risk (prod exposure), Scaling --> Expensive + manual, Transformation -->No built-in ETL.
Note: Fabric contains Separate analytics layer, No impact on prod, Built-in ETL + BI and Lower total cost.
8. Copilot usage: Works in Power BI (narratives, DAX, Q&A) and Fabric notebooks (code assist)
Note: It is not full pipeline automation and Not replacing ETL. It is a“Enhancement layer, not core architecture”.
9. Python vs SQL roles: Python Ingest APIs, Transform, Aggregate and Remove PII. SQL contains Views, Joins and Semantic layer.
10. Ingestion Pattern (overwrite vs CDC): Simple approach (recommended for F2) Overwrite or append and Use timestamps. Better approach: Incremental loads (by date) and Partition tables.
Note: CDC Overkill for your case and Requires more complexity. You do NOT need Azure Data Factory. No extra infra needed Everything is SaaS and no hidden infra costs.
11. Backup Plan (Power BI only approach): Yes you can, but No central data store, Repeated API calls, Not reusable and Not scalable.
Note: It acceptable only for Very small MVP only. Power BI-only creates datasets and is not a reusable data platform.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
v-dineshya Wow, thank you so so much
1. I am new to fabric/onelake, any examples, sources, links or materials on how to actually implement, setup and dev this?
2. any more on the motivation and way of working, just checking there's anything you missed please that can help me show how this design will provide long term sustaianabilty too please?
3. On the Fabirc is it similar to SQL/Azure where theres databse sections with sql agent for jobs and and ssis to importat pakacges or the pything is directly inserted in sceduler task?
4. What would the DB size limit be, can i do overtime? like you said best to overwrite but i have 10 client product data and no clue what size im looking at, is there a limitation like 1TB or do i get charged on top of the F2 $262 for storage or does it include everything with a size limit?5. Are there more F2 AI Benefits other thant the copilot summaries and client performance analyses etc for my enevironment and reporting please?
Hi icassiem ,
Please refer below practical implementation for your project.
1. Setup: Create Fabric workspace, Enable Fabric capacity (F2) and create a Lakehouse
2. Ingestion: Create notebook with below sample code.
import requests
import pandas as pdclients = ["client1", "client2"]
for c in clients:
data = requests.get(f"https://api/{c}").json()
df = pd.json_normalize(data)# remove PII
df = df.drop(columns=["email","name"], errors="ignore")# aggregate
df = df.groupby(["product","date"]).sum().reset_index()# write to lakehouse
df.to_parquet(f"/lakehouse/default/{c}_data")
3. SQL Layer: In Lakehouse SQL endpoint, create a view with below code.CREATE VIEW vw_client_metrics AS
SELECT
client,
product,
SUM(metric) AS total_metric
FROM silver_table
GROUP BY client, product;4. Orchestration: Create Pipeline, Add Notebook activity and Schedule weekly.
5. Power BI: Connect via Direct Lake and SQL endpoint.
Long-Term Sustainability:
1. Reusability Layer: Your SQL views is a reusable datasets. Instead of:
Report --> API --> logic, now you have API --> Fabric --> Shared data model --> Multiple reports. This reduces duplication massively.2. This is enterprise-grade design.
Layer Responsibility
AWS Operational data
Fabric Analytics
Power BI Visualization
Fabric vs Traditional SQL/Azure Stack:Traditional (Azure SQL) as SQL DB (storage), SQL Agent (jobs) and SSIS (ETL)
Fabric Equivalent:
Traditional Fabric
SQL DB Lakehouse
SQL Agent Pipelines
SSIS Notebooks (Python/Spark)
Stored procedures SQL viewsScheduling: Python runs inside Notebook. Pipelines trigger it and No separate SSIS packages.
Storage Size & Limits: There is NO fixed DB size limit like SQL DB. Fabric uses OneLake (data lake storage).
Cost model:
Component Cost
F2 capacity ~$262/month
Storage Charged separatelyStorage pricing: ~$23 per TB/month (approximate).
Note: Keep aggregated data only, Partition by date and Avoid raw logs.
Can it scale over time?
YES, No hard limit and Pay-as-you-growAI Capabilities in F2:
1. In Notebooks: Code generation (Python), Debugging assistance and Data exploration.
2. In Power BI: Narrative summaries, Q&A and DAX suggestions.
3. Data exploration: It has limited Auto insights.
Please refer below links.
End-to-end tutorials in Microsoft Fabric - Microsoft Fabric | Microsoft Learn
Microsoft OneLake documentation - Microsoft Fabric | Microsoft Learn
Pipeline Overview - Microsoft Fabric | Microsoft Learn
Connect and Query SQL Database in Microsoft Fabric | Microsoft Learn
Microsoft Fabric quotas - Microsoft Fabric | Microsoft Learn
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
v-dineshya Thank You 👍
schu i have a lot to learn, last ques:
1. Is the storage/pipeline/notebooks all in PowerBI and not Azure, like a PowerBI Extended capability framework and everything is managed from powerbi?
2. I wont go more than 1TB, do you think its wise to go for the F4 Reservation 1yr or pay as you go better as my current model we thinking of monthly refresh of 10 reports, are there benefits from F2 to F4 other than processing power? so its F2 $263+$23 or Reservation F4 $312+$23 - is it worth the motivation does the business see the difference like extra featires etc or just pocessing?
3. This "Data exploration: It has limited Auto insights.", i want to provide client analyses monthly but in full essay narratives for 15 clients or is this something different, is thois the per visual summary limit?
4. So i need to create a PowerBI worksapce and assign the license to it and i work within that workspace on powerbi or azure - is azure falling away what about the synapse dw/databricks if fabric can also do medallion & databricks?
5. With the Fabric Notebooks i can do forecasting and schedule in fabirc peipleine correct but access data in the Lakehouse or does fabric also have databircks available etc?
Sorry for all the ques, im learning so much.