need ideas/help to pull data from Redshift to Power BI in efficient way

[rcxpowerbi]

Hello Everyone,

we are new to the Power BI tool. we are using high configuration Redshift DWH cluster(OLAP DWH) and Power BI(reporting tool). Power BI Gateway is in the same VPC with the Redshift cluster.

We would like to develop Power BI reports with the last few month's data (more than 19 Million records) for Analysis, Year-Over-Year comparisons, and trends, and current on-going business trends. We tried both the methods Import and Direct Query to extract data from Redshift to Power BI.

Issues faced on Import Method:

1. Import method is taking too much time for each schedule refresh compare to on-demand refresh. We observed that, backend it is triggering multiple SQL's, to refresh a single table.

2. Incremental refresh is not supported for custom SQL's for Redshift VS Power BI combination.

3. For Historic reports, We have a dataset having more than 1GB. In Power BI, each data set should be 1 GB limitation.

Issues faced on Direct Query Method:

1. If Redshift is busy with other user's queries OR huge data extractions from Power BI queries, Direct Query fails to load data from Redshift to Power BI for detail level and analysis reports.

Please suggest to us, Is there any other way to pull data from Redshift very fast.

[v-xicai]

Hi   @rcxpowerbi ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai]

Hi @rcxpowerbi ,

You may use Amazon Redshift connector to connect data, see more details: Connect to an Amazon Redshift database in Power BI Desktop.

In your scenario which has a large data source, recommend you choose DirectQuery mode, while there are some limitations you may considerate: Use DirectQuery in Power BI Desktop. And you may use Incremental refresh which refreshes only data that has changed, see more detail steps: Incremental refresh in Power BI Premium .

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GilbertQ]

Hi there

Here is a blog post where I detail how to connect to Redshift via ODBC. Whilst this is for Azure Analysis Services, it works exactly the same way.

What you can then do is to create a view in Redshift, which Power BI can connect to and then you can enable incremental refreshing.

https://www.fourmoo.com/2019/08/20/using-azure-analysis-services-to-connect-via-an-odbc-source-redsh...