Forum Discussion
"In Power BI PPU, Dataflow Gen 1 does not support direct query.
"In Power BI PPU, Dataflow Gen 1 does not support direct query. If we want to avoid querying the source MSSQL database, which is hosted on AWS, directly, what is the most cost-effective option under $20 per month to enable data transformation and reporting without compromising performance?"
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.
18 Replies
- saritasw
Resolver II
Hi lavendar ,
In my opinion, Use Azure Blob Storage (Hot Tier) to stage your MSSQL data from AWS and connect it to Power BI Dataflow Gen2 for transformations and scheduled refreshes. It's cost-effective (Blob Storage ~$5/month) and avoids direct querying while staying within the $20/month budget under Power BI PPU.
Best Regards,Sarita
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.
Hi lavendar ,
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.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 ,
Just to clarify, while the DirectQuery option does appear in the UI when connecting to Dataflow Gen1 in Power BI Desktop, it's disabled and not supported for execution. This applies even in PPU workspaces.
According to Microsoft documentation, DirectQuery is only supported for Dataflow Gen2 in Microsoft Fabric workspaces, not Gen1: Using DirectQuery with dataflows - Power BI | Microsoft Learn
If there's a working DirectQuery scenario with Gen1 in PPU you've seen, I'd like to learn more. Appreciate your input as always.
Thank you.
The data is in Data flow Gen 1 , we have ppu licence and trying to connect to power bi. But for very large daatsets where importing is not an option, we are unable to use direct query with gen 1 data flows.we dont want to directly connect to mssql db (Aws RDS), what is the most cost effective solution ?
- KNP
Super User
Thanks for the additional info lavendar.
Unless the data is extensive, import is still an option. You can set up import mode with incremental refresh to ensure semantic model refresh times remain efficient.
Also, a direct query is possible with Dataflow Gen 1. You need to go into the Source step of the PowerPlatform.Dataflows(null) step to change the option.
Direct query should be avoided whenever possible. No matter what the source is.
Hi lavendar ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.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 ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
