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
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.
Hi icassiem ,
Please refer below.
1. It’s not just Power BI, It’s Microsoft Fabric a SaaS platform. It is managed from the Power BI / Fabric portal. You access everything via https://app.fabric.microsoft.com. Inside that UI you get Power BI, Lakehouse, Notebooks, Pipelines and SQL endpoint.
2. There are NO extra features unlocked in F4, only more capacity. You said 10 clients, Monthly refresh, 10 reports and Aggregated data. This is VERY LOW load.
changes from F2 --> F4
Area Difference
Compute More power (2x)
Concurrency More users supported
Performance Faster loads/queries
Features SAME features
Note: Start with F2, Enough for your pipelines, reporting, development and monitor usage metrics. If necessary Upgrade later.
3. Fabric / Power BI does Per report: Smart narrative visual and Generates insights per dataset.
Copilot: Can generate summaries, insights and explanations.
Note: It is not a multi-client report generator engine and not automatically generating 15 separate essays per client. AI narrative is assistive, not a full reporting engine.
Workaround: Use Python in Fabric, Generate text summaries, Store in table
and display in Power BI.
4. Yes, You don’t need Azure SQL, Data Factory and Synapse. Fabric replaces everything.
5. Fabric Notebooks are Spark-based, Python and SQL. It can Ingest data, Transform and Forecast. yes you can do forecasting. Please refer below example.
from sklearn.linear_model import LinearRegression
You can schedule it by Using Pipelines: Run notebook weekly/monthly and Chain tasks.
You don't need Databricks, Fabric already includes Spark, Notebooks and ML capability.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hi icassiem ,
1. Can Q&A generate a full client-specific 6‑month analysis?
Yes, but not reliably as a full “consulting-grade essay”. Power BI Q&A (LLM) is an LLM-powered semantic query layer. It can Understand natural language, Generate measures/visuals and Summarize trends. It can produce a response, but not structured like a proper report/essay, Output varies (not deterministic), Not multi-paragraph consulting-quality narrative and works best for quick insights and short summaries.
2. Can Copilot generate full-page narrative (your slicer-based report)?
This is closer to what you want but still not perfect. Power BI has “Smart Narrative” visual, it reads entire page (all visuals). Responds to slicers and filters. It generates multi-sentence summaries, trend descriptions and KPI insights.
Yes, your slicer-based idea WILL work with Smart Narrative, Copilot and Q&A. but it will give summaries, not full essays. If you want true client-level narrative reports, the best solution is Generate narratives in Fabric (Python) and serve them dynamically in Power BI.Please refer below practical setup.
Phase 1 (F2 setup): Use smart Narrative visual, Simple Copilot Q&A. It show value fast.
Phase 2 (upgrade your “wow factor”): Add Python-generated narratives and
Stored narrative tables.You want Client-level, 6-month, detailed narrative (essay style). Please refer below workaround.
Combine Fabric + Python + Power BI.
1. Generate narrative in Notebook. Please refer below sample python code.
def generate_summary(df):
growth = df["revenue"].pct_change().mean()
trend = "growth" if growth > 0 else "decline"summary = f"""
Over the past 6 months, the client shows a trend of {trend}.
Key drivers include product usage increases and seasonal variation.
Peak performance occurred in {df['month'].max()}.
"""return summary
2. Store in table:
client | narrative | date_range
3. Show in Power BI: Use table/text visual and filter by slicer. Now you get Stable, Repeatable, Client-specific and “Essay-like”.
I hope this information helps. Please do let us know if you have any further queries. If you face any issues or need any help in future. please create a thread in Fabric community . we are happy to help you .
Home - Microsoft Fabric Community
Regards,
Dinesh
v-dineshya Thank You Very Much, really appreciate your help ❤️🙏
Wish me luck, i will post in the fabric in the near future
v-dineshya Thank You so so much, you have been a star in helping me. Thank You for your patience and knowledge sharing 🙏
3 Last things:
1. This is important for me "Note: It is not a multi-client report generator engine and not automatically generating 15 separate essays per client." so i will start with F2 as there's no additional capability benefit with F4 but i was hoping to have a slicer to select a client and the Q&A and ask "give me a full analyses of the past 6 months based on the selected client for example or should i say give me a summary past 6 months per client" as this is internal reporting this part - would that workm is the Q&A an LLM in PowerBI could do that?
2. I am ready to accept as solution, im just wondering im going to need more future help as i go along, how do i do can i reach out to you or should i create another ques but this might go deeper into fabric?
3. Are the copilot narratives strong in detail meaning, if my page is a quarterly view per slicer client, could it generate a essay or is that only in the Q&A or summary per visuals as i would like a page detail narrative of all visuals based on slicer client selected?Sorry for the questions