Forum Discussion
Helper IIIBest way to Load-Transform-Cache for Power BI Report fetching data from AWS S3 Bucket
We want to connect from Power BI within Azure to AWS S3 Bucket using ODBC DSN and want to populate the Power BI cache which can be used for reports to use.
We will be combining multiple tables including dimension and facts along with some transformations having million of rows in target result set which will be kept in cache.
We will be having two types of power BI roles which will be used for data level security.
We should be able to handle incremental refresh daily in Semantic Model cache.
We are thinking of having of two approaches given below. Which one is the best way to handle this or if there is any other method which we can follow
1. Should we create separate Semantic Models and combine multiple tables and fetch the data ftom AWS S3 bucket incremenatally daily
and populate the cache and report should display the latest data from these SMs?
2.
Should we create a Lakehouse and create x2 gen2 dataflows within that, which will populate x2 tables and I can see them in the Lakehouse editor
then I can create a semantic model (which is based on the lakehouse) and a report based on the semantic model
I think with this approach we are just using the dataflows as an ETL and we have good transparency on the data in the lakehouse (including ability to query in SQL) and the SM against the lakehouse seems to work nicely.
If your Lakehouse, Dataflow Gen2, and Semantic Model live in a workspace backed by Fabric capacity (F-SKU), then Pro users can consume them, but they cannot create or manage Fabric items in Pro-only workspaces.
7 Replies
- cengizhanarslan
Super User
Option 2 is the right direction for this scenario.
Using Dataflow Gen2 only as an ETL layer and landing the curated fact and dimension tables into a Lakehouse gives you a clean separation between ingestion, transformation, and consumption. It also makes the data transparent and reusable, since you can inspect and query the Lakehouse tables directly in SQL or Spark without having to reverse-engineer logic from a semantic model.
Building a single semantic model on top of the Lakehouse keeps your reporting layer simple and avoids duplicating refresh logic, transformations, and security rules across multiple models. Incremental processing is easier to control upstream, and your semantic model refresh becomes more stable and predictable.
This approach also scales better as data volume grows and aligns well with Fabric’s intended architecture, especially when you have multiple facts, shared dimensions, and role-based security requirements.
GauravSinghPBI- cengizhanarslan
You need Fabric capacity (F-SKU), without capacity, you’re limited to classic Power BI patterns (Import + incremental refresh inside the semantic model).
Hi GauravSinghPBI ,
Thanks for reaching out to Microsoft Fabric Community.Just wanted to check if the response provided by cengizhanarslan was helpful. If further assistance is needed, please reach out.
Thank you.Hi GauravSinghPBI ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.
