Forum Discussion
"In Power BI PPU, Dataflow Gen 1 does not support direct query.
Hi lavendar ,
Thank you for your response, and I appreciate KNP for offering clear answers.
Performance issues with Gen1 dataflows, even with ECE enabled, are uncommon and often mean some query steps aren't folding as expected. It's a good idea to use the Performance Analyzer in Power BI Desktop or check the dataflow refresh logs to see if Enhanced Compute Engine is being used effectively.
For slow "Close & Apply" times in Import mode, this is usually due to merge operations that aren't folding to the source, so processing happens locally. To improve performance, try moving merge logic into the dataflow or into a SQL view upstream. Gen1 doesn’t require explicit storage since it uses managed storage internally.
Your architecture, which avoids direct connections to the production SQL DB, is good. The slowness is likely related to specific transformation steps rather than the platform itself.
Hope this helps. Please reach out for further assistance.
Thank you.
Hi lavendar ,
Thank you for reaching out to the Microsoft Fabric Community, and we also appreciate the helpful insight already shared by saritasw .
You're correct Power BI PPU doesn't support DirectQuery for Dataflow Gen1, and your goal to avoid directly connecting to your AWS-hosted SQL database while staying within a $20/month budget is both practical and common.
As suggested, using Azure Blob Storage (Hot Tier) as a staging area is a smart and cost-efficient approach usually costing around $5/month. You can export your transformed data from the SQL database into Blob Storage, then connect that to Dataflow Gen2 in Power BI. This avoids live connections and makes use of Gen2's improved performance, larger data handling, and support for incremental refresh.
To further optimize your setup:
- Schedule regular exports from SQL using lightweight ETL or SQL jobs.
- Use Import Mode in Power BI for faster reports and offline access.
- Adjust your refresh frequency based on reporting needs to control costs and performance.
This approach gives you a low-cost, efficient, and scalable reporting setup fully supported within Power BI PPU.
Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.
Thank you.
But if Azure Blob storage also cannot be used for Direct query then what is the solution?I want to direct query from power bi desktop.
Hi lavendar ,
You are correct, Azure Blob Storage is useful for staging data, but it does not support DirectQuery in Power BI. Most file-based connectors, such as CSV, Excel, and JSON files (including those in Blob), are only available in Import mode. Additionally, if you are using Power BI PPU, DirectQuery is supported only for Dataflow Gen2 in Microsoft Fabric workspaces, not in Dataflow Gen1 or PPU environments. Microsoft provides more details about this limitation here: Using DirectQuery with dataflows - Power BI | Microsoft Learn
If you prefer not to connect directly to the AWS-hosted SQL database but still want to use DirectQuery from Power BI Desktop, a good approach is to replicate the necessary data into a source that supports DirectQuery, like Azure SQL Database or Azure Synapse Serverless. These options are cost-effective, integrate well with Power BI, and you can use simple ETL tools or scripts to regularly transfer data from AWS RDS to this new location, enabling DirectQuery access without connecting to the original database.
If having near real-time data isn't critical, you could also consider using Import mode with incremental refresh through Dataflow Gen2 or Azure Blob. This setup offers fast performance, offline access, and can help you stay within your $20/month budget.
Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.
Thank you.
Hi KNP ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.