SQL Server 2008 R2
Written by Kay Ewbank   
Tuesday, 26 October 2010
Article Index
SQL Server 2008 R2
Business Intelligence

If you are looking for SQL Server 2010 you are not going to find it. The version released this year is instead SQL Server 2008 R2 and while there aren't any massive improvements aimed specifically at the developer you'll find the general improvements make it easier to create applications that do what your users want.


Of the changes for the database developer, the majority of the improvements to the new release are intended to make it easier to create Business Intelligence (BI) applications. These start with improvements to Reporting Services and continue with a new facility designed to let users create their own OLAP reports using a product called PowerPivot. Master Data Services provides the means to manage data from multiple sources, and StreamInsight gives you a way to handle near-real time data. Other changes are aimed at better management of larger databases.

Master Data Services

One addition to SQL Server 2008 R2 is Master Data Services (MDS). Essentially, this gives a simpler way to consolidate data from multiple sources so that each has the same data. If you have to create applications, particularly reporting and BI applications, that pull together information from a number of sources, you'll know how problematic it can be when data looks different from one source to another. The classic example of this is the customer record; is the version that's held in the accounting database the 'right' one, or should it be the one from the marketing department that it used to send out the catalogues? Which address should you use? Master Data Services is designed to overcome this.


(Click in picure to expand it)

 Setting up a Master Data Services package


You get a portal that can be used by the database users to manage the data. While this may induce nightmares at the thought of departmental wars, it does mean data stays accurate. MDS can be used to enforce rules on data quality. If changes are made, you can define workflows that carry out actions such as informing the people who 'own' the data, then once the change is OK-ed, share the changed data with all the systems that are affected. When changes are made, the user, date and time of the change are logged, along with the type of change, and previous value. This gives you a great audit trail and a way to reverse changes if necessary.


StreamInsight provides a way to handle continuously streaming data. A classic example of this is financial information on stocks and shares. Unstructured data such as text from Twitter or blog posts can also be handled. Handling streaming data involves complex event processing (CEP), and StreamInsight gives you the means to create applications where users can see trends in almost real-time.

In fact, StreamInsight is available for use without SQL Server 2008 R2. Whichever data source you're using, you develop applications in Visual Studio using StreamInsight libraries. The applications consist of input and output adapters connected through LINQ (Language Integrated Query) queries. If you're unfamiliar with LINQ, you can find out more about it here: http://msdn.microsoft.com/en-us/netframework/aa904594.aspx.

When used with SQL Server, the StreamInsight data engine lets your applications handle thousands of incoming transactions per second. You can use StreamInsight to pre-process the data so SQL Server receives aggregate or average values, or write CEP applications to gather and process the data using LINQ queries to perform calculations, filter and group the data, aggregate the information or use your own user-defined functions.

Reporting Services

If you're not familiar with SQL Server 2008, Reporting Services is the element that you use to create, deploy, and manage reports. It has APIs that you can use to extend the pre-provided data and report processing options, and the tools work within Visual Studio.

There have been quite a few changes to the version in the new release, and though none of them are massive, they all make it easier to give your business users snazzy reports to keep them happy. One useful addition is the ability to take parts of your reports or data regions, and publish them to the report server. From here they can be re-used in other reports, even by business users via Report Builder. They just drag and drop the elements onto their own report to create a composite view.

Datasets are now also treated separately from the report, so can be shared, stored, processed and cached externally from the report. This being Microsoft, we couldn't get much further without mention of SharePoint and Office. Reporting Services gets cosier still with SharePoint 2007 and SharePoint 2010, with a new SharePoint List data extension with query designer support.





Last Updated ( Wednesday, 27 October 2010 )