Power BI, SSRS 2016 or Both? [Commonly Asked Questions]

Summary

In a comprehensive webinar hosted by Pragmatic Works, Sean Werick delves into the commonly asked question: Should you use Power BI, SSRS 2016, or a combination of both? The presentation highlights key decision factors such as your organization's specific needs, financial capabilities, and data security requirements. Power BI is presented as a versatile, cloud-based analytics tool offering interactive, real-time reports and dashboards, while SSRS 2016 is positioned as a robust, customizable server-based reporting solution. Werick discusses features, limitations, and pricing strategies for both, ultimately advocating for a tailored approach based on business requirements.

Highlights

• Power BI provides a 360-degree view of your business data and is accessible on multiple devices 📊
• SSRS 2016 offers server-based, page-oriented reports with detailed customization options 📄
• The ability to share reports easily with collaboration features enhances team productivity 👥
• Real-time data connection boosts decision-making efficiency with Power BI ⏱️
• Using both Power BI and SSRS can provide a comprehensive solution, combining the best features of each ☯️

Key Takeaways

• Consider your organization's specific needs and budget to choose between Power BI and SSRS 2016 🔍
• Power BI offers a cloud-based, interactive analytics solution that is quick to set up and use 💡
• SSRS 2016 is a server-based solution that allows for more customization and is best for on-premises deployment 🖥️
• A combination of both Power BI and SSRS might be the most beneficial approach for some organizations 🤝
• Keep in mind the scalability and user access requirements to make an informed decision 🚀

Overview

Sean Werick thoroughly explores the decision-making process involved in choosing between Power BI and SSRS 2016, emphasizing that it's not a one-size-fits-all situation. The choice largely depends on organizational needs, goals, and budget constraints. Power BI is highlighted for its simplicity in creating insightful, real-time dashboards and reports, especially suited for those looking for a quick start with powerful analytics.

In contrast, SSRS 2016 is pitched as the go-to for businesses that require robust, server-based reporting. It caters well to companies needing detailed, customizable reports and who manage their hardware and upgrades in-house. Sean explains the different approaches you can take with these tools, showcasing their distinct roles in business operations.

Ultimately, Werick suggests that pairing both Power BI and SSRS might offer the best of both worlds – leveraging the intuitive, agile analytics of Power BI along with the stability and customizability of SSRS. This dual approach allows organizations to harness the specific strengths of each platform, addressing varied reporting and analytical needs seamlessly.

Chapters

• 00:00 - 01:00: Introduction The chapter introduces the discussion about PowerBI and SSRS 2016, commonly asked about by clients at Pragmatic Works. It explains that the choice between the two is not straightforward and depends on various factors.
• 01:00 - 22:00: Overview of Power BI In this chapter titled 'Overview of Power BI', the speaker introduces the company, Pragmatic Works, highlighting its various offerings including products, services, and training. The speaker notes that although Pragmatic Works is well-known for its training programs, most of the company's revenue comes from consulting and mentoring services. Sean Werick, a principal consultant, emphasizes that the needs of a company in terms of Power BI usage depend on several factors such as company direction, size, and financial situation.
• 22:00 - 57:00: Features of Power BI The chapter titled 'Features of Power BI' introduces the topic by acknowledging the familiarity of the audience with Power BI. The speaker provides their contact information, including a mobile number, email address, and website (seaunick.com). The intent is to quickly review Power BI features, catering to an audience already somewhat familiar with the tool. The chapter sets the stage for a more detailed exploration of Power BI in subsequent sections.
• 57:00 - 90:00: Reporting Services 2016 The chapter titled 'Reporting Services 2016' begins with an introduction to PowerBI and SSRS (SQL Server Reporting Services) with the aim to compare and contrast them. PowerBI is described as a suite of business analytics tools that enable data analysis and sharing of insights, providing a comprehensive 360-degree view for business users. It is praised for consolidating metrics in a single place, real-time updates, and compatibility with multiple devices, as per Microsoft's website.
• 90:00 - 118:00: Mobile Reports and Visualizations The chapter explores mobile reports and visualizations, emphasizing the intuitive nature of certain analytical tools. It highlights the ease with which one can explore data and create dashboards using these tools. Key features include intuitive interfaces and the integration of 50 innate connections to popular business applications, such as Salesforce and Dynamic CRM.
• 118:00 - 130:00: Power BI and SSRS Integration The chapter titled 'Power BI and SSRS Integration' discusses the integration of Power BI with SQL Server Reporting Services (SSRS). It explains the concept of content packs, which are pre-built dashboards designed by experts familiar with specific industries. The chapter outlines the structure of Power BI, highlighting its compatibility with various applications like QuickBooks and Salesforce, among others. It emphasizes the role of Power BI Desktop as the primary development tool, which is a standalone application that needs to be downloaded and installed. This platform serves as the core for creating and managing reports and dashboards.
• 130:00 - 147:00: Pricing and Strategy The chapter discusses the deployment and publishing of developments to PowerBI services, hosted in Azure. It highlights where dashboards reside and clarifies a common misconception about data storage, explaining the option to connect to on-premises data via PowerBI gateway.
• 147:00 - 166:00: Questions and Answers In the 'Questions and Answers' chapter, the discussion revolves around data storage and access with PowerBI. Although PowerBI operates in the cloud, it's possible to keep data on premises if cloud storage violates certain privacy policies like HIPPA, CHI, or CPI. The chapter highlights that there are various secure methods to connect to PowerBI without compromising data security, including PowerBI gateways and private connections, enabling efficient data access while adhering to compliance requirements.

Power BI, SSRS 2016 or Both? [Commonly Asked Questions] Transcription

