Business Intelligence and SQL Server

Business Intelligence Seminar Series

2009-10-30T15:41:00.000-04:00

Do more with what you already have. Leverage the products you already own and the data your systems produce to gain operational efficiencies and improve your company's business intelligence capabilities!

Baltimore
Tuesday, Nov. 3 (9:00 a.m. – 11:30 a.m. EST)
Hyatt Place
4730 Painters Mill Road
Owings Mills, MD 21117
Register

Reston, VA
Wednesday, Nov. 18 (9:00 a.m. – 11:30 a.m. EST)
Microsoft Corporation
12012 Sunset Hills Rd.
Reston, VA 20190
Register

Washington, D.C.
Tuesday, Dec. 1 (9:00 a.m. – 11:30 a.m. EST)
Microsoft Corporation
5404 Wisconsin Ave.
Chevy Chase, MD 20815
Register

Philadelphia
Wednesday, Dec. 2 (9:00 a.m. – 11:30 a.m. EST)
Philadelphia Marriott West
111 Crawford Avenue
West Conshohocken, PA 19428
Register

Hosting SQL Server on VMWare

2009-10-30T13:08:00.000-04:00

One of our clients was investigating the logistics of running SQL Server on VMWare. In discussions with Microsoft, the response included four (4) important links. I personally have seen various SQL Server performance issues when virtualizing especially in a SharePoint environment and I would never trust that configuration. There have been many enhancements with SQL Server 2005 and now SQL Server 2008 in order to consolidate servers and take advantage of reducing hardware needs and license costs. A good SQL Server 2008 consolidation white paper can be found here.

Here is the info from the Microsoft technician:

If you are running ESX V3.5 the latest hotfixes from VMware to disable write-caching are included in update3 and update 4.

1. The Microsoft Virtual Server support policy

2. The VMware knowledge base article that you can contact VMware about for hosting SQL Server

3. This article that explains the issue and discusses the problem domain

4. Here is an presentation from VMWare about how to disable the disk write issue.

To MERGE or not to MERGE in SQL Server 2008

2009-10-29T11:03:00.001-04:00

I was reviewing some demo material for the MERGE statement that Jim Pletscher put together. It was a standard scenario where we wanted to UPDATE, DELETE, or INSERT rows from a source table to a target table. A general overview of this is available here.

Inserting is harmless but what if sometimes we don't want to update or delete something from our target table?

My first step was to add a new column to the target table. I named this column "DoNotTouch" and made it a bit defaulting to zero (0). I then set this value to one (1) on one of the target table rows.

So now how do we tell the MERGE statement to not touch anything where this bit is flipped on? You cannot use a WHERE clause in the MERGE statement. The answer is within the WHEN clause of the MERGE statement. All you need to do is add an additional condition to each WHEN for the update and delete operations:

(TargetTable is the name of the target table and SourceTable is the name of the source table)

WHEN MATCHED AND TargetTable.DoNotTouch<> 1 THEN

UPDATE SET TargetTable.FirstName = SourceTable.firstname,

TargetTable.LastName = SourceTable.lastname

WHEN NOT MATCHED BY SOURCE AND TargetTable.DoNotTouch<> 1 THEN
DELETE

So that worked! It did not touch the row where I set the DoNotTouch flag to one (1).

Introduction to Change Data Capture

2009-10-22T16:12:00.021-04:00

The Change Data Capture feature is a new feature in SQL Server 2008 that allows processes to
quickly and easily identify changes to a table in SQL Server 2008. It primarily works by watching the SQL server transaction logs, and running processes that pull changes from the logs and moving them into tables.

Change Data Capture Setup

Enabling Change Data Capture is a two step process. First, you must enable it at the database level, then you must enable it for each table for which changes must be recorded.

The first step in setting up Change Data Capture is to enable the option at the Database Level. The easiest way to do this is to use the system stored procedure sys.sp_cdc_enable_db.
Simply execute this stored procedure from the desired database, and it will enable change data capture for the database. This stored procedure requires sysadmin privilege in order to execute it.

(click to enlarge)

Once it is enabled at the database level, the Capture Change Data option must be enabled for each table for which changes should be captured. Use the sys.sp_cdc_enable_table stored procedure to enable the feature, passing the table name as an argument in order to enable the feature. By default, all of the columns in the source table are identified as captured columns. If only a subset of columns need to be tracked, such as for privacy or performance reasons, use the @captured_column_list parameter to specify the subset of columns.

(click to enlarge)

Once Change Data Capture (CDC) is enabled for a table, several things occur. First, an associated capture instance is created to support the collection of the change data in the source table. The capture instance consists of a change table and up to two query functions.

All inserts, updates, and deletes to the source table are stored in the change table. The two query functions are specific to the source table, and can be used to fetch changes from the change table.

The first five columns of a change data capture change table are metadata columns. These provide additional information that is relevant to the recorded change. The remaining columns mirror the identified captured columns from the source table in name. These columns hold the captured column data that is gathered from the source table.

Each insert or delete operation that is applied to a source table appears as a single row within the change table. The data columns of the row that results from an insert operation contain the column values after the insert. The data columns of the row that results from a delete operation contain the column values before the delete. An update operation requires one row entry to identify the column values before the update, and a second row entry to identify the column values after the update.

In additon to the Change instance, two SQL Agent Jobs are created: one that is used to populate the database change tables, and one that is responsible for change table cleanup. Both jobs consist of a single step that runs a Transact-SQL command. The Transact-SQL command that is invoked is a change data capture defined stored procedure that implements the logic of the job.

(click to enlarge)

The jobs are created when the first table of the database is enabled for change data capture. The Cleanup Job is always created. The capture job will only be created if there are no existing transactional publications for the database.

Using Change Capture Data

Change data is made available to change data capture prcoesses via the two functions that are created when the change instance is created. Both functions require Log Sequence Numbers (LSNs) to help define the date range of change data to return in the resultset.

The function cdc.fn_cdc_get_all_changes_ returns all changes that occurred for the specified interval. Entries are always returned sorted, first by the transaction commit LSN of the change, and then by a value that sequences the change within its transaction.

The function cdc.fn_cdc_get_net_changes_ returns one change per modified source table row. If more than one change is logged for the row during the specified time period, the column values will reflect the final contents of the row.

Several functions are provided to help determine appropriate LSN values for use in querying the change data capture functions. For example, the functions sys.fn_cdc_map_time_to_lsn and sys.fn_cdc_map_lsn_to_time are available to help place LSN values on a conventional timeline.

To illustrate, insert a single record into sample table created earlier, then update the columns.

Now we can use the cdc.fn_cdc_get_all_changes to set how the change table is populated:

Note that the resulting change table contains four records, with the source table columns populated with the table data surrrounding the change. The first record reflects the initial insert. The 2nd and 3rd records reflect the update (a delete followed by new insert), while the fourth record reflects the final delete.

Note that running the cdc.fn_cdc_get_net_changes returns only a single row, which represents the newest version of any modified row. This procedure will only return one change table record per source record changed, regardless of how many changes were made to the source record.

Business Intelligence and Silverlight

2009-09-28T23:26:00.002-04:00

Looking forward to an upcoming book on two of my favorite areas of interest: Business Intelligence and Silverlight.

http://silverlighthack.com/post/2009/09/20/Announcing-Next-Generation-Business-Intelligence-Software-with-Silverlight.aspx

SQL Server 2005 vs 2008 Query Performance

2009-09-28T22:33:00.003-04:00

I was recently working on a transactional web application for a client. This web application had some "operational" type reports included within it that required aggregations of, what will eventually be, millions of rows of data. This meant I'd be writing queries directly against the transactional tables doing the aggregations (SUMs and COUNTs mostly) "on demand".

Note: In more heavily used applications, aggregating as much data as we were on demand might not be acceptable, but in this case we were measuring in transactions "per minute" and "per hour" rather then "per second", so we did not anticipate any blocking of our queries by database writes.

These queries were going to be used on the landing page for web application seen by every employee within the company. The landing page receives 20,000 hits per day over roughly 17 hours of operation a day. This is about 19 hits per minute or 1 hit every 3 seconds. Not all that much compared to many applications I've seen, but definitely enough to warrant some sort of load/performance testing under a larger data load.

I set up some SQL scripts to, somewhat randomly, load a data sample that was representative of about 2 years worth of data. My initial query times were awful, and as I suspected, there was some room for improvement. I did some refactoring (with some help from a friend) and had things running at about 1-2 seconds per query. I thought there was still some room for improvement, but this was within an acceptable range.

Thinking things were fine, I promoted my code from my local development environment to our integration environment. I then ran my data load script to test in that environment, and suprisingly, was seeing query times in the 20-25 second range. WHAT?!? I just tuned things. After some tinkering, and reloading of data, I was seeing the inconsistencies in my query times. Sometimes in the 20-25 second range, other times in the 3-5 second range. What was going on?

