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

[gp10]

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?

[gp10]

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

[MAwwad]

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.

[gp10]

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.

[ucicpowerbi]

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.

[gp10 · 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.

[ucicpowerbi]

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?

[gp10]

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:

[ucicpowerbi]

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

Anyways thanks for your prompt response.