Change Data Source of Tabular Model to Amazon Redshift

[mandmiADA]

What I Did

I successfully deployed an AAS tabular model to Power BI Service (Premium Capacity).

What I Need Help With

I want to change the data source of the newly created semantic model from SQL Server to Amazon Redshift.

What I Tried

• We have a working gateway to the Redshift database. If I create a new report and connect to it, it works fine—it creates a functional semantic model using the Redshift gateway and refreshes without issues.

• I need to update the data source for my existing tabular model instead of recreating it from scratch since it has a lot of tables and hundreds of measures.

• I'm using Tabular Editor 2.25.0, and XMLA endpoint is enabled in our Premium Capacity workspace.

The Problem

• When I try to update the data source, I get this error:
  "You can't schedule refresh for this semantic model because the following data sources currently don't support refresh."

• I tried these connection strings, but neither worked: 

  • Driver={Amazon Redshift ODBC Driver}; Server=xxx.redshift.amazonaws.com; Database=yyy; Port=5439; UID=abc; PWD=123;
  • Extension{"extensionDataSourceKind":"AmazonRedshift","extensionDataSourcePath":"xxx.redshift.amazonaws.com:5439;yyy"}
    

Question

How can I successfully update the data source to Redshift via the gateway? Any suggestions would be really helpful!

[mandmiADA · Accepted solution]

The only working solution I found out is to delete model Data Source and each table Data Source (Legacy) partitions and replace them with M equivalent that is using Power Query native Redshift connector. ( let Source = AmazonRedshift.Database...)

Since my Tabular model is serialized I managed to create quick python script to replace all table partitions with M query.

[v-prasare]

HI @mandmiADA,

Thanks for reaching MS Fabric community suppport

Modify the Data Source in Tabular Editor. Locate the Data Source object in the model. Updated the connection string to match Redshift format. Saved changes and deployed back to Power BI Service.
Provider=MSOLEDBSQL;Data Source=xxx.redshift.amazonaws.com;Initial Catalog=yyy;User ID=abc;Password=123;Port=5439;
(or)
Extension{"extensionDataSourceKind":"AmazonRedshift","extensionDataSourcePath":"xxx.redshift.amazonaws.com:5439;yyy"}

In Power BI Service go to Settings and Manage Gateways. Selected the existing gateway connected to Redshift. Updated the data source credentials and authentication method.

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

[mandmiADA]

Hello @v-prasare ,

thank you for reply. I tried your solutions:

Provider=MSOLEDBSQL;Data Source=xxx.redshift.amazonaws.com;Initial Catalog=yyy;User ID=abc;Password=123;Port=5439;

This one lets me deploy to Power BI Service however when I try to manage the gateway in settings it doesnt show any gateway:

"You don't have any data gateways.

We didn't find any suitable data gateways to connect to the data sources that this dataset uses. Either install an on-premises data gateway and add the data sources that this dataset uses to it, or install a data gateway in personal mode to connect this dataset to its data sources."

We do have AmazonRedshift gateway connection that works in other sematic models.

Your second solution:

Extension{"extensionDataSourceKind":"AmazonRedshift","extensionDataSourcePath":"xxx.redshift.amazonaws.com:5439;yyy"}

This connection string doesnt even let me deploy to PBI Service. It crashes during deployment with the following message:

"Error during deployment:

Synchronization of metadata with Power BI service could not be finished. Try to repeat the operation later.

Technical Details:

RootActivityId: ...

Date: ..."

[v-prasare]

Hi @mandmiADA,

1. Clear and Reconnect to the Data Source. Remove the existing SharePoint connection. Reconnect and ensure all columns are pulled in from the start.

2. Check Column Visibility in SharePoint--> Go to SharePoint List Settings → Columns and check if the column is hidden. If hidden, make it visible and refresh Power BI.

3. Use Table.ColumnNames() to Identify Missing Columns
In Power Query Editor, add a new step:
Edit
= Table.ColumnNames(Source)
This will list all available columns in the data source.

If your column isn’t in the list, it’s likely a SharePoint issue.

4. Load Data into a Staging Table Instead of transforming the data immediately, load the raw SharePoint data into a separate Power BI table.

Then, check if the column appears before applying transformations.

5. Check Column Data Type Compatibility, If the column has mixed or unsupported data types, Power BI might exclude it.

6. Increase API Version in Query In Advanced Editor, modify your query:
Try changing ApiVersion from 15 to 14 or 16 to see if it retrieves missing columns.

7. Manually Add the Column in Power Query
If Power BI still doesn’t detect it, manually add it:

#"Added Custom Column" = Table.AddColumn(Source, "MissingColumn", each null)
This ensures the column exists and can receive data upon refresh.

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

[mandmiADA]

I believe you made mistake and replied to the wrong post.