On-Prem vs. Cloud?

[Candaz007]

Hi,

We are just about to embark on our PowerBI journey after doing some preliminary POC demos late last year.

Since we already use SSRS quite a bit, one question we are trying to get clarity on is what are the main differences between deploying PowerBI reports on-prem vs pushing them to the cloud (costs aside)?  Are there any key functionality drawbacks for either option?  Is on-prem just a 'temporary' solution to use as a stepping stone to get us into the cloud as an end-game?

- edit 2018/01/23:

Also, can we run Report Server without purchasing Power BI Premium?  I understand that with Premium the Report Server in included, but I am assuming we can licence Report Server on it's own (if we don't also get it included with either our SSRS or other corporate Microsoft licencing)?

Are BI Pro licences for content producers sufficient (with others that will only be consumers having just 'regular' Power BI licences)

-/edit 2018/01/23

Any feedback is welcome and appreciated!

Thank you!

[Anonymous]

Hi everyone,

My BI team is considering to switch from on premises/HPE VM to Azure Cloud. At this point we have tried both on premises/HPE VM options and we have encoutered issues like space on C drive, not being able to schedule automatic refresh, speed for running queries and getting the data, low memory error message etc. 

In terms of connection type we are using PBI direct query trough ODBC to the database, the ammount of queried data being really huge (app 7 million lines).

Do you think that switching from the above mentioned infrastructures to Azure Cloud would be a smart move?

Thank you,

Lorena

[des]

I have used both Lorena, previously PBI Report server on prem, and now most recently as a service at my new job in Azure.  For the amount of data you are talking you could use either, also I don't envisage any issue with bringing 7 million rows into the cloud either through data factory setup or on premise gateway, we are doing billions of rows without issue with Data Factory.  There are so many other reasons to switch to the cloud, mainly it is a more featured product and you are constantly getting the latest and greatest updates.  Also providing you provision the cloud portal correctly (memory, disk space etcc) in the setup phase you will not have any of the performance issues you describe.  Auto refresh in power bi as a service is just standard and should work without issue.  The main issue for you would be cost.  When I last checked, Power BI on prem (report server) has the big advantage of being free providing you are using enterprise edition sql server, all you have to pay is a token amount for hte power bi desktop user licenses, if you shift to the cloud you will need to pay, how much will depend on what options you choose.

[Anonymous]

Hi,

We use the On-Prem server extensively, without it we would not be able to use Power BI in any meaningful way as we are not able to put our data in the cloud. This is due to governance and licensing issues.

We have software assurance on our SQL Server licences so we can use the on-prem server under those agreements without incurring further charges, something that for us is a major win. The Cloud based deployment can get very expensive if you have a large number of users needing access.

We also have an existing SSRS investment that the on-prem server allows us to integrate with.

Generally we are finding the Report Server a stable and useable option, we are currently on the Oct 2017 release. There are a few problems, some bugs, and missing functionality, but on the whole it’s a working solution.

We are however concerned that the PBI team are not forthcoming about the release schedule for Report Server. The cloud version gets monthly updates, so far the on-prem version is 3 months out of date with no upcoming catch-up release date announced. I have post requests for an update but they have gone unanswered. The only indication I have been given is that a new version will be released “towards summer time”, that’s not good.

In summary, the on-prem server is good, and we are using it extensively. But it needs a more open and professional approach from Microsoft when it comes to a roadmap for future releases.

Rob

[des]

Rob another question,

I have since downloaded the power BI desktop client, in the fifth paragraph you mentioned that the release schedule for report server is not on par with the cloud version which gets monthly updates.  The way I understand it you need a power BI pro license to publish power BI reports to the on-premise server.  So I downloaded Power BI Desktop and it works without issue, I can save my rerports to the server I have setup.  I seem to be getting updates though very regularily for the power BI desktop product, monthly I believe as you mentioned.  When you mentioned that the report server gets three monthly updates are you referring to the server component not the desktop client tool used to design the reports?  If so have you found that the implication of this is that if some feature is realeased in power BI desktop, say a visualisation for example, that has not yet been released in the 3 monthly server update does it just not work when you deploy the report or does the option to use the new feature just not appear in power BI desktop?  Have you found this to be an inconvenience, or am I completely off track with how this works.  thanks again. 

Des

[Anonymous]

---

Hi @des,

The Power BI Desktop and PBI RS come as a matched pair. We use the latest versions available released Nov 1 2017. You are recommended to only use the PBI desktop designed for use with the RS, and with the same release version. 

It sounds like yo are saying you are using the normal PBI Desktop for the cloud for building PBIX files to publish to the RS. If so I'm surprised this works. I think you are dancing on thin ice if this is so, likely to hit deployment errors at anytime.

As to licensing, you need either a power Power BI Premium subscription or a SQL Server Enterprise license covered by Software Assurance to be covered to use the PBI RS on prem.

And you need Power BI Pro lic to publish PBIX from desktop to PBI RS.

---

[des]

Robert,

Thank you again for your clarification, I have been busy testing pre implementation so haven't checked back for a while, what you said makes complete sense.  So basically every 4 months, I believe this is what MS are commiting to at the moment you need to update server side software as well as you Power BI design clients software, as you mentioned the Power BI RS version not the normal cloud client S/W I was using.  I have since dowloaded the correct and latest one from here https://www.microsoft.com/en-us/download/details.aspx?id=56722 and as per your clarifiation the option to save is now back.  I can only guess that at some point I installed the RS power BI client design tool (which had the save as to server option), then installed the cloud one (which got rid of the option), and now after installing the version above it's back as it should be.

I assume that having to do this matched pair style release might exlude us from being able to use the dowloadable "import from marketplace" custom visuals? Or can you only download some in line with the current Power BI RS client version?  Is that your experience? If so that's a shame. 

I found an explanation here as to why cloud and RS can't be kept in sync.  https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20469499-updates-to-report-server... certainly a valid explanation but still unfortunate for on-premise implementations

thanks,

Des.

[Anonymous]

---

Hi @des,

Glad the info was helpful.

You are right that some of the custom visuals do not work under the On-Prem server, but others do, I'm not sure how you find out if they do or don't other than by trying to build and deploy a test PBIX. For example the ArcGIS mapping visual does not function. I have posted questions about this, but had no real response from MS. I think it is likely that where a custom visual is developed by a 3rd party who wants to sign users up to a monthly subscription in order to use the "upgraded" edition of the visual then it is likely to remain a cloud only visual.

Regards
Rob

---

[des]

Thanks good to know,

For the custom visuals that you have used that do work did you have to install a server component or does it just get taken across when you deploy it to the server?

thanks,

Des

[Anonymous]

The custom visuals get rolled up into the PBIX file as you deploy. So they get extracted back out and rendered when the user views the report. No need to install anything on the server.

[des]

Hi Rob,

Was just wondering if you wouldn't mind answering acouple questions based on your experience.  After much deliberation, we are currently just about to implement an on-prem solution. I noticed in your post below you mentioned in the second paragraph you have software assurance on your SQL server licenses and therefore you can use on -prem under those agreements, I was aware of this however the way you mention it seems to imply that if you had decided to use the product in the cloud you would have had to pay extra on top of the S/A to use the product in the cloud.  is this correct?  I had thought that if you had assurance you get to use Power BI on prem or in the cloud without incurring extra cost, after reading your post I am thinking that is not the case can you shed any more light on this?

In the thrid paragraph you mention you have a SSRS investment, we do too, when you say the on-prem server allows integration do you mean that you host your SSRS reports on the power BI server? We currently use share point for hosting SSRS reports and before that SQL server native when we had standard edition.  So I am interested to know if we still need to use sharepoint to host our SSRS reports, or if once we deploy the on-prem power BI solution we can deploy our SSRS reports there as well and have everything in one place.

Thanks in advance

Des

[Anonymous]

---

Hi @des,

Happy to help, but a little caveat,  I am not a MS Licensing expert, so you need to check this with the people you buy your licenses from or Microsoft direct maybe. I'm looking at the SQL Server 2017 Licensing Datasheet ( https://www.microsoft.com/en-gb/sql-server/sql-server-2017-pricing ) as I write this.

My understanding is that if you Buy a SQL server 2016 / 2017 Enterprise License with Software Assurance then you are allowed to download and install the Power BI Report Server on the same server you have the SQL Sever installed. It has to be Enterprise and it has to have SA. This is what we have done, and it works fine. But this is only On-Prem, as I understand it.

To be able to use PBI in the cloud you need a different Cloud based subscription, if you want to have both On-Prem and Cloud I think you best option is to purchase Power BI Premium  (  https://powerbi.microsoft.com/en-us/power-bi-premium/  ) this gives you both the On_Prem Report Server and the Cloud Based service.

With regard to the SSRS (or paginated reports as they are referred to now !) yes we have deployed both the PBI and the SSRS reports to the same PBI Report Server. We have actually gone a stage further and managed to dynamically link SSRS reports (with parameters) to PBI reports.

Hope this helps, happy to answer anything else if you need more info.

Kind Regards

Rob

---

[Anonymous]

Hi Rob,

We are trying to link SSRS reports to PBI reports dynamically with parameters as you have mentioned. So far, I have only come across the option of dynamically generating URLs with slicer values to connect to PBI from SSRS, but I am not sure if this is the best way to do it. Would you mind sharing how you did it?

Regards,

Sharmin

[Anonymous]

Hi @Anonymous,

Happy to pass on the technique we use.

Essentially the trick is to use DAX. We create a calculated column in our data model that is a concatenated of the SSRS report URL and the parameters we wish to pass, job number, person ref etc.

We then mark that column as a web link / URL and on the PBI dashboard we include it in the table etc. As its a link when you click on it PBI navigates to the link,and that then opens SSRS report viewer.

if I remember right there is a "Guy In A Box" Youtube video that explains it, probably better than I have.

If you get stuck come back to me and I'll include a bit more detail.

Rob

[Anonymous]

@Anonymous Great, thank you!

[R_hambleton]

Has anyone compared the total costs of the On-Prem vs. Cloud pricing for Power BI?  I've seen the online calculators, but they seem to assume your are going for the $5K/option and we don't have that many users.  Thanks in advance for any feedback.

[des]

Thanks Rob,

That was very helpful.

Regards,

Des

[Anonymous]

We use both and I think each has a purpose. Some of the advantages of the on-prem server (besides cost) are that if you already use SSRS then you probably have a security model in place that you can leverage. I think it's easier for users as they only have to go to one place for their reports (depending on your setup). It works really well connecting to on-premises datasources. I like the report server branding option, and the ability to monitor report execution by querying the report server database.

Some of the drawbacks and issues we've had:

• it can be a little buggy. I got to work one day and all of our connection strings were displaying in chinese (literally) and wouldn't work. We had to restart the service. Other times we couldn't upload reports and had to restart the service.
• It doesn't have all the same features of the web service. No dashboards or QA.
• You have to use a different version of PowerBI desktop to publish to the report server, and it's a few months behind the normal version in terms of features.
• We've had issues refreshing data from online sources. E.g. OAuth2 isn't supported so we can't schedule refreshes from SharePoint online or OneDrive for business.
• Depending on your setup, you can only access the reports on-prem.

That being said, we're planning to expand our use of the on-prem server, but we still use the cloud service for any reports sourced from cloud data sources, or anything that needs to be accessed from anywhere.