My initial thought was that there was something different in the query processing between my local development environment (SQL Server 2008 Developer Edition) and the integration environment (SQL Server 2005 Standard edition). Some quick searches found that I might be using the Star Join Query Optimization available in 2008 Developer/Enterprise editions. There are ways that I could have confirmed this assumption, but I did not spend the time to do so. Regardless if I was or wasn’t using this optimaztion, I still didn’t want to take a chance that there might have been something else in the query processor that was making things more “efficient” on 2008 Developer vs 2005 Standard. I thought it’d be more reliable if I just set up a more representative environment and did my testing/tuning there.

I built a 2005 Standard virtual machine, built my application, loaded sample data, and ran the queries. Started seeing query times similar to our integration environment. OK, so it was reproduceable. Now let's tune it. I went straight to an indexed view I was using for the query, made a minor tweak. As I ran the script to rebuild the indexed view, it hit me. I had just loaded 2 years worth of data, and had done no database maintenance that would "normally" be done gradually, such as rebuilding indexes and defragging the hard drive. I rebuilt my indexes, defragged the drive, and sure enough my query times were back down in the 1-2 second range.

After tweaking just a bit more by including my numeric columns used in my query aggregations in an index, I redeployed to our integration environment and finally started getting consistent and performant results.

A few important take-aways here...

In order to truly gauge/test performance, perform maintenance on your development/test environment just as you would in a “normal” environment, especially after large data loads. That is rebuild your indexes, defrag the hard drive, etc.
Always (or to the extent possible) develop and/or test on machines that are representative of the environment you are actually going to deploy to, especially with regards to versions/editions of applications being used. There are many features within SQL Server that are only available in the Enterprise Edition of the product, including some query optimizations. I usually do this, but for a long period of time on my current project, the client couldn’t give a definitive answer on what our environments would look like, so we continued development with what we originally started with (which was no longer valid). Once I built a more representative environment of what I was actually deploying to, then deployed my application and test data there, I was able to troubleshoot and fix the issue more quickly.

Excel Services Part Deux - Excel Web Services

2009-09-10T12:54:00.007-04:00

In an earlier blog entry, I discussed in very broad terms Microsoft Excel Services – a server side calculation engine that can be leveraged for SharePoint Business Intelligence solutions. Excel Services, you may recall, is divided into three components; Excel Calculation Services (ECS), Excel Web Access (EWA), and Excel Web Services (EWS). Excel Web Services allows access to the Excel Calculation Engine via Web Service calls, and is today’s focus.

Excel Web Services utilizes SOAP over http and acts as an interface between custom client programs and Excel Services. In order to call the service, it is necessary to establish a reference to the Excel Web Services WSDL, accessed through ExcelService.asmx?wsdl. However, if you are writing code within SharePoint Services (for example, developing custom web parts), Microsoft recommends linking directly to Microsoft.Office.Excel.Server.WebServices.dll and making local calls rather than using loop-back SOAP calls.

EWS - What is it good for?
Absolutely nothing (say it again…)? No, there are a number of practical examples where utilizing Excel Web Services comes in handy. One common scenario occurs when a company’s business logic is embedded in Excel spreadsheets. These spreadsheets can be maintained on a corporate server, and custom applications developed to utilize the logic in the spreadsheet without having to recode it in a “conventional” programming language. This also means that the spreadsheet logic can be maintained by business Subject Matter Experts (SMEs) who are comfortable working in Excel. Custom code can be developed to set values to cells and ranges, process the workbook, and retrieve calculated values (or even the entire workbook).

Coding Steps 101
Dealing with EWS is relatively straightforward, and numerous code examples can be found in the literature and on the web, such as here: http://blogs.msdn.com/excel/archive/2005/11/21/495454.aspx

Typical steps involved in coding against EWS are as follows:

1. Instantiate the web service via creation of ExcelService object
2. Set the URL via ExcelWebService property
3. Set the credentials via Credentials property. Default credentials (using applications own credentials) can be used by setting this property to System.Net.CredentialCache.DefaultCredentials.
4. Start the Session by calling OpenWorkbook() method
5. Set Cell Parameters via SetCell() method
6. Calculate Spreadsheet (if workbook not set to auto-recalculate) via Calculate() or CalculateWorkbook() method
7. Retrieve the Results via GetCell() method
8. Close Session via call to CloseWorkbook() method

Other methods are available as well, including the ability to set a range of cells and read an entire workbook into memory.

Business Example
An RDA client manages a customer support system that utilizes a custom authorization engine that controls which support agents have access to specific functionality within the application. For example, agents that support a specific product line do not have access to workflows supporting other product lines, and only agents of a certain tier level have the rights to issue refunds to customers. Although this authorization data is stored in a relational database, an Excel Spreadsheet on a SharePoint site is used by both the business and application support personnel to maintain and modify this information. When changes are made, the spreadsheet is loaded into the relational database through custom software.

This approach has a number of benefits:

1. SharePoint is used as a means of version control, to maintain a historical record of changes made.
2. Anyone with access to the SharePoint site can view the details surrounding the current authorization hierarchy, without the need for special software or reports.
3. The spreadsheet itself contains business logic to help organize the information and maintain data integrity.
4. The spreadsheet is easy to use and modify for subject matter experts who may not have a programming background.

However, as initially conceived there are some difficulties associated with this approach:

1. Making changes and deploying to the relational database involves a number of time-intensive manual steps, including downloading the spreadsheet, exporting to XML, applying an XSLT transform, and executing a custom application.
2. In addition to the labor involved, the manual steps are prone to error.
3. In the event that the changes made are not what were desired, either through human error or a change in business strategy, rollback of the changes can be difficult.

Excel Web Services can provide an ideal platform to address these shortcomings. A SharePoint web part or other custom application can be developed that, utilizing Excel Services, can recalculate and read the latest spreadsheet data, generate the necessary output, and load the relational database in (from an end users point of view) a single step. No modifications to the existing spreadsheet logic would be necessary, and the business users can use and maintain the spreadsheet as before. SharePoint security can be leveraged to restrict access to the Web Part. In addition, custom rollback functionality can be added via code.

Finally…
As indicated in my last installment, relying on business logic residing in an Excel spreadsheet may not be considered “ideal” from an architectural standpoint – but is often a necessity when practical considerations such as time, budget, staff experience, and maintainability come into play. Excel Web Services provides an IT organization a valuable tool to leverage that platform for maximum benefit.

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

2009-08-24T08:20:00.008-04:00

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.

Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready

Figure 2 - Parameters List (SSIS automatically resolved)

With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.

Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.

Figure 4 - Adding parameters manually

Figure 5 - Parameters added manually

Figure 6 - Map the parameters to Input columns

Reporting Services 2008 Parameter Dependencies & Choosing Multiple Parameter Values

2009-08-21T13:04:00.009-04:00

As was the case with prior versions, Reporting Services 2008 allows you to establish parameter dependencies and to allow mutiple parameter values be chosen. This means that parameter values provided to the end user can be dependent upon or driven by prior parameter value choices. In this example we’ll see how this can be done using a very simple AdventureWorks2008 Sales Data example report.

The AdventureWorks Sales Detail Report displays line-item sales details for a given product category, product subcategory and product. Our goal is to allow the end-user to specify the product category from the following available list (Accessories, Bikes, Clothing, Components). Once a Product Category choice is made, the available Product SubCategory choices for the chosen Product Categories are determined and displayed in the Product SubCategory drop-down list. In the screen snapshot below we chose Bikes and Clothing as our Product Category choices. For our Product Subcategory choices we chose Mountain Bikes (Bikes), Road Bikes (Bikes) and Caps (Clothing).

Notice that our Product Category is displayed along with the Product Subcategory in the following format: Product SubCategory (Product Category)

Our product subcategory choices are below:

Mountain Bikes (Bikes)

Touring Bikes (Bikes)

Caps (Clothing)

This was done just to tell the report viewer what category a subcategory belongs to. This was done because the subcategory / category relationship was not always obvious by name!

Parameters and Dependencies
Our sample report uses two parameters: Product Category and Product SubCategory. The subcategory choices are driven by or dependent upon the product category choice. In this section we’ll see exactly how this is done and also look at how we provide both the category and subcategory names to the end-user.

Product Category Parameter
The Product Category choices are provided by selecting the Name column form the ProductCategory table in the AdventureWorks2008 database. Below is the SQL code to provide the ProductCategory choices:

SELECT distinct
Name as ProductCategoryName
FROM
Production.ProductCategory

Notice that this parameter does not include any parameter dependencies since it is our initial choice. The Report Data window below allows us to define the parameter specifics such as the parameter name, parameter label, parameter data type, where the available values come from, what default value should be used, …

Below we see the Report Data window which allows us to create and fine-tune our parameters.

If we double-click on our highlighted parameter entry we see the Parameter Name, label and data type specified below. Notice that we also allow end-users to choose more than a single parameter value.

In the Available Values tab we specify how the parameter choices are driven by a query. Since this is the case, we specify the DataSet name that is used to provide the available Product Category values.

Product SubCategory Parameter

The Product SubCategory choices are provided by selecting both the Name column from the ProductCategory table and the Name column from the ProductSubCategory table both in the AdventureWorks2008 database. Using the SQL below, end-users are provided with a list of SubCategory names with the corresponding category name in the following format:

