Introduction to SQL Reporting

Maybe you’ve heard of Windows Azure and SQL Server, and even SQL Server Reporting Services. But what exactly is Windows Azure SQL Reporting? In this introduction, I start by providing an answer to this question. Next, I provide a comparison of SQL Reporting to Reporting Services, so that you can better understand the similarities and differences between a cloud-based and an on-premises reporting solution. At this point, you might still wonder why you would need SQL Reporting instead of using a perfectly good Reporting Services solution, so I also explain some of the advantages of SQL Reporting and describe some common scenarios for which it’s well suited.

Note: Although familiarity with SQL Server Reporting Services is helpful, it is not required. You will learn the basics necessary to successfully create, deploy, manage, and view reports in this e-book.

What Is SQL Reporting?

A simple broad explanation is that SQL Reporting is a version of Reporting Services…in the cloud. But what does it mean to be “in the cloud?” Conceptually, an application in the cloud relies on a hardware, security, application, and networking infrastructure that you access over the Internet and is built, maintained, monitored, and tuned as a commercial service by a third party. Typically, you pay a subscription to use the computing power delivered by the cloud provider. In the case of SQL Reporting, that third party is Microsoft.

Like its on-premises predecessor, Reporting Services, SQL Reporting is a platform that supports three different types of activities that we call the reporting life cycle:

• Report development
• Report management
• Report access

Although SQL Reporting does not itself provide tools for report development, it supports the same tools commonly used by professional report developers and less technical business users who create reports for Reporting Services. SQL Server Data Tools, Business Intelligence Development Studio, and Report Builder allow you to create a report locally. You can then later connect to your SQL Reporting server to save it.

After you have saved one or more reports to SQL Reporting, you use a portal to perform a variety of report management tasks. You can organize reports into folders, secure reports, and monitor usage.

SQL Reporting supports report access to users through a portal, direct access by using a URL, or a custom application using a Web service method. Users can view a report online, or export it to a variety of other formats, such as Excel and PDF.

How Does SQL Reporting Compare to Reporting Services?

Although SQL Reporting is similar to Reporting Services, it doesn’t have parity with the features in Reporting Services. Table 1 summarizes the features supported in each product.

Feature Comparison

Reporting Services is a mature, full-featured, on-premises reporting platform capable of providing access to reports in a variety of ways to people inside or outside your organization through on-demand execution or scheduled delivery processes. To ensure optimal performance, it includes options for background processing and cache management.

By contrast, SQL Reporting is a component of the Windows Azure platform that is used as a cloud-based service for a portion of the reporting life cycle. In essence, you exchange your on-premise server for a comparable server, or farm of servers, in the cloud. Although you continue to build reports using the same tools available for Reporting Services, you cannot customize SQL Reporting reports through the use of embedded code or external assemblies. Furthermore, you cannot use external images or export a report as a data feed.

After you publish the reports to SQL Reporting, you can use an administration portal to organize and secure content in the cloud. However, many of the administration features available in Reporting Services are missing. For example, you cannot configure background processing options such as caching, snapshots, or subscriptions, nor can you manage report history.

When you access reports from SQL Reporting, you can access them from a reporting portal, but it lacks the interface that you find in the Report Manager web application available in Reporting Services. Instead of a polished user interface, you see a list of links to folders and reports. Once you open a report, the experience between SQL Reporting and Reporting Services is very similar. One exception is the inability to export a SQL Reporting report to a data feed. All other export options found in Reporting Services are available in SQL Reporting.

Why Use SQL Reporting?

Why use SQL Reporting if it doesn’t have feature parity with Reporting Services? For some situations, SQL Reporting is definitely not the right solution. However, there are many scenarios for which it is a perfect fit.

Before considering such scenarios, let’s consider some advantages you gain by having a cloud-based reporting solution. First, you can add a new server in just a few minutes, whenever you need it, and for as long as you need it. You simply can’t make that happen in your own organization as quickly and cost-effectively considering the time and money needed to acquire hardware and software and then to get it configured, secured, installed with software, and so on.

