Welcome to the Rencore Tech Talks show. In this episode, I’m catching up with John White on SharePoint 2016 and the BI workloads.
[Recorded: 2017-01-18]
Listen to this show to learn more about things like:
- Excel Services, what’s going on here?
- PowerBI, where does that fit in?
- What is Azure Analysis Service?
- Admin Audit Logging (My favorite!)
- And more
Guest: John P White
John P White is the Chief Technical Officer and Co-Founder of UnlimitedViz, the makers of tyGraph. He holds a Master’s degree in Engineering from the University of Guelph and has been a Microsoft SharePoint MVP since 2010. He has spent far too long (since 1990) in the Information Technology space and possesses a skill set that spans both architecture and development. He has been instrumental in delivering projects and applications that have been recognized with both local and global awards from Microsoft and IBM.
Show notes:
- The White Pages (John’s blog)
- What’s deprecated or removed from SharePoint Server 2016 (TechNet)
- Microsoft Power BI
- Azure Analysis Services
Full transcript
[Tobias] = Tobias Zimmergren
[John] = John P White
[Tobias]: All right, welcome John, to the show.
[John]: Well, thanks for having me, man. It’s been a little while coming.
[Tobias]: Yeah, I know! We met up at the MVP Summit last November and said it would be fun to catch up over a podcast episode and I guess here we are.
[John]: I think it was originally over a beer but hey, we take what we can get.Tobias: Yeah, exactly! I think we discussed a bit about SharePoint and BI workloads and what’s changing because that landscape is like changing every single day.
[Tobias]: Yeah, exactly! I think we discussed a bit about SharePoint and BI workloads and what’s changing because that landscape is like changing every single day.
[John]: Yeah, I mean the BI stack is moving very, very quickly. It’s refreshing for me, especially who’s been involved in BI and SharePoint. I’m one of the few people who straddle that massive gap between the two product teams.
[Tobias]: Yeah, and I get a lot of questions from people about this stuff: What’s happening to this thing in BI and I’m like, I used to work with that but there’s so much now, it’s impossible for me to answer that. So I thought hey, let’s go with John White because he’ll know the answer to that.
[John]: Well, I’ve been living it and trying to make it work and both at the same time last year with all the preview bits of SharePoint 2016 and I was like, OK, let’s see what this stuff looks like in 2016 and it’s a radically different. At least it is on the backend and not so much on the front end.
[Tobias]: Right, I guess that’s what we’re going to dig into. One of the common questions that I get and that I, of course, wonder myself not being in the BI stack for a while is I know some big customers that I used to work with. They use Excel Services, but some of the stuff in Excel Services is being deprecated, I know, in SharePoint 2016. For example, you need to have the Office Online server with Excel Online if you want to continue using any kind of Excel Service functionality the way I understand it. So what does that mean for a customer running it today?
[John]: Deprecated is a nice way of putting it. Removed completely is really the situation. It’s not there anymore. So yeah, if you go back to SharePoint 2007 if you remember – I like to throw this into my “what’s changed talks” when I do them – but if you go back to the old 2007 SharePoint you remember that thing, you know. SharePoint in the middle of the world with the six main workloads hanging off of it. One of those workloads was BI. And this was defined by the Office team at the time. And what they really meant was Excel Services. So that was one of the major aspects of BI. SQL fills in the rest of it with SQL Server Reporting Services, PowerPivot for SharePoint. Those are SQL products.
PerformancePoint was the SQL team that got moved over to the Office team. Well, it stayed the same since then pretty much. It hasn’t changed in an appreciable way, but those are kind of the four major pillars in the SharePoint BI world. And it’s certainly the major one from the Office team and now it no longer runs on SharePoint.
So what they have done and when they first put the announcement out, everybody just freaked out. Excel Services is deprecated in 2016, what? And what they said was…
[Tobias]: The time invested, all the money invested, right?
[John]: Yeah, and they said it’s being replaced with the functionality in Excel Online Server. And they didn’t bother explaining at the time that Excel Online Server was simply the renamed Office Web Applications Server. Everybody figured we were going to drive under the cloud. That’s not the case.
So yeah, what they did, was they took all the capabilities that were in. Well, most of the capabilities. And certainly, all of them from an end user standpoint save one from Excel Services and put them in what we used to think of as the Office Web Applications Server and is now called the Office Online Server.
My buddy Jason Himmelstein is fond of calling it “Office Online Server on-prem. OOSOP, but that’s not an official name. The theory is, they just lifted that capability and stuck it on there. What that does is make Office Online Server mandatory, if you’re doing BI in SharePoint. It used to be an option.
But we also used to have these two things. We had Excel Services on SharePoint and we had this Office Web Application Server. And when it came time to rendering Excel workbooks, we had to choose one or the other and that was a farm level setting.
So if we needed the capabilities of doing say power for the data models and the internal models, you had to tell that your server farm use Excel Services for rendering Excel workbooks not Excel Online and those are two different things. Now it’s been unified and in a single platform and that’s all to the good if you ask me. But from a server administrator standpoint, it is more complex to do.
[Tobias]: Yeah, so it’s TLDR that is. You can still use a lot of the functionality, but it looks a bit different in the setup in the architecture on the backend. Right?
[John]: The architecture is wildly different now because we used to be able to count on the connection to our backend data system. We still have the double hop problem in original SharePoint, but now we have a triple hop because we have the SharePoint server now needing to communicate with the Office Online Server, which in turn then communicates with your backend data source in a connected data fashion.
And it gets worse if we’re trying to use connection documents. ODC files or something like that that are sitting in a SharePoint library, because you make that connection with the Office Online Server, the Office Online Server then needs to be able to read that ODC file back out of a SharePoint library so it has to have permission to do all that stuff and from there it then, in turn, makes its connections to its data source so it gets pretty complicated pretty fast.
[Tobias]: Wow! I guess that’s something that admins will need to be aware of but also like from a user perspective, if I’m a user of SharePoint all the technicalities aside what – like functional wise if I don’t think about think about double hop, triple hop connection issues set up anything like that but just as a user, do I still have workbooks in Excel the same way I used to do? What is the user experience and what differs for the user, if we disregard all the technical aspects of the backend.
[John]: It’s an excellent question. If you do your job right as a SharePoint administrator, users won’t notice the difference. The only difference they’ll notice is the top of the screen when they’re viewing a workbook full screen will now say Excel Online. It will no longer say Excel Services.
To that end, Microsoft in Office 365 made that switch about a year and a half before the release of SharePoint. I’m guessing it was about a year and a half before the release of SharePoint 2016 on-prem Server and I didn’t notice for months. And I pay attention to this stuff.
It really should be the same thing from an end-user standpoint. It’s not much. I have a talk on what’s new in BI and SharePoint 2016 and it’s a whole lot of technical mumbo jumbo. At the end, it’s like there’s really nothing to demonstrate because it’s exactly the same.
[Tobias]: Right, I guess that’s important for the user to know that and also for the technical fellow implementing this stuff that the user adoption is not going to be the biggest thing because that’s normally the problem I’ve seen in a lot of Internet projects specifically. Whatever we implement, it can be the technically most awesome and most savvy solution, but if the users don’t get it, you know, it’s a fail.
If there’s no change in the UI or little change, that’s even easier for the user to just continue working even though you replaced everything that happens on the backend.
[John]: Yeah, I mean that goes alongside of if you look at SharePoint 2016 in general. For the most part, there were very few changes to the user interface itself. Most of the changes were architectural and that’s certainly the case here. It’s not a lot of fun to demonstrate, but it does set the stage from an architectural standpoint.
[Tobias]: Yeah, that’s awesome. So another thing that I know was recently announced was the January technical preview of SSRS or SQL Server Reporting Services vNext or VersionNext. This was made available yesterday as of this recording. What’s the deal here? What’s that all about?
[John]: Well, I’ve tested it fully by now. I could honestly promise you I’ve read the blog post.
[Tobias]: Yeah, okay. Fair enough.
[John]: What’s the big deal here? I’ll start out by telling you what the big deal is. It includes what you might want to think of as Power BI on premises, which is a big deal. People have been asking for this for years and it gets into a discussion of… I won’t dive into that discussion, at least not yet. But what Reporting Services is all about. But in a nutshell, Microsoft has decided that SSRS is its platform for delivering reporting on premises.
In the cloud, it’s Power BI, but on premises it’s SSRS. And if you look at the way they define reports, both of those platforms should be able to provide the same capabilities. They don’t today but they’re converging. And what’s so exciting about SSRS vNext and this preview is a standalone. You don’t actually have to have SQL vNext to run it, but it does have a set of requirements that you’ll want to go check out.
What’s so exciting about it, is in addition to running RDL files like you’ve always been able to do or RDLX files, which are the mobile format that we first saw in Reporting Services 2016, you will be able to render Power BI files. And that is exciting.
That is it basically and there’s no trickery going on here. You’re not connecting to the cloud. You don’t need a Power BI account – none of that stuff. You can totally not have an Azure Active Directory identity. Not that I recommend it. But you could get away with not having one at all and using the capabilities of Power BI via SSRS on premises.
[Tobias]: All right, so it’s just using the same engine to render the data and show it to the user. Right?
[John]: That’s right. There are fundamental differences under the hood, but that’s essentially it. Coming back to will the user notice the difference, you shouldn’t. Today, the Preview has to be connected to an SSAS, SQL Server Analysis Services backend. So the data models must live there and that’s a big difference with the Power BI platform in the cloud today, but you can see where we’re going with all of this stuff.
Yeah, it’s pretty exciting. I’ll even extend that out. So what isn’t there, but they have talked about publicly now, is one of the roles because they defined four different report types: Classic SSRS, RDLX which is the mobile format, PBIX which is Power BI and the other file format that is considered a report type for Microsoft is Excel, XLSX.
So logically, now if we’re going to render it, both of these platforms Power BI in the cloud can render Excel just fine. It’s using Excel Online. So logically, shouldn’t SSRS do it? Well yeah, they’ve said it’s going to and they’ve worked on it. I’ve actually seen it working and it’s using Excel Online Server to do that. So I see a future where both of these platforms can do all. Microsoft hasn’t stated this. This is my analysis, but I think we’re heading in that direction.
[Tobias]: That kind of brings us to PowerBI and how that integrates with SharePoint. I used BI a bit myself and to be a bit deep key, I’m using it actually to visualize my greenhouse and the humidity and temperature data charts connected to it using IOT. It’s a ridiculously geeky project but that’s the way for me to get into Power BI and test it out.
[John]: So you’re using it like an event hub and pushing the data up. So you’re doing real-time data from your greenhouse through Power BI?
[Tobias]: Oh yeah! And that’s being read every ten seconds or five seconds or whatever it is now and I push that data. I think I have several million lines in that database at the moment which is in an Azure storage and that’s just rendered using Power BI connecting to Azure Storage. It’s just so easy when it’s right there.
[John]: Because you can take the event hub and push directly into a Power BI data model and that literally lets you leave the screen up and you can watch the numbers change. That’s through a dashboard but what you’re talking about is you at least want to persist that data because then you get to keep the data as long as you want and analyze it any number of ways. There are limitations to using that direct push model and I’m just teeing off on this, aren’t I?
[Tobias]: It kind of gets me going and a side project. Thank you for that because I have enough time to spend on these things already.
[John]: I’m a gardener as well so you shouldn’t have told me about that.
[Tobias]: I’m going to show you this project when it’s entirely done and I can give that to you as well.
[John]: I’d love to see it. I don’t have a greenhouse. I rely on others to have greenhouses. Well, I did something like that with weather. I used weather underground sensors. There’s a weather underground station right outside my house as opposed to going out buying my own weather station. I just tie into that and I do exactly the same thing that you’re talking about.
[Tobias]: Nice! Yeah, that’s fun but in that project, I’m obviously not using SharePoint. So if we talk PowerBI, how does that integrate today with SharePoint if you are online. If you’re on-premises, I guess it is the SSRS option as you mentioned previously but if you were on SharePoint Online and Power BI, how does that work?
[John]: This really gets to a bigger issue. If we think about what we were just talking about with Excel, Excel Services has moved from running on SharePoint to running with SharePoint. If we think of all of the BI workloads that SharePoint serves up today and actually other workloads when you think of but if we take the BI workloads and take that concept, it helps inform us of where Microsoft is going with all of this stuff. And it’s all to the good because everything becomes componentized and you have a lot fewer dependencies.
We will come back to this topic but if you wanted Power View before, you had to have SSRS and SharePoint. So all these SQL guys who want to use Power View are now challenged with having to stand up with SharePoint farm just to get SSRS running and all of them want to use SSRS.
So by bringing it out first of all and Power BI is the cloud side of that. We’ll get the on-premises side of that in a bit but the cloud side of that is Power BI. It is a self-contained thing and if you remember when Power BI was first introduced, there was a V1 of Power BI that didn’t really catch on. It was really expensive and kind of cumbersome and it was essentially what we get with Power Pivot for SharePoint on-premises but in the cloud and with a connector so you could refresh your data from on-premises.
That’s really what it was and it maintained that SharePoint dependency. In middle of last year, this V2 of Power BI was lit up and it’s caught on like wildfire. It’s a lot cheaper, it’s easy to use. There are no dependencies so it lives on its own. It can do its own thing.
Now, we’re all used to these wonderful integrations and they are wonderful with SSRS and SharePoint. What do we do in the cloud? That’s really the question you’re asking and there hasn’t been a good story and frankly still today there isn’t a great story on this. Up until now, the best you could do from a codeless integration into SharePoint was an app in the App Store that allows you to put Power Bi tiles directly on a SharePoint page but I had the good fortune to be able to reveal at Ignite the upcoming Power BI web parts.
There’s a client-side. It’s basically the new client-side rendering model.
[Tobias]: – the SharePoint Framework.
[John]: Thank you – the SharePoint Framework! It’s implemented on the new Share Point Framework and you can basically just take a web part like you would expect to, drop it on a page and point it at a report and bang, you’ll be able to choose what report page you want to have rendered and that’s it.
Today there’s no parameters like we’re used to in Power BI. You don’t have a whole lot of control over the layout. You’ve got some but it’s highly responsive, it’s totally interactive and it’s all client side. That’s what’s coming at least initially from a Power BI Share Point integration. Now that’s in the cloud. What about on-premises?
What I just said before about the web part, it’s the new SharePoint Framework. The issue here is on-premises we don’t have the SharePoint Framework but once we get it, it will work exactly the same way and the beauty is this will not care whether you’re in the cloud or on-premises. It will work exactly the same way. As long as you’ve got access to the underlying report, you’ll be able to have it rendered on your report.
[Tobias]: So what happens in the meantime? I mean, you said when the SharePoint Framework is going to be fully available in the web part is going to be there on-premises, it’s going to work exactly the same but what happens in the mean time before everything is feature parity between on-premises and online?
[John]: We keep doing things the way we’ve done them all along. Have I mentioned SSRS?
[Tobias]: Yeah. I want to clarify that because I got exactly this question the other day or a couple weeks back before Christmas about if I’m using Power BI, how do I present my chart in SharePoint on-premises? I didn’t know how to answer that question but I do now.
[John]: You could iframe it. It’s the short answer. You could build something yourself that embeds an iframe in a page.
[Tobias]: Yeah, and essentially that’s what SharePoint Framework is all about anyway. It’s not simple but it’s a clean model for integrating other things into your SharePoint without actually installing it on the SharePoint server.
[John]: Yeah, I’m a big fan of putting a link on the page and saying click here in those cases.
[Tobias]: As long as it’s on a trusted source because otherwise, you get those emails that say: Hey, click here. You won the lottery. Click this link.
[John]: That’s right. Actually, I think I do know the answer to this. I’m not even going to go there. I was thinking in my head. The reporting services web part that we have today in SharePoint on-premises work with the new Power BI embedded reports and I‘m positive that it won’t.
I don’t know if this is coming up or not. I don’t want us to get ahead of ourselves but the reason for that is that there is no more integrated mode reporting services have been deprecated and that’s where that web part comes from.
[Tobias]: Nice! I mean, here is a lot of the information I did not know. You’re enlightening me every single minute in this call here. It’s like a world of new information and I’m like, I need to try this out.
And speaking of trying new things out, I’ve also seen and scoped out that something called the Azure Analysis Services which is now in a preview. Do you know what that is?
[John]: Oh, yeah.
[Tobias]: Is it cool or is it cool?
[John]: It’s cool. It’s not something I’ve had my hands on yet. I thought I was going to need it about half a year ago but some changes to Power BI meant I didn’t need to. In a nutshell, Azure Analysis Services is exactly what you think it is. It’s an analysis service as a service in Azure so it’s a PAAS version of SSAS.
We didn’t have that up until now. Well, actually we did but we didn’t know it for what it was. If you look at what’s behind Power BI, it’s that so you just had to go through Power BI to do it and there were ways to tap into its power. For example, if I deployed out a Power BI data model into the Power BI Service, I could then connect to that model directly with Excel just as if I was connecting to a big analysis services server.
There were limits around Power BI initially. The model size couldn’t be larger than 250MB. It can be a gigabyte now and most of our data model is still within that gig but what I was excited about with Azure Analysis Services is if you had massive data models, you can deploy them out to Azure Analysis Services without having to worry about connecting them back on-premises. You could then connect your Power BI reports to that and you don’t have any scale issues whatsoever. You can scale that out as much as you want and you’re not charged for your storage within the Power BI Service.
There are limits around a free user gets a gigabyte of storage or a Pro user gets ten gigabytes of storage, etc, but if you need to offload that it’s another option to standing up your own SSAS Service on-premises and connecting to it through a gateway. So that’s really what it’s all about.
One other piece of it though I find very exciting is if you look at how you connect to it and this is also true of that case when you connect from Excel to the Power BI data model is if you look under the covers, that connection is using Claims authentication. SSAS doesn’t support Claims authentication today, does it? But it does in the cloud and the team has talked about it coming to on-premises.
This is just John saying this. This is not Microsoft’s saying this but I would expect to see Claims authentication in SSAS on-premises in vNext.
[Tobias]: OK, that would make sense. It would be a nice move as well.
[John]: It does. SSAS supports effective user name which is a nice way to get around having a lot of that stuff but yes, it would it would be nice to be able to connect directly.
[Tobias]: Yeah, so this thing is built on SQL Server Analysis Services and you can essentially get the same kind of data points connected or a data connection from both on-premises and in the cloud now with this thing. That means you can connect to Excel Services, SharePoint, SQL data warehouse, SQL server database, whatever, right?
[John]: Exactly that. I mean, anything you could use Analysis Services with today, you can use as far as I know in the Azure Analysis Services. One caveat I believe it’s only tabular mode right now. It’s in preview. It only does tabular models right now I think but I need to check on that. I’m not one hundred percent sure of that sort so don’t take that as gospel.
[Tobias]: Yeah. Well, that’s kind of cool, though. It’s kind of like, how would you say? It’s the gap between what was previously on the on-premises up to now to the clouds. You can get kind of the same data everywhere. It is in preview and everyone knows when you test a preview also known as a beta, you might not get all the features. There might be some hiccups and things like that. Hopefully, the features will just be pushed in there once it’s stable enough.
[John]: One of the oddities with it is that you need to go through a gateway to get at it which is odd to me like a non-premises gateway. We have to have that stood up. I don’t know if that’s going to be the case in GA or not but the last time I played with it, you had to stand up one and it was a special version of the on-premise data gateway. That will converge but I’m not exactly sure of the of the reasoning for that.
[Tobias]: So you need your SQL and you need your analysis if you will on-premises and then the gateway and then you can connect?
[John]: Yeah, it’s the same gateway or will be the same gateway that Power BI, Flow, and PowerApps.
[Tobias]: OK, I get it now. I really like this thing. It’s like everything’s coming together in the cloud and I do like the way that Microsoft is targeting their cloud offerings versus on-premises. For a while, a couple years back or almost up until today, a lot of people have been thinking that everything goes into the cloud and nothing stays on-premises but that’s not really the case, is it? Feature parity is more important than running hybrid and is more important than neglecting on-premise, going on the cloud or vice versa.
[John]: You see it across the board. You’ll find features that are only available in the cloud and that makes sense. Things like Delve. It doesn’t make sense to even try to do that on-premises. There’s quite a few other ones, Flow, PowerApps, etc. but you can still work.
They’re not trying to push you off premises any longer. In fact, the hybrid environment is not seen as a transitional state anymore. It’s seen as just of valid end state and it’s perfect. I love the way Power BI does hybrid.
With that gateway, if I’m worried about my data if I have sensitivity requirements. I mean, in Europe, you guys are crazy about that -that’s data privacy. I can keep my data on-premises but I can still use that service in the cloud and tap into it. I don’t have to persist it up in the cloud storage anywhere so that satisfies those requirements, enterprises’ concerns with sensitive data, the privacy stuff.
It also addresses scale because like I said with the Analysis Services and Azure, you can do that on-premises yourself too. You can scale up your own box. You might want to manage your own platform for whatever reason or you may have legacy things that need to be on-premises so you can do all of that stuff.
And Microsoft is saying: Totally valid! Use this piece here, use that piece there. Do what you like. I mean, it’s not all there yet but it’s getting there. I mean, that’s the aspiration.
[Tobias]: Sweet! I’m way out of questions now because I don’t know any more stuff about this. All my qualified questions are gone. Is there anything you’d like to add on top of what we’ve already discussed? Is there anything you’d like to recommend to people to check out?
[John]: Well, I’ll clarify a couple of things I just kind of touched on earlier. I mentioned that a Reporting Services integrated mode is being deprecated going forward. That means the next version of SQL Server will only have Reporting Services native and SharePoint people go, what? What needs to happen at that point is you need to have that wonderful web part. It is wonderful. It lets us control parameters, etc. That’s going to need to work with native mode reporting so I don’t have a problem with the concept of SSRS not running on SharePoint. I applaud it but we need to have that connectivity back for sure. That’s just not there today to a native mode.
So right now if you want to stand up 2016, you want to have all those cool new features in SSRS, you need to stand up a native mode server and you probably are going to have to stand up an integrated mode SharePoint to get things like Power View which is now by the way considered legacy.
Power View is not moving forward. All of the capabilities that were in Power View, we can now describe as Power BI. The big difference is the UI. The way the UI is rendered are two different technologies but it’s the same fundamental principle under the covers. So if you’re using Power View today and you’re on-premises, you’re kind of still stuck with things like Silverlight requirements.
[Tobias]: Oh my God! I haven’t thought of that in ages.
[John]: Well, if you want to use Power View in a browser on-premises, you’re going to be using Silverview- sorry, Silverlight. Power View, Silverlight,…
[Tobias]: There are so many things. You can’t count them all, right?
[John]: Exactly. So I can see that going away. If you look at Power Pivot for SharePoint in 2106, it’s kind of the same as Power Pivot for SharePoint 2013. It’s not much difference with it. I expect to see all that stuff get rationalized in the near future.
Be aware that the on-premises story is still being fleshed out but what’s exciting for me is that there is a very clear direction from Microsoft on where they’re going with this stuff and that gives me confidence making these recommendations – here’s what you want to do, here is what you want to do there. Before you were just never sure so I’m pretty excited about that.
[Tobias]: It makes a lot of sense. All right, sweet! John, thanks for coming to the show and doing this recording. It’s been ages except for when I saw you at the MVP Summit. Hopefully, I’ll see you again soon and then we maybe can catch up, maybe do another recording.
[John]: Sounds like a plan maybe over that famed beer.
[Tobias]: That beer just escapes us every single time, doesn’t it?
[John]: Yeah, exactly. It’s just after nine here so I’m still in coffee mode right now.
[Tobias]: Yeah, it’s about beer o’clock for me here.
[John]: Perfect!
[Tobias]: So thanks again for coming to the show and hope to see you soon.
[John]: Thanks, Tobias. See you soon.
[Tobias]: That concludes yet another episode of the Rencore Tech Talk. Thank you, John, for the time and thanks to everyone who tuned in to listen. Find out more about John and his experiences by checking out the links in the show notes.