subcategory (category)

SELECT
PSC.Name + ' (' + PC.Name + ')' as productsubcategorynameandcategoryname,
PSC.Name as ProductSubCategoryName
FROM
Production.ProductSubCategory PSC
INNER JOIN
Production.ProductCategory PC
ON
PSC.ProductCategoryID = PC.ProductCategoryID
WHERE
PC.Name IN ( @ProductCategoryName)
GROUP BY
PSC.Name, PC.Name
ORDER BY
PC.Name, productsubcategorynameandcategoryname

Notice this SQL references the ProductCategory parameter using the ‘@’ parameter designator. (This entry was highlighted) This is like saying : “Provide us with Subcategory values given the product category choice that was already made and stored in the @ProductCategoryname parameter.” This is exactly how one can introduce parameter dependencies in reports -- your SQL code can reference one or more report parameters.

Extracting the Report Data

So far, we've looked at the SQL-code used to create parameter choices. The following SQL Code is used to extact the actual reporting data:

SELECT
P.Name as ProductName,
PC.Name as ProductCategoryName,
PSC.Name as ProductSubCategoryName,
SOD.OrderQty,
SOD.UnitPrice,
SOD.OrderQty * SOD.UnitPrice as LineTotal
FROM

Sales.SalesOrderHeader SOH
INNER JOIN
Sales.SalesOrderDetail SOD
ON
SOH.SalesOrderID = SOD.SalesOrderID
INNER JOIN
Production.Product P
ON
SOD.ProductID = P.ProductID
INNER JOIN
Production.ProductSubcategory PSC
ON
PSC.ProductSubcategoryID = P.ProductSubcategoryID
INNER JOIN
Production.ProductCategory PC
ON
PC.ProductCategoryID = PSC.ProductCategoryID
WHERE
PC.Name in ( @ProductCategoryName)
and PSC.Name + ' (' + PC.Name + ')' in

(@productsubcategorynameandcategoryname)
ORDER BY
ProductName,
ProductCategoryName,
ProductSubCategoryName

Notice in the highlighted section we are referencing both the Product Category and the Product SubCategory parameters. In fact, we are actually building the string that include the SubCategory Name and Category Name (i.e. Mountain Bikes (Bikes) ) so this filter matches the choice format the end-user is provided with.

Conclusion
It seems as though things have gotten easier in Reporting Services 2008. I’ve authored reports in both SQL Server 2000 and SQL Server 2005 and I remember using a Transact-SQL function (Split) to slice and dice values from a comma separate string.

SSIS Dynamic Data Driven Extracts

2009-08-17T15:52:00.023-04:00

Extracting data from multiple tables: a dynamic SSIS Data-Driven Approach

This blog entry looks at how you can use SSIS to extract data from multiple tables leveraging a ‘source table’ that provides the table names to extract data from. This solution utilizes a For Each Container, Package Variables, Package Expressions applied to a package variabe along with an OLEDB Source whose SQL Extract Command is stored in a package variable.

In our sample scenario we receive sales data in tables from a 3’d party. This data is delivered on an unpredictable schedule and when received we’d rather not have to modify our SSIS solution. Instead, we rely on inserting a new row into the source table that tells SSIS what it needs to know about the new source table.

Our Source Table / data driven approach
Our dynamic approach involves the use of a source table to drive the SSIS Extract process and designate the tables to extract.

The Source Table SSIS utilizes is dbo.SalesSource and its schema is below:

CREATE TABLE [dbo].[SalesSource] (
[SalesSourceID] [int] IDENTITY(1,1) NOT NULL,
[SourceTableName] [varchar](64) NOT NULL,
[ExtractProcessedFlag] [int] default (0) NULL,
[ExtractProcessingOrder] [int] NULL,
[RowsExtracted] [int] NULL,
[DateTimeExtracted] [datetime] NULL
)

Initially, we have 2 source files to work with so, we start out inserting the following two rows into our dbo.SalesSource table (Notice we really only need to supply values for 3 columns). The SSIS package updates the ExtractProcessedFlag, RowsExtracted and DateTimeExtracted columns upon sucessful completion.

The ExtractProcessingOrder column allows us to specify the order in which we would like to extract data from in our Source Tables. The reason this was required was that our source data may very well contain duplicate data. We specify the order in which we extract and load and have our SSIS packages flag the duplicate rows based on a business key and keep only the most recent data from our duplicates. (In our scenario, new data trumps old data)

Let’s take a look at our SSIS Solution and then dissect each component:

The SSIS package uses the following Package Variables.

As we progress and dissect each SSIS task, we will look at each package variable it utilizes. The SSIS package uses a For Each container to cycle through each row present in the Source Table with the CurrentTable package variable holding the current table that needs to be extracted. Notice that we supplied an initial value for the CurrentTable. This way we can preview data in this table from the OLE DB Source connection we will see in the Data Flow task.

The package is fairly simple. It contains a highest level Sequence Container along with an inner For each Container. The For Each Container specifies an ADO Enumerator as its ‘Collection Type.’ The result set that feeds this container is below is produced by the Exec SQL Find Tables to Extract SSIS Task. The SQL Extract code is below:

select SourceTableName
from dbo.SalesSource
where ExtractProcessedFlag = 0
order by ExtractProcessingOrder

Notice that we are only extracting source tables where the ExractProcessedFlag is set to 0. The SSIS package sets this flag column to 1 when a source table is extracted. This EXEC SQL Task specifies the ResultSet Package Variable to ‘hold’ the results of the above select statement. (ResultSet is of type object)

The For Each Container Specifies a ‘For each ADO Enumerator’ as the Collection type and points the ResultSet package variable as the result set holder. ( Notice the setting for the Enumerator and the ADO object Source variable choice)

Also, the CurrentTable package variable is specified in the Variable Mappings tab. This allows our CurrentTable variable to essentially hold the current table being processed each time we cycle through the For Each loop. We'll see this package variable used in the expression that assigns a value to the SQLExtractCommand package variable.

The Data Flow task specifies that the SQL to extact data is present in a Package Variable. While this choice if probably not the most common Data Access Mode choice, it does provide us with the flexiblity we need. This package variable has its value dynamically set using Expressions. If you choose a package variable and display its properties (F4 will provide the properties) you will notice that by default the Evaluate As Expression property is False. This was changed to True as is seen below:

If you click on the the ellipse in the Expression value, you will be provided with the expression builder dialog box. The SQLExtactCommand’s contents are created using the quoted string along with the CurrentTable package variable as seen below. (Notice I did specify square brackets to surrount the table name since the table name may include non-alphabetical or other strange characters)

I found it helpful to click on Evaluate Expression and then paste the results into a Management Studio window. This way you can execute and validate the actual code.

The Data Flow Task that specifies the SQLExtractCommand package variable is below. You will notice that you are able to view columns and even preview data because we supplied an initial value for our CurrentTable package variable.

The final step in our SSIS package is a EXEC SQL statement that updates the source table to designate the SourceTable as being sucessfully processed.

UPDATE
dbo.SalesSource
SET
ExtractProcessedFlag = 1,
DateTimeExtracted = getdate(),
RowsExtracted = ?
WHERE
SourceTableName = ?

Parameters used in this update statement include the number of rows extracted from the source table along with the source table name.

Conclusion
What we left with is a SSIS package that extracts the tables to process from a source table. As new source tables arrive, we insert the rows into our source table without having to modify our SSIS package.

SQL 2008 SSMS Table Save Error

2009-08-04T15:46:00.002-04:00

Using SQL Server Management Studio (SSMS) for SQL 2008, I had to make a change to one of my tables to add a column. However, I did not want to add the column at the end of the table, I wanted to add the column between two other columns. When I attempted to Save the table change, I had the following error appear:

In order to resolve this issue, I had to update the following setting in SSMS. Go to Tools-->Designers-->Table and Database Designers and uncheck the "Prevent saving changes that require table re-creation" checkbox:

Retrieve Web Page Results Using SSIS

2009-07-24T15:36:00.001-04:00

Scenario

A client is wanting to use the web to retrieve coordinate information from Google Maps to store the information for a Marketing campaign.

Solution

The SSIS Script task can connect to the web page and return the results. The following script provides sample code on how to get the Script Task to perform the action. The script task was developed in SQL SSIS 2008 using VB, the 2005 version is similar but the guts of the HTTP code remains the same (thanks to my colleague Andrew Crowder for putting together the main syntax):

First, a package is created with two variables of type String. The variables are passed into the script task in the following manner:

ReadOnly: User::URL
ReadWrite: User::HTML

Now, the script itself:

Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Net

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()
        Try

            'setup the variables, URL is set in the package as a var and read
            Dim URL As String = Dts.Variables("URL").Value
            Dim request As HttpWebRequest = WebRequest.Create(URL)
            Dim response As HttpWebResponse = request.GetResponse()
            Dim reader As StreamReader = New StreamReader(response.GetResponseStream())

'read the http data into a string to be used later in the package
Dim str As String = reader.ReadToEnd()

