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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.