• 00:00 - 00:30 All right, thanks. Good morning everyone or afternoon wherever you may be. Um, so we're going to kind of go through PowerBI SSRS 2016 and talk about which one is going to work for you um or whether even both might work for you. So this is a question we get very very frequently here at Pragmatic Works. They they they hear about PowerBI um or they hear about reporting services and they say which one can I use, which one can I use? Um and it's not really a cleancut answer. There's there's a lot of um it depends and I hate to say that but that
• 00:30 - 01:00 is the that is the fact of the matter. It's going to depend on what your needs are. Um you know what direction your company's headed to, what the you know the size of your company, the financial situation, all sorts of things. So without further ado, let's just go and jump right into it. Um just to quickly kind of summarize who we are, Pragmatic Works. We have products, services, training. We're probably most well known for our training, but um believe it or not, the bulk of our uh revenue is based on our services. We do consulting, mentoring, um provide solutions. And once again, my name is Sean Werick. I'm a principal consultant here at Pragmatic
• 01:00 - 01:30 Works. That is my mobile number. You're actually welcome to call me. I may not respond right away, but uh feel free to. That is my email address. That's wheric pragmaticworks.com. And I do have a out there. It's um seaunick.com. So, let's just jump right into PowerBI. So, uh, you know, I know most of you are familiar with this, so I'll probably go through this kind of quickly, but I wanted to give everyone an idea. Um, looks like most of you know
• 01:30 - 02:00 one or the other. Some of you may not know both. So, I'm going to kind of go into, you know, what PowerBI is, go into what SSRS is, and then we'll talk about some of the finer details and compare and contrast because that's what we're really looking to do here. So, PowerBI is a suite of business analytics tools to analyze data and share insights. Um, it provides a 360 degree view for business users. Um, allowing you to get all your metrics in one place, can be updated in real time. Um, and it can be available on almost all devices. Now, this is from the Microsoft um, website.
• 02:00 - 02:30 So, I kind of have to put some of those keywords in there uh, to make sure that you understand that just right out of the box some of these things don't show up like that. Um, with one click, you can explore the data, look at the dashboard, and the key key word here is intuitive. So these tools are intuitive and finding answers are very easy. So this is a very analytical tool. We'll talk about that. Creating dashboard is simple. Um they do have 50 innate connections to popular business applications. So that would be Salesforce, uh dynamic CRM, any
• 02:30 - 03:00 of those things. And those are what some of you may know uh refer to as content packs. So, uh, that they do come pre-built with some dashboards already there with, you know, some experts that, uh, supposedly know the industry. Um, so this is kind of this the structure of how PowerBI works. So, you have the 50 apps, you know, you get the QuickBooks, Salesforce, you know, 48 others, but actually more than that. You have PowerBI desktop. This is where all your development is going to be done. So, this is actually a thick client. You actually go out and you download this
• 03:00 - 03:30 client. It is updated once a month. Um, and this is where you do all of your development. Then you deploy or you publish those and you connect your applications to the PowerBI services which is out there in Azure. So um that's where all your dashboards reside. Now the data itself may or may not reside and we'll talk about that um because you can connect to your on premises via PowerB gateway. So a lot of people have this misconception that you
• 03:30 - 04:00 know my data is in the cloud and you know we can't really use it. So what do we do about that? Um well just because PowerBI itself resides in the cloud the data itself can sit on premises. So if that is a risk for you if it doesn't follow your HIPPA or CHI or CPI um um policy. You can have your data on premises and have connections that go back and forth. And there's different ways to connect to. So you have PowerBI gateways but you can even get more robust um and connect privately. There's there's a lot of different ways to do that. And then you access everything
• 04:00 - 04:30 through that PowerBI service on the cloud. So that would be your mobile device, your tablet, and of course your your PC. So let's talk about some of the features that PowerBI has. It's very, very fast to start up using, and I'll kind of walk through that very quickly, do a quick demo. Um, it's free to start. So anybody can go out there, download PowerBI, connect to some data sources, and start using it for free right away. out of the box dashboards which are the content packs we referred to earlier. So
• 04:30 - 05:00 if you're just if you're just looking for a dynamics or you know a specific data set Salesforce any of those top um particularly SAS solutions out there software as a as a solution those are going to be available um immense number of data source options like I said um there's you you can literally connect to I don't know 100 different data source options and then even outside of that you do have the workaround of um you know ODBC and things like that. So it is possible to do real time
• 05:00 - 05:30 reporting that would be direct query. So once again where your data resides somewhere else you can connect to that data directly and it would be real time or is real as your um source system is refreshed. There is a Q&A feature which is very nice. It's also referred to as a natural language where you can go out to the PowerBI website and you can say what was my revenue for 2011 and provided that you've already created the report and the content and you've aliased your
• 05:30 - 06:00 columns with keywords like revenue and year and you name them appropriately, you'll actually get an answer right away. It's very it's very neat. You have some collaboration capabilities in there. the ability um to share to lock down some things. Um it's pretty nice. It does come with a mobile app, so it's both the iOS and the Android. Uh and I will hopefully get that working. I had it working yesterday, but for some reason it's not working today. Um that's just my screen
• 06:00 - 06:30 sharing part, not the app itself. Um, there is integration and this is one of the things that I really like that they kind of undersell but there is a rest API so you can connect back and forth to your data in PowerBI and that includes the logging and the auditing features. So you can actually do BI on who's accessing your VI. So there is a screen to go and look at all that information but I really like to drill into that information too and see okay what are most of my users accessing? What are their roles within my company? There's a lot of things you can do if you use the rest API and kind of pull it into your
• 06:30 - 07:00 own um repository. And once again, the security there's various levels of security. You can actually lock things down as at an all or nothing level. Um or you can do row level security. So you can say my sales guy in the northeast, he's not allowed to see um sales in the southwest. And then there is web integration. So this is a great way to share content with external users without necessarily having to provide another license. Now there is an
• 07:00 - 07:30 additional cost for that. You do have to pay for the number of renders which is kind of if you think about it's the number of clicks that you make in a PowerBI session if you will clicking on things and and slicing and dicing. Um, but that's a great way to share that content um with some of your external users or you can actually provide them with a um you know with a with a a username and password and set them up in your own tenant if you want. So I kind of refer to the licensing, but this is a big deal. This is really important to know because when
• 07:30 - 08:00 you start to compare and contrast PowerBI with SQL Server 2016 reporting services, you're going to want to know how much it's going to cost. And a lot that's going to be based on the number of people you have. So starting off, they do call it for individuals and for business. Um the for business of course they call the pro service. Excuse me. The um the individual one is free. So like I said, anyone can go out there and download this. The business one is about $10 per user per
• 08:00 - 08:30 month. Um both of them connect to cloud sources. Both of them have simple dashboard sharing, but oh and and then uh you know that's where the the similarities start to drop off. Now you're going to have to start paying for some of these additional features. So um the ability to consume more than a gig of of data um per user. Um for PowerBI Pro, it's capped to 10, but once again, we can get around that with the direct query. Um the refresh
• 08:30 - 09:00 schedules are different. If you're running the free, you can only do it daily as opposed to the pro service you can do it hourly. Um, so once once again kind of the data thresholds here, 10,000 rows per hour. Um, if you're using Pro, it's 1 million rows. Now, these three bullet points are assuming that you're actually pulling the data into PowerBI as opposed to just connecting directly to something else. And then that's, you know, the direct query I was talking about where you can connect to live data sources. So most commonly um people using something
• 09:00 - 09:30 like SQL DW or um much easier and cheaper analysis services either tabular or multi-dimensional. And then you can collaborate with your team using Office 365 groups. Manage access and sharing through Active Directory. This is a really big plus. This is um somewhat recent when they they give you the capability to do that using um user principal name mapping and all sorts of things. um it is a little bit more cumbersome than SQL Server reporting services, but we'll kind of discuss that. And then you can share data
• 09:30 - 10:00 queries to the data catalog. So um if you don't really want your users to go out to PowerBI and use those dashboards and they still want to use Excel, you can share that data query, they can actually open it with Excel and um do their normal pivot tables or whatever they want with Excel. So let's talk about security. This is a really big factor that you need to take into consideration. There's multiple layers of security with PowerBI. There's the Office 365 and tenants. So what it is is it's um basically if you're an 0365 user, there
• 10:00 - 10:30 is an admin center where you can add users um you can grant them access to create reports um to be admins of certain workspaces. Um that is for PowerBI itself. Now once you're actually on the site, then you can actually manage the permissions to grant members access to edit the PowerBI content that's there, give them access to view it. Um and then going even a step low lower, you can do role level security define roles. You can map them up to 80 roles. Um you can do it differently. Um there's there's a lot of flexibility
• 10:30 - 11:00 there. And the nice thing is they they did finally push that back to PowerBI desktop. I think originally it was um it was only on the um PowerBI website. So when someone would go to redeploy a PowerBI solution, they would have to recreate all that real level security. Well, now it's all integrated with PowerBI desktop. So you still have kind of that one place of um of deployment or a source of truth if you will and and it helps with the data governance and um and release schedule. So there are some limitations
• 11:00 - 11:30 with PowerBI um particularly if you're connecting to workbooks. So a lot of people are getting their data from workbooks. If you're not, some of these things don't apply to you. But if you're pulling your data from Excel workbooks, there's about six different situations and scenarios here. Um Adam Saxon has a great blog on this. I would recommend going and and looking at his um uh his videos out there on YouTube. It's um I think it's guy in a cube. Um and he talks about a lot of these things and this one in particular talks about the different scenarios. Um which ones require a PowerBI license, which ones
• 11:30 - 12:00 don't and then there some of them may or may not require a license, but some of them you have to have an action to be able to to to complete that. So for example um if you have um scenario number five where you have less than a 10 megabyte work size um workbook size and your data model size is between 330meg and 250meg you will have to have a license and you will have to do a couple little tweaks to get it to work correctly. Um so those are just some things to keep in mind. I would if you really want to know some of these hard
• 12:00 - 12:30 limits like this, I would click on the the link and Liz will supply this later and uh you know see some of those details there. Okay, so just to show you a quick PowerBI demo. So one of the greatest things about PowerBI is the ability to build something extremely fast. So um in my role we are required well kind of not really um required to track our time and how we're spending it. So, um, just to show you how quickly this is, I found this incredibly useful when I was trying
• 12:30 - 13:00 to figure out, you know, oh my gosh, I'm, you know, I've been working more hours lately. Why is that? Um, I can actually go out here and pull in my data and quickly find out where I'm spending my time. And the the big thing that PowerBI does so much better than a lot of other applications out there is the ability to drive insight very, very quickly. So, as you can see, I have a list of fields here that are coming directly from my PowerBI workbook. Um, and you can see I can just
• 13:00 - 13:30 I just want to let's see how my work is split up by customers, excuse me, and the number of hours that I spend on the customers. Actually, I don't want customers. Let's do by region. So, we have different regions here in the US. And I'll make that a a bar chart. And you can see right away I can already see yes you can have your Excel workbook and you can see the numbers but you can really people derive a lot more insight from graphical representations of things as opposed to um you know numbers. Now I can pull in a
• 13:30 - 14:00 responsibility group. Basically this is the type of work that I'm doing and I can make it a slicer. And now I can just look at general and see look at this in the northeast I'm doing a lot of quote unquote general work in my role. In other words, this isn't really project related work. It's just part of my normal role. But if I were look at project support, the Mid-Atlantic has really taken up a lot of my time in the Midwest. And so this is this was done in a matter of, you know, seconds. I think what 30 seconds to put this together. So you can see right away that it's very very quick to put things together.
• 14:00 - 14:30 Um so when you get everything that you want done um now you can go in here and you can you know you can come in here and you can um you know when you go and you get your data there is a language called mashup which will actually take in all of your data sources and you can do um you know some column conversions some type casting uh some of those things. So, if I would actually look at this uh where it go
• 14:30 - 15:00 here, most people see this screen, but you can actually um go a little bit deeper and Uh, where' it go? There we go. So, here's here's what
• 15:00 - 15:30 most people see. And then you can come in here and you can uh, you know, you can you can add columns, you can remove duplicates, you can merge query. So, in other words, that's kind of the same way of doing a join in SQL Server, split columns up. So, for example, if you have a full name column, you can split it up into first name, last name, um, a lot of things like that. And this is the advanced editor that most people don't really use, but I use it quite frequently. This is this is the mashup language I was talking about. So in here you do have the ability to um do recursion to have uh variables and you
• 15:30 - 16:00 can pass parameters in here. It's very very flexible. So, um, while PowerBI is very, very quick to stand up and very easy to get going, um, it also does have the ability to do some more advanced things such as, uh, you know, looping over multiple web pages, looping over files, uh, things like that that you, you know, aren't done with the click of a a button. So, it's a pretty nice piece of functionality. Um, so once you pull all of your data in, you do have the ability
• 16:00 - 16:30 to create a data model. So, for those of you that have done um you know, Excel, you do have the ability to do pivot tables. It's almost the same exact thing. So, I could have four or five tables in here. I can join them up. And what this does is this is where the real work is being done. All the horsepower of when I click on things, it knows what to show me based on how I relate them. So, if I had multiple tables, I you know, click and drag the columns that link together. And when I click and click on different items in those in that model or in this page, it knows
• 16:30 - 17:00 that when I click on this to show me all the things in the other thing, um that's where a lot of the power comes in for PowerBI. Um so, uh real quickly, I can, you know, publish this in a matter of seconds. I can save it, publish it. Um you I can publish it wherever I want. And uh for those of you that don't know, this is actually if you go ahead and actually look at the file itself, it's apbix file, but this is actually a zip file. So it's what it is is six or seven different files that are kind of crunched together and compressed together. So if you wanted to change
• 17:00 - 17:30 this file and change the extension to dozip, you can extract it and actually look and see what's going on. So you can see how big your model size is, you can see how big your data is, uh you can see some of these advanced um settings that you may not normally look at. So um one of the great things so if I were to go here so this is PowerBI now let's go out to the PowerBI site. So I do all that work and I deploy it. So this is where it goes. So this is app.p powerbi.com. So, I'm in here and this was that natural language that I was talking about and I can say
• 17:30 - 18:00 um I can say how many hours spent for the northeast and what it's doing is it's showing me all the northeast work that's been going on. But right away it's actually deriving some insight and I could actually further define it and now it's filtering. And you
• 18:00 - 18:30 notice that Rachel's name is the only one that shows up. I can keep filtering it and filtering it. And for me the power in this is maybe people that are not as technically savvy or maybe you don't want to traverse um you know a a directory that's seven layers deep to find what you're looking for. you you you have an idea what you want to go look for. You don't know where the report is. Reporting services does give you the capability to type in the report name or a field that's in the report, but you can't ask a question. You can't say what was my revenue for 2012. Here I
• 18:30 - 19:00 can actually see the amount of time spent in the Northeast region by Rachel Martino. So it's actually very very cool. Um, of course this comes with the caveat that you do need to build out the content. you do have to build out the reports um and put some value to those to those things that you want to have questions asked for. Um so one of the great things is the ability to collaborate and share. So as you can see somebody shared this with me. This is census data. I can turn around and I can actually share that with somebody else.
• 19:00 - 19:30 Jason Shu shared this with me. This is actually a really cool ML demo he did on census data. But I can go here and I can want I can say I want to share this to Steve Hughes who's my boss. I can say, "Hey, Steve, here's some great censored data, census data, sorry." And I and then I can allow them to share it if I want or I can just say, "All right, send me a notification when they receive it." Hit the share button and he'll get an email with the link. He clicks on the link and boom, it shows up on his um on
• 19:30 - 20:00 his workspace. Um so you do have a lot of other capabilities in here that don't a lot of people don't really know about. So for example, this particular fake data set here is a fake data set, but it's um based on uh contributions for a church. You can actually click on the data set, click on this um quick insights or analyze in Excel. So this going back to what I was saying, if you wanted to just share the data set, your users can analyze them in Excel just like they wanted to before. But I can do quick insights. And what that does is it
• 20:00 - 20:30 literally takes the data and does its own kind of um machine learning heristic and algorithm against it to come up with very very quick insight. About half of it is not really worth much but about half of it actually I find somewhat useful things that I didn't really know just by clicking around in my own data models. Things that you may look at and say hey I think I want to make a report around that. Soon as it's done you'll so for example you can see notice Minnesota has noticeably more pledged amounts. Um, right away you can see it's really giving a lot of content. Some of it is
• 20:30 - 21:00 obvious. Um, so for example, there's a correlation between the amount people pledge and the amount people they actually give. Yeah, that that's pretty pretty common sense, but there's some of them that don't make, you know, don't aren't aren't really uh intuitive. So these different campaigns, this one's doing better than this. These are outliers, things like that. So it's really pretty cool. Um trying to think there are other some a lot of other features in here but uh I just wanted to brush on those somewhat quickly and show
• 21:00 - 21:30 you some of the things it does. It does do the role level security. Um it does do you know where you can lock it down by region but let's just go back here. So let's talk about reporting services 2016 now. So we kind of get an idea of PowerBI how quick it was for me to stand something up. So reporting services 2016. So the definition it's a serverbased report. This is an important distinction. Um, PowerBI is software as a service. You don't manage the servers. You don't
• 21:30 - 22:00 manage the patches, the upgrades, the hardware. Um, it does all that for you. The only thing you do have to do is update your PowerBI desktop. And you do get an alert. So, actually, every time I open up PowerBI, there's a setting in there that if there is an update, it will tell me in the bottom right corner saying, "Hey, there's a new update. Go download it." So um they do release content every month because it is patched. They they're they're very flexible, very agile. Um but SQL server reporting services is a serverbased report. So you do have to maintain your
• 22:00 - 22:30 own hardware with that unless you go up to the cloud. That's another discussion I suppose. Um but it's part of Microsoft SQL Services. I think everybody knows this. Uh here's the distinguishing one of the distinguishing factors. They have pageionated reports. They've done this since 2004 I think. And then of course it became packaged in 2005. So um the only thing is it hasn't really changed very much. Like most people would say it's been the same thing for about a decade. Um yes there have been a couple of improvements here and there but nothing really changed until they did
• 22:30 - 23:00 the acquisition of data. They went out and bought a company that did some really fantastic visualizations. A lot of you have probably used this before. And for those of you that do have it, there is a migration wizard. you can actually migrate your data in reports to reporting services 2016. Um, but the data in technology is great and what it did was it took the really nice active directory integration with reporting services. know to me the visualizations were never that appealing but the ad security was very good very stable um
• 23:00 - 23:30 allowed you to you know grant access to certain things based on their role um or their principle and data does the same thing um except now you've got that nice ad feature to go along with it as well so it can kind of you can kind of keep the same security model that you had um data in technology you can actually or the world reports you can obviously access it from your your um your cell phone but you can also access it from your tablet or just straight through the web page. So um it has multiple um
• 23:30 - 24:00 mediums of delivery and then of course SSRS does have analytical reports and charts um you can create those with Excel uh you know and that's this is this is the on premise approach so remember data uh PowerBI is going to be your cloud solution SSRS is your um on premises solution but we're going to give you a little kicker at the very end where you might actually be able to use build. So there are different versions of SQL Server
• 24:00 - 24:30 pointing service 2016. You need to know what those are and you need to know what can do what. So there is an integrated mode. Some of you are probably running SharePoint. That would be integrated mode. And what that does is it gives you the ability to scale out. Uh native mode is one server and one server only. So integrated mode you can have a farm and you can actually have 10 servers running reporting services and um you can kind of scale that out. Um the only thing is if you're running in integrated mode you're not going to get all of the kind of new nice hotness if you will whatever you want to call it of um the data
• 24:30 - 25:00 acquisition. So you're not going to be able to have your customized parameters pane. You're not going to be able to get the web portal, the mobile reports, the KPIs, can't pin to PowerBI, you can't render PowerBI reports. So some security basics with reporting services. Um it's important because it's important in every company. You need to be able to lock down data um at the right level of granularity to the right role. Different types. You can have data security or report security. data obviously you have access to a data
• 25:00 - 25:30 set or you don't report security you have access to a report or you don't and sometimes you can combine the two um usually it's role based so you will fall under an active directory role or group um or principle however you like to phrase it u so for example you're part of a BI team usually you have a BI role and in that BI role they have read access to this write access to that etc etc so Um there [Music]
• 25:30 - 26:00 are saying the publisher browser people that can look at it. Um you know business users that go out and create the reports and then my reports role etc. There are system in other words you have the ability to add users you have the ability to basically do any of those things. Um and then a system user role which is exactly what it says. Um now one of the things a lot of people don't really consider that you should
• 26:00 - 26:30 is 2016 really consider using SQL server 2016 they do have PowerBI the policies at the database level um you do have to pass authentication through so in other words you can't have a stored credentials um but if you're passing the user's security all the way through you can actually have it to where Bob only sees what Bob's allowed to see in the northeast and um
• 26:30 - 27:00 Sue only sees what she's allowed to see in the southwest. Um and then of course users do have to be part of an 80 role. And just to kind of dig in here and show you reporting service um reporting services in the difference it's I mean it's completely turned upside down and that's directly due to the data acquisition. So you still have these same you know
• 27:00 - 27:30 pixel perfect standard visual studio reports that you can put together. Um, but there's some new things, too. You'll notice there's this PowerBI icon here. You can actually pin any graphs and charts that you have. So, going back to what I had here, you'll notice where' it go. I do have a reporting services report that I pinned to PowerBI. And when I click on this, it can take me to reporting services. So,
• 27:30 - 28:00 this is what I was talking about where you can actually use both. You don't have to use one of the other. So you can have maybe some of your analytics and your KPIs on PowerBI and then when they click on that boom they go to the detail view right here and they can see some of those um really lower level grain reports. So to me that is what the purpose of pageionated reports is is the lowest grain. Um you're not going to be doing a lot of analytics on it. These are more uh you know things you want to print out and look at and maybe deliver to customers. Um so reporting services 2016
• 28:00 - 28:30 you have the ability to export any of the work etc. Um want to change for example PowerPoint I think the default is um 16 by9 but if you don't want that if you want it to be can change it but it is at the server level. I'm hoping this is a change they're going to make where they can actually give you the ability to export a PowerPoint in 4x3 or 16 by9. So, for example, if I click on this PowerPoint link, I would hope that another box
• 28:30 - 29:00 would pop up saying, "What format do you want it in, things like that, but as of right now, it's an all or nothing type thing. It's a service setting." So, either you do 16 by9 for all of your reports or you do 4x3. And if you want to look at what some of those settings are, they are in config files and you can reach out to me and I can give you some content on that. Um, you also have the ability to, um, so of course we have the the save the print the PDF, you also can print the PDF. Um, and this is HTML 5 now. So what that
• 29:00 - 29:30 means is it's cross browser compliant. So no matter what browser you open up, it should look almost the same every single time. Um, with that you don't have the security holes of I think it was active X before with the printing and um, you know, you open it up on Explorer and it would look one way and or sometimes it would only open up with Explorer and it wouldn't open with any other browsers. Um, now you have cross browser compatibility. You have the ability to have your parameters moved around. So now uh, you know, before uh
• 29:30 - 30:00 it was a little bit difficult to move these parameters around. You can actually move them wherever you want. uh it's a little bit easier to use and when you look at the report you can actually collapse it. I think that was part of 2014. It's not new but it's nice to have. Um but now you go you know we still have the same report. This is the one that I pinned. Um and of course you know this is an analysis services cube connection. But so those are the pageionated reports
• 30:00 - 30:30 and you know those are all in here. Um and and you'll notice they break it down. So this is this is kind of their they call it the web portal and the reason is they want it to be a one-stop shop for all of your on- premises users. So you have folders you can break these folders down to different business groups. So for example you might have an accounting folder, you might have uh an operations folder, you might have a finance folder, you might have a developers folder and in each side of those you might have KPIs, mobile reports um and pageentionated reports.
• 30:30 - 31:00 So those are the three levels of reports. The KPIs will show you, you know, what your goal is against your target. Um, those are usually very very high level. And of course, your mobile reports are uh are something like this. So, this is a brand new feature. This is uh this is some dummy data, but you'll notice when I click and hold the mouse down, I do get some additional information that pops up at the top. So, it's telling me category A, metric 1, $10.5 million, etc., etc. Now these
• 31:00 - 31:30 reports can actually drill down. So you can have um something that's a higher level drill down into a little bit lower level. Um but they are only between mobile reports. So it's a it's a pretty cool feature. You know you can select on this and it looks if you notice it's very similar to the way PowerBI works. You click on things and you know you'll see the numbers change. There's changing these shift. Um so it so it is um it is used for for analytics as
• 31:30 - 32:00 well. Um one of the other nice things about reporting services um besides this you can actually uh change the way it looks and feels. You go to the site settings that's called branding. Um first off you can change the name. So if you just want to give it a generic name now it's going to show up as my web portal. Um, but you can also come in here and do what's called branding, which is you give it your own look and feel. So, here, Pragmatic Works. We want to give it, we're gonna
• 32:00 - 32:30 go make it look like our colors, our logo. Uh, and just like that, it looks a lot different. Uh, you know, it's got our logo at the top, it's got our colors. Um, it's not as granular as, you know, for those of you that have SharePoint experience. It's not that low grain with the CSS stylesheets where you can, uh, you know, change every single little thing. um you do have one or two values for some things that are used for multiple things. So what this is is this is actually a um an image file combined with an XML file which points to the
• 32:30 - 33:00 image file and then it points to another file which is a color file and that color file is actually a JSON file. So in there you can specify what color you want your positive KPIs to be, what color you want your negative ones to be because you don't want probably a hard red, you might want something a little bit softer. You might want them to maybe be yellow. uh you may want, you know, this font to be a white instead of blue. So there's there's a lot of uh there's a lot of flexibility, but it isn't the lowest grain of flexibility. And as of right now, once again, you can see it
• 33:00 - 33:30 does have PowerBI desktop reports at the bottom, and you can share them. This is the security model where you click on the manage has the active directory security where I come in here and I can actually add a group. But as of right now, when I click on this report, what it does is it's similar to clicking on a movie file or an Excel file. Um, it just asks you to download it. And if you click open, it's going to look for the mine type of that file and see if you have an application that matches it. Sure enough, I do have PowerBI desktop. So, what it's doing is it actually is
• 33:30 - 34:00 opening the client. It is not rendering in reporting services. So, as of right now, that's the way it works. You click on this, you know, it's just like just like the way we had it um in PowerBI sites. So it was a it was something they threw in I think last minute just to show that they really wanted to have an on- premises solution for PowerBI. Um and I'll show you in a bit they do in fact render um in a technical preview. So moving forward you will be able to
• 34:00 - 34:30 click on this and it will render. So I think that's all I wanted to show about that. Let's go back to So, let's talk about pricing. This is the kicker. Um, you know, each each of you is going to have an EA. You know, I don't know what your enterprise agreement is. I don't know who your reseller is. This was MSRP straight off the website. So, this is the kind of number crunching you're going to have to do. So, if price is a big factor, you're going to have to figure out these two keys. What are the number of users? How
• 34:30 - 35:00 many users do I have? And how long am I going to keep the solution? Because if you plan on upgrading SQL Server 2016 to 2018, which probably will come out, um you may not want to do that. Um you know, if you have a low number of users, you can see you save a lot of money with PowerBI. It's about 25 $30,000 cheaper. But once you get to a larger number of users, now the price really starts to go up. So this is because of the per user per month. You're paying for each user
• 35:00 - 35:30 as opposed to each server. And now I did just the standard eight cores. Eight cores is this, you know, this amount. Um, so the these are the numbers you're going to want to look at. And of course, you'll want to get this detailed information from whoever your um whoever your your your reseller is. So that's important. But let's talk about the strategy, too. You have a strategy hopefully within your organization. How do we want to present content to our users? What do we want to do in terms of um sharing, uh,
• 35:30 - 36:00 collaborating, all of those things? So let's talk about the different types of SSRS reports and compare them to PowerBI. So mobile reports is kind of a design first approach. It's I I think both of them and I'll go over this in a minute. I think both of them should be more of a data approach but it's a design first approach has the on premises capability larger reports KPIs they both have KPIs some limited vis limited visuals. Um what you get with mobile reports is what you get. PowerBI on the other hand you do have a cloud
• 36:00 - 36:30 service. Um, you do have an interactive tool and you can actually create your own custom visuals. It's actually, I think, Node.js and D3 and all sorts of things. You can create your own custom visuals. And if you don't want to create some, but you don't like all of the ones that are in PowerBI, you can actually import custom ones that other people have written. Um, and the number of data sources are much more numerous with PowerBI. Pageentated is another key point. Do I have to have pageionated reports? I can tell you that nine out of
• 36:30 - 37:00 10 times you don't. Um, we've done so much work for customers with reporting services and you'd be surprised. I think what happens is people get stuck in a mindset. I have to see my data. I have to have my Excel just like this. It has to print out. They can still have their Excel with either one of these solutions, right? You can still point to a reporting services data source for Excel or you can point to a report uh data data source for PowerBI. But those pageentated reports, if you have to have those, and the only time I ever see it being a requirement is SEC compliance,
• 37:00 - 37:30 socks compliance, you're actually sending a pageionated report to the customer. Um, you don't need them. You you really don't. Most of the time, people take those reports and they're looking at the numbers and in their head, they're actually deriving their own kind of bar charts and looking at the, you know, the the gradients and the slopes of things and figuring out the, you know, the the relationships in their head. Um so once again to compare them PowerBI is a cloud first approach. Um SSRS is on premises. PowerBI has monthly updates.
• 37:30 - 38:00 SSRS you have to patch and you have updates. Um they're not as frequent. I think we've had one CO2. We've had two updates since since uh since RTM since release. Um PowerBI's pass reporting services IAS. This is a real big one. So if you want to move away from supporting any kind of infrastructure. So in theory PowerBI can scale infinitely. That's the kicker. reporting services not so much unless you go with integrated mode and then even then you're losing some of the functionality. Um PowerBI no pageionated
• 38:00 - 38:30 reports. So if that's a dealbreaker got to have it you know you got to go with reporting services. Both have real level security. Um reporting services has branding. PowerBI does not. I don't know if that's a big deal for you but um PowerBI has custom visualizations. I meant to say no custom visualizations for reporting sources. Um PowerBI has the quick insights. PowerBI has a natural language and PowerBI has dashboards. So reporting services 2016 I don't think will have dashboards. I think all it has is um uh the web portal
• 38:30 - 39:00 and what you can do on the web portal is you can actually you know over here on your home button you can actually click on things that you really like and you can put a star on it or not. So what happens is you see right now I have no favorites. If I were to come over to here, click on add to favorites, what happens is every time you log in, it will default to favorites. And so you can actually default the ones you want, but you have to go out and find those. You have to go out and find the reports that you want, make them your favorite,
• 39:00 - 39:30 and then you'll see them every day. So I always like to start with a foundation. I always like to start with the lowest grain. I capture my data at the lowest grain. And this is usually a table, right? a table the report type it's a detailed report it's a pageionated report and usually it's something for external customers I build on top of that I usually take and I aggregate that and I have a medium grain that's maybe data instead of the lowest grain might be by day medium grain might
• 39:30 - 40:00 be by month or by quarter or something like that those are your analytics those are your dashboards those are your quick insights and some of your mobile those are your business users your analysts data scientists etc then I go one level higher That's the high grid. Those are my KPIs. Those are my operational reports. And those are my mobile reports. And those are usually your executives and some of your analysts. And of course, that doesn't mean that any other role can't use KPIs, but those are the primary users. And now they have the capability
• 40:00 - 40:30 um with I think it was two or three days ago they actually made it public. They do have um SSRS 2016 can render PowerBI reports. So if I come back to the homepage, they do have PowerBI desktop reports. And when I click on it, notice that the page is rendering it. It's not downloading it. It's actually being run by the reporting services. And I can do the same things that I could before mostly. So the
• 40:30 - 41:00 important thing to bear in mind is this is a technical preview. Um, as of right now, you cannot see. So just to back up here, you can see mobile reports on the PowerBI app. See if this is working here. Nah, it's not working. I'm trying to
• 41:00 - 41:30 share. But you the PowerBI app you can actually use for both. So this is what I was talking about. They were really trying to let you use both of them. If you use the PowerBI app for Android um or iOS, you can use it to look at your PowerBI reports. You can also use it to look at your reporting services 2016 mobile reports. What you cannot use it for right now is the reporting services 2016 PowerBI reports. So when I went and I just did this last night, I have no
• 41:30 - 42:00 idea what's work working now. But um when I went and looked, I could look at all my mobile reports, but this new functionality, I cannot see that with my phone yet. So I am pretty confident it will be there. However, I do not think they're going to have dashboards. So for example, in our PowerBI, we do have um you know, any of these dashboards. You know, you can you can put a whole bunch of different reports there, and that's kind of like your go-to screen. Well, remember reporting services has the same thing, right? So, they have their own thing where they want, you know, your
• 42:00 - 42:30 own landing page where you come here and you click on your favorites and it shows you all the things that you really want to see every day. So, I don't think they're going to have quote unquote dashboards. Um, now the other thing to note is that this version only works with so where it go. So, the rendering of PowerBI and reporting services only works on tabular multi-dimensional data. right now they will be incorporating other data sources. Um but remember so for the I I
• 42:30 - 43:00 didn't really go into a lot of detail but if you are using direct query in other words that's how they're connecting to tabular that's how they're connecting to multi-dimensional um pulling in those other data sources you can't pull those into um direct query mode and the same model it gets it gets really hairy really tricky one of the other nice things that I saw so you know we had in PowerBI the ability to share data and send a message but what if I really want to go out there and collect collaborate and say um you know
• 43:00 - 43:30 hey Bob look at the sales for June they're why are they so much lower than they were for May um in reporting services you have the capability to do that in the new technical preview version you can click on comments and I can say things like this comes from the data MPs this they had this before if I say notice how New Mexico has no data why is that so you'd be able to do that. Can't
• 43:30 - 44:00 do it now. They're working on it. But you'll be able to post a comment. Oh, yeah. You can. There you go. And it shows my name. So, I'm logged in as me. But you can see that. And then somebody can respond to me right there. Or you can start a new thread. Very similar to how you do in a Yammer or Slack or some of those things um to really collaborate. So, that to me is a really cool capability. And I say that because I had to build this by hand and it sucked. So this is a really really nice feature that a lot of executives whether someone like myself like a developer I didn't really see the I didn't really see a lot of value in it until I started working with some of the senior level management it's extremely valuable
• 44:00 - 44:30 because their time is so valuable being able to jump on here and collaborate without email without all these things looking at the visual and putting comments and responding to each other is extremely valuable. So, um, so I think that was it. One of the last things I did want to say is that um, you can use both. You know, you can use both. You can use PowerBI for most
• 44:30 - 45:00 of your stuff and you can also have reporting services to serve up your pageionated reports if that's what you actually really had to have. So, um, we had a lot of content in a very short amount of time, but hopefully you can see which one might work for you. It's a case by case situation. Know what your pricing is, know what your business requirements are, know what your user loads are, um, know how much you need to scale, etc., etc. Questions? Yep, there's a lot, so bear
• 45:00 - 45:30 with me. All right. Um, do you have to have a prolic license of PowerBI to view an SSRS PowerBI report? No. So, if you're using the mobile app, you do not have to have a prolic. Oh, wait. To to look at um Yes, you do. You do have to have a pro license to look at the to use the mobile reports. That was in Let me go back here. Right here. So, that's the mobile reports piece. You do have to have it's
• 45:30 - 46:00 native mode, but you do have to have it for the um Well, you know what? Let me I have it bookmarked. Let me look it up and I'll get back to you. I'll put it I'll put it to you, Liz, and we can I can send that question and answer out. Okay. Um any word on what features will be available for PowerBI on SSRS 2016? That is on the technical preview. um you have the ability to join to analysis services tabular analysis services multi-dimensional and also one thing to throw out there for those of
• 46:00 - 46:30 you that don't know and are interested in moving to the cloud and really want to scale they are um it's in preview mode now analysis services software as a service so you can literally go out there spin up a tabular cube in a matter of minutes um scale it up scale it out um but going back to the question um they're going to be able to connect to I think all the same data sources as before so as of right Now, if I think I tried to deploy my PowerBI site that was connected to Salesforce, it didn't work because it's only accepting the two connections of tabular or
• 46:30 - 47:00 multi-dimensional, but they will be able to do all the same connections as before. Um, the only thing is whether it renders in the app. I don't know. I would assume so, but I think it's going to be a lot of the same functionality. I don't know that they're going to have role level security because I think they're kind of trying to use SQL Server 2016 security. A lot of it I just don't know. They just presented this stuff to pass last week. So um PowerBI service versus desktop, what's the difference?
• 47:00 - 47:30 PowerBI service runs in Azure. PowerBI desktop your it's your own machine. So that's a good question. I didn't really specify that, but PowerBI desktop um it's the it's a similar technology except um it uses your laptop resources or your machine resources as opposed to the cloud. So if you have a really big model, you might notice your laptop is dragging. That's because it's putting it in memory. It's using the X velocity engine. It's compressing it. Um it's doing it all on your machine. And then once you deploy it to the cloud, what happens is it kind of it takes that file. It's, you know, unzips it and does
• 47:30 - 48:00 all these things with it. And then it scales it out because remember it's software as a service. So it can scale out as wide as it needs to be scaled. Can PowerBI access business objects universe? I am 99% sure it can. I can go look, but I'm pretty sure. Okay. Um, is a language you referred to as mashup, also known as M? Yes. Um, can you restrict others from sharing with others?
• 48:00 - 48:30 Yes. So, when I went and I went to the to do the share, so I'm assuming that's a PowerBI site when I want to share a data set. So, let's say I come here and I want to share this. Here we go. Now, it's a dashboard. Yeah, it's a dashboard. So, if I want to share it, um, you just uncclick this and don't allow them to share your dashboard. Great. Can an on-screen visualizer be clicked
• 48:30 - 49:00 to show the underlying data set? Can it show the underlying data set? Um, well, what you want to do, I think I understand what you're saying. If I if I click on something, can I see the detail for it? And the closest example I have to this is something that let's see where' it go is something like mine. Give it a sec to open up here. And what what I do is um I have a list of customers. So this goes back to the pageionated issue. I don't need pageionated. I don't need to see data by page. I just need to be able to drive some insight. And as I'm
• 49:00 - 49:30 clicking on things, I want to see how it all relates. So I have a list of accounts down here. You'll notice it's got multiple pages. But I don't really care about this unless I'm looking at something by region. So when I click the South Central, I've only got four different customers here. So the answer is yes, but you have to set it up the way you want it to be presented. Okay. Can you develop mobile reports in Visual Studios? Uh so there is a mobile reports um show
• 49:30 - 50:00 you here. There's actually a mobile reports. There we go. This right here. So there's actually a SQL server mobile report publisher. It's not done with Visual Studio. Visual Studio is still going to be used for your pageentated reports and you can use Visual Studio to create your data sets. But to create your mobile reports, you're going to use this SQL server mobile report publisher. And this is almost an exact replica if you've used it as the data publisher. So what you do is you first create your
• 50:00 - 50:30 visualizations, then you point it to data, and then you do some tweaks. So this is a new tool, and that brings up a good point. PowerBI is much more intuitive, much quicker to stand things up. This is by no means terrible. It's not like, you know, the reporting services of 2005, but it does take a little bit of learning. And we do have a a course on that. We have a mobile reports um class on that. Can SSRS 2016 work against SQL 2014 or do you need to have SQL 2016 installed?
• 50:30 - 51:00 No. So you have to have SQL server reporting services 2016 installed but but it can report against any data that you want. So I think the question is can I can I not pay for a license at all and just report just install 2016 reporting service 2016 answer is no. You do have to have a license but you only need the one license for the report server instance. So you know you might you might save some money and I you know doesn't mean so going back to the security piece you don't have to upgrade to the SQL server 2016 database where
• 51:00 - 51:30 your data might be housed like I was talking about the role level security you can have a SQL 2014 instance that feeds reporting services it's 2016 we have SSRS 2016 on native I do not see the web portal how do I enable that feature there. Um, it should just in native mode you don't see the web portal. It should just be localhost slash and now mine is report. It's usually reports, but I
• 51:30 - 52:00 changed mine to say web portal because I really do feel that this is more like a web portal. So, it should be whatever your host is IP addresses slash reports just like the other reporting services. If you don't see it, you don't have access. I have a feeling. I don't know. Something something's going on with the security. Okay. Look at the look at the report server um configuration. So, go to the report services configuration to
• 52:00 - 52:30 make sure that the service accounts are correct and stuff. Can we publish our PowerBI reports on SSRS 2016 on prem? Uh with the technical preview, yes. So what happens is actually in the PowerBI desktop. So if I were to come here, what you do is you do file save as and I don't have the newest edition, but there's a there's a there's a way to you can publish from desktop to
• 52:30 - 53:00 um to reporting services. Um or you do like I did here and I do the new or I do I'm sorry I do upload. And I can go and select the um reporting uh the PowerBI file. Sorry. But that's only in technical preview as of now. So if you have reporting services just the regular one, you're going to get the the version where it it will download the file and open them with PowerBI desktop. You
• 53:00 - 53:30 would have to have the technical preview which is available in the Azure marketplace. Um or you can actually I think you can download it as well. um in in order for it to render within reporting services. Uh is the ability to do PowerBI within SSRS 2016 available in both standard and enterprise SSRS 2016? Uh they haven't released that information yet. It's still in technical preview, I would assume. So, but I don't know. Um yeah, I if we go back to that slide,
• 53:30 - 54:00 they may not. I I I don't I don't have any answer though because they haven't released that information. I'm not important enough to know it. Okay. Um I know we are out of time and Sean there are a ton more questions so I'm going to send all these questions to Sean and I'm sure he usually posts the blog and then we can post that on our site as well. Um as always the session is recorded. Um so you will get an email with the link to that tomorrow. Sean I have a couple
• 54:00 - 54:30 people asking if you're going to share your slide deck. Yes, I'm going to email it to you right now. Okay. Well, there you go. So, if you would like Sean slide deck, please email me, Liz. It's the email that comes with all your go-to webinar information. Um, if you have any questions that you didn't get to put in the panel, you can either email me or Sean if you happen to save his email address. Um, and we will be happy to get those answered for you. Thanks everybody for joining us today. Thank you, Sean. And we will see you guys next time. All right. Thanks everyone. Bye-bye. Bye.