we'll look at how Reporting Services is put together. We will walk you through the various processing components, data source extensions, and rendering extensions and take a closer look at how the Reporting Services Web Service works. This chapter also includes a series of illustrations to help you visualize each component.Once you have completed this chapter you will have a good understanding of the Reporting Services "big picture". This knowledge will carry you through the following chapters and help you draw it all together.
This chapter covers:
THE REPORTING LIFECYCLE
Before digging into the architecture of Reporting Services, you need to understand the fundamentals of reporting lifecycle. Reporting platforms can be evaluated by their support for the following areas—authoring, management, and delivery. We will take a look at what is included in each of these phases and later see how Reporting Services implements them. Take a look at the reporting lifecycle block diagram shown in Figure 2-1:
Authoring
The authoring phase is concerned with the actual development of reports. Authoring generally includes the following features:
These capabilities are important for the initial development of the report. They must be flexible enough to handle diverse reporting needs and structured enough to be easy to use.
Management
After developing the report, you move into the management phase, which is concerned with setting properties of reports specific to the production environment. These properties include:
Management phase is generally performed by the administrators. Most of the user access to reports is defined in this phase.
Delivery
The delivery phase looks at how reports get to the end users. Delivery includes:
A common concept in reporting platforms is push/pull delivery. Push delivery constitutes the reports that are sent to the user. Pull delivery constitutes reports that can be accessed on-demand by the user . Users are required to take the effort to get the report information. The report could be emailed to the requestor of the report, or published to a specified fileshare.
REPORTING SERVICES FEATURES
Having seen the main phases in a reporting platform, let's look at the specific features in Reporting Services that make the three phases of reporting services possible. In this section, we will look at the Report Designer, Reporting Services, and the programming interface, and then move on to the specific components that make these features possible.
Visual Studio .NET 2003 Integration
Any respectable reporting platform must provide report writers with a rich set of design tools. Microsoft has created a designer to do just that. Because of the integration with Visual Studio .NET, users can take full advantage of the established development features. The designer also gives several design options to fulfill the users reporting requirements.
The Report Designer is fully integrated with Visual Studio .NET 2003. Through this integration, the Report Designer can take advantage of a number of established tools. Let's take a closer look at what the designer has to offer.
Query Designer
The query designer allows users to visually create data source queries. It works with multiple data sources and should be familiar to people already working with Microsoft products. Users can graphically drag and drop database objects to create SQL queries. They can also switch to a generic query designer to create freeform queries.
Server Explorer
The Visual Studio .NET Server Explorer allows users to view and work with multiple servers. This is extremely helpful when working with Microsoft SQL Server. Users do not have to switch from the Report Designer to other tools to view database objects. They can simply open the Server Explorer and browse for the specific object.
Visual Source Safe
Visual Studio .NET also integrates with Microsoft Visual Source Safe. Report writers can easily store and maintain the version history of their report files. This can be an invaluable tool when working on both large and small scale reporting projects.
Report Designer
Microsoft has created a couple of new Visual Studio .NET templates that allow you to create Reporting Services Report Projects. These Report Projects give you a graphical interface for creating their report definition. This interface displays the data sources used by the report, the layout of the report and also allows you to preview a report before it is published.
The Report Designer also provides a number of controls to facilitate report writing. You can create table reports, matrices, and freeform lists. The ability to combine these controls gives you even more possibilities.
If you want more control over the actual report definition, you can simply switch to Code view and see the XML generated by the designer. Working with the XML might seem a bit complicated, but when you need to do a search and replace a given word, this can be invaluable. It also lets you see a little more of what is created under the covers. Unlike other proprietary formats, XML allows you to easily read and debug report definitions.
Report Server Features
The Report Server is the main component of Reporting Services. It takes care of all report processing, data access, security, and rendering. For a while now, I have looked for what I truly felt was a service-based application—you hear a lot about it in development circles. I believe Microsoft has hit it on the head with this one. Reporting Services is truly a serviced application. Microsoft has elegantly encapsulated all the reporting functionality into one neat package. In this section we'll take a quick look at what's available.
Central Report Storage
Reporting Services creates one central store for your reports. This eliminates the need to deal with a bunch of messy fileshares. On a recent client engagement, I used it to help consolidate all of their different report areas into a hierarchical structure. This is easily accomplished with features available in Reporting Services. Central storage also makes it much easier for your users to access their reports—no more searching a bunch of directories for the item you want!
Security
In any reporting environment, you will have the need to secure certain items. Reporting Services integrates with Windows security to create a flexible role-based security model. This model allows you to create roles with a number of different permissions. You can then assign Users and Groups to these roles.
Reporting Services security is really the combination of three different items:
Report Delivery
Once you have created your reports, you will need an easy mechanism to deliver them. Reporting Services follows the standard push/pull model for reports. Push/pull refers to the ways in which a user can access information.
When you go to a web site and view stock quotes, you are pulling information, hence the push/pull part of the model. Users must be able to access information freely. Reporting Services allows users to navigate through the web to see listings of available reports.
Let's say you order a book online from http://www.amazon.com/ (hopefully this book). After you enter your credit card information, email address, and so on, and press Buy, Amazon sends a receipt to your inbox. This is an example of a push report. Email is just one delivery method for push reports. Reporting Services supports both email and fileshare push deployment along with the ability to create your own delivery extensions.
To take maximum advantage of a truly effective reporting system, users should have the ability to grab information when convenient and get information delivered on a regular basis.
Scheduling
Along with being able to deliver reports via email or a fileshare, a reporting system must have some mechanism to send these items on a regular basis. Reporting Services relies on SQL Server Agent schedule and execute given tasks. Scheduling features in Reporting Services allows individual users and administrators to subscribe to reports on a schedule they define.
But, delivering information is not the only area where a scheduling tool comes in handy. If we think about what part of generating a report takes the longest, it is generally the actual retrieval of data. Reporting Services helps you eliminate some of this wait time by scheduling reports to execute (retrieve their data) on a regular schedule. So, if you update your sales information every Sunday, you could easily schedule a report to run early Monday morning. So when users come in on Monday, they'll have quick access to their information.
Programming Interface Features
When selecting a reporting platform, it is crucial to be able to extend that platform and incorporate it in existing systems. Microsoft has provided a web service interface for accomplishing these goals. Through the web service, you have complete access to the Reporting Services platform. Anything from rendering reports to creating subscriptions can be performed programmatically.
Open Architecture
Why did Microsoft use a web service interface? Web services are built on an open architecture. This means you do not need to have Microsoft development technologies to take advantage of them. The key to web services is that they are built on industry standard technologies. Through the use of XML, SOAP, and HTTP just about any platform can call and use web services.
Complete Access
Not only did Microsoft create a platform-neutral programming interface, they also let you do anything you need to through the interface. It is common to work with an API where the developer has limited control over what happens. With Reporting Services, the world is really open to you. If you just don't like the administrative tools that come with Reporting Services, you could write your own.
Most people do not go to this extreme, but it is important to have the flexibility. This means that as an application developer you can add any part of Reporting Services you need into your application. A common example would be creating your own custom interface for rendering reports. You could make it much easier for your users to get the report in exactly the format they want. Another example would be creating your own subscription interface. You might already know the users email information, so they click a few buttons and the subscriptions are done.
The possibilities for using the Reporting Services Web Service are only limited by what the product can do. This should be incredibly good news to all the code junkies out there.
REPORT SERVER COMPONENTS
Now that we have taken a look at what Reporting Services can do, let's get down to the nuts and bolts of how it works. In this section we will focus on the various components of Reporting Services. We'll move through processing of the report and data to rendering and delivering reports.
Report Processor
Report processing is the main driver in the Report Server. The Report Processor is responsible for handling user requests and returning the appropriate report and data. Along with this task it also performs caching of reports to improve performance. Let's take a look at the individual components that constitute report processing. The main job of the Report Processor is to combine the report definition and report data to create and return this data.
Report Request Handling
When a report request is received, the report processor takes the following steps:
Report Definition
The report definition is an output format and a neutral representation of the report. Reporting Services was designed to support numerous output formats, so the report definition is not aware of how the report will actually be rendered. The report definition defines the query and layout of a report. These are things like the tables contained in the report, their position in the report, and number of columns. The query information is then used to retrieve data and to combine it with the layout. Once the report definition and data are combined, they form an intermediate format.
Intermediate Format
The intermediate format is an internal format of the report used by Reporting Services for rendering and caching. It is this format that is sent to the rendering extensions. It is a combination of both the data and report definition. The size of the intermediate format will depend mostly on the data that is returned.
Caching
The report processor also handles the caching of reports. When working with a reporting solution, the main bottleneck in report performance inevitably is the execution of queries. To solve this problem, Microsoft has developed a number of caching strategies. These strategies offer various performance gains and flexibility. The basic premise of a cached report is that we stored the report definition and data together. Thus when a user requests a report, the only thing that needs to be performed is the actual rendering. Rendering by comparison with other activities, is a relatively inexpensive part of the report processing in terms of server resources. Let's take a look at each of the caching strategies.
Session Cache
Since Reporting Services works over HTTP (we will talk more about this later), it must maintain some information about each user request. This is referred to as session information. If the same user asks for the same report in a relatively short period of time, it does not make sense to query that information again. So, when a user makes an initial request, the report definition and data are stored in the session cache. Session cache is used with on-demand reports (reports not cached).
Cached Instances
Cached instances also store the report definition and data, but they must expire at some given time. This time frame could be an hour, week, or month. The actual start time, however, is not defined. With a cached instance, the first user that requests the report has to wait for the query to process. After this initial request, the timeout is started. Once the report expires, the next user requesting the same information will have to execute the associated query.
This caching strategy is perfect if you have slower changing data, or even data that changes frequently but is not critical to update as soon as it changes. A good example of this would be stock quotes. They update constantly. It would be incredibly taxing on software systems if they had to keep up with this rapid rate of change. So, instead of updating every millisecond, stock quotes are generally updated every five to ten minutes.
Snapshots
The final type of caching strategy is the snapshot. Like the name implies, it is a snapshot of the data at a given point in time. Unlike cached instances, snapshots have a defined start time and not definite end time. Let's say you have a group of users that needs summary reports every Monday at 7:00 am for a weekly meeting. You are in the data warehousing group and have jobs that process data late Sunday night in preparation for the meeting. Once the data is processed, it does not change. This data is also very large and takes a long time to query. In that case, it makes perfect sense to store the reports right after the information is available. This way when people come in Monday morning to run the reports, they are kept ready and the users will not have to wait for any query processing. Therefore, by using snapshots, data is made available at a specific time within minimum amount of processing time.
Working again from our example above, a week goes by and you are again ready to run your reports. Would you want to get rid of the previous week's information? Certainly not—as soon as you do someone is bound to ask for it! So, you need a mechanism to store that information away. Reporting Services gives you the ability to store a history of report snapshots. That way, even though the new snapshot has been created, the old one is still available.
Report Processing Illustrated
Figure 2-2 shows the process of requesting a report and moving through the Report Processor:
DATA PROCESSING EXTENSIONS
Now that we have seen the Report Processor, let's take a look at how the actual data is retrieved. Data is returned to the Report Processor through the Data Processing Extensions. The Data Processing Extension that is used will depend on the data source defined in your report. We describe the common functions of all these extensions and then those that are supported by Reporting Services.
Data Processing Defined
Data Processing Extensions are used to return data from a given data source. The architecture in Reporting Services supports the .NET managed providers and allows you to create extensions for your own particular data source.
The common functions that all Data Processing Extensions perform are as follows:
Supported Providers
Reporting Services supports the .NET managed providers for returning data. These include SQL Server, OLEDB, ODBC, and Oracle. Since they are managed providers, they take full advantage of the .NET Framework. Using these four data providers, users should be able to connect to just about any data source. Let's take a look at some common providers.
SQL Server Provider
Using the SQL Server provider, users can retrieve data from SQL Server tables, stored procedures, views, and User Defined Functions (SQL 2000). The SQL Server managed provider is optimized to connect to SQL Server. Extra layers such as OLEDB and ODBC have been removed for optimal performance.
Oracle Provider
Although Reporting Services uses SQL Server to store its metadata, you can use Oracle as a source for your reports. Like the SQL Server managed provider, the Oracle managed provider is optimized for Oracle and removes extra layers such as OLEDB and ODBC.
OLEDB Provider
The OLEDB provider gives report writers a great deal of flexibility. Using this provider, you can query a number of different data sources. The following is a list of just a few:
ODBC Processing Extension
ODBC works through the .NET OLEDB managed provider. The ODBC Processing Extension allows users to access any system with a compatible ODBC driver. This opens the door to reporting on a number of legacy systems, such as dBase and FoxPro. ODBC drivers have been written for most of today's database systems.
Be careful when using an ODBC driver. It is possible to connect to SQL Server, Oracle, and a number of data sources listed earlier. If you use an ODBC driver to connect to these data source instead of the native .NET managed provider or OLEDB provider, you could seriously hamper query performance. With your data sources, look for a .NET managed provider first, then an OLEDB provider, and if neither of these options is available, use an ODBC driver.
Data Processing Extensions and Data Providers
Along with the four .NET managed providers, users can also create their own custom Data Processing Extensions or data providers through the Reporting Services API. This allows users to expose the functionality of their data source to the end user and achieve some performance gains. The .NET Framework also allows the creation of .NET data providers. Since Reporting Services supports .NET data providers, this would be another viable option for connection to your custom data source. The role of Data Processing Extensions in relation to the report processor and the data source is shown in Figure 2-3:
Supported Rendering Extensions
Reporting Services also supports a number of different rendering extensions. When creating a report in Reporting Services you are creating them in a neutral output format. In the report, you define the query, the fields, and how they should be laid out. It is the job of the rendering extensions to take this information combined with the data and create a useful output. Often, this is not an easy task. Let's take a look at some of the supported extensions. Microsoft has provided seven different rendering extensions. Each of these can be used to return report information.
Excel
The Excel rendering extension takes report data and outputs it to Excel. This is a common format for many users, especially for those users who will perform further analysis on the information.
The Excel rendering is more sophisticated than competing reporting platforms. Many reporting platforms lay out reports in a banded format. If you are familiar with Microsoft Access, you will understand the different bands for data detail, grouping headers and footers, page headers and footers, and report headers and footers. While this type of banded report does offer an extremely flexible report design, it does not always translate well into an Excel document.
By incorporating table and matrix controls in reports, users can create report layouts almost like they would in Excel. This type of layout lends itself nicely to rendering in Excel. At the time of writing, Excel rendering is limited to Office XP and Office 11.
PDF
Microsoft also provides a rendering extension for PDF Format, which is probably the most popular document format on the web. It is clean and easy to read and has printing capabilities. You would most likely choose this format for reports that are widely distributed but not analyzed by the end users. The reports that are in PDF format cannot be altered. Some common examples would be invoices, inventory pick tickets, weekly sales summaries, and a company's public financial documents.
PDFs also support document map functionality. This feature in Reporting Services allows you to define bookmarks within your report. Once the report is rendered, users can click on links to easily navigate to different areas of the report.
End users can download Adobe Acrobat Reader for free and you do not need a license to distribute PDF documents generated by Reporting Services.
HTML
Probably the most common output format for reports in Reporting Services is HTML. Since both the Designer Preview and Report Manager work in this view, reports can be rendered in HTML 4.0 or HTML 3.2. The .NET Framework looks at the user request to determine which browser is being used and then renders the report in the appropriate HTML format.
HTML rendering is good for interactive reports. By navigating to a web site, a user can easily manipulate report parameters to find specific information. HTML rendering also supports dynamic visibility, which gives users the ability to drill down to detailed information and supports document maps for easier navigation. Users can also render reports to HTML with Office Web Components. This allows for even greater manipulation of report information.
HTML rendering, however, is not good for printing. HTML pages are truly meant for displaying information. Most web applications will allow users to click a link and print printer friendly information. In Reporting Services, users can simply export a report to PDF or Excel and print it from there.
Web Archive (MHTML)
Web Archive or MHTML is commonly found in email messages. MHTML stands for MIME Encapsulation of Aggregate HTML Documents and these files have a .MHT extension. Generally an HTML document references a number of external resources such as images and style sheets. Although HTML allows for rich formatting of documents, it is hard to transport them when they reference other independent objects. MHTML takes care of this by encapsulating the externally referenced information such as images into one document.
MHTML documents are useful when sending out subscriptions. If users would like to view reports through email without opening an attachment, then MHTML is the appropriate format. One thing to note though is that not all email clients support this standard, so check your User Communities setup first.
CSV
The Comma Separated Value (CSV) format takes the report definition and data and transforms it into a flat file. This type of output is not appropriate for reading. It is suitable as a data exchange format. You might have customers with legacy systems that are very good at parsing and consuming flat files. In this case, you could electronically send reports in CSV format to these users. They could in turn consume the data and report on it or manipulate it how they see fit.
TIFF
The Tag Image File Format (TIFF) is a widely used format for storing document images. Many facsimile programs use this format to transfer data. Many organizations store documents in document management systems such as SharePoint Portal Server. Reports rendered in TIFF format would be excellent candidates for this type of document system. You could place historical snapshots of reports into your document management system and then remove them from the Report Server. This would allow you to take advantage of common document management features such as indexing.
XML
The Extensible Markup Language (XML) is very different from CSV rendering, but can serve many of the same purposes. XML is a structured markup language that lets you define data. Reporting Services uses this markup in a number of areas. When reports are rendered as XML, they include both the report definition and data, much like CSV rendered reports. Similar to CSV files, XML files are designed explicitly for the exchange of information. You could send XML rendered reports to customers or other applications for additional processing, or you could run the XML rendered report through an XML Transform document to control the standard formatting of the document.
Customized Extensions
Along with the seven supported rendering extensions, the Reporting Services API also allows users to create their own rendering extensions. So, if you want to output a report to a Word or a GIF file, you could create your own extension. Report rendering is illustrated in Figure 2-4:
Scheduling and Delivery Processor
The Scheduling and Delivery Processor has two major functions, creating report snapshots and delivering subscriptions. These tasks hinge on the use of Microsoft's SQL Server Agent. The SQL Server Agent is responsible for queuing scheduled events. Reporting Services monitors these events and takes appropriate action as and when required. Scheduling and delivery have been encapsulated together because they use similar functionality. In both cases, Reporting Services is watching for a given event, processing the report, and then either storing that processed report or delivering it. Let's take a closer look at both the scheduling and delivery areas.
Scheduling
Scheduling refers to the actual setting up of the report execution and delivery schedule. When we store report schedules, this information is relayed to the SQL Server Agent to queue the request at the appropriate time. Both users and administrators can define schedules for report execution and delivery.
Delivery
Delivery deals with the mode of delivery of reports in Reporting Services. Users can have reports delivered via email, a fileshare, or a customized delivery extension, you will see more on delivery extensions in the forthcoming sections.
There are two types of subscriptions available in Reporting Services, standard and data-driven subscriptions.
Standard Subscriptions
Users can create their own subscriptions through Report Manager or a custom interface. Additionally, administrators can also create subscriptions for users. When setting up a standard subscription, information such as parameter values and rendering format can be set along with a schedule for report delivery.
With standard subscriptions, users can define their own schedule for receiving a report. This is important for small scale reports and gives users a great deal of freedom in how they receive certain information.
Data-Driven Subscriptions
Data-driven subscriptions offer a great deal of flexibility when delivering reports. You can create reports for any number of users, use different rendering formats for each user, and even change report parameters for each. This allows you to create a very custom report experience for users with a minimal amount of work.
Think of a large retail organization where each store in the organization has a store manager. Each week the store manager receives the sales numbers from the previous week. The report is identical for each manager except for the reference to the actual store. So, using data-driven subscriptions, you could dynamically set the store report parameter for each report and then email these individual reports to each manager. In the end you have created only one report, but quickly tailored it for a number of different users.
In both standard and data-driven subscriptions, the delivery of these reports is event-driven.
Schedule-Based Events
One of the common methods of determining when reports are to be delivered is doing so through some sort of schedule. The report could be delivered every month, week, day, or at any such pre-decided interval of time. Reporting Services gives users a number of different options when setting schedules. These schedules can be either specific to a given subscription or shared through Reporting Services.
Let's imagine that your organization has a set of reports that have their underlying data updated every Sunday evening. Executives for a Monday morning meeting can use this information. You could define a shared scheduled that contains information such as "Weekly, Monday, 6 am". This shared schedule could then be used for any number of reports. If later you decide that sending the report at 6 am does not meet new requirements, you could simply edit the shared schedule and thereby change the schedule for each report using it.
Snapshot Update Events
Delivery of reports can also be triggered by the update of snapshot reports. Many reports in an organization have set intervals after which they are updated. For example, data for a monthly sales report is always updated on the last day of the month. Once this has happened, the data is frozen and does not change for an entire month. If you want to create a report from this information, does it make sense to query the database each time the report runs? No, the information at this point is static. So, we create a snapshot of the data at the end of each month and store the entire report. At this point when users display the report you no longer have the overhead of a database call.
If we were going to update our reports according to a given schedule, it would only make sense to deliver them to the appropriate users when they are ready. Reporting Services allows users to set their subscriptions based on updates to snapshots. Through this method, we do not have to worry about setting a defined time when we think the report will be done processing; instead it will send off the delivery when report processing is finished.
Scheduling and Delivery Processor Illustrated
The following set of illustrations (Figure 2-5) will walk you through the various tasks performed by the Scheduling and Delivery Processor. Let's begin by handling the initial subscription and then move on to running snapshots and delivering subscriptions.
Now that we have seen how report schedules are created, we can look at both snapshot processing and subscriptions processing (Figure 2-6).
The final piece of scheduling and delivering is subscription processing. In Figure 2-7, you can see the individual steps in the processing of report subscriptions.
Delivery Extensions
Delivery extensions are tied heavily to the Scheduling and Delivery Processor. They are used when sending subscriptions to users. Microsoft has provided two delivery extensions and given users the ability to develop their own. Reporting Services comes with two delivery extensions—email and fileshare. Let's take a look at each.
Email
The email delivery extension allows users to receive reports directly in their inbox. You can specify the rendering format that you would like the report to be delivered in and whether or not to include a web link to the report. Depending on the rendering extension used in the report, users will either see the report directly in their mailbox or receive it as an attachment. As mentioned earlier, you could use the Web Archive (MHTML format) to embed reports and their images in an email message. To send email deliveries, Reporting Services must be able to communicate with a valid SMTP server. This setting is initially set when installing Reporting Services.
File Share
Reports can also be delivered directly to a fileshare. For this, Reporting Services must have Write permissions to the share. You can also specify credentials to use when sending reports to a fileshare.
Custom Extensions
Along with the supported extensions, Reporting Services also allows for the creation of custom delivery extensions. Say you like monthly reports to be delivered directly to a printer after they have been processed. You can create your own delivery extension and then schedule a subscription to use this delivery extension. In the Reporting Services sample folder, you can find an example for creating a delivery extension for a printer. Delivery extensions are illustrated in Figure 2-8.
Report Server Databases
Reporting Services relies on SQL Server for storing its metadata. This allows for greater scalability in large reporting applications. This also allows you to take advantage of features inherent to SQL Server, such as backup and transaction logging.
Reporting Services uses two SQL Server databases to store data, ReportServer and ReportServerTempDB. In the next section, we will take a look at the major components of each database and describe how they are used. We will also take a quick look at a Data Transformation Service (DTS) package provided for monitoring information.
ReportServer Database
The ReportServer database is the main store for data in Reporting Services. It houses all report definitions, data sources, schedules and delivery information, security information, and snapshots and snapshot history. There are a series of tables for each functional area. The database schema is open and generally easy to follow. Updating or querying these database tables is not recommended, but an understanding of how they are arranged should give you a better understanding of how Reporting Services works.
The following table lists some of the tables in the ReportServer database and their related functions: When working with Reporting Services, it is important to pay close attention to the ReportServer database. It contains all critical information related to Reporting Services and should be backed up on a regular schedule.
ReportServerTempDB Database
As the name implies, the ReportServerTempDB database stores temporary Reporting Services information. User session information is stored in the ReportServerTempDB. Because Reporting Services communicates using HTTP, no state is maintained between the client application and the server. Session state about the reports that the user is running must be stored between each server call. The ReportServerTempDB stores this information in a SessionData table.
ReportServerTempDB also stores report cache information. When a report is set as a cached instance, there is no definite time when that report is executed. It depends on which process requests the report first. Once the report is executed, the intermediate format and data are stored in the ReportServerTempDB database. If this database were to fail, the cached information would be lost. But, since it is executed when a user views the report, there is no real loss of information. Snapshots, on the other hand, are not stored here. Their execution time is usually at a set moment to ensure that the data on the report is correct. Therefore, this information is stored in the more permanent ReportServer database. Reporting Services will not be able to function without the ReportServerTempDB database. It is not ncessary to backup the data in the ReportServerTempDB.
Viewing Execution Information
As mentioned earlier, it is not recommended to view or modify the underlying SQL Server tables. It is also very difficult to analyze execution information in the ReportServer database. So, Microsoft has provided a DTS package for moving this data out of the ReportServer database.
To use the DTS package, you must first create a database to hold execution log information. Microsoft provides a number of scripts in Reporting Services to help with this task. Once the database has been created, you can use the DTS package to move information into it. Once the information has been moved to the new database, you can run queries against it or maybe even create a Reporting Service report.
THE REPORTING SERVICES WEB SERVICE
One of the most outstanding aspects of Reporting Services is its open programming interface. Everything that we have seen so far can be performed through the Reporting Services Web Service. The Reporting Services Web Service is a set of functions you can use to render, subscribe to, and publish reports.
Reporting Services takes advantage of technologies already implemented in Internet Information Server (IIS) and the .NET Framework. Both these components provide the backbone infrastructure for web services. IIS performs web request handling and routing along with some security. The .NET Framework provides classes for consuming and publishing web service interfaces.
To understand the Reporting Services Web Service, you must first understand the underlying technology.
Web Services
Web services have really been a hot topic over the last few years. With them comes the promise of various applications exchanging information freely with one another. No more complicated interfaces for calling code on disparate systems—just one set of standards that everyone can follow.
The standards are what make web services so inviting. In the past, different companies have come up with their own standards for interfacing different sections of code. COM for example is a specification that allows code written in Visual Basic to talk to code written in C++. This is fine if you are working on Microsoft platforms, but what if you want your Visual Basic application to use functionality in a COBOL application. You would have to write some cumbersome code to make this happen. Let's take a look at the standards that make web services possible.
Open Standards
There are a number of open standards that make web services possible. When you think of code communicating with other code and all the tasks that entails, you quickly find some similarities. First of all, you need some transport mechanism for sending information.
One of the most widely adopted standards for sending information is Hypertext Transfer Protocol (HTTP) which is the default standard for web communication. It has the ability to send information back and forth between remote machines and has a huge implementation base. All major platforms today support sending information via this protocol. Now that we have a transport mechanism, we need to package (or address) the information.
Simple Object Access Protocol (SOAP) is a messaging protocol designed specifically for the distribution of information via HTTP. SOAP messages define a standard to package data and send it across the Internet. Some of this information includes header information for the message, security information, and the actual message body of itself. SOAP also uses XML as part of its protocol. So, you've got a package and a way to send it—now all you need is the message. Web service messages are sent encoded in XML. This allows services to have richly defined interfaces and yet be able to use the structure of XML. It also allows other systems to easily read and manipulate data.
Visual Studio .NET Integration
Visual Studio .NET has complete support for using web services. Consuming web services is almost as easy as working with objects in the .NET base classes. Using the Visual Studio .NET IDE, you can easily add web references to your projects and get full access to a web services. Using the Reporting Services Web Service through Visual Studio .NET allows you to take advantage of the IDE built-in functionality.
Although Visual Studio .NET makes it easy to work with web services, it is not the only development option. Because web services are built on open standards, any development tool supporting these standards can be used to work with them. For example, if you want to integrate Reporting Service functionality into your Microsoft Office applications, you can. Through Visual Basic for applications, you can write code that calls web services and therefore can call Reporting Services. You might want to build a list of reports into an existing Microsoft Access application. This could be easily accomplished in a few lines of code using the Reporting Services web service.
Available Features
Any feature that you use in the Report Manager interface can be used or accessed through the Reporting Services Web Service. There are no special calls from the Report Manager to Reporting Services.
Here is a list of just a few things available through the Reporting Service Web Service. The rest of the book will go into detail on these topic areas:
The list could continue on with a number of different features. Just remember that anything you do in Report Manager can be done through the Reporting Service Web Service.
REPORT DESIGNER
Creating reports in Reporting Services is very straightforward. Microsoft has provided a set of tools that allow you to easily build and publish your reports. In this section, we will take a look at how the Report Designer is incorporated in Visual Studio .NET and also explain the RDL file created by the designer.
Visual Studio .NET
Microsoft has chosen Visual Studio .NET as the standard development tool for their products. Along these lines, they have incorporated the Reporting Services Report Designer into Visual Studio .NET. Visual Studio .NET provides a number of other features other than the Report Designer.
Once you have installed the Reporting Services, there are a couple of project templates added to Visual Studio .NET. Inside Visual Studio .NET you will see a folder called Business Intelligence Projects. This folder contains both the Report Project Wizard template and Report Project template. Choosing either of these project templates will load the Report Designer.
Report Definition Language (RDL)
Now let's take a look at what exactly the Report Designer does. The Report Designer allows you to visually layout reports and build their underlying queries. This information is used to create an, RDL file. An RDL file is a XML document that defines the elements of a report. It is this file that is eventually published to the Report Server. Once the file is published, the report definition is stored in the ReportServer database. Any subsequent publishing of the report replaces most of the definition stored in the ReportServer database. You will learn more about RDL in Chapter 11.
REPORTING SERVICES TOOLS
There are a couple of tools included with Reporting Services. These tools allow you to publish reports, modify data sources, set security information, and a number of other tasks. Each of these tools relies on the Reporting Services Web Service. Anything that you can perform with these tools can be written in your own custom code. Let's take a look at a couple of the major tools, Report Manager and RS.EXE.
Report Manager
Report Manager is the main management tool for Reporting Services. It provides the following functionality:
We will take a closer look at the Report Manager in Chapter 5.
Report Server Command-Line Utility (RS.EXE)
Reporting Services also comes with a command-line utility to simplify management of reports. After installing Reporting Services, you will find a file named RS.EXE. This utility contains a reference to the Reporting Services Web Service and allows users to call any of the service methods. To use the Reporting Services command line utility, you must first create a Visual Basic .NET input file. This file contains one main procedure and then instructions for working with the Report Server. The commands in the Visual Basic .NET file rely on the Reporting Services Web Service to perform their tasks. This file can then be passed into the command-line utility for execution.
A common example of this would be deploying development reports on a set basis. You can create a command line utility that checks for files updated in the Report Definition files. Once file changes are found, you could then use the Reporting Services command line utility to publish these files to the report server. Reporting Services comes with a few samples that demonstrate how to perform various tasks using RS.EXE.
REPORTING SERVICES ILLUSTRATES
Now that we have seen all the components that make up Reporting Services, let's take a look at an overall illustration. Figure 2-9 shows the different components of Reporting Services and how they are grouped. Notice that the actual functionality of Reporting Services is all encapsulated in one area. The databases storing data for the Reporting Service are separate. This means that we can physically separate the different components of Reporting Services to take advantage of web farm configurations (multiple servers combined to distribute processing), allowing us to create highly scalable applications.
Also notice that all calls to the Reporting Service go through the Reporting Services Web Service. This means that you can create your own custom front ends and have complete access to the Reporting Service.
Tuesday, February 12, 2008
Reporting Services Architecture
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment