SQL Server 2008 R2 Basics: SSAS, SSRS, and SQL Service Broker

SQL Server Analysis Services (SSAS)

SSAS delivers online analytical processing (OLAP) and data mining functionality for business intelligence (BI) solutions. SSAS offers a rich set of data mining algorithms to enable business users to mine data, looking for specific patterns and trends. These data mining algorithms can be used to analyze data throughout a Unified Dimensional Model (UDM) or directly from a physical data store. SSAS uses both server and client modules to supply OLAP and data mining functionality for BI apps. SSAS consists of the analysis server, processing services, integration services, and a number of data providers. It has both server-based and client-/local-based analysis services capabilities. This essentially offers a complete platform for SSAS. The basic modules within SSAS are all focused on building and managing data cubes. SSAS allows you to build dimensions and cubes from heterogeneous data sources. It can access relational OLTP DBs, multidimensional data DBs, text data, and any other source that has an OLE DB provider available. You don't have to move all your data into a SQL Server DB first; you just connect to its source. In addition, SSAS allows a designer to implement OLAP cubes, using a variety of physical storage techniques directly tied to data aggregation requirements and other performance considerations. You can simply access any OLAP cube built with SSAS via the Pivot Table Service, you can write custom client apps by using Multidimensional Expressions (MDX) with OLE DB for OLAP or ActiveX Data Objects Multidimensional (ADO MD), and you can use a number of third-party OLAP-compliant tools. MDX enables you to formulate complex multidimensional queries.

SSAS is commonly used to execute the subsequent tasks:

Execute trend analysis to predict the future. As an example, based on how many widgets you sold last year, how many will you sell then year?
Combine otherwise disconnected variables to increase insight into past performance. As an example, was there any connection between widget sales and rainfall patterns? Searching for unusual connections between your data points is a typical data mining exercise.
Execute offline summaries of commonly used data points for instant access via a web interface or custom interface. As an example, a relational table might contain one row for every LeftClick on a website. OLAP can be used to summarize these clicks by hour, day, week, and month and then to further categorize them by business line.

Included for Analysis Services in SQL Server 2008 R2 is PowerPivot for Excel and PowerPivot for SharePoint. PowerPivot for Excel and SharePoint are client and server modules that integrate Analysis Services with Excel and SharePoint. PowerPivot for Excel is an add-in that allows you to create PowerPivot workbooks that can assemble and relate large amounts of data from different sources. PowerPivot workbooks typically contain large, multidimensional datasets that you create in a separate client app and use with PivotTables and PivotCharts in a worksheet. The PowerPivot add-in removes the one million row limit for worksheets and offers rapid calculations for the large data that you assemble.

PowerPivot for SharePoint extends SharePoint 2010 and Excel Services to add server-side processing, collaboration, and document management support for the PowerPivot work-books that you publish to SharePoint. Together, the PowerPivot client add-in and server modules provide an end-to-end solution that furthers business intelligence data analysis for Excel users on the workstation and on SharePoint sites.

SQL Server Reporting Services (SSRS)

SQL Server Reporting Services is a server-based reporting platform that delivers enterprise, web-enabled reporting functionality so you can create reports that draw content from a variety of data sources, publish reports in various formats, and centrally manage security and subscriptions. Reporting Services includes the subsequent core modules:

A complete set of tools you can use to create, manage, and view reports
A report server component that hosts and processes reports in a variety of formats, including HTML, PDF, TIFF, Excel, CSV, and more
An API that allows developers to integrate or extend data and report processing into custom apps or to create custom tools to build and manage reports

There are two design tools for building reports: Report Designer, a powerful development tool integrated with Visual Studio, and Report Builder 3.0, which is a simpler point-and-LeftClick tool that you use to design ad hoc reports. Both report design tools provide a WYSIWYG experience.

Reports are described using the Report Definition Language (RDL). RDL contains the description of the report layout, formatting info, and instructions on how to fetch the data. After a report is defined, it can be deployed on the report server, where it can be managed, secured, and delivered to a variety of formats, including HTML, Excel, PDF, TIFF, and XML. Various delivery, caching, and execution options are also available, as are scheduling and historical archiving.