Dts.Variables("HTML").Value = str.ToString()

            Dts.TaskResult = ScriptResults.Success
        Catch ex As Exception
            'fire the info event and return as error from the task
            Dts.Events.FireInformation(0, "Web Script Task", ex.Message.ToString(), "", 0, 0)
            Dts.TaskResult = ScriptResults.Failure
        End Try
    End Sub

Execution

The following URL value is set and returns the coordinate information into the HTML variable:

URL: http://maps.google.com/maps/geo?q=1600+Amphitheatre+Parkway,+Mountain+View,+CA&output=csv&oe=utf8&sensor=false&key=ABQIAAAAP6yK9BJ44wmvT1Dd7V5cKRQz9eeYvRAhuPGO19pGZUf7Pj2DYxRZ0Zbtz0o1UAkk17kmD6xk5qDnzg

HTML: 200,8,37.4219720,-122.0841430

The script task can also return the HTML from a page, for example http://www.rdacorp.com, and allow for other SSIS tasks to manipulate the HTML variable as per the requirements and design of the package as it would like any other variable within the package.

Introduction to Excel Services

2009-07-22T13:14:00.003-04:00

Microsoft and other software vendors offer a dizzying array of BI, business productivity, and data management tools from powerful relational databases on up. Yet research (and personal experience) has shown that a large number of corporations still maintain a significant quantity of business logic in plain-old Excel spreadsheets . Logic stored in spreadsheets can lead to a proliferation of cut-and-paste in an attempt at reuse that can become a maintenance nightmare. Responding to the needs of its customers, Microsoft introduced Excel Services for SharePoint Server 2007.

Basically, Excel Services is a server-side version of the Excel calculation engine. Some common usage scenarios to Excel Services are:

A set of SharePoint web parts. This allows anyone with access to a SharePoint site to interact with Excel Data stored on a server without necessarily having full control to modify the data or change how it is presented. A Business Intelligence Dashboard showing KPIs (Key Performance Indicators) would be a common application.
An interactive spreadsheet viewer. Even if a customer has Excel installed on their workstation, this allows the user to view up-to-date Excel data without having to leave their browser when accessing their corporate SharePoint site
Maintaining “one version of the truth” of data (such as quarterly sales, etc.) on a server without having to worry about changes to multiple copies floating around individual users desktops.
A set of APIs for developers to leverage the calculation abilities of Excel from within their own applications.

Architecturally, Excel Services is also divided into three components:

Excel Calculation Services (ECS) – handles the spreadsheet refresh and recalculation
Excel Web Access (EWA) – handles the rendering of the spreadsheet to the client, e.g. SharePoint site
Excel Web Services (EWS) – allows access to the Excel calculation engine via web service calls.

Alas, Excel Services does not provide you with 100% of the full power of Excel to your SharePoint site; limitations include lack of support for VBA Macros, references to other Excel workbooks, ActiveX controls, and most graphical elements.

Excel Services may not be the most glamorous technology, and on the surface might seem like a “hack” – using a lowly spreadsheet to manage your important business data, and to provide the calculation engine for custom applications that might be better served by interfacing with a commercial or homegrown software library. However, in the real world of legacy systems and shrinking budgets, Excel Services has its place. In future blog entries, I will be diving in and exploring the different components of Excel Services in greater detail.

Default Cube Action in Excel

2009-07-22T10:46:00.003-04:00

Scenario

When using Excel 2007 to connect to a SQL 2005\2008 SSAS cube, double-clicking a cell, or right clicking the cell and selecting Show Details, within a Pivot table brings up details about the specific sliced cell within the Pivot table. However, the default nature of the cube is to bring up the raw Fact table data for that slice, which displays all fields within the Fact table for the records that make up the cell. The fields within a Fact table are often surrogate keys that point to the Dimensions that join to the Fact table as well as all Measurements stored within the Fact table. This default nature is not the most helpful information to business analysts since the keys have no business meanings to the users.

The question then is how to change this default behavior to bring back information to supply meaningful details to a business analyst when using the cube. The answer is to create a Drillthrough Action with the SSAS cube and set the Default property to True. The Drillthrough Action will return the information that the business defines as meaningful when wanting to see the details within the cube.

The following lists out the steps on how to create the Drillthrough Action within the SSAS project for the cube and then demonstrates how to use the Drillthrough Action in an Excel Pivot Table.

Setup the Drillthrough Action

In the SSAS cube, navigate to the Action table and select the icon for the New Drillthrough Action:

After selecting the New Drillthrough Action, setting up the what the Action should do is the next step. For this example, the AdventureWorks cube is being used to build the action and the example will return the following fields for the sliced cell as its default:

Reseller Sales Amount, Reseller Order Quantity
Date
City, State, Country

The following screen prints lays out these fields and sets up the Default property to True:

Using the SSAS Action

After creating the action and processing the cube, an analyst can now connect to the cube via Excel 2007 and create a Pivot table as its starting report. For the example, the report is going to select Sales Territory by Calendar Year for Reseller Sales Amount.

With the Pivot setup, the following methods will display the the SSAS action, note these actions can only be performed on Non Calculated Members within the cube:

Double click the cell within the Pivot Table
Right click on the cell to drill into and select Show Details
Right click on the cell and select Additional Actions-->AW Default Action (this is the name of our Drillthrough Action in this example).

Right Click-->Show Details Menu

Right Click-->Additional Actions Menu:

Excel Report

Regardless of the method chosen, Excel will create a new Tab within the Excel workbook and display the data as defined within the Drillthrough Action:

Conclusion

As demonstrated, a Drillthrough Action within a SSAS cube is another tool for a business analyst to dive into their data and answer questions that is pertinent for the question at hand. Setting up a Drillthrough Action with the Default behavior set to True allows for the users to not see data that is meaningless to the users, since the users will not be able to translate surrogate keys to actual dimension text, but instead provides useful information to the analyst to help make business decisions. The tool of providing of this useful information is the point of developing the cube in the first place.

A Toolkit book for your BI Toolbox

2009-07-21T10:43:00.003-04:00

As part of a ramp-up plan to get more familiar with the latest Microsoft stack of BI tools and products, a colleague suggested I take a look at The Data Warehouse Toolkit by Ralph Kimball and Margy Ross (ISBN 978-0471200246, on Amazon). I am not going to provide a detailed synopsis of each chapter, you can check the key concepts listed in the text for that, but I want share some thoughts on how I found it useful and an interesting read as well. The book follows a case study format, which helps provide real-world context to the concepts being explained.

If you are new to data warehousing, the first chapter provides all the background and vocabulary you need to get started. This chapter contains explanations and descriptions of facts, dimensions, measures, etc and draws parallels to relational concepts as well.

A four-step design process is laid out early in Chapter 2 and referred to throughout the book. Key here is the notion to select a business process to model. Similar to other database or software development efforts, you need to start with the business requirements first. This is no different in a data warehouse design. The concepts of a data warehouse bus architecture and bus matrix are introduced in Chapter 3. While it’s not exactly the same as an Enterprise Service Bus, occasionally seen in a software solution, there are definitely some parallel ideas.

The case studies in the early chapters start out simple and somewhat generic, but they help illustrate the basic design principals. The more complex case studies in later chapters still provide a good reference for more complex models, techniques, and industry specific scenarios.

A chapter near the end of the book pulls it all together with a project lifecycle description to design and build a data warehouse. Here again, not too different from any other software development effort: gather requirements, design, implement, test, communicate with stakeholders, etc.

Overall, I found the book very useful and plan to keep it handy as a companion reference. I was able to apply some of the modeling techniques directly in a solution leveraging Microsoft SQL Server 2008 Analysis Services, Integration Services and Reporting Services.

Using a View for a Fact Table in SSAS Project

2009-07-17T11:08:00.002-04:00

I recently was involved in building a custom data warehouse and 2005 SSAS cubes which contained many financial calculations off of the original data. Many of the calculations needed to be at the grain level of the warehouse before aggregations could be applied and therefore it made sense to perform these calculations as Named Calculations in the Data Source View (DSV). After the fields were added to the DSV, the new fields were added to the cube and then MDX calculated members were created in the cube for the final calculations to be consumed by the business users.

However, after many Named Calculations were added to the DSV, the performance of processing the SSAS partitions was becoming very poor and from looking at the SQL that the SSAS project was generating, it was clearly not optimized.

The solution was to create a database view that had the optimized SQL code containing the granular calculations and then within the DSV, replace the Fact table with the view. The following screen print displays the menu that appears by right clicking on the table to be replaced within the DSV and then navigating to the Replace Table\With Other Table option:

The view also provided a benefit for the SSAS partitions that were created by being able to setup each partition as a SELECT * from VIEW WHERE condition. The SELECT * allowed for more fields to be added to the view without having to go back into each SSAS partition and recreate the SQL that made up the partition for the additional field. Originally, all of the SSAS cube partitions had the non-optimized SQL code as the source and any additions to the FACT table would require going back to each partition and adding the new field to each and every partition created. Considering were had 150 partitions, this was not an enviable position to be going forward.

