what model framework should I choose - Import or Direct Query ?

[ashmitp869]

Hi ,

I have a pbix file whose data source are -

1. OData feed ( have API to get data)

2. SQL Server 

The question is for SQL Server - what model framework should I choose (Import or Direct Query ?)
I know it got limitations with Import (like 1GB size that's why concern ?)

Please suggest .

Currently using Import options and when I check the workspace setting , I found the size is 6 MB.

Wondering if the data grows - will it be problem ?

PS: I don't have a request of real time data.

Daily 4 times refresh is fine.

I have Power BI premium license and also using custom visuals like (Inforiver reporting matrix for export some reports)

Thanks

[v-prasare]

Hi @ashmitp869,

Hope your doing well.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution we have provided for your issue worked? or let us know if you need any further assistance here?

Your feedback is important to us, Looking forward to your response. 

Thanks,

Prashanth Are

MS Fabric community support.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query.

[ashmitp869]

Thanks for your reply

[v-prasare]

@ashmitp869, Hope your doing well.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution we have provided for your issue worked? or let us know if you need any further assistance here?

Your feedback is important to us, Looking forward to your response. 

Thanks,

Prashanth Are

MS Fabric community support.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query.

[Omid_Motamedise]

When deciding between Import and DirectQuery in your Power BI model, consider the following factors based on your scenario:

Current Observations
Data Size: Your current file size is 6 MB, which is far below the 1 GB limit for Import mode in Power BI (10 GB in Premium workspaces).
Refresh Frequency: Daily refresh (4 times) is manageable with Import mode since it doesn't require real-time updates.
Premium License: This allows for increased capacity, supporting larger datasets and more frequent refreshes (up to 48 times daily).

Pros and Cons of Each Model
1. Import Mode
Advantages:

Faster performance: Data is stored in-memory for quick querying.
Supports complex DAX calculations and advanced transformations without impacting query performance.
More features: Works seamlessly with custom visuals like Inforiver.
More flexibility: Handles offline usage scenarios.
Limitations:

Dataset size: If the SQL Server data grows significantly (approaching the 1 GB/10 GB limit), managing and refreshing data could become challenging.
Refresh time: As data size grows, refresh times can increase.
Resource usage: More memory-intensive on the Power BI server.
2. DirectQuery Mode
Advantages:

No size limitations: Queries data directly from SQL Server without loading it into Power BI.
Up-to-date data: Ensures data is as current as the SQL Server database (if needed).
Ideal for large datasets: Useful when datasets grow beyond Power BI's memory capacity.
Limitations:

Slower performance: Queries are sent to the SQL Server at runtime, increasing latency.
Feature limitations: Some DAX functions and custom visuals may not work as intended.
Dependency on SQL Server: Every user query hits the server, which could cause performance bottlenecks if server resources are constrained.

[v-prasare]

@ashmitp869, Thanks for actively participating in MS Fabric community support.

You can continue using Import Mode with scheduled refreshes for now. If your dataset grows significantly in the future, consider enabling Incremental Refresh or switching to a Hybrid Model.

When to Use DirectQuery:

For real-time reporting directly from the API.
Your API can handle frequent queries without hitting rate limits.
The data volume is too large to import efficiently.
You don’t need complex transformations in Power Query.

data source is too dynamic to rely on periodic refreshes.

Thanks,

Prashanth Are

MS Fabric community support.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos"

[lbendlin · Accepted solution]

You seem to be very far away from a scenario that would require Direct Query mode.  Continue with Import.