Report Server data refresh options

[mpo]

Hi all,

We are looking into deploying PowerBI reports to our customers using the PowerBI Report Server.

Our master data is in a Sql Server database(s).

We are trying to figure out the optimal deployment model.

Our main criteria are:

- Minimal impact on the master database during business hours.

- As up-to date data in the reports as possible, (but not strictly real-time)

- Not requiring Azure AD / O365 accounts for our end customers

Current plan is

- to host Tabular SSAS

- schedule an hourly incremental/partitioned refresh,

- reports with live connection to the SSAS

- embed reports in our software and wrap authentication in our existing authentication

Is this a viable solution? Can the plan above be improved in any way?

Also, are there better ways to refresh the data, for example to combine live and warehoused portions of data?

Thanks for any tips.

[manalla]

Hi, Any update on this?

Do we have it in report server as of yet? I see some article for Jan Server version, but not accurate or i missed the documentation link.

Thanks

[Anonymous]

I too can not refresh data from Report Server.  The cursor finishes spinning, no errors are thrown on either the screen or in the log, BUT THE DATA DOES NOT REFRESH.

[Anonymous]

Replying to sdastous, I have the same issue running on development PBIRS licence, data can be refreshed in the desktop application but refreshing server side does nothing. Anyone know why? DirectQuery is to limiting for our application.

[mpo]

Just wanted to bump the topic to see if anyone has any more suggestions.

I'm especially interested in alternative appropriate configurations I did not think of (for example exactly like jcollinson2001 suggested).

[jcollinson2001]

PBIRS runs SSAS behind the scenes with a built in mechanism for scheduling data refreshes. Unless you need control over your tabular models or have other constraints (see below), then I'd drop your plan to host SSAS tabular and just use what PBIRS gives you with your reports running in "Import" mode.

Contrary to my advice, we have actually implemented our own SSAS Tabular instance in a similar architecture to what you've described. Because our finances are limited and we can only afford PBIRS under an SQL EE with SA licence for 4 cores only, we came to the conclusion that the server running PBIRS/SQLEE would not be fast enough to handle our usage requirements, so we implemented SSAS under our SQL Standard licence on an extremely fast server.

If money were no object, I would not have done this. I would of used PBIRS out of the box.

[Bubbler]

Assuming you drop the SSAS plan and keep only PBIRS over an SQLServer EE with SA, Is it possible to run incremental refresh as in cloud Premium PBI Service?

If NO..... Is there any way to solve incremental refreshes over on premises data with PBI?

[Anonymous]

@Bubbler  Did you get your answer for using incremental refresh in Power Bi report Server yet? We are using PBIRS on premise and but I was unable to figure out how and what to do for getting this feature.

[Bubbler]

Yes. Microsoft support said that:
- This feature is only on preview stage.
- Works only on cloud

[Anonymous]

@Bubbler 

Thank you so much for your quick reply. So are you doing anything to resolve the incremental refresh issue?

I mostly use the import mode to upload all the data and at each refresh everything gets refreshed though 90% of the dataset does not chagne at all.

[mpo]

Thank you, that's useful.

What are the advantages of not using a dedicated Tabular SSAS?

We planned multiple reporst to share a data model.

Not sure if incremental/partitioned data refresh is possible in PBIRS hosted SSAS?

I also got an impression that SSAS tabular is easier to automatically manage and deploy for multiple environments.

[jcollinson2001]

Simplicity is a big advantage to not using dedicated SSAS.

There are also limitations within PBI when connecting directly to a dedicated SSAS - e.g. you're forced to create calculated columns and hierarchies within the SSAS model. The analysts on my team (fresh from their PBI training courses) can't stand this! They have to add columns to the SSAS model within SSDT, build the asdatabase, send it to infrastructure for deployment, then head back into PBI and refresh their data source. They find this cumbersome and want to work directly in PBI at all times.

Another reason for having a dedicated SSAS server (which I haven't mentioned before) could be if you need to scale SSAS/PBIRS differently.

[mpo]

Thanks again, this makes sense.