BI on SQL Server 2008: Finding Business Opportunities with Business Intelligence

November 16, 2009

It's tempting to think of Microsoft's SQL Server 2008 as simply a "database product," but the term doesn't really do it justice. It's certainly true that a relational model database server lies at its core, but a huge range of applications and services have been developed around this core database that can transform SQL Server 2008 into a complete Business Intelligence (BI) platform.

BI systems built on the SQL Server 2008 platform can scale to meet the needs of the very largest organisations and provide businesses of all sizes with a competitive advantage—supporting better decision making and helping executives spot emerging business opportunities.

How does SQL Server 2008 work as a BI platform? By providing the services and functionality needed to integrate data from many different sources such as CRM, ERP, and other financial systems and store it in a data warehouse— a central data repository powered by SQL Server 2008. And since SQL Server 2008 includes reporting and analysis tools that can look into that data warehouse, it enables organisations to exploit the data it contains to provide end users with accurate business intelligence that can be accessed easily and presented clearly.

A data warehouse built using the SQL Server 2008 BI platform enables an organisation to:

• exploit business opportunities by more accurately predicting future trends through data mining
  
  
• make better decisions based on analysis of all of its data, not just the subset that is easily available
  
  
• make more accurate decisions and predictions based on data that has been cleaned and checked for inconsistencies
  
  
• liberate corporate data and put it at the disposal of any employee, in any department, who might be able to use it profitably
  
  
• easily analyse and cross-reference data from any of the organisation's operations, even if the source data is held in different database systems with different underlying structures.
  
  
• measure complex key performance indicators to manage the business more efficiently
  
  
• enhance the value of systems such as CRM and ERP by find unexpected relationships between various types of data contained in these applications and other corporate systems
  
  
• uncover business trends that are hidden or difficult to find using the application that is the source of the data
  
  

Building the Warehouse
Building a data warehouse is a skilled and potentially complicated task, but one that can be made considerably easier using Business Intelligence Development Studio, an extension of Microsoft's Visual Studio that includes project types that are specific to SQL Server business intelligence.

SQL Server 2008 also has a number of features that are particularly useful when building data warehouses, including:

• Data Compression (including backup compression): to shrink data warehouse fact tables, improve query performance, and enable large data warehouse backups to be performed quickly
  
  
• Resource Governor: a way to differentiate between workloads such as report generation or administration tasks and limit the resources given to each type of workload, to prevent a single "runaway" query from slowing down the whole system.
  
  
• Change Data Capture: a mechanism to easily track changes in a table to maintain a complete record of data changes over time
  
  
• Partition Table Parallelism: a feature that speeds up queries by distributing the available threads among the partitions being queried.
  
  

Extract, Load, Transform
Populating a data warehouse can also be a major undertaking, because all of the source data must first be identified, accessed, cleaned, de-duplicated, and transformed into a consistent set in a process known as extract, transform, load (ETL). This is a key step because the quality of the data entering the data warehouse dictates how useful the business intelligence it yields will be. SQL Server 2008 Integration Services, available as part of SQL Server 2008, has been designed to make it easy and cost efficient to build enterprise-class data integration applications to integrate diverse data sources including Oracle, SAP, Siebel, and Microsoft Dynamics into a SQL Server 2008 data warehouse.

Powerful Reporting
Once a SQL Server 2008-based data warehouse system is up and running, it can only start to generate a return on the investment made in developing it when end users begin to exploit the comprehensive, consistent, "single version of the truth" data set it contains to gain insights into the underlying business.

Microsoft SQL Server 2008 Reporting Services provides the means to deliver these insights to all parts of the business. It makes it easy for business users in any department to explore all of the organisation's data by creating their own reports and for reports to be distributed easily using Microsoft Office SharePoint, by e-mail, or by subscription for automatic delivery at specific times.

Reports can be rendered into Microsoft Office Word format for immediate viewing, and can be made instantly understandable through the use of powerful visualisation graphics. Depending on the type of information contained in the reports – and the preferences of the people using them – data can be presented using dashboards, scorecards, analogue needle gauges, analytic views, or simply as Excel workbooks.

The power of this type of approach can be illustrated with a simple example: a sales director can subscribe to a report providing weekly sales figures broken down by product and by region, delivered by e-mail every Monday morning at, say, 11:00 a.m. This scenario makes it easy for the sales director to spot if sales are declining in a particular market, but also to carry out a broader analysis to discover why. They can do this by using the extensive analytical capabilities of Microsoft SQL Server Analysis Services, drilling down into the sales figures and slicing and dicing the data to uncover the underlying reason for the sales decline.

They can also implement "what-if" scenarios and carry out predictive analysis using the sophisticated data mining algorithms and multi-dimensional OLAP capabilities built in to SQL Server 2008 Analysis Services to work out the optimum strategy to pursue. These can be carried out from the familiar environment of Microsoft Excel using the Data Mining Add-Ins.

What we have seen, then, is that Microsoft SQL Server 2008 is much more than just a relational database package: using SQL Server 2008 as the foundation, organisations can build highly scalable data warehousing and business intelligence systems. When this is integrated with Microsoft's Office system and other productivity tools, it can offer businesses a very fast return on investment and a key competitive advantage.