Forum Discussion
Best Practice for Large Pre-Aggregated Fact Table: Import vs DirectQuery (Databricks, Power BI Embed
I’m working with a very large data set—about 500 million to 1 billion rows in a single fact table. The data is already pre-aggregated at the required grain, so all my business metrics are present in this fact table. I’m intentionally avoiding heavy DAX calculations or measures in Power BI, relying instead on pre-aggregated metrics in the source table. Dimension tables are used for filtering, and the fact table contains daily aggregates over a 5-year period, which is why the data volume is so high.
After some POCs, I’m leaning towards using Import mode, but I have a couple of questions and concerns:
1. Power BI Embedded A6 Capacity: I’m using an A6 Power BI Embedded license. Is this sufficient to load and refresh this volume of data (from Databricks), or should I consider DirectQuery to potentially save on capacity/costs?
2. DirectQuery Concerns: If I move to DirectQuery, there will be a significant number of queries generated by user interactions. I’m not sure how this will impact both Databricks costs and overall performance of the solution. Has anyone faced a similar scenario?
What would be the best practice for handling such large, pre-aggregated datasets in Power BI with Databricks as a source? Any guidance or experience with A6 Embedded and DirectQuery/Import trade-offs would be greatly appreciated!
Hi nitinbourai
For very large, pre-aggregated datasets like yours—500 million to 1 billion rows—using Import mode in Power BI is generally preferred, especially since your metrics are already calculated and you’re avoiding heavy DAX. Import mode provides the best performance for slicing, filtering, and visual interactions because the data is cached in-memory, but it does require sufficient capacity to load and refresh such a large volume. With an A6 Power BI Embedded capacity, you may be able to handle this dataset, but you need to carefully monitor memory usage and refresh times, as very large imports can approach or exceed the capacity limits. DirectQuery can reduce memory pressure because data stays in Databricks, but it generates many live queries during interactions, which can slow down reports and significantly increase compute costs on Databricks. In practice, for pre-aggregated datasets where performance is critical, Import mode is typically the best choice, possibly combined with incremental refresh to manage refresh duration and memory usage. DirectQuery is more suitable if your dataset cannot fit in memory or if real-time data is required, but you should carefully test query performance and Databricks cost implications before committing.
5 Replies
Hi nitinbourai
For very large, pre-aggregated datasets like yours—500 million to 1 billion rows—using Import mode in Power BI is generally preferred, especially since your metrics are already calculated and you’re avoiding heavy DAX. Import mode provides the best performance for slicing, filtering, and visual interactions because the data is cached in-memory, but it does require sufficient capacity to load and refresh such a large volume. With an A6 Power BI Embedded capacity, you may be able to handle this dataset, but you need to carefully monitor memory usage and refresh times, as very large imports can approach or exceed the capacity limits. DirectQuery can reduce memory pressure because data stays in Databricks, but it generates many live queries during interactions, which can slow down reports and significantly increase compute costs on Databricks. In practice, for pre-aggregated datasets where performance is critical, Import mode is typically the best choice, possibly combined with incremental refresh to manage refresh duration and memory usage. DirectQuery is more suitable if your dataset cannot fit in memory or if real-time data is required, but you should carefully test query performance and Databricks cost implications before committing.
Hi nitinbourai ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you Poojara_D12 for the prompt response.
I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.
Thank you.
On high level , it seems the import mode is the best solution. Need to work on the recommendations shared.
No help required as of now , will reconnect if I have anything else.
Hi nitinbourai ,
May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.
Thank you.
Hi nitinbourai ,
As there is no help required as of now, we are closing this thread.Please raise a new thread in the community if you need any further assistance.We are happy to help.
Thank you.
