Analysis Services Connection Questions

[DawnSP]

Hi

I work for a midsize company that began using PowerBI in January 2023.

We have a 'live connection' to our datacube via SQL Server Analysis Services.

The live connection disables all the modeling capabilities.  I am a dashboard designer/end user and I'm not allowed to change the data cube at all.  This is causing a lot of problems with getting data to slice and display in the ways that I want.

So my question is - what do other companies do?  I can't be the only one who needs the data to refresh daily without manual intervention.  is it possible to get PowerBi to sit on top of a data warehouse that refreshes every day and still have access to all the modeling features? 

Thank you!

Dawn Boley

Data queen

[RossEdwards · Accepted solution]

Power BI has an Analysis Services connector.  When you are using the live connection, you are using the SSAS model as your reports model.  Thus you are having your edit issue.

Alternatively, you can connect to your SSAS model via import and download data into your own model.  Power BI runs on SSAS tech, so you can rebuild the model in Power BI.  SSAS isn't great for transferring large blocks of data but you could do a mix where you grab the summarised outputs from SSAS but get your large transacational data from the original sources.

One note, when you do an import connection to SSAS it will treat it like a cube.  So make sure to make in dividual connections to individual tables if thats what you were trying.