What does the refresh button really do?

[hokiefan1113]

What does this refresh button actually do? It's definitely not refreshing my datasets when I click it, and my business users find it very confusing as well. 

[Anonymous]

Hi,

I have an issue with this Refresh Button. We are running reports in Report Server (Jan 2020 version). Schedule is refreshed, but when I hit "Refresh" data is not changing. Data changes when I refresh the whole page (refresh from the browser). 

This has not happened in our previous version of Report Server (Jan 2019). Could it be a change of behavior in this version?

Thanks in advance!

Regards,

Agustin

[Anonymous · Accepted solution]

This is what I understand happens (I could well be entirely wrong as most of ths is guesswork from observation rather than knowing stuff for sure)

When you upload a PBIX file to PBI-SSSR Server it creates an entry in the ReportServer DB dbo.catalog table. It also creates two entries in dbo.CatalogItemExtendedContent table. One of these is the visuals, m code etc, the other is the data blob.

If you take a PBIX that has imported data and rename it to .zip you can Unzip it. Inside you will find a file called DataModel. This is a super compressed blob of data. This is the thing that gets split out as the data blob in the dbo.CatalogItemExtendedContent table. Everything else is in the visuals blob.

I assume this is so that you can setup a scheduled refresh of the data and it just overwrites the data blob.

When you view a PBIX report the SSRS server magically stitches them back together somehow. Actually what it looks like it does is fire up a captive instance of SSAS tabular (msmdsrv.exe) and feeds it the data blob. It sends the visuals to your web browser and spins up some web services that your visuals send queries to. The web services forward the queries to the captive SSAS instance and ferry the results back to your browser. So your visuals are effectively connected to this temporary data source and as you slice and dice your charts the queries are sent to the captive SSAS instance via the web services. Once you've spun your report down the captive SSAS instance is quietly killed off, or after a timeout of no activity on the captive SSAS Instance/web services. Some of this is guesswork as there's very little hard info out there. But you can see instances of msmdsrv.exe spinning up and down as users browse PBIX reports.

If you are using direct query or live query data sources its a bit different as you don't get a captive SSAS instance so I assume there's just some web servcies that ferry the queries to the actual data source/s.

When you hit refresh in your web browser while looking at a visual you are asking for a new copy of the visuals and for the data to be reloaded. If the data blob has been updated by a scheduled refresh or upload you would see new numbers. Equally if someone has loaded up a new version of the PBIX with revised visuals you would see that. If the PBIX is connected to live data you would get all the queries issued again to the data source and hopefully see some new numbers, assumimg the data source has been updated somehow.

However if nothing has changed you just spin up a new copy of exactly the same visuals and data and issue the same queries which hopefully return exactly the same results (it would be bad if they didn't), hence the appearance of it not doing very much.

We have some dashboards of our call logging system that are connected to the Live backend SQL DB. When we push that refresh button the numbers move/change when the report re-renders. 

[pbx]

I think you are mostly correct, except that the instance of SSAS (which is exactly what it used to be called PowerPivot mode of SSAS) runs all the time in PBI RS. It's just that the model (with data) gets loaded to there upon calling a PBI report. This architecture causes inefficiencies if you are running PBI in a scaled out environment, specially for large models, as data may have to be loaded multiple times. Hope MS fixes this flaw at some point.

[Anonymous]

yeah. You are correct. There's a single msmdsrv.exe instance running under the PBI reporTServer Service. the other instances I see flashing up are people using PBI Desktop on the server. (It's a dev server).

I'm wondering if I can attach to that instance and maybe run some monitoring against it as we do for our regular SSAS.

I'm also wondering if that invalidates some of my math. If the model that is loaded is shared between multiple reports then it scales better than I suggested. I'll investigate

[pbx]

You should be able to attach to that, it typically listens on 5132 port on localhost. By default a flightrecorder trace is also running, you can check that out. I don't think extended events are supported/initialized on that instance though.

[Anonymous]

I only seem to be able to connect via SSMS using localhost:5132 it doesn't seem to accept remote connections.

If I remote onto the machine I can connect but get no databases, howevere if I run SSMS as Administrator and then connect I can see the GUIDs to the DB's. These seem to correspond to the info I see in the log files for the service.

I'll see if I can run an extended event trace on it the way we do with conventioanl SSAS instances.

As a quirk. The 5132 instance says its running in SharePoint Integrated mode!

[pbx]

Well, no surprises there regarding SharePoint mode, the whole thing is the same technology as PowerPivot, it's just that PBI desktop replaced Excel and PBI Service/Server replaced SharePoint/PowerView. As for only listening on localhost, probably you can change it by hacking through msmdsrv.ini file, not sure what security risks you'd be introducing by doing that though.

[Anonymous]

Good call on msmdsrv.ini Am guessing its this line

<ListenOnlyOnLocalConnections>1</ListenOnlyOnLocalConnections>

I hadn't thought about the PowerPivot SharePoint deployment type. It is as you say exactly that just without all the SP front end. Which does make me wonder why it took them so long to get an on prem version out there. But hey ho.

[pbx]

As for MS taking long to release the on-premise PBI, my gut feeling is MS didn't want to release the on-premise and only did that when they noticed companies not really jumping on the cloud service due to various technical and non-technical issues.

[avalonds]

Know this is an old topic but following up on your thoughts ...

Is this why when the server report is refreshed, the refresh does not update the powerBi desktop data?

I noticed when I hit the "Edit in PowerBI" button on the server, the dataset in the desktop version is not refreshed even though the PowerBI server report has refreshed.

[Anonymous]

Yes. I think you get the version of the report that was saved to the server not the "split version" that's held in the Report Server backend DB. I guess reconstituting it is a bit pointless as you're about to edit it.