Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gp10
Advocate III
Advocate III

Can't import large dataset in desktop and service with ODBC connection

Hi community,
I'm facing an issue. I have a dataset of about 200+ M rows, that grows by million of rows daily, and I cant import the data either in Desktop to create a dataset there, or in Service to create a Dataflow or Datamart.

I'm using an ODBC connection to an Amazon Redshift database.

On desktop the data never load or, when trying to tranform the data, the evaluation never ends in Power Query.  Same on Service, when trying to create a Dataflow or Datamart, the evaluation gets cancelled in Power Query when reaching the 10 minute limit. Then when I click save I get a "PQO Evaluation Failed" error.

Are there any suggestions on how to deal with this issue?

1 ACCEPTED SOLUTION

I have created a filtered pbix file that has only a snapshot of the data. Set up incremental refresh on it. Published and refreshed. That created the partitions.
Then you need to use external tools like SQL Management Studio and XML Toolkit to remove the filtering and refresh the partitions.
Check this video:
https://youtu.be/5AWt6ijJG94

One other way is to create a parameter to filter the data and change it or remove it from the dataset settings on Power BI service.

View solution in original post

8 REPLIES 8
gp10
Advocate III
Advocate III

Any ideas on how to import a large dataset with an ODBC connection?

MAwwad
Super User
Super User

There are several strategies you can try to deal with large datasets in Power BI:

  1. Use a direct query connection instead of importing the data. Direct query connections allow you to query the data directly from the database, rather than importing it into Power BI. This can be more efficient when working with large datasets, as it reduces the amount of data that needs to be transferred and stored in Power BI. However, it can also be slower, as the queries are executed in real-time and may require more resources from the database.

  2. Use data sampling to reduce the amount of data that is imported or queried. Data sampling allows you to select a random subset of the data to import or query, rather than the entire dataset. This can be useful for testing and prototyping, as it allows you to work with a smaller and more manageable dataset.

  3. Use incremental refresh to update only the new or modified rows in the dataset. Incremental refresh allows you to set up a schedule to refresh only the rows that have been added or modified since the last refresh, rather than the entire dataset. This can be more efficient when working with large datasets that grow over time, as it reduces the amount of data that needs to be processed and stored in Power BI.

  4. Use data modeling techniques to optimize the data for querying and analysis. Data modeling techniques such as indexing, partitioning, and columnstore indexes can help to improve the performance of queries on large datasets by optimizing the way the data is stored and accessed.

Thanks for your time @MAwwad , I have tried all the steps that you mention and this is not what I need.
1. Direct Query is not an option for ODBC.
2, 3,4. Not my issue, I want to import and load all my data. Incremental refresh comes after that.

Hi, did you find the solution to this problem, I am also facing the same issue while importing huge datasets in the Power BI at the initial level.

 

Please let me know your findings.

I have created a filtered pbix file that has only a snapshot of the data. Set up incremental refresh on it. Published and refreshed. That created the partitions.
Then you need to use external tools like SQL Management Studio and XML Toolkit to remove the filtering and refresh the partitions.
Check this video:
https://youtu.be/5AWt6ijJG94

One other way is to create a parameter to filter the data and change it or remove it from the dataset settings on Power BI service.

Can you explain this in details please?

 

One other way is to create a parameter to filter the data and change it or remove it from the dataset settings on Power BI service.

 

From where to remove the filter in Power BI Service?

If you create a date parameter in Power Query and filter your data according to it, when the report is published you will be able to see the parameter here and change its value:

gp10_0-1685622012589.png

 

Thanks for your reply. We use Professional License hence this will not work in our case.

 

Anyways thanks for your prompt response.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors