What do you do when you want to use the Visualizations of Power BI but management doesn’t want to send company data to the cloud? Well, you can implement Power BI Report Server! In this episode we discuss the differences in architecture with the on-premises version of Power BI along with the limitations and options this version brings.
“In my mind, Power BI Report Server is a superset of SQL Server Reporting Services. I’m fairly certain that a lot of the bits underneath are exactly the same.”
“That’s the price you pay of wanting a cloud service on-prem, so you’re always going to be a few months behind. I mean, they generally sync up every quarter, but you’re going to be lagging behind.”
“You can use Report Server to publish through the Power BI service, but you cannot do it the other way around.”
Listen to Learn
00:38 Intro to the team & topic
01:21 Compañero Shout-Out
01:37 SQL Trail Update
02:12 Do you want a podcast t-shirt?
02:50 SQL Server in the News
04:51 More details on the Power BI Performance Analyzer
08:16 Report Server is the on-premises offering for Power BI
11:02 Eugene lost power, but he made it back to the conversation!
11:50 Reporting Services and Power BI Report Server on the same server?
14:15 Power BI is the next version of Reporting Services
18:00 Report Server can publish through the Power BI service, but not the other way around
20:17 And then it will feel a lot like Reporting Services
22:10 Kevin’s experience with Power BI Report Server
23:22 Has anyone leveraged automated sending of reports? Cultural battle?
25:43 Other limitations Power BI Report Server
26:38 How data gets updated in Power BI Report Server
27:57 There is a hybrid option – using both the cloud and on-premises options
29:22 Closing Thoughts
Music for SQL Server in the News by Mansardian
Carlos: Compañeros! welcome to another edition of the SQL Data Partners Podcast! I am Carlos L Chacon, your host. This is Episode 174. I am joined today, whoop, by the magic ding of Eugene Meidinger.
Eugene: That’s why they call me Meidinger.
Carlos: And Kevin Feasel’s also with us today.
Kevin: So I am.
Carlos: Our topic is Power BI Report Server, and just before we started talking, we were talking about Texas BBQ, so hopefully we can get through this episode and stay focused on Power BI Report Server, because all of a sudden, I’m starting to think about BBQ.
Kevin: I just had lunch, so I’m good. A good lunch, too.
Carlos: Before we get into it, I have a shout-out. Mel Vargas shouted out from Twitter, saying he’d been away, his job is not letting him do so much in SQL Server anymore but came back and said hello, so thanks Mel, for saying hello. For SQL Trail updates, I don’t know that we’ve said this, yet, but we had a change. We’ve actually had to change our date. Long story. So the new dates are October 2nd to the 4th. We are a little slower getting this out than we would have liked, so I can’t remember exactly when this podcast is going to go live, but the tickets should be available shortly, but we’re still working out some details because we had to change the dates and then we had to go back and reconfirm our speakers, and then that’s been kind of a mess, so that’s part of the delay process. Okay, so for t-shirts, and I’m not sure if it’s the recording schedule issue, maybe compañeros, you’re just not that interested in t-shirts. But we kind of had an initial burst and I don’t have any mentions this week, or since our last recording, rather, and so I’m not quite sure what to do here. So if you want a t-shirt, hit me up, say “hey, I want a t-shirt” and I’ll send you one and I think we’ll just go from there. And then if I have a bunch of t-shirts, I’ll have to give some to Eugene and Kevin, and then, you know.
Kevin: That’s good, I need a new t-shirt.
Carlos: There you go. Okay, so SQL Server in the News. So not so much news, but taking a peek at the blog, it looks like there’s some, I don’t know, backup trouble in Azure. Not so much trouble. I guess these are both for Azure SQL Managed Instance. While lots of features, I know Rick Heiges in our last episode talked about all of the things that have come a long way, if you want to go outside of the automated backup process, it seems like you’re in uncharted territory. So for example, one of the issues they were looking at was taking a copy-only backup which would have enabled TDE. In essence, not supported. So you’d have to unencrypt the database and then re-encrypt it and so a lot of people are not so happy about that, to say the least. So just a little, again, it just continues to evolve a little bit, but some of the growth pains that folks are experiencing when they want to use Managed Instance.
Kevin: You know, on that topic, maybe I’m just out of it at this point, but I read Giovann’s blog post and said “this seems reasonable”. It’s the same on-premises, where if you have a database encrypted with transparent data encryption and you want to take a backup and restore it some place where you don’t have access to the key, it’s going to work the same way. The only way you could take that backup is if you got rid of transparent data encryption. I understand the on-prem difference is I can take a backup of the key.
Carlos: That’s right. Yeah, in fairness, yeah, so Managed Instance won’t allow you to take a backup of the key, currently, right?
Kevin: Yeah, and, okay, that’s understandable, but it seemed like this was a bit of a kerfuffle about something that is pretty straightforward.
Carlos: Sure. And I think the idea is that, yes, you would be able to have the key, and I think maybe because they put a blogpost about it and it was another couple of things about backup and restore, it just seemed to be, odd is maybe the word I was looking for, or a workaround. They presented a workaround.
Carlos: Yeah. Okay, and a piece of good news, I know we mentioned this earlier. I think it was Episode 173, we were talking about measuring performance in Power BI and I know we glossed over the Performance Analyzer, which had just become available, but now we have a bit more hands-on experience. And so Eugene, do you want to take us through that a bit?
Eugene: Yeah, so this is awesome. And I say that, in part, because it literally came out the same week that a customer I was doing work for said, “hey, can we fix the performance issues on this report?” It’s really, really great. So the short version is that you open up the pane, you tell it “start recording”, and then you go to a page or you hit refresh or you do whatever, and it’s a lot like the network analyzer for Chrome. So for each resource, it’s telling you how long it’s taking to load, and it breaks it down into three categories. It’ll be the DAX Query, the Visual Display and then Other, and Other’s kind of nebulous, but the DAX Query is how long it takes to get the DAX information back from the data store. The Visual Display’s how long it takes to render the Java script and HTML. And then Other’s kind of waiting on other things and some miscellaneous kind of stuff that you can’t control real well. And this is great because you may have a slow visual and it’s hard to tell what the problem is. So with this, I was able to identify, “okay, well for this page we’ve got like 40 things on screen, so there’s your reason it’s slow. But for this one it’s just the data model had some scope creep”. So first we had, “okay, we just want to be able to add up sales”, and it’s like, “well, we want month-to-date, year-to-date, and quarter-to-date, so we want to select it for that.” “Well, we also want to be able to select whether we’re dealing with local currency or USD.” “Well, we want to try and make it go faster, so we want it to hit these aggregate tables first, and then these other ones if those aren’t filtered” or blahblahblah. So it’s really great to be able to say, “okay, is it just there’s too much stuff on screen, or is the DAX slow?” And if the DAX is slow, you literally can just hit Copy Query instead of having to hook up Profiler to the secret SSAS service on the back end. You just hit Copy Query, and then you can use DAX Studio to re-run that query manually and get an execution plan and all of this kind of stuff. So this is a really nice tool if you’re working with Power BI.
Carlos: Yeah, that does come in handy. I mean, the ability to see inside the process is very, very handy, so at least then you can point it some direction and anything that can help you avoid the, “well, it’s not my problem” kind of a thing. “Well, it’s not the DAX.” Very easy to get into some of those, particularly as we get layers of people who are writing the reports and who’s responsible for what.
Eugene: Well, especially, too, since one of the perennial challenges with Power BI is Microsoft said, “all right, we’re going to start from scratch and optimize this tool for Chris in Accounting and make it so that someone who’s an Excel pro can use this without any issue. And so many times they’ve kind of had to reinvent the wheel or do things that we’ve had for 20 years, since the 80’s in other tools. And so this is just another example that where we’ve had so many of these performance capabilities in SQL, and now we’re just starting to get them in Power BI.
Carlos: Sure, sure. Very cool. Okay, so our show notes for today’s episode will be at sqldatapartners.com/powerbi or sqldatapartners.com/174. Okay, and again, our topic for today is the Power BI Report Server, and ultimately, this is the on-premises offering for Power BI, and so if you don’t want to use the Power BI service, put your stuff in Azure, then you have the option. Now the trick is that it is only available through Enterprise, if you have Enterprise Edition with Software Assurance or you can buy Power BI Premium.
Eugene: Right, so either way you’re going to be spending tens of thousands of dollars.
Kevin: Well, if you already have that Enterprise Edition with SA, it comes for free.
Kevin: Think of it that way.
Carlos: That’s right. So there is an added benefit there.
Kevin: It’s not my money, so it’s free.
Eugene: I guess so. It’s like when you get first class and they throw in some chips and drinks and things. Yeah.
Kevin: That’s right, those are the fancy chips.
Carlos: Right, so we saw this earlier and I know lots of people have asked, are they going to merge or consolidate Power BI and Reporting Services? And in my mind, this is the first instance or iteration of that merge. And I think we’ll get into some of the other pieces here, surely, but ultimately, one of the other differences that you’ll have is that Reporting Services is the– I mean, there are some other pieces, you still get to use the .pbix file and whatnot, but from a hosting perspective, if you’re familiar with Reporting Services, you’ll feel very familiar with Power BI Report Server. One of the other things you get when you install it, you don’t need a database server, per say, but like Reporting Services, you’re going to have to have a database to point it to once you install it, and you’re going to end up with a Power BI Report Server on the server wherever it gets installed.
Kevin: Yeah, which is a Reporting Services instance.
Carlos: That’s right, which is ultimately a Reporting Services instance, yep.
Kevin: Which makes it really interesting if you have both of them installed on the same server.
Carlos: Yes, that’s right. Oh, you’ve done side-by-side?
Kevin: I have not. We use Power BI Report Server. Reporting Services is installed on different machines, but I can just imagine the fun of trying to install both of them concurrently.
Eugene: Sorry about that.
Carlos: And he’s back. Yeah, so I just got a text, and I’m like, “oh, I think he’s–” I saw it flicker and it was Eugene and I’m like, “that’s strange” and then I noticed he wasn’t on the call.
Eugene: Yeah, remember that whole flood warning? Had the power flicker on and off. So I’ve switched to the hot spot on my phone and I’m on my laptop.
Carlos: Oh, called back and–
Kevin: This is what Eugene does for you people in the audience. You should deeply appreciate him. He is literally risking his life. It is like one of those CNN reporters who’s out during the hurricane.
Carlos: Yeah, that’s right.
Eugene: Yeah, it’s exactly like that. I’m embedded.
Carlos: Yeah, all so that we can get you to talk about Power BI Report Server. Okay, so we were talking a little bit about potentially installing Reporting Services and Power BI Report Server on the same instance, or on the same server, rather. Have you ever heard of anyone doing that, Eugene?
Eugene: No, and there’s really no reason that you should need to.
Carlos: Either use one or the other? Well.
Eugene: No, no, so in my mind, Power BI Report Server is a superset of SQL Server Reporting Services.
Carlos: Sure, that’s right. Because you can host both Power BI reports and the RDL files.
Eugene: Yeah, and I’m fairly certain that a lot of the bits underneath are exactly the same. So I think of Power BI Report Service, they needed an on-prem solution for Power BI, because that’s what the customers were asking for and they designed by user voice ticket and it was the number one ticket back in, I want to say like 2016, December 2016, because I was like, “they’re never going to bring it on-prem” and then a Microsoft friend of mine was like, “yeah, they are.” And so I think they said, “okay, this is the quickest way to get there.” And it’s funny because so often you’ll find that you can change around the branding if you know where to look. So, an example, they announced like, I forget the naming. It’s like Power BI Paginated Report Builder or something.
Eugene: Literally SSRS report designer, report builder, and there’s like some flag that David Eldersveld was able to find where you could change it and it had the same branding as 2019 CDP II.
Carlos: Oh, interesting.
Eugene: I’ve done the same thing with Power BI Report Server. So I had the issue where on my course on how to deploy stuff, I had to do these demos, and I was like, “you know, I really don’t want to install it twice” and I found out that for the branding piece– before, I was just going to change the title and change the logo like by hand and just pretend I was dealing with one or the other. But for the front end, at least at the time, they were using Angular and if you look at the code, you can see that it’s like Power BI Report Server branding. There’s like some flag and I was able to change it and suddenly it was SSRS in terms of the look and feel. So I’m pretty confident that underneath the hood, it’s all the same bits. I would think of it more as an added module to support Power BI and different licensing.
Carlos: Yeah, that is interesting, and I guess now it’s as good a time as any to talk about– and I guess we’re not prognosticating, here, but this idea of again, the merger of the two. Eugene’s point was, “well, why install both?” And I think that at some point, because Power BI has introduced paginated reports and now Report Server can host Power BI reports, there will be some consolidation. There’s been a lot of– although we did get quite a few updates with Reporting Services 2016, some of the questions when Mobile Reports was the big one. It almost does seem now, in hindsight that they were just developing Power BI stuff and then tossed it into Reporting Services, and that Power BI is really going to be the next version of Reporting Services.
Eugene: Yeah, I mean I feel that way. So I think it’s illustrative to compare this to DAX vs MDX. So the impression that I seem to get with MDX and basically the old Multidimensional mode for SSAS is that it’s effectively dead. That you have organizations that still have a significant investment in it and they’re not going to just like pull the cord at any moment, but you don’t see Multidimensional for SQL Server or Azure Analysis Services. You don’t see a ton of updates. There’s still features it has, like say Writeback and other things that DAX doesn’t have, but it just seems like all Microsoft’s development energy’s being poured into DAX. I don’t see the exact same issue with paginated reports, in the sense that it was dormant for a half a decade. The time between 2010 with SQL Server 2008 R2 and up until 2016, nothing changed.
Carlos: Nothing changed, that’s right.
Eugene: They added better support for Chrome as a change, so it wouldn’t show up blank, because I know that if you try to look at 2008 R2 reports in Chrome, it doesn’t show up, but other than that, like nothing changed. And then they kind of got forced to make some changes to their rendering engine because of the whole HTML 5, but probably also to better align with Power BI. But the thing is, though, I do see marketing for, okay, Feature Friday with Paginated Reports in the cloud and I see them investing effort. And then the other thing I’ve been working on this book about Power BI for the SSRS developer and I think Power BI represents a whole different paradigm from a company cultural perspective and a design perspective, because back when Power BI came out, Satya had just taken over, and his motto for the company was “Cloud first, mobile first.” And I think that’s changed to probably “Cloud first, AI first” these days, but it explains a lot when you think about Power BI, because it was designed to support a much broader range of resolutions and screen types and interfaces. And trying to build Paginated Reports off of that is a giant pain, and it took us three years before we had any way to export to PowerPoint or PDF or print or anything. So I feel like it’s going to be a long time before that gap is adequately filled, and I think they’re trying to make Paginated Reports work to be able to fit that. But yeah, so instead of like DAX versus MDX where it’s like 90/10 in terms of Bellman Effort, I feel it’s maybe closer, like 70/30 or 80/20 with Power BI and SSRS.
Carlos: Yeah, yeah. So it’ll be interesting to see how that continues to play out and now, the other major difference that we currently have with the Power BI Report Server is the version or the– I want to say version. So if Power BI Desktop optimized for Power BI Report Server.
Eugene: Yeah, which means two revisions behind. That’s for sure.
Kevin: Pretty much.
Eugene: Yeah, hey, that’s the price you pay of wanting a cloud service on-prem. So you’re always going to be a few months behind. I mean, they generally sync up every quarter, but you’re going to be lagging behind. One of the good things, though, is that that optimized version still works in the cloud.
Carlos: Oh, have they gotten that? My understanding was at one time they were completely separate and weren’t compatible.
Eugene: I guess I’ll have to double-check on that, but I’m fairly certain that you can publish to either with the optimized version.
Carlos: Okay, because it is interesting, so one of the reasons that I guess the indications that I thought that was still true was because the way they want you to install the Power BI Desktop through Reporter Server is actually on the Report Server home page.
Eugene: That is true.
Carlos: So they’re not telling you to go download Power BI Desktop from the web, so it made me think that they were–
Eugene: Well, I think that’s because they’re pretty aggressive about not providing older versions of Power BI Desktop.
Carlos: Ah, ta da.
Eugene: It’s May, right, and they’ve pushed out the May version. If you want to get the April version, you’re on your own.
Carlos: Right, right.
Eugene: Like, I do not know of a good way to go back to an older version. So yeah, I would have to double-check, but I’m fairly certain. I feel like I’ve done this before. You can publish to the cloud as well. It’s just that they don’t want you using the regular version of Power BI because it’s not going to necessarily be supported in Power BI Report Server.
Carlos: Very good. So, you can use Report Server to publish through the Power BI service, but you cannot do it the other way around.
Eugene: Right, in terms of which version of Power BI Desktop you have, right, right.
Carlos: Okay. And so those are the two big differences, but from there, it’s going to feel a lot like Reporting Services from here on out.
Eugene: Oh, tremendously.
Carlos: You so still get the Power BI, you know, there’s a look and feel to it, but like you just mentioned, it’s almost like a theme change, but it still looks very similar in the sense that you have the folders and you can publish data models, etcetera, etcetera, from an administration perspective.
Eugene: Yeah, it’s very, very similar in that respect.
Carlos: The nice thing is that they still make the mobile apps. You can design those in the Power BI Desktop and at some point I was a little afraid that you were going to have to kind of go– I probably shouldn’t say revert back, but leverage the Reporting Services Mobile Reports and it looks like they’ve found a way to make that seamless to us, so that’s nice.
Eugene: Yeah, that is probably worth looking into more, Well, can you clarify when you say you think they’ve made it seamless? Help me understand.
Carlos: So you can still use the phone report layout in the desktop to create those reports. So the publishing experience is still the same, whereas you don’t have to use Reporting Services to then create mobile report versions of your reports.
Eugene: Got it, okay, yeah, that makes sense to me. I think the whole Reporting Services Mobile Reports is still a thing and it’s still a separate sort of thing. So I wasn’t sure if you were saying that they had merged the two, but I think they still have the two separate products. Like the thing that used to be Datazen that they kind of merged into SSRS and then separately mobile view for Power BI.
Carlos: So let’s see, so Kevin, you’d mentioned that you have kickstarted this thing. Any thoughts or things that we should be talking about, here?
Kevin: Yeah, so I have used this in production scenarios. Most of our reporting dashboards are built off of Power BI Report Server, so like has been mentioned here, you have very much the same experience that you would get with Power BI Desktop or going to Power BI online, except for a couple differences. For example, you’re not going to be able to get real-time updates. Instead, similar to Reporting Services, “well, I can schedule data refreshes.” So that’s my hacky version of a close to real-time update where every couple minutes or so, I’ll refresh my data sources and see what the current status of services is. But once you get past that, the experience is pretty smooth. You can use the same set of skills that you’ve developed around Power BI development. And even though it is a lot of Reporting Services under the covers, the feel is still Power BI-like.
Carlos: Sure. Now one of the other big features that Reporting Services had that Power BI reports didn’t have, which sounds like they’re starting to close the gap there, and that is on the idea of like a subscription or an automated sending of reports. Is that something that you guys have leveraged, as well?
Kevin: No, I have not. Yeah, all of my reporting is intended for current view as opposed to alerting.
Eugene: Yeah, I haven’t done– back at my last job I did a ton of subscription-based reports for SSRS. Haven’t done anything with the Power BI kind of side of things, although I think there’s definitely value in it, because I know we have– there’s one customer that wants this kind of daily sales report in the Power BI Service, which has just started to be adding some of those kind of scheduled capabilities.
Carlos: Right. Yeah, it’s amazing. I think part of that is just a culture that instead of having a link that I can go to, to see the report any time I want, I need to have it in my inbox. And maybe that’s just a link being sent to me, but it is a very powerful cultural need, if you will, or desire that folks have, still.
Eugene: Yeah, well, you see that cultural battle with Office 365 in general, because at my last job, we got rolled under the SharePoint team, and I know in the SharePoint world, it was a big kerfuffle that when you moved to Office 365, you didn’t get much in the way of custom branding. You didn’t get a custom URL. You were sqldatapartners.sharepoint.com, not sqldatapartners.com/sharepoint or whatever. And a lot of that was for administrative reasons, but you know, there’s kind of these cultural ripples of, “no, Office 365 or powerbi.com is the place you go to, instead of kind of visa versa.”
Carlos: Right, right, there are still some of those type of battles that you may have to face. Which, I guess, so having said that, your landing report, you’re still ultimately going to server/reports, right?
Carlos: So if that is the experience that you’re used to, then that makes a very straightforward to keep going there. Okay, so I guess other limitations, maybe that we should talk about or other features we haven’t covered?
Kevin: Sure, there are limitations that I run into around mostly– the one that pained me the most was automated real-time updates, because I like seeing those real-time dashboards on Power BI online, but we weren’t about to put our data out into a publicly available– or in a cloud format that we were potentially putting at risk, somehow, so internally, the answer was, “nope, you can use Power BI Report Server.” There are a couple of limitations. Nothing too huge that I remember. Yeah, some data sources, I want to say there are limitations around Direct Query, but I don’t think we even use Direct Query in general.
Carlos: Okay. One thing we haven’t talked about, so there’s the Power BI Service, we have the idea of the Power BI Data Gateway. So that doesn’t exist in the Report Server option so how then does data get updated in the Power BI Report Server?
Kevin: The way I do it is through basically generate a scheduled report.
Eugene: Yeah, but behind the scenes, it’s creating a SQL Server Agent job to schedule the data refresh, that sort of thing. But yeah, they have built-in capabilities for handling that.
Kevin: And that works pretty well. If it fails, getting details on why it failed is not always the best, especially if you have something that’s failing, like it’s trying to run every few minutes, but you can usually discern what’s happening. The Agent log will tell you the first 38 characters of what is a really useful text at like character 39, but then you dig further down and find in the logs what’s actually going on.
Carlos: Sure, sure, so that would be another difference, because you’re using the Agent, that from a refresh perspective, you can get away with many more updates. Or of course, I guess you’re at Premium at that point, so you do have a few more options. We should say that through going the Premium route, you also– well, Kevin’s– they’re not using it. You do have the hybrid option, and I guess this is what we were talking about earlier, about being able to use both the cloud and your on-premises option, should you want it to go that way, for whatever reason.
Eugene: So, it’s worth mentioning that there’s kind of an in-between option in terms of, “okay, I want to limit how much data is out in the cloud.” Microsoft, if you go search for Power BI white papers, basically, they’ve a bunch of white papers, but one of them is on their security model in general and how they kind of architect things. And my understanding is that if you stick to Direct Query or live connections in the service, in the cloud, then none of your data is stored at rest. Now, you still have the issue with your connection strings and all that kind of stuff, so it’s not perfect. But my understanding is that, basically, the summary data needed to display the report is stored in memory, but none of it’s stored at rest, so if someone takes a forklift and steals the server that was rendering your reports, I guess they still may have your connection string, but they don’t have your data itself. So it’s not perfect, but there’s an in-between option available.
Carlos: There are some safeguards, if you will.
Carlos: Okay, good deal. So that’s all I had. You guys have anything else?
Eugene: No, I mean really, a lot of it’s just going to come down to is either you need your data to be on-prem, or you have a high skill-base in SSRS. And if one of those is true, then this makes a lot of sense. Otherwise you just use the online service, because everything is going to start out there. They’re building the features there, and then every quarter bringing them back down to Earth for the rest of us mortals.
Carlos: Yeah, so I think a lot of that depends on your licensing as well, because to be able to dip your toes in the water, Power BI Service gives you that ability. You don’t really have that opportunity with Power BI Report Server.
Eugene: Well, I mean, so, largely–
Carlos: I guess there’s an evaluation version.
Eugene: Yeah, right.
Carlos: I guess you have 180 days, I guess, so there is that.
Eugene: You have that. They have a VM in Azure along the same lines, so you can spin that up and play around with it and then just shut it down.
Carlos: Right, right. Use your credits that way.
Carlos: Okay, good deal. Well, I think that’s going to do it for today’s episode, compañeros. Thanks again for tuning in. As always, let us know what you’re thinking about, what you’re talking about. You can hit us up on social media. Eugene?
Eugene: Yeah, you can find me at sqlgene on Twitter, or sqlgene.com for my blog.
Kevin: You could find me, but only if you’re on my Report Server.
Carlos: And compañeros, you can reach out to me on LinkedIn. I am at Carlos L Chacon, and we’ll see you on the SQL Trail.