TE3 Enterprise + Power BI Desktop + SSAS Tabular server performance problem

[pgschr]

I have a setup that's working fine but with huge performance problems. Power BI Desktop is connected to SSAS Tabular server, TE3 Enterprise edition connected in parallel to SSAS, measures and data in SSAS. My problem is that whenever I make a small change in a measure, after saving the model to SSAS from TE3, I refresh the model in Power BI Desktop and that takes 3 minutes (and probably will take longer as I add more measures and data tables in SSAS). What is the correct way to develop in this environment? Everything runs in my laptop, including MS SQL Server.

[greggyb]

It's unclear what the connection from PBI Desktop to SSAS is. Are you doing an import into PBI from Tabular or a Live Connection?

[greggyb]

Is it DQ or Live Connection? DQ implies that you've got a composite model in PBI Desktop, and there may be other things going on.

When you make a new file in PBI Desktop and you use Get data > Analysis Services, you get a dialogue like below, which only allows import and live connection.

DQ with AS means that you've got a composite model, and so there could be other things going on in your composite model that would slow down things when you hit 'Refresh' in PBID.

If it is not DQ and therefore not a composite model, then the most likely thing that causes slowdowns when clicking 'Refresh' is that you're on a report page that is slow to query+render.

So, again, can you confirm the model type in PBI Desktop: Import, Live Connection, or DQ/Composite?

And then is the performance the same on a new empty page in PBID as on one of your report pages?

[pgschr]

I'm developing an application that will embed Power BI reports on Power BI Service and access SSAS tabular server on the cloud. For development, I use Power BI Desktop in my laptop connected to SSAS/SQL Server in my laptop as well.

Every time I add data to the SQL Server database, I have to refresh the table in TE3 to make it available to Power BI.

I thought I had a DirectQuery connection between Power BI Desktop and SSAS because when I retrieve tables from SSAS I see this screen:

So I don't see Live Connection in the dialog box, only Import and DirectQuery. My final goal is to have Live Connection and run everything in SSAS. Why do we have different screens?

NOTE: I just created a new Power BI Report, and the options are Import and Live Connection, does that mean that in my current report I DON'T use live connection?

[greggyb]

NOTE: I just created a new Power BI Report, and the options are Import and Live Connection, does that mean that in my current report I DON'T use live connection?

That is the case so far as I understand things. Microsoft has made a bit of a hash of things with naming for the cluster of features around connecting to source systems and delegating queries to them, rather than importing.

I'd try a new PBI Desktop file and use a Live Connection, and see if you have the same performance issues.

DirectQuery implies (as I understand it) that you have a composite model. That means that you have a semantic model in Power BI Desktop, and in the semantic model, some subset of it is composite, pushing queries back to AS.

[pgschr]

Thanks, I will try that. If I have PBI Desktop connected to SSAS and I change a measure with TE3 in SSAS, should that change be reflected in PBI automatically or I have to refresh manually? If I click Refresh in PBI, does it also refresh anything related to tables/data in addition to measures?

[greggyb]

I do not recall off-hand if PBI automatically sees metadata changes like that automatically.

It may:

• automatically pick them up with no action on your part
• pick them up on the next query to AS (e.g. on a viz interaction or creating a new viz or running a new DAX query in DAX Query View)
• require refreshing via the refresh button

I think those are the scenarios to check.

[TinyTimothy2]

It should take a few seconds to make a save. You do have to refresh pbi to see the changes reflected in report. That should also be a brief referesh. I would raise a ticket with your DBAs. That should be monitoring anyway and able to trace what is happening