Power BI SQL Server Analysis Services integration explained
Power BI SQL Server Analysis Services integration lets modern reports sit on top of the cubes and tabular models a company already trusts. Microsoft documents a SQL Server Analysis Services database connector with two modes, Connect live and Import. Because the server runs on your own network, the Power BI service reaches it through a gateway. So the plan covers the model, the gateway and user identity together.
- Connect live or Import
- On-premises data gateway
- SSAS roles respected
Last updated
How Power BI SQL Server Analysis Services integration works
The author chooses the SQL Server Analysis Services database connector in Power BI Desktop. Then they enter the server name and, if they wish, a database. After that they pick Connect live or Import.
Connect live keeps the data on the server. Each visual sends a query and shows the answer, so the measures and hierarchies come straight from SSAS. Import runs a query once and copies the result into a Power BI semantic model instead. The live mode is usually preferred, since it keeps one definition of every figure.
Microsoft's article lists several authentication kinds, including Windows and an organizational account. It also states a simple prerequisite: Analysis Services must be installed with a database that holds data. We confirm the instance mode as well, because tabular and multidimensional models behave differently.
Tabular or multidimensional: what changes for reports?
Both model types can feed Power BI, but each brings its own habits.
Tabular models
These use tables, relationships and DAX. So they feel familiar to anyone who has built a Power BI model.
Multidimensional cubes
These use dimensions, measure groups and MDX. However, Power BI still shows them as fields an author can drag.
Hierarchies
Both types expose hierarchies. Then users can drill from year to month without extra work.
Calculations
Logic stays on the server. Therefore a change to a calculation reaches every report at once.
Feature gaps
Some cube features have no direct match in Power BI. So we test the ones your users rely on.
Performance
Speed depends on the server and the model design. When a page is slow, we look at the query first.
Why does the gateway matter for an SSAS source?
The Power BI service runs in the cloud, while SSAS runs on your network. An on-premises data gateway bridges the two. It sits inside the network, so nobody has to open inbound firewall ports. We also plan a second gateway machine, because one server alone is a single point of failure. Then a restart or a patch on one machine does not stop the morning reports.
Identity is the subtle part. For a live connection, Microsoft documents how the gateway passes the name of the signed-in user to Analysis Services. As a result, the roles on the model decide what each person sees.
That only works when names line up. The sign-in name in Power BI must match an account that Analysis Services can recognize. When the two differ, Microsoft describes user name mapping as the fix. We therefore check this early, because it is the most common reason a first live report shows an error. If the model has already moved to the cloud service, the Power BI Azure Analysis Services integration page covers that setup and how it differs. Model design itself is part of Power BI data modeling.
Faults in Power BI SQL Server Analysis Services integration
These are the most common causes behind a failing SSAS report.
- User names that do not match between cloud and domain
- A gateway account without rights on the server
- One gateway machine with no standby
- Imported copies that lose model security
- Cube features assumed to work and never tested
- Processing jobs that run while people report
Steps: Power BI SQL Server Analysis Services integration
Four stages take the work from a review of the model to reports in daily use.
Review model and roles
First we read the model, its roles and the questions people ask of it today.
Set up the gateway
Then we install and configure the on-premises data gateway with your administrators.
Prove identity
Next we test a live report as several users, so we know the roles filter data correctly.
Build and hand over
Finally we build the report pages, show them in weekly demos and document the setup.
Analysis Services data in Power BI: common questions
Does Power BI have a native connector for SQL Server Analysis Services?
Yes. Microsoft's Power Query connector list includes a SQL Server Analysis Services database connector, and its article marks it as generally available. The article lists Import and Connect live as capabilities for Power BI semantic models. It also lists several authentication kinds, including Windows and an organizational account.
Does Power BI need a gateway for on-premises Analysis Services?
Yes, for reports in the Power BI service. The server sits on your network, so the cloud service reaches it through an on-premises data gateway. Power BI Desktop on the same network connects directly. We plan the gateway machine, its account and a standby with your administrators before go-live.
Will SQL Server Analysis Services roles apply in Power BI reports?
With a live connection, yes, when identity is set up properly. Microsoft documents that the gateway passes the signed-in user's name to the server, and the model roles then filter the data. With Import, the roles do not travel. So we rebuild the security in Power BI for any imported copy.
Should we keep Analysis Services or rebuild models in Power BI?
Either can be right. A sound model that people trust is worth keeping, and a live connection adds modern reports at low risk. However, an old cube that few people can maintain may be better rebuilt. We assess the model and explain both paths in plain terms before you decide.
How do we begin a project on our SSAS models?
Phone (480) 485-7373 or send a note. One of our consultants responds within 2 hours and sets up a free 30-minute assessment. Power BI Agency is a division of Sataware Technologies and is independent of Microsoft. We fix the scope in a written quote and deliver online.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.