However, one property within the DSV needed to be changed to ensure that the DSV did not lose all of the relationships that were previously defined when originally using the Fact table itself. The warehouse had FK relationships amongst the Fact table and the many dimensions that joined to the Fact table. When the DSV was originally created, the relationships were read from the data source and automatically created. When replacing the table with a view, the relationships are not pointing to the view within the database and when you hit OK, you see all of your relationships disappear!

The fix for this problem is to change the DSV RetrieveRelationships property to False. The plus, you do not lose your relationships already defined in the DSV. The minus, you must manually create any new relationships that exist going forward, which is an acceptable task considering the many relationships that would have been lost and any future refreshing of the DSV would eliminate the relationships that were recreated.

The following screen print displays the location of the RetrieveRelationships property to update:

The final result of changing the Fact table from the warehouse with Named Calculations to a SQL Database View was being able to update the processing time for each partition from the SSAS generated query that was taking minutes to return to retrieving the same data within seconds by using the SQL Database View. In addition, we were able to improve the maintenance of the partitions for field updates from a painstaking manual update procedure and then a cube process to simply reprocessing the cube when field changes were made.

Reporting Services and SharePoint - Context Expired Exception

2009-06-23T10:18:00.003-04:00

Problem
At one of our client locations, developers and users complained of issues in accessing reports within SharePoint. The error was not occurring consistently. We quickly decided that since it wasn't happening every time, it may be a web-front-end (WFE) issue (as there are three WFEs load-balanced together) . After thinking about this, we narrowed it down to one of the WFEs.

When trying to open a report or even looking at a data source, SharePoint would throw an exception: "Report Server has encountered a SharePoint error. ---> Microsoft.SharePoint.SPException: The context has expired and can no longer be used. (Exception from HRESULT: 0x80090317) " . The full error message is at the bottom of this post.

Solution
I tried the usual remedy steps such as IISRESET and rebooting the server. I then tried to repair the RS Add-In and even run the SharePoint Configuration Wizard. Nothing was working but I did notice in the logs that the timestamps were not the right time.

After several other (almost) drastic actions, I decided to fix the clock on the server to have the correct time. Voila! This resolved the context issue and everything started working. The clock was so much different than any client machine that the security context was assumed to be expired.

Sometimes a simple thing can cause major problems and sometimes major problems can be fixed by a simple solution.

Full Error Message
System.Web.Services.Protocols.SoapException: Report Server has encountered a SharePoint error. ---> Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error. ---> Microsoft.SharePoint.SPException: The context has expired and can no longer be used. (Exception from HRESULT: 0x80090317) ---> System.Runtime.InteropServices.COMException: The context has expired and can no longer be used. (Exception from HRESULT: 0x80090317) --- End of inner exception stack trace --- at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.GetDataSourceContents(String DataSource, DataSourceDefinition& Definition) at Microsoft.ReportingServices.WebServer.ReportingService2006.GetDataSourceContents(String DataSource, DataSourceDefinition& Definition)

PerformancePoint Connection issues

2009-06-05T11:42:00.002-04:00

I have been troubleshooting some PerformancePoint connection issues in setting up a new instance of PPS and, as you may or may not know, getting the configuration right can be a challenge. There's lots of good info online about setting up the Application Pool (Nick Barclay has a particularly good post), but for those not familiar with certain applications, sometimes what's NOT said can give you fits.

Case in point - when making identity changes to the App Pool, BE SURE TO RESET IIS or those changes aren't in effect. (Recycling the App Pool is not sufficient.)

For those not familiar, the easiest way to cycle IIS is to open a command prompt and enter IISRESET. It's a quick process, but as always, be sure to coordinate with anyone else that may be using web services at that time.

Coexistence of Report Viewer Versions 8.x and 9.x in SharePoint

2009-06-02T10:13:00.008-04:00

In one of our clients' SharePoint environments, both Report Viewer version 8.x and Report Viewer 9.x were installed. The assemblies can live together in the Global Assembly Cache (GAC) because of the different versions. The development team had created a custom reporting interface solution using the Report Viewer 9.x version(s) of the assemblies and controls. In order for the pages to render properly, an entry under HTTP Handlers within the web configuration (web.config) was required:

<add verb="*" path="Reserved.ReportViewerWebControl.axd" type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

This was fine. However, the SharePoint usage report page would not render because it requires the Version 8.0.0.0 HTTP Handler entry. The error message actually stated that that the line was missing. It wasn't missing, it was in there:

It came first in the HTTP Handler section and we quickly realized that the last entry for the same path wins the battle. There seems to be no way of having two HTTP Handlers for the same path (which make sense). So in our case, either the custom reporting solution was broken or the Site Usage reports would not work; this would not be acceptable for long.

I needed to find away to have both Version 8.0.0.0 and Version 9.0.0.0 work within the same web application. I tried various configuration settings and "hacks" in attempt to get it to work until finally I noticed something in the Reporting Services web config. There was some sort of an assembly redirect to tell IIS (ultimately) to use a different version of an assembly. It wasn't the assembly I was dealing with but I figured I may be able to use the same approach.

Therefore, within the SharePoint web.config for port 80, I entered the following within the <runtime> <assembly binding> section under the <system.web> settings :

<dependentAssembly>
<assemblyIdentity name="Microsoft.ReportViewer.WebForms" publicKeyToken="b03f5f7f11d50a3a" culture="neutral" />
<bindingRedirect oldVersion="8.0.0.0"
newVersion="9.0.0.0"/>
</dependentAssembly>

Essentially, this was telling SharePoint that "if you are looking for the 8.0.0.0 version, use the 9.0.0.0 instead". This allowed the Site Usage page to at least render! It rendered with all of the web parts however the web parts all contained the same error message. They were now looking for the 9.0.0.0 version of the Microsoft.ReportViewer.ProcessingObjectModel assembly which didn't exist. I looked in the GAC and there was only the Version 8.0.0.0 in there. So I figured this was probably deprecated with the latest Report Viewer updates for SharePoint.

So I decided to be tricky and use the same redirect for the Microsoft.ReportViewer.ProcessingObjectModel assembly but using the opposite version settings:

<dependentAssembly>
<assemblyIdentity name="Microsoft.ReportViewer.ProcessingObjectModel" publicKeyToken="b03f5f7f11d50a3a" culture="neutral" />
<bindingRedirect oldVersion="9.0.0.0"
newVersion="8.0.0.0"/>
</dependentAssembly>

This actually worked! The Site Usage reports rendered and the development team's custom reporting interfaces were still functioning. We thought we were going to have to apply MOSS 2007 SP2 and/or SQL Server SP3 and worse case open a case ticket with Microsoft Support - not anymore!

SSAS and Dynamic Excel Reports

2009-05-14T16:47:00.001-04:00

Excel can be a very powerful tool for allowing power users to analyze data in cubes. However it is also possible to create dynamic reports using Excel. This can be accomplished using Excel Cube functions. Cube functions allow you to create the same type of functionality achieved in a pivot table but in a individual cell. Then utilizing other Excel functions it is possible to parameterize the report making it dynamic.

For this example, I will use one of the Adventures Works Cubes. The first step is to create a pivot table that contains the information we want. For this example I created a comparison of Internet Gross Profit sales for current month, prior month and the same month a year ago. This data is further divided by primary sales territory.

Personally I found that using the Cube Functions in the formula editor to be complicated. Thankfully there is a menu option in Excel 2007 that can help. It is called ‘Convert to Formulas’ and is found in the PivotTable Tools –> OLAP Tools menu.

Once this table has been converted we can analyze a few key cells to understand what is going on. The first is A3 where the measure is located. (All cell references relate to the image of the pivot table above.) This cell now contains the formula:

=CUBEMEMBER("AdventureWorksDW",
"[Measures].[Internet Gross Profit]")