Furthermore, if you need to support a lot of users, you need to create an environment that scales. An on-premises solution requires more time, money, hardware, and resources to achieve scale, but with SQL Reporting, you can add more servers as needed very easily, and you can take them away just as easily. Like other services available for the Windows Azure Platform, SQL Reporting is designed to support elastic scale. You only pay for what you use and can add or release excess capacity, depending on your needs. Based on your day-to-day, month-to-month, or seasonal requirements, you can easily and quickly add subscriptions and report servers to extend the capacity of your SQL Reporting environment or delete subscriptions and servers to decrease it.

A cloud-based reporting solution frees you from maintenance overhead activities such as applying patches to a server and ensuring it has been updated with the latest service packs. At this point, the extent of management for your SQL Reporting Server is limited to adding users and granting them permissions to the server resources. SQL Reporting includes a management portal where you can perform these tasks manually, or you can develop your own security management application using the Web service API.

SQL Reporting offers enough flexibility to adapt to a variety of needs. Just as no two Reporting Services implementations are identical, there are a variety of ways to use SQL Reporting. Let’s consider four possible use case scenarios for SQL Reporting:

• Small company
• Large company
• Limited project life span
• Cloud application developer

Small Company

Even a small company has questions about how well the business is doing. You might have one or two business applications collecting data, but you need a better way to use that data. When you don’t have a formal IT department, you might not have anyone in your business with the right skills to set up and maintain a report server. In this case, SQL Reporting can deliver the capabilities you need without adding staff.

SQL Reporting allows you to start gaining insights into your data even without a formal set of reports in place. You can set up a variety of report parts that people can use to construct their own reports as part of SQL Reporting’s self-service business intelligence features. That is, people can build their own reports in response to specific questions using pre-constructed report parts and datasets without knowing how to write queries or configure data regions. Even better, when your organization has a centralized place to store the resulting reports, everyone in the company can take advantage of what someone else has created, and that’s how new insights are developed. The bottom line is that business intelligence is not just for big companies.

Large Company

In a large company, you have an IT staff and a technical infrastructure in place to support reporting needs. However, the need to support mobile workers has become increasingly more common. These mobile workers need web access to corporate data when they’re away from the office whether that’s through a browser or a mobile device such as a smartphone or tablet. Although this scenario is supported by Reporting Services, it’s not trivial to set up. Security can be challenging to configure, any time you open access to the corporate network to the Internet, there is a real risk that this opening can be exploited.

You can isolate data that people need for reporting by putting it in SQL Database, a cloud-based relational database platform. Then build the reports your mobile users require and deploy those reports to SQL Reporting. That way, they can retrieve the data they need securely without putting your corporate network at risk.

Limited Project Lifespan

Regardless of the size of your company, another scenario that lends itself well to a SQL Reporting solution is a project that has a limited lifespan but requires you to scale a solution quickly for thousands of users. As an example, let’s say you have a quarterly survey for which there is a lot of activity spanning a few weeks, during which time you gather the data, and then during the next few weeks, you share the aggregate data with these users.

When you use an on-premises solution based on SQL Server and Reporting Services for a project such as this, there are many steps to take before your solution is ready for production. You must acquire the hardware, install the software, perform thorough load testing, and so on. Considerable time and money is spent to support a requirement whose lifetime is measured in weeks.

On the other hand, with a cloud solution based on SQL Database and SQL Reporting, you can add servers when you need them, enable your application, gather the data, and deliver reporting with minimal effort. It takes only minutes to get a scalable and highly available infrastructure in place. Later, when you’re ready to retire the project, you can archive the data back to an on-premises server.

Cloud Application Developer

Yet another scenario to consider is integration with a Windows Azure cloud application. Let’s say you’re a developer of an application that performs a service and, like a traditional on-premises solution, gathers data in the process. Further, you want to include reporting as part of your solution. There’s no need to develop a reporting mechanism yourself. You can simply add SQL Reporting to your application architecture and easily deliver reporting capabilities to your users.