SQL Server Magazine

SQL Server 2008, while not a revolutionary release, provides rich insight into your data for your organization’s business intelligence (BI) needs. I’ll take you on a brief tour of SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), and SQL Server Reporting Services (SSRS) enhancements, so you’re armed with information to help you make decisions about implementing SQL Server 2008 BI solutions. First, I want to point out two relational engine (query optimizer) enhancements that set the stage for better BI—partitioned table parallelism and star-join query optimizations (Optimized Bitmap Filters). Even though this tour is just a taste of the many enhanced BI features found in SQL Server 2008, it should be enough to give you food for thought when you consider whether upgrading to SQL Server 2008 BI is a smart move, based on your organization’s BI architecture and requirements.

Partitioned Table Parallelism
SQL Server 2008 improves performance on partitioned tables that reside on multi-CPU-based systems. The query optimizer can elect a parallel query execution plan on these hard systems to provide improved performing query and index operations. Fact tables are often candidates for partitioning in a data warehouse because they typically contain a few columns with a very large number of records.

Does your data warehouse have large fact tables residing on multiple CPUs? You can benefit by upgrading to SQL Server 2008 because there’s a new parallel query execution strategy on partitioned tables. SQL Server 2005 uses a single thread per partition parallel query execution strategy. In SQL Server 2008, multiple threads can be allocated to a single partition, thus improving the query’s response time. As of this writing, you can enable this functionality by setting the trace flag 2440, although this is expected to change when the product ships. Note that table and index partitioning requires SQL Server 2008 Enterprise Edition.

Star-Join Query Optimizations (Optimized Bitmap Filters)
The query optimizer uses bitmap filtering to eliminate rows from a second table based on values taken from the first table. Bitmap filtering is a common query filtering technique found in star-schema-based queries. SQL Server 2008 introduces optimized bitmap filtering. The query optimizer can now introduce bitmap filters dynamically in the query plan during generation, as opposed to just after query plan optimization, as in SQL Server 2005. Optimized bitmap filtering results in filtering from multiple dimension tables and bitmap filters are now applicable to more query operator types. Optimized bitmap filtering enables better performing data-aware house queries that reference the common star-based schemas.

SSIS Enhancements
Any BI solution includes extraction, transformation, and loading (ETL) of an organization’s data. ETL is implemented in SQL Server using SSIS. In SQL Server 2005, the SSIS pipeline execution engine doesn’t scale up to utilize more than one processor in a single execution tree. The SQL Server 2008 SSIS data flow engine can execute multiple components (threads) in a single execution tree. Overall, the 2008 SSIS engine is more stable and scalable. It eliminates the potential for deadlocks that occasionally occur in SQL Server 2005 SSIS when you execute packages with complex user data in large organizations.

Lookup transformation. SSIS can be used in a variety of scenarios, however, it’s most commonly used in ETL. One of the most common SSIS components used in ETL solutions is the Lookup transformation. The SQL Server 2005 SSIS lookup component used against tables with row counts of over a million rows occasionally causes a performance slowdown. SQL Server 2008 no longer has this limitation. You can perform a lookup against any data source by using the standard providers, which include ADO.NET, XML, OLE DB, and other data sources. You can even perform lookups against other SSIS packages.

The enhanced TxLookup transformation component of the SSIS package in SQL Server 2008 supports internal redundancy on the lookup chain. TxLookup also includes several other improvements over SQL Server 2005: There’s now a pre-charge query in addition to the cache-miss query. And for each cache-miss query, multiple rows can now be returned. The cachemiss query now has a separate connection manager. If you use a full or a partial cache query, SQL Server 2008 loads the hash table and uses the pre-charge query. However, if you use a no cache query, SQL Server 2008 behaves like SQL Server 2005 and uses only the cache-miss query. SSIS in SQL Server 2008 improves the performance of lookups to support the largest tables.

Data profiler. Good news for ETL gurus—SQL Server 2008 SSIS has a data profiler. Now you’ll have visibility into the source system data before you build your ETL solutions, and the ability to code, configure, and build based upon data patterns. With the data profiler you can generate source system metadata statistics, which you can then view using the standalone Data Profile Viewer. This viewer also displays candidate keys and data distributions. Data profiling has long been a requested capability of DTS/SSIS and the larger SQL Server product. It’s good to see a formal solution.

SSAS Enhancements
Following the typical progression in a BI solution, I’ve discussed the first stage—ETL and SSIS—and now we’re ready to look at creating cubes and mining models. One of SQL Server 2008’s many improvements to the SSAS architecture is Cube Designer enhancements.

Cube Designer enhancements. A critical component to SSAS is the practice of good cube design. The ultimate success or failure of your BI rollout depends on it. I’ll briefly survey what’s new with Personalized Extensions, Best Practice Alerts, the Dimensional Designer, the Aggregation Designer, and Named Sets.

You can use Personalized Extensions to create new SSAS objects and functionality, and then provide these objects and functionality dynamically in the context of the user session. You don’t have to create detailed specifications about where or how to find the extended functionality. You can share these new objects and functionality immediately with both end users and your fellow developers.

The Cube Designer now has a Best-Practice Alert functionality that spans all objects and is generated through Analysis Management Objects warnings. The warnings alert you when you violate design best practices or make logical errors in database design. You can detect potential problems with the design in a non-intrusive way because these warnings are integrated into real-time designer checks.

New and improved features for the Dimensional Designer include the Attribute Relationship Designer, a simplified and enhanced Dimension Wizard, and the Key Columns dialog box. You can use the new Attribute Relationship Designer in the Dimension Editor to easily browse and modify attribute relationships. The Dimension Wizard, which has been modified to align output with best practices, auto-detects parent-child hierarchies, provides safer default error configuration, and supports specification of member properties. In the new Key Columns dialog box, the enhanced Dimension Structure tab works with the Attribute Relationship Designer, making modifying attributes and hierarchies easier.

Continued on page 2

   Prev. page   [1] 2 3     next page