The function CUBEMEMBER allows you to select the measure. If you edit the text you can select other measures, just delete all the text inside quote after this ‘[Measures].[’ and Excel will offer other options.

The second cell we want to inspect is B8 (or any other value cell.)

=CUBEVALUE("AdventureWorksDW",$A$3,$A8,B$7)

You can see that this cell just contains the name of the data source and references to other cells. One thing to note is that not all cells that were in the pivot table are used by the cube functions so they can be deleted. Here is my cleaned up table.

The next step is to parameterize the dates reference in my converted table so that we can make the report dynamic. Evaluating the cell D4 will help us do that.

=CUBEMEMBER("AdventureWorksDW","[Date].[Calendar].[Month].&[2004]&[5]")

You can see that the year and month number are used, however this will vary depending on how the time dimension in your cube is setup. The first step is to place the date to drive the report into cell B1. Then change the formulas in B4, C4, and D4 to use this date.

I replaced the formulas in cells B4 and D4 with the ones below.

B4: =CUBEMEMBER("AdventureWorksDW", CONCATENATE("[Date].[Calendar].[Month].&[",YEAR(B1)-1,"]&[",MONTH(B1),"]"))

D4: =CUBEMEMBER("AdventureWorksDW",
CONCATENATE("[Date].[Calendar].[Month].&[",YEAR(B1),"]&[",MONTH(B1),"]"))

The only difference is we subtract 1 year from cell B4. Cell C4 is a little more difficult because when the current month is January the prior month is December of the prior year. If it is January the we need to add an if statement to see if we need to subtract 1 form the the year and set the month to 12. If not we can just subtract 1 from the month number.

C4: =CUBEMEMBER("AdventureWorksDW", CONCATENATE("[Date].[Calendar].[Month].&[",IF(MONTH(B1)=1,YEAR(B1)-1,YEAR(B1)),"]&[",IF(MONTH(B1)=1,12,MONTH(B1)-1),"]"))

The final spreadsheet now looks like the image below but now the data in the table, which is pulled from the cube can be updated by changing the date in cell B2.

This is a straightforward example but could easily be expanded to handle more complex cases. Graph data can also be updated using the same methodology. So it is possible to see that this now gives you a complete report authoring environment that you can give to Excel power users and allow them to build their own reports from the cubes your organization has built.

Connecting to DB2 on AS/400 via OLE DB

2009-05-12T13:53:00.001-04:00

To connect to a DB2 database via OLE DB, you’ll need the Microsoft OLEDB Provider for DB2 (IBM also has one), which is part of the Microsoft SQL Server 2008 Feature Pack.

After installing the provider, there are several ways you can test connectivity: creating a UDL file, using the “Data Access Tool” that ships with the Microsoft provider, or creating an OLE DB Connection in BIDS to name a few. Below is a screen shot of the test connection’s property grid. As you might expect, I’ve replaced the values of user id, IP address, schema, etc., with fake ones. Properties to note are:

DBMS Platform: in my case, this is DB2\AS400
Default Schema: same as the library name in DB2. Ex., the library name in the following query is “LIBNAME”: “SELECT * FROM LIBNAME.FILENAME”
Package Collection: same as above
Initial Catalog: Your AS/400 DBA should be able to provide this. Or, if you have access to a linked server, expand it in SSMS and you should see the catalog name.

At this point, my test connections were unsuccessful. But the Microsoft OLEDB Provider for DB2 ships with a handy “SNA Trace Utility” that revealed more information:

|00000f50.000008b0 DDM 001B1153 E4E2C5D9 40C5E7C9 E340C4C5 D5C9C5C4 40C1C3C3 C5E2E2
|00000f50.000008b0 DRDA AR message: Name: RDBATHRM, Severity: Error, Diagnostic: USER EXIT DENIED ACCESS, Database: S1033BC1

Notice the DRDA “DENIED ACCESS” message. Interestingly, the Microsoft OLEDB Provider for DB2 uses the DRDA protocol. Below is a screenshot from a 5250 session with the AS/400. Notice the configuration setting “DDM / DRDA request access”, which is configured to reject all DDM / DRDA connections. Changing this value to “*OBJAUT” resolved the issue. For more information, see the CHGNETA Command Description and look for DDMACC (DDM / DRDA request access). See also Microsoft KB article 246714.

Non-Standard uses for SSIS Package Configurations

2009-05-01T16:29:00.002-04:00

by Joe Toscano, RDA

SQL Server Integrations Services Package Configurations: Much more than dynamic connections
Package Configurations in SSIS allow you to dynamically change object property values such as a connection’s ConnectString simply by changing a row in a SSIS Package Configuration table or an XML Configuration File. They make your SSIS solution much more flexible in many ways and while connection strings are tops on the package configuration hit list, this blog entry discusses additional ways one can take advantage of SSIS Package Configurations.

Why do we need Package Configurations?
Think about how this feature can be used. As your SSIS packages moves up the food chain from Development to Test and eventually to Production, you may need to change many package attributes along the way. Maybe while in TEST, your packages extracted sample data from a partial copy of the production data. Maybe while in TEST your package wrote to a TEST destination (SQL Instance), or FTP-ed files to a ftp development folder from an FTP TEST Site. Finally, maybe while in TEST your solution emailed a group of developers if there were any package failures. What if you thoroughly tested your SSIS solution in this environment and you are ready to move your solution into production?

Scary memories of DTS and package promotion / deployment
If you worked with SQL Server 2000’s DTS, one of the only ways to do deploy a package to a new server was while in the DTS Package Editor issue a Package à Save As and change the SQL Instance where the Package physically resides. Once this was done, in many cases you had to then manually change all of the connections to point to the correct product-mode sources and destinations. This was a painstakingly manual process. In many cases, many other package attributes needed to change in addition to sources and destinations. Enter SSIS Package Configurations. They allow you to modify rows in a Package Configuration Table or XML configuration file to achieve the same end-result.

How Package Configurations Work
While there are several types of Package Configurations in this example we have chosen to store our package configuration / dynamic values in a SQL Server table. Further, we have chosen to create a database called SSIS that contains our package configuration tables along with some other tables and stored procedures used by our auditing subsystem. SSIS allows you to define a Package Configuration Environment Variable, and we use this to store a connect string to the SQL Instance that houses this SSIS database. Our environment variable was called SSISPACKAGECONFIGURATIONS. We maintain multiple copies of the PackageConfiguration table each with different values to support the different environment. For example – we have one package configuration table that is used in my local development environment to point to my local SQL 2005 Developers Edition, while another was created for our shared test SQL Instance and yet another for our official production SQL Instance.

Both standard and non-standard Package Configuration Examples

SSIS package Configurations are easily created in Business Intelligence Development Studio. (SSIS -> Package Configurations)
Let’s look at our package configuration categories and provide a typical Business Intelligence Development Studio (BIDS) Solution that utilizes them:

1. Source and Destination Connections and directories
SSIS Package configurations can be used to hold the connection strings for our connections to our SSIS, Stage and SalesDW databases which are databases used by our solution. In this solution, we are extracting monthly sales data from a FTP Site, so we can dynamically store the directories used to support this operation along with the FTP security credentials.

a. SSIS, Stage and SalesDW OLEDB Connections
These represent standard OLEDB connections used by packages as either a source or destination. This may the most common use for package configurations. For these package configurations we choose to designate the ConnectString property of these connections making it the dynamic property we can easily change.

b. FTP Working Directory
Our solution uses an ftp .bat file and ftp scripts to drive the FTP get and put commands. We’ve found this gave us more flexibility than using the SSIS FTP task. This package variable designates the directory that houses the .bat and script files used by the FTP command. We had one location while our solution was in development, but in production our servers were clustered so we couldn’t reference a server’s local drive.

c. FTP Local Directory, FTP Remote Directory
Once connected to the FTP site you can change your local and remote directories using the lcd the cd commands. The local directory setting determined where files resided after they were gotten using the FTP mget command. As packages moved from development to test to production these did need to change so creating package variables whose values were package configuration items saved us on package maintenance.

d. SSIS Package Directory
Initially this was set to the ‘working directory’ while we were working with the SSIS Solution in the Business Intelligence Development Studio. (In our case, the working directory was a SourceSafe local working directory setting) Once the packages were deployed to a production server, this changed in the package configuration table present in the production server to the actual deployment path on the production server. (for our solution, we stored our package files in the file system as opposed to in SQL Server)

2. FTP Attributes

We’ve found that while in development mode we relied on a test FTP instance, but once our solution was deployed to production we were asked to use the official production site. All security credentials changed; therefore, storing these as package variables whose values were package configuration items saved us on package maintenance.

a. FTP Site Name, FTP Login, FTP Password
The FTP Security credentials were stored in package variables as package configuration items. This allows us to easily make changes without having to modify our SSIS Solution. Instead, we modified rows in the package configuration table.

b. FileToExtract
Our FTP site supported the use file wildcards (% matched any single character) and we were able designate the file to extract making the Year and Month (YYMM) portion of the file match any year and month. This way no matter what files were dropped to our FTP Site, our solution would auto-discover them and then extract and load them.

c. ParentTraceMode
We’ve found that while in development mode we actually wanted to the FTP results. What files did FTP get or what files did FTP move/rename. At first, this wasn’t possible because the FTP window appeared and disappeared too quickly since we were working with very small files. We decided to create a package variable called ParentTraceMode whose value is obtained from the calling parent using the Parent Package Configuration. If this value is set to 1, SSIS Precedence Constraints force the execution of a trace mode FTP command that simply does not provide the ending FTP quit statement. Sounds simple, but without the quit command the FTP window stays displayed for us to visually inspect. I found this trace flag also useful to direct the flow of the package to enable other tasks such as Script Tasks the display useful information via message boxes. Also, our FTP processing normally cleans up after itself by removing the FTP Script files that were just used. Good thing because there’s some sensitive security credentials in this file! With trace mode enable these script files remain in the FTP Working Directory for inspection.

3. Email Recipients
While in development our email recipients were hard-coded to be our development team members. As we got close production deployment, we realized there were many more interested parties; therefore, this list was changed to a package variable whose value was a package configuration.

a. EmailRecipients
Stores a semi-colon separated list of email recipients. This package variable is then passed to our stored procedure that sends out an SMTP Email notification.

4. Auditing Related Configurations

The auditing subsystem uses package configurations extensively. Below one package configuration example:

a. ParentPgkID
Each package is assigned a unique identifier by the auditing subsystem. This is a parent package configuration (a package configuration value obtains from the calling or parent package) that allows track parent and child package execution hierarchy from our auditing tables. This has proven very useful since it is very common to have a master package whose job it is to execute all extract and load packages in the correct order – thereby producing an entire batch extract and run.

Sales for past six months using SQL Pivot Operator

2009-04-15T13:44:00.006-04:00

Problem Statement
A simple revenue report needed to be generated that displays the summarized revenue dollars for each customer from a SalesDetail table. The YYMM column that holds the year and month ('0904' = April, 2009 ...). This YYMM column show be transposed and displayed as a column header in the report rather than have it display in each row of data.

Background
Each month, we get revenue new data that is appended to the SalesDetails table. At first, we simply modified the SQL Code that is generated the report data to include the new month's data. Each month our report got a bit wider, but it was nice to see all of our past data and new total revenue. Our client decided that they would rather have a no-maintenance solution that simply reports on the most recent 6 months (They didn't need to go back any further). This way they didn't have to make any report changes from month-to-month.

