In-Depth
SSRS Steps up in the Shadow of Power BI
Power BI has quickly given Microsoft supremacy in analytics and real-time reporting, but it's only available as an online cloud service. Seeking parity, a revamped on-premises SQL Server Reporting Services 2016 will deliver rich features, such as a new UI, delivery of KPIs and HTML5-based reporting, but integration with Power BI for now is only a statement of direction.
When Microsoft released the first major upgrade to its Power BI service last year, the company jumped to the head of the class in the crowded field of reporting, analytics and self-service business intelligence (BI). Now that Power BI is an app available as an option in Office 365, Microsoft recently revealed 5 million people now subscribe to the online tool for creating reports and dashboards.
Power BI is a great choice for those seeking a cloud based solution, but there's still demand for on-premises and hybrid solutions. With Power BI's strong showing and with the wind at its back, Microsoft is now moving to extend and unify its on-premises reporting, analytics and BI offerings. Microsoft's next target is a rebuilt SQL Server Reporting Services (SSRS). Not only is Microsoft updating and enhancing SSRS, but it's potentially unifying it with the cloud-based Power BI. A unified Power BI/SSRS platform with the new SQL Server 2016 would be impressive.
The improvements to SSRS and Power BI are empowering more workers. Years ago we mostly just had reports and reporting tools. Advances in technology later led to decision support systems (DSS) and executive information systems (EIS). Today, there are dashboards, key performance indicators (KPIs), statistical packages and the ever-present Excel spreadsheet. These tools allow you to leverage data to gain insight and solve problems. They allow you to display the data, read the data and interpret the data. And to do so visually, with graphics, charts and diagrams, which communicate faster and more effectively.
In virtually every company, one or more analysts and staff members are using data to answer questions. Established questions typically have standardized reports, dashboards and even KPIs. It's a great start, but every insight revealed by a report generates more questions: Why did this change? This should have gone up! What happened here? And the list goes on. The new questions lead to the next stage of deeper analysis, where Excel typically takes over. It's not that analysts can't part with Excel -- a good analyst knows the many weaknesses of Excel.
The real reason for that is Excel has been the best, most widely used tool. It's easy, it's fast and fluid. It allows you to get to an answer quickly. Before Power BI, the other tools had a command-and-control mindset. IT sets up the platform, controls the reports, manages the data, sets up the dashboards. Business moves fast, and analysts don't have the time to wait for IT. This is what self-service BI should be all about: Provide a tool that analysts want to use. And this is why the updated Power BI has done so well.
Microsoft's Struggles with Data Presentation
Aside from Excel, Microsoft has historically struggled with the presentation side of data. Back in 2004, Microsoft launched SSRS as a supplement to SQL Server 2000 and it was fully incorporated beginning with SQL Server 2005. When first released, SSRS was a capable, minimalist reporting tool kit. It was a nice start, but, unfortunately, after its initial release, enhancements mostly dried up. Support for browsers beyond Internet Explorer was difficult, and report development was targeted to IT developers. Acceptance increased when SSRS was configured to work with SharePoint. A few years later, Microsoft released Performance Point, perhaps envisioned as the future for an analytics portal. While powerful, it never gained market acceptance and was quietly incorporated into SharePoint.
In an attempt to support self-service report creation beyond Visual Studio, Microsoft created a standalone Report Builder for SSRS. It was useful, but like SSRS, it had a minimalist look and feel with only a small following. Meanwhile, competitors like MicroStrategy Inc., Qliq and others moved into the self-service and analytics space. The world was moving to more powerful, user-friendly reporting and BI tools. Losing ground, SSRS was relegated to departments and low-budget projects.
One Report Format: Unification Goal
With Microsoft encouraging Office users to migrate to the Office 365 cloud and Power BI, you might conclude the company would ignore the SQL Server legacy SSRS components or, even worse, simply abandon them. Fortunately, for those who work with SQL Server, Microsoft has chosen to offer an alternative path -- one that potentially strengthens the on-premises version of SSRS and sets it up as a bridge to the cloud. A path that, if done well, will unify SSRS with Power BI, providing the benefits of Power BI that support on-premises reporting and a hybrid path to the Power BI cloud.
There's one obvious solution for Microsoft to take advantage of the advances of Power BI, while leveraging the existing base of SSRS: Completely rebuild SSRS to support the Power BI Desktop reports while providing backward compatibility for the legacy SSRS reports. Microsoft is building a brand-new SSRS server service. In earlier Community Technology Previews, both the legacy SSRS service and the new SSRS service were running in parallel. But, starting with Release Candidate 1 (RC1), a feature-complete, totally rebuilt SSRS has been revealed. And it's impressive. With the demise of Internet Explorer, the new SSRS targets all modern browsers utilizing HTML5. The interface is clean, modern and closely mirrors Power BI. Enhancements to the Power BI mobile apps enable mobile presentation for SSRS mobile reports. End users only need a single Microsoft mobile app (that runs on Apple iOS, Google Android and Windows) for both SSRS and Power BI reports. That's huge. Finally, to ensure SSRS will look professional, custom brand packages can be loaded allowing it to have a look and feel to match your organization. The ugly duckling of the reporting world has gotten an extreme makeover. But there is one slight problem. All of these enhancements are great, but they continue to position SSRS and Power BI as separate, distinct platforms.
Long term, it only makes sense for Microsoft to support a single unified report file format. One format that can be created using a powerful, yet easy-to-use tool accessible to everyone. One that supports on-premises reporting, the cloud and hybrid variants. With the functionality present in the Power BI Desktop, that should be the standardized format.
So the question is, will Microsoft support the Power BI Desktop reports in the rebuilt SSRS? Apparently, yes. Both the Power BI team and the SSRS team have indicated that this is their long-term goal.
In an October 2015 post on the Microsoft SQL Server Blog outlining the company's reporting roadmap, the team stated: "Just as we've added mobile report delivery to SSRS in SQL Server 2016, we intend to add governed Power BI Desktop report delivery in the future." A month later, Power BI Product Manager Will Thompson noted: "Aligning our cloud and on-premises solutions, including the ability to run Power BI Desktop reports on your [on-premises] SSRS servers [is our goal]."
Selection Considerations
The proposed future for Microsoft-based reporting and analytics looks good. On its own, SSRS 2016 is becoming a powerful, on-premises reporting platform, while Power BI offers one of the best cloud-based solutions. And a unified SSRS/Power BI solution would offer a solid on-premises solution with a clear path to the cloud. But, SSRS 2016 doesn't yet support Power BI Desktop reports, and there's no clear timeline when it will. So the question becomes: "How does SQL Server 2016 SSRS look until it achieves unity with Power BI?" Let's review some of the key areas for self-service BI and analytics. These areas include:
- Mobile
- Cross-browser support
- Self-service
- Powerful, yet easy to use
- Multiple data sources
- Cloud and on-premises
- Advanced custom reporting
Mobile
Microsoft has already unified its mobile reporting solution. The updated Power BI mobile app, available for the iOS, Android and Windows-based devices, now includes support for on-premises SQL Server 2016 SSRS mobile reports.
SSRS mobile reports are created using the new SQL Server 2016 Mobile Report Publisher. Mobile Report Publisher does a nice job of building basic mobile-targeted reports, but its power is far short of the Power BI framework, has data limitations and introduces an additional report creation tool to be learned.
Cross-Browser Support
Chrome and Firefox dominate browser usage, with Internet Explorer and its replacement Edge trailing behind. To handle the updated browser standards, SSRS uses a new HTML5 rendering engine. This update allows SSRS to support Chrome, Firefox, IE, Edge and other modern HTML5-compliant browsers.
Self-Service
When first released, SSRS report creation required a developer and Visual Studio. Definitely not self-service. A few years later, the SSRS Report Builder was released. It was fairly solid -- if not exciting -- and it bypassed the need for Visual Studio. For 2016, Report Builder has been updated with a new look and feel that closely matches Power BI. It can be freely downloaded by anyone, so it does meet part of the self-service requirement, but it still requires a bit of training and report planning. The new Mobile Report Publisher, like Report Builder, is a separate report creation tool. Very usable, but where Power BI is intuitive, the Mobile Report Publisher takes time to understand and, for most users, will benefit greatly from training.
Powerful, Yet Easy to Use
Where Power BI is both powerful and easy to use, both Report Builder and the new Mobile Report Publisher feel more like traditional development tools. And while Power BI supports statistical R Scripting, SSRS tools do not. Same with power views, limited modeling and other features: Power BI Desktop, yes; SSRS tools, no. Still, established report builders will pick up the SSRS tools quickly and easily.
Multiple Data Sources
Thanks to the ODBC protocol, virtually all report tools have access to traditional data sources. But in our cloud-based, unstructured Hadoop world, report tools need more than just traditional data support. Power BI has this baked in. Report Builder and the SQL Server Mobile Report Publisher are mostly limited to older, traditional data sources. And as it stands now, the mobile publisher requires a pre-built data set typically created using Report Builder. Until such time that SSRS does support Power BI Desktop reports, SSRS will probably be limited to traditional data sources.
Cloud and On-Premises Deployment
Some organizations cannot or will not publish reports, dashboards or other bits of data in the cloud. Ideally, this is where a completely updated SSRS server engine with support for Power BI Desktop reports becomes useful. When, or perhaps if, SSRS fully incorporates the new Power BI Desktop report format, Microsoft will have an impressive on-premises-to-cloud strategy. Until such time, SSRS is still an on-premises reporting server.
Advanced Custom Reporting
Despite the strengths of Power BI Desktop and the enhanced SSRS Report Builder, and the continual movement to self-Âservice BI, there may be a time when a highly customized report is necessary. SQL Server Data Tools, as part of Visual Studio, has been the go-to tool for custom reports, and it continues to fill this need for SQL Server 2016.
Should You Consider SSRS?
With the expectation that SSRS will eventually support Power BI Desktop reports, should an organization consider SSRS and/or Power BI, either in part or as a unified SSRS/Power BI platform? Of course, this might be predicated on the expectation that the new SSRS server will support reports created by the Power BI Desktop application along with the legacy SSRS reports. For you that may well depend on whether your organization intends to rely solely on the cloud, only needs an on-premises solution or is looking for a hybrid that supports both on-premises and cloud-based reports.
Power BI Only
If you're planning on moving forward with Power BI as your core self-service BI platform, it has just about everything you could ask for in a tool. It's easy to use, powerful, inexpensive and can access a vast array of on-premises and cloud-based data. Business users, power users and report builders will find things they like about Power BI. It is cloud-based, so scalability issues don't require more hardware, plus mobile support is automatic. Power BI dashboards and reports display both in a browser and in the Power BI mobile app.
On-Premises Only
Microsoft has completely revamped SSRS and rebuilt it for SQL Server 2016. Improvements include a new interface, HTML5 reports, KPIs, mobile reports and the ability to create a custom brand package. With this combination of power, a high level of customization, support for both browser and mobile, and overall low cost, SSRS is expected to become a much bigger player in the reporting sector.
Mimicking Power BI, the UI has been completely overhauled with a modern design. Menus are well designed and performance is improved. Along with improvements to the UI, one of the technologies from the Datazen acquisition includes the ability to create a custom brand package. This allows IT to easily overlay a corporate logo and custom colors as replacements for the default settings. Reports are rendered using HTML5, allowing access by all modern browsers. Reports can be traditional paginated reports, mobile reports and KPIs. But with all these advances, development tools are fragmented, relying on multiple tools for the creation of reports and components. KPIs are created within the SSRS Web site. Traditional paginated reports require Report Builder or Visual Studio. And mobile reports use the new Mobile Report Publisher. Overall, the improvements to SSRS are impressive, making it a viable on-premises reporting platform.
Hybrid SSRS On-Premises with Bower BI Cloud
Once SSRS supports the Power BI Desktop report format, potentially an organization will be able to create a single Power BI Desktop report and deploy it both on-premises to SSRS and in the cloud. But that is a big "if" premised on a couple of Microsoft blog postings. If SSRS in the future supports the Power BI report format, a unified SSRS/Power BI platform could become a market leader. Without that support, and until SSRS can render Power BI Desktop reports, SSRS and Power BI are just two totally different report systems with different tools and different approaches.
Expectations
All of this uncertainty presents a great risk for those looking for a single, unified BI/analytics and reporting engine. On its own, Power BI and Power BI Desktop provide one of the most powerful yet easy-to-use self-service-oriented platforms. But as of now, it's limited to Microsoft's cloud service or the purchase of a Pyramid Analytics license. If and when Microsoft follows through on its stated commitment to make SSRS its on-premises reporting solution with support for Power BI Desktop reports, the unified Power BI/SSRS combination will be one of the best toolkits available.