One major set of enhancements in SQL Server 2008 R2 includes the new and improved features in Reporting Services, which includes:

New features for SharePoint integration with Reporting Services—These features comprise support for multiple SharePoint Zones, support for the SharePoint Universal Logging service, and a query designer for SharePoint Lists as a data source.
Report parts—These are reusable report items that are stored on a report server or on a SharePoint site integrated with a report server.
Shared datasets—These datasets can be shared, stored, processed and cached exter-nally from the report, thus providing a consistent set of data that can be shared by multiple reports.
Cache refresh plans—These plans allow you to cache reports or shared dataset query results on first use or from a schedule.
Sparklines and data bars—These simple charts convey a lot of info in a little space, often inline with text. Sparklines and data bars are often used in tables and matrices.
Indicators—These minimal gauges convey the state of a single data value at a glance. Indicators can be used by themselves in dashboards or free-form reports, but they are most commonly used in tables or matrices to visualize data in rows or columns.
Calculating aggregates of aggregates—You can now create expressions that calculate an aggregate of an aggregate.
Better report pagination—Page breaks on tablix data regions (table, matrix, and list), groups, and rectangles give you better control of report pagination.
Map reports—Report Designer now offers a Map Wizard and Map Layer Wizard to add maps and map layers to your report to help visualize data against a geographic background. A map layer displays map elements based on spatial data from a map in the Map Gallery, from a SQL Server query that returns SQL Server spatial data, or from an Environmental Systems Research Institute, Inc. (ESRI) shapefile.
Business Intelligence Development Studio Support for SQL Server 2008 Reports and Report Server projects—Business Intelligence Development Studio now supports working with both SQL Server 2008 and SQL Server 2008 R2 reports, and with Report Server projects in the SQL Server 2008 R2 version of Business Intelligence Development Studio.
Improved Previewing of Report—Report Builder 3.0 offers a better preview experience with the introduction of edit sessions that enable the reuse of cached datasets when previewing reports. Reports render more quickly when using the cached datasets.

Report Manager has also been updated in the SQL Server 2008 R2 release to provide an improved user experience and look and feel. This includes an updated color scheme and layout in an effort to provide easier navigation to manage report properties and Report Server items. You can now use a new drop-down menu on each report or Report Server item in a folder to access the various config options for the report or item you choose. Subsequent are some of the key enhancements to Report Manager in SQL Server 2008 R2:

Workflow has been improved for viewing and managing reports and report server items. You can use a new drop-down menu to access various config options for each report or report server item in a folder.
The need to render a report before accessing and configuring report properties when in default view has been eliminated.
The visible display area is now larger in the Report Viewer when rendering reports.
An updated Report Viewer toolbar includes some updates to the toolbar controls, as well as the ability to export report data to an Atom service document and data feeds.

SQL Server Service Broker

SQL Server Service Broker offers a native SQL Server infrastructure that supports asynchronous, distributed messaging between DB-driven services. Service Broker handles all the hard work of managing coordination among the constructs necessary for distributed messaging, including transactional delivery and storage, message typing and validation, multithreaded activation and control, event notification, routing, and security. Service Broker is designed around the basic functions of sending and receiving messages.

An app sends messages to a service, which is a name for a set of related tasks. An app receives messages from a queue, which is a view of an internal table. Service Broker guarantees that an app receives each message exactly once, in the order in which the messages were sent. Service Broker can be useful for any app that needs to execute processing asynchronously or that needs to distribute processing across a number of systems. An example would be a bicycle manufacturer and seller who need to provide new and updated parts data to a company that implements a catalog management system. The manufacturer need to keep the catalog info up-to-date with its product model data, or it could lose market share or end up receiving orders from distributors based on out-of-date catalog info. When the parts data is updated in the manufacturer's DB, a trigger could be invoked to send a message to Service Broker with info about the updated data. Service Broker would then asynchronously deliver the message to the catalog service. The catalog service program would then execute the work in a separate transaction. When this work is performed in a separate transaction, the original transaction in the manufacturer's DB can commit immediately. The app avoids system slowdowns that result from keeping the original transaction open while performing the update to the catalog DB.