Using PIVOT Operator, we had the report working by hard-coding each YYMM value, but we are looking for a solution without any hard-coding. Here is the sample of SQL using hard-coded YYMM values

SELECT
CustomerNumber,
CustomerName,
[0810] as 'Oct 2008 Revenue',
[0811] as 'Nov 2008 Revenue',
[0812] as 'Dec 2008 Revenue',
[0901] as 'Jan 2009 Revenue',
[0902] as 'Feb 2009 Revenue',
[0903] as 'Mar 2009 Revenue',
[0904] as 'Apr 2009 Revenue',
[0810] + [0811] + [0812] + [0901] + [0902] + [0903] + [0904] as 'Total Revenue'
FROM
(SELECT
CustomerNumber, CustomerName, Revenue, YYMM
FROM SalesDetails
) SD
PIVOT
( SUM(Revenue) FOR YYMM IN ([0810], [0811], [0812], [0901], [0902], [0903], [0904])) as pvt
ORDER BY
CustomerNumber, CustomerName

Solution
This problem can be solved using PIVOT operator and without Hard-coding the YYMM values, but getting the column names as mentioned in the sample SQL above could be difficult.

PIVOT Operator works on fixed number of attribute values. Hence the number of values cannot be dynamic. Hence I tried work around this issue.

Here is SQL to create the Table and load sample data

CREATE TABLE dbo.SalesDetails
(
YYMM CHAR(4) NULL,
CustomerName VARCHAR(64) NULL,
CustomerNumber VARCHAR(32) NULL,
Revenue MONEY NULL
)
GO

----- Sept 2008 Sales
INSERT dbo.SalesDetails VALUES ('0809', 'Sears' ,'11111', $34.07)
INSERT dbo.SalesDetails VALUES ('0809', 'Sears' ,'11111', $24.07)
INSERT dbo.SalesDetails VALUES ('0809', 'Kmart' ,'22222', $41.11)
INSERT dbo.SalesDetails VALUES ('0809', 'Kmart' ,'22222', $1.78)
--- Oct 2008 Sales
INSERT dbo.SalesDetails VALUES ('0810', 'Sears' ,'11111', $34.99)
INSERT dbo.SalesDetails VALUES ('0810', 'Sears' ,'11111', $124.00)
INSERT dbo.SalesDetails VALUES ('0810', 'Sears' ,'11111', $11.11)
INSERT dbo.SalesDetails VALUES ('0810', 'Kmart' ,'22222', $61.78)
--- Nov 2008 Sales
INSERT dbo.SalesDetails VALUES ('0811', 'Sears' ,'11111', $84.39)
INSERT dbo.SalesDetails VALUES ('0811', 'Kmart' ,'22222', $124.00)
INSERT dbo.SalesDetails VALUES ('0811', 'Kmart' ,'22222', $11.11)
INSERT dbo.SalesDetails VALUES ('0811', 'Kmart' ,'22222', $61.78)
--- Dec 2008 Sales
INSERT dbo.SalesDetails VALUES ('0812', 'Sears' ,'11111', $2.99)
INSERT dbo.SalesDetails VALUES ('0812', 'Sears' ,'11111', $41.81)
INSERT dbo.SalesDetails VALUES ('0812', 'Kmart' ,'22222', $283.23)
--- Jan 2009 Sales
INSERT dbo.SalesDetails VALUES ('0901', 'Sears' ,'11111', $2.99)
INSERT dbo.SalesDetails VALUES ('0901', 'Sears' ,'11111', $41.81)
INSERT dbo.SalesDetails VALUES ('0901', 'Kmart' ,'22222', $43.44)
INSERT dbo.SalesDetails VALUES ('0901', 'Kmart' ,'22222', $10.34)
INSERT dbo.SalesDetails VALUES ('0901', 'Kmart' ,'22222', $29.00)
INSERT dbo.SalesDetails VALUES ('0901', 'Kmart' ,'22222', $111.34)
--- Feb 2009 Sales
INSERT dbo.SalesDetails VALUES ('0902', 'Sears' ,'11111', $2.99)
INSERT dbo.SalesDetails VALUES ('0902', 'Sears' ,'11111', $41.81)
INSERT dbo.SalesDetails VALUES ('0902', 'Kmart' ,'22222', $43.44)
INSERT dbo.SalesDetails VALUES ('0902', 'Kmart' ,'22222', $10.34)
INSERT dbo.SalesDetails VALUES ('0902', 'Kmart' ,'22222', $29.00)
INSERT dbo.SalesDetails VALUES ('0902', 'Kmart' ,'22222', $111.34)
--- Mar 2009 Sales
INSERT dbo.SalesDetails VALUES ('0903', 'Sears' ,'11111', $12.89)
INSERT dbo.SalesDetails VALUES ('0903', 'Sears' ,'11111', $21.81)
INSERT dbo.SalesDetails VALUES ('0903', 'Kmart' ,'22222', $33.40)
INSERT dbo.SalesDetails VALUES ('0903', 'Kmart' ,'22222', $110.94)
--- Apr 2009 Sales
INSERT dbo.SalesDetails VALUES ('0904', 'Sears' ,'11111', $14.89)
INSERT dbo.SalesDetails VALUES ('0904', 'Sears' ,'11111', $15.81)
INSERT dbo.SalesDetails VALUES ('0904', 'Kmart' ,'22222', $13.40)
INSERT dbo.SalesDetails VALUES ('0904', 'Kmart' ,'22222', $156.94)

Since PIVOT works on fixed set of Attributes, I used a CASE statement and passed parameters to segregate past 6 months data as [SIXTHMONTH], [FIFTHMONTH], [CURRENTMONTH] etc. I used this new fixed attributes in the PIVOT portion. Here is the solution

DECLARE @SixthLastMonth char(4)
DECLARE @FifthLastMonth char(4)
DECLARE @FourthLastMonth char(4)
DECLARE @ThirdLastMonth char(4)
DECLARE @SecondLastMonth char(4)
DECLARE @LastMonth char(4)
DECLARE @CurrentMonth char(4)

SELECT @SixthLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -6, GETDATE()), 12), 1,4)
SELECT @FifthLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -5, GETDATE()), 12), 1,4)
SELECT @FourthLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -4, GETDATE()), 12), 1,4)
SELECT @ThirdLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -3, GETDATE()), 12), 1,4)
SELECT @SecondLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -2, GETDATE()), 12), 1,4)
SELECT @LastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -1, GETDATE()), 12), 1,4)
SELECT @CurrentMonth = SUBSTRING(CONVERT(VARCHAR, GETDATE(), 12), 1,4)

SELECT
CustomerNumber,
CustomerName,
ISNULL([SIXTHMONTH], 0.00) as 'Sixth Last Month Revenue',
ISNULL([FIFTHMONTH], 0.00) as 'Fifth Last Month Revenue',
ISNULL([FOURTHMONTH], 0.00) as 'Fourth Last Month Revenue',
ISNULL([THIRDMONTH], 0.00) as 'Third Last Month Revenue',
ISNULL([SECONDMONTH], 0.00) as 'Second Last Month Revenue',
ISNULL([LASTMONTH], 0.00) as 'Last Last Month Revenue',
ISNULL([CURRENTMONTH], 0.00) as 'Current Last Month Revenue',
ISNULL([SIXTHMONTH], 0.00) + ISNULL([FIFTHMONTH], 0.00)
+ ISNULL([FOURTHMONTH], 0.00) + ISNULL([THIRDMONTH], 0.00)
+ ISNULL([SECONDMONTH], 0.00) + ISNULL([LASTMONTH], 0.00)
+ ISNULL([CURRENTMONTH], 0.00) AS 'Total Revenue'
FROM
( SELECT
CustomerNumber,
CustomerName,
Revenue,
CASE YYMM
WHEN @SixthLastMonth THEN 'SIXTHMONTH'
WHEN @FifthLastMonth THEN 'FIFTHMONTH'
WHEN @FourthLastMonth THEN 'FOURTHMONTH'
WHEN @ThirdLastMonth THEN 'THIRDMONTH'
WHEN @SecondLastMonth THEN 'SECONDMONTH'
WHEN @LastMonth THEN 'LASTMONTH'
WHEN @CurrentMonth THEN 'CURRENTMONTH'
ELSE 'OTHER'
END [YYMM]
FROM SalesDetails
) SD
PIVOT
( SUM(Revenue) FOR YYMM IN ( [SIXTHMONTH], [FIFTHMONTH], [FOURTHMONTH], [THIRDMONTH], [SECONDMONTH], [LASTMONTH], [CURRENTMONTH])
) as pvt
ORDER BY
CustomerNumber, CustomerName

This solves the issue of Hard-coding the YYMM values. But the column names will be like “Sixth Last Month Revenue” instead of “October 2008 Revenue”. Well this can be solved by tweaking the headings expression in the SSRS reports.

Alternate Solutions

Using SSRS reports
One can use PIVOT reports to solve this problem by filtering out sales which is older than six months. The column headings expression need to be tweaked to show the values like “October 2008 Revenues” etc. Here is the Sample Screen shot

Using Dynamic SQL
Dynamic SQL can be used to solve this issue too. Here is the code.

declare @ColNameSixth char(40), @ColNameFifth char(40), @ColNameFourth char(40), @ColNameThird char(40), @ColNameSecond char(40), @ColNameLast char(40), @ColNameCurrent char(40)

select @ColNameSixth = datename(mm, dateadd(mm, -6, getdate())) + ' ' + cast(YEAR(dateadd(mm, -6, getdate())) as varchar) + ' Revenues'
select @ColNameFifth = datename(mm, dateadd(mm, -5, getdate())) + ' ' + cast(YEAR(dateadd(mm, -5, getdate())) as varchar) + ' Revenues'
select @ColNameFourth = datename(mm, dateadd(mm, -4, getdate())) + ' ' + cast(YEAR(dateadd(mm, -4, getdate())) as varchar) + ' Revenues'
select @ColNameThird = datename(mm, dateadd(mm, -3, getdate())) + ' ' + cast(YEAR(dateadd(mm, -3, getdate())) as varchar) + ' Revenues'
select @ColNameSecond = datename(mm, dateadd(mm, -2, getdate())) + ' ' + cast(YEAR(dateadd(mm, -2, getdate())) as varchar) + ' Revenues'
select @ColNameLast = datename(mm, dateadd(mm, -1, getdate())) + ' ' + cast(YEAR(dateadd(mm, -1, getdate())) as varchar) + ' Revenue'
select @ColNameCurrent = 'Current Revenues'

DECLARE @SixthLastMonth char(4), @FifthLastMonth char(4),@FourthLastMonth char(4),@ThirdLastMonth char(4),@SecondLastMonth char(4),@LastMonth char(4),@CurrentMonth char(4)

SELECT @SixthLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -6, GETDATE()), 12), 1,4)
SELECT @FifthLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -5, GETDATE()), 12), 1,4)
SELECT @FourthLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -4, GETDATE()), 12), 1,4)
SELECT @ThirdLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -3, GETDATE()), 12), 1,4)
SELECT @SecondLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -2, GETDATE()), 12), 1,4)
SELECT @LastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -1, GETDATE()), 12), 1,4)
SELECT @CurrentMonth = SUBSTRING(CONVERT(VARCHAR, GETDATE(), 12), 1,4)

DECLARE @SQL NVARCHAR(4000)

SELECT @SQL = '
SELECT
CustomerNumber,
CustomerName,
ISNULL([' + @SixthLastMonth + '], 0.00) as ''' + @ColNameSixth + ''',
ISNULL([' + @FifthLastMonth + '], 0.00) as ''' + @ColNameFifth + ''',
ISNULL([' + @FourthLastMonth + '], 0.00) as ''' + @ColNameFourth + ''',
ISNULL([' + @ThirdLastMonth + '], 0.00) as ''' + @ColNameThird + ''',
ISNULL([' + @SecondLastMonth + '], 0.00) as ''' + @ColNameSecond + ''',
ISNULL([' + @LastMonth + '], 0.00) as ''' + @ColNamelast + ''',
ISNULL([' + @CurrentMonth + '], 0.00) as ''Current Last Month Revenue'',
ISNULL([' + @SixthLastMonth + '], 0.00)
+ ISNULL([' + @FifthLastMonth + '], 0.00)
+ ISNULL([' + @FourthLastMonth + '], 0.00)
+ ISNULL([' + @ThirdLastMonth + '], 0.00)
+ ISNULL([' + @SecondLastMonth + '], 0.00)
+ ISNULL([' + @LastMonth + '], 0.00)
+ ISNULL([' + @CurrentMonth + '], 0.00) AS ''Total Revenue''
FROM
( SELECT CustomerNumber, CustomerName, Revenue, [YYMM]
FROM SalesDetails
) SD
PIVOT
( SUM(Revenue) FOR YYMM IN ( [' + @SixthLastMonth + '], [' + @FifthLastMonth + '], [' + @FourthLastMonth + '], [' + @ThirdLastMonth + '], [' + @SecondLastMonth + '], [' + @LastMonth + '], [' + @CurrentMonth + '])) as pvt
ORDER BY
CustomerNumber, CustomerName
'

EXEC(@SQL)

Reporting Services Print from SharePoint: "Unable to load client print control"

2009-04-13T15:54:00.004-04:00

In an enterprise environment that doesn't allow users to download ActiveX controls, the printing of reports from within SharePoint may not work correctly as the IE Browser cannot load the client print control.

We attempted to install the RSClientPrint.cab manually and register the DLLs but that didn't work. Even creating a new MSI file did not work. Finally, after going through various steps, we were able to allow the ActiveX controls to be silently installed within the locked-down environment. In order for the steps below to work, any group policy must "Allow download of signed ActiveX controls". This was confirmed to be resolved using IE 6.0, 7.0, & 8.0.

Here is the summary of our troubleshooting for issue “Unable to load client print control” :

1. When we tried to print any report from Sharepoint Integrated Reporting Services, we got an error “Unable to load client print control”.
2. We checked that Reporting Services is on 9.00.3042 (SP2) version.
3. Upgraded Report Server with Security Update for SQL Server 2005 Service Pack 2 (KB954606) : http://www.microsoft.com/downloads/details.aspx?familyid=4603C722-2468-4ADB-B945-2ED0458B8F47&displaylang=en
4. That went successful and Report Server version changed to 9.00.3073
5. Then we installed the updated version of the SharePointRS.msi on all Web Front Ends in the SharePoint farm. The new version of the add-in can be found here: http://www.microsoft.com/downloads/details.aspx?FamilyID=1e53f882-0c16-4847-b331-132274ae8c84&DisplayLang=en
6. Verified that the Microsoft.ReportingServices.SharePoint.UI.WebParts.dll has been updated to the correct build of 9.00.3294 by looking at the version of this file in the C:\windows\assembly folder.
7. Now users are able to print the report

BI 'Appliances', reference configurations, and very large-scale Data Warehouses

2009-02-27T08:57:00.005-05:00

As a BI consultant, one thing I find interesting in the BI industry is talk of a Data Warehouse 'Appliance'. Sounds like something you plug in, turn on, and --bam-- instant data warehouse! Would be nice, but while that's not likely to happen, what is becoming available from the hardware vendors are preconfigured systems (aka, an appliance) that are designed for the demands of a data warehouse.

Well, Microsoft has partnered with Dell and HP to pre-configure and pretest DW ‘reference’ configurations to compete with similar offerings from other big players. The thing I liked seeing was the ‘cost per terabyte’ - granted, these would be for very large implementations, but I thought this was an interesting reference to keep in the back of the mind for high-level scoping.

According to a news article on TDWI.org ...
“DW appliance pricing varies, but -- if Microsoft can come in at its $13,000-per-TB figure -- it can plausibly claim to field one of the cheaper DW appliance entries. On the other hand, "cheaper" in the DW appliance segment is something of a moving target. Prices have plummeted to such an extent that even DW powerhouse Teradata Corp. -- which appliance players Netezza Inc. and Dataupia Inc. like to position as a pricey proposition -- now markets a system (the Extreme Data 1550) that it says sells for about $16,500 per TB.” (Netezza sells a unit for $18,000 per TB) (you can read more at http://www.tdwi.org/News/display.aspx?ID=9325)

The other take away from this is that (for anyone still wondering) - yes - Microsoft SQL Server is truly an Enterprise-class BI platform or we wouldn't even be talking about them with the likes of Teradata and Netezza. Furthermore, the features that are planned for the next version of SQL Server as well as Project Madison mean that exciting things are still to come.

For more info, check out -
Project codename "Madison"
http://www.microsoft.com/sqlserver/2008/en/us/madison.aspx
and for a slide-show about Project Madison...
http://sharepoint.microsoft.com/sharepoint/worldwide/us/southeast/Connections%20BUSINESS%20INTELLIGENCE/Connection%20SE%20Jan%202009%20-%20Madison%20Final.pptx