Forum Discussion

icassiem's avatar
icassiem
Post Prodigy
2 months ago
Solved

Data Environment Options Follow-Up

Good day, I previously requested assistance on designing a small data platform for Portfolio reporting Solved: Data Environment Options - Microsoft Fabric Community   My source enviornment is acr...
Need Help
  • v-dineshya's avatar
    v-dineshya
    2 months ago

    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

icassiem's avatar
icassiem
Post Prodigy
2 months ago

v-dineshya  Thank You very much

1. do you have an ERD envioronment & solution archetetcure examples or supporting links and docs please

2. and motivation or risks i need to cover for my proposal

3. i probably cant do the medallian due to storage and cost becuase i guess that additional costs, maybe into silver layer where the pythin pipeline agg and removes PII data, maybe only a silver with the sql views as the gold mantic layer iots not storage but its bus tranformed or pre access layer?

4. orchestration?

5. how do i query ssms?

6. how would i access fabric from pc for dev and powerbi connect a gateway to sql views?

7. have I covered all angles theres nothing else like a db in azure or they will ask why not a rds db in aws but that gives powerbi security to rds prod issus and a db on prod product and ev etc

8. thereafter i can use copilot in powrbi narratives, does it allow m,e to use copilot else where in my data management or ingest, transform?

9. so everything is via python correct, the ingest to store to transfomr and sql only for views and query?

10. would my ingest be overrite be or is there a cdc manner or can i use data factory with pythoin or is that dddiotnal cost and is pc dveelopement that needs to deploy to server are those all additional costs?

 

Sorry for all the ques, im really trying to be sure i cover all agles etc

Please help

icassiem's avatar
icassiem
Post Prodigy
2 months ago

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

  • v-dineshya's avatar
    v-dineshya
    Community Support
    2 months ago

    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

    • icassiem's avatar
      icassiem
      Post Prodigy
      2 months ago

      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?

      • v-dineshya's avatar
        v-dineshya
        Community Support
        2 months ago

        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 pd

        clients = ["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 views

         

        Scheduling: 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 separately

         

        Storage 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-grow

         

        AI 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