New BI features of SQL Server 2008

With the introduction of OLAP Services in version 7.0, BI features have always been a critical part of SQL Server. From a high level view the SQL Server BI offering is a complete and integrated offering with widespread delivery of business intelligence including Microsoft Office.

In this blog entry I will share some of the new BI features of SQL Server 2008 on three main areas SSIS, SSRS and SSAS.

SQL Server Integration Services (SSIS) 2008

With version 2008 a more scalable and efficient architecture for extraction, transformation, and loading (ETL) is offered to the customers. Some of the new features include the following.

SSIS pipeline improvements: In SQL Server 2005, the SSIS pipeline execution engine did not scale up to multi-proc machines with more than two processors or CPUs. The Integration Services design in SQL Server 2008 improves the scalability of runtime to multiple processors

SSIS data profiling: The new data profiling feature in SSIS 2008 identifies patterns in data. This feature generates statistics that can be viewed by the stand-alone Data Profile Viewer.

Lookups: Performance is improved, access to new data sources including XML and a new component TXLookup transformation is added.

The new MERGE SQL statement allows you to insert, update, or delete data based on certain join conditions in the same statement where in previous versions of SQL Server you had to create separate statements.

CDC (Change Data Capture) is used to capture insert, update and delete activity on a SQL table using the transaction log and placing these data in a separate relational table.

Data Warehousing: It’s now easier to manage large tables more effectively by transparently breaking them into manageable blocks of data. Also with the resource Governor you can now assign limits on CPU and memory use for each workload. And finally with native data compression techniques disk I/O can be dramatically reduced increasing overall performance.

SQL Server Reporting Services (SSRS) 2008

In SSRS 2008, a report server is implemented as a Windows-based service that hosts the Report Manager, the Report Server Web service, and background processing feature areas decreasing the dependence on IIS and also consolidating server applications into a single service reduces the configuration and maintenance tasks.

The report server has new management features to set a memory threshold for background operations and performance counters for monitoring service activity.

SRS 2008 supports two modes of deployment for report server, the native mode and the SharePoint integrated mode. In the native mode, a report server is a stand-alone application server that provides viewing, management, and delivery of reports and report models.

In the SharePoint integrated mode, a report server becomes part of a SharePoint Web application. Users of Windows SharePoint Services 3.0 can store reports in SharePoint libraries and access them from SharePoint sites that are used for accessing other business documents.

Office Word 2007 rendering extension can be used to export a report to a Word document and Office Excel renderer has been enhanced to support features like nested data regions and sub-reports.

Report Designer improvements:

· Entity hierarchies: Provides a flattened analytic-style metadata browser that presents all entities as a flattened list.

· Live data in design view: Allows display of live data by using simple iteration of design-time elements.

· Instances in metadata browser: Extends the metadata browser to include instance data.

· Filtering on the design surface: Adds UI elements for defining basic filter conditions directly on the design surface.

· Multiple data sources: Builds reports against more than one data source and has more than one data region for each report.

· Office12 ribbon: Provides UI parity with Microsoft Office 2007 products.

· Conditional formatting: Provides conditional formatting, which is a recommended customer feature.

SQL Server Analysis Services (SSAS) 2008

In SSAS 2008, Multidimensional Expressions (MDX) query performance has improved because Analysis Services can deal better with cube space. SSAS 2008 divides the space to separate calculated members, regular members, and empty space to better evaluate cells that need to be included in calculations.

The new MOLAP-enabled write back capabilities in SSAS 2008 remove the need to query relational online analytical processing (ROLAP) partitions. This provides you with enhanced write back scenarios from within analytical applications without affecting the traditional online analytical processing (OLAP) performance.

A single read-only copy of SSAS 2008 database can be shared between many SQL Servers through a virtual IP address. This creates a highly scalable deployment option for an Analysis Services solution.

With SQL Server 2008 backup compression, less storage is required to keep backups online. The backups also run significantly faster because lesser disk I/O is required.

In SSAS 2008, data mining models have been enhanced by appending a new algorithm to the Microsoft Time Series algorithm. This improves the accuracy and stability of predictions in the data mining models. The new algorithm is based on the Auto Regressive Integrated Moving Average (ARIMA) algorithm, and provides better long-term predictions than the Auto Regression Trees with Cross Predict (ARTxp) algorithm that has been used in SQL Server 2005 Analysis Services (SSAS 2005).

You can use the Analysis Services personalization extensions to create new Analysis Services objects and functionality, and then provide these objects and functionality dynamically in the context of the user session.

SQL Server 2008 generates Analysis Management Objects (AMO) warnings spanning all objects to alert you when you violate any of the design best practices or make logical errors in database design

The improvements in designing dimensions include features such as the Attribute Relationship designer, a simplified and enhanced Dimension wizard, and the Key Columns dialog box.

Comments

• Anonymous
  March 21, 2010
  there are several ssas items that might worth adding, like "Synchronization" - finnaly works. Dynamic sets.