Power BI <> Redshift Integration

11 min

Power BI ↔ Redshift Integration Architecture and Limitations

Operational document

Overview

End-users connect to Redshift through a single, non-materialized raw view. They can't create additional SQL objects — no joins, no aggregations, no materialized views, no new schema objects. That constraint pushes essentially all data modeling work into Power BI.

Architecture diagram

%%{init: {'theme':'base', 'themeVariables': {'primaryColor':'#4CD0E2','primaryTextColor':'#002A32','primaryBorderColor':'#00778F','lineColor':'#00778F','secondaryColor':'#EDF8FA','tertiaryColor':'#FFFFFF','fontFamily':'DM Sans, sans-serif'}}}%%
flowchart LR
    RS["Redshift\nSingle raw, non-materialized view\n• No joins • No aggregations\n• No new SQL objects • Read-only"]
    PBI["Power BI\nBecomes the entire semantic layer\n• Power Query: cleaning, joins, modeling\n• Data model: relationships\n• DAX: business logic & measures"]
    RS -- raw rows --> PBI
    style RS fill:#EDF8FA,stroke:#00778F,color:#002A32
    style PBI fill:#4CD0E2,stroke:#00778F,color:#002A32

Where data modeling happens

In Redshift — nothing

The raw view is the only structure available. No joins, filters, business rules, or aggregations happen at this layer.

In Power BI — everything

Data cleaning and normalization

Derived columns and aggregations

Relationships and DAX measures

All business logic and semantic modeling

Done via Power Query (M) for transformations and the Power BI data model for relationships and measures. Power BI is the semantic layer, full stop.

DirectQuery vs Import mode

Mode	Behavior under this access model	Verdict
DirectQuery	Tries to push transformations back to Redshift, but joins/aggregations can't fold against a raw view. Most logic runs locally, performance degrades, visuals can time out.	Not recommended
Import	Loads the raw view, then builds the full semantic model inside Power BI.	Recommended

Gateway requirements and ownership

Redshift is not publicly reachable — access requires IP whitelisting. Because of this, the Power BI service (cloud) can't connect directly; an on-premises data gateway is required to bridge the connection.

Item	Detail
Why it's needed	Power BI's cloud service has no route to Redshift unless a gateway, installed on a machine whose IP is whitelisted, relays the connection.
Where it runs	On a server/VM with network access to Redshift (i.e., its outbound IP is on the whitelist) — typically on-prem or in the same VPC/network as Redshift.
Who sets it up	IT / data infrastructure team — installing and configuring the gateway, and adding its IP to the Redshift whitelist, isn't something report authors do.
Who maintains it	Same team — gateway software needs updates, the host machine needs uptime/monitoring, and credentials used by the gateway need to stay current.
Cost ownership	No separate Microsoft licensing fee for the gateway itself. The cost is the hosting/compute for the machine running it (VM, on-prem server), which falls under whatever team owns infrastructure spend — not the Power BI/reporting budget.
Refresh impact	All scheduled refreshes and DirectQuery calls route through this gateway, so its uptime and capacity directly affect report reliability — another reason it sits with infra, not with individual report owners.

Net: this is an infrastructure dependency, not a Power BI configuration step. Whoever owns the whitelist and the hosting environment owns the gateway.

Limitations

Modeling

No star or snowflake schema can be built from Redshift directly — no joins to dimension/lookup tables, no enrichment from other Redshift data. All of it has to be rebuilt manually in Power BI.

Transformation

Complex transforms increase refresh time, large datasets strain memory, and logic that could be optimized in SQL has to be recreated in every report.

Performance

Larger raw datasets mean bigger Power BI datasets, longer refreshes, more gateway load, and degraded responsiveness — especially under DirectQuery.

Consistency

Without a centralized semantic layer, business logic can diverge between reports — same metric, different definition depending on who built it.

Risks

Risk	Detail
Scalability	As volume grows, import refreshes may exceed time limits or dataset size caps; memory pressure increases and reports get unstable.
Maintenance	Any change to the raw view structure means updating every report, rebuilding transforms, revalidating DAX, redoing relationships.
Data quality	No upstream normalization means duplicates, nulls, and inconsistent formats land in Power BI and have to be fixed manually — errors can propagate across reports.
Governance	No shared semantic layer or enforced logic means no shared KPI definitions, and analysts may implement the same metric differently.

Bottom line: Redshift is acting purely as a data source here — one read-only view, no modeling. Power BI is absorbing every modeling, transformation, and governance responsibility that would normally sit upstream. Import mode is the only mode that holds up under this constraint.

Summary Redshift Single raw, non-materialized view • No joins • No aggregations • No new SQL objects • Read-only accessraw rows

Power BI Becomes the entire semantic layer • Power Query: cleaning, joins, modeling • Data model: relationships • DAX: business logic & measures • Import mode (recommended) - Loads raw view, models locally, builds full semantic model in Power BI's own engine • DirectQuery - not recommended Query folding fails on joins/aggregations, Transformations run locally → slow, visuals may time out