RDA BI/SQL Server Practice Group Blog

Using FOR XML PATH to Combine Multiple Record Values into One Column

2011-10-13T21:12:00.001-04:00

Scenario

I have been asked on numerous occasions to provide a data feed that provides data for a customer, or plant or some other entity with a catch, can you take a one to many relationship and make one column of data out of it. That answer, of course, is “Sure, I can do anything!” but then I get into the how and I start to build a stored procedure that uses temp tables, involves a WHILE loop of some kind and I have to manually code the correct answer. The process is nothing complicated but it definitely involved some extra steps before providing back the results.

My thinking though is that it would be great if I could do that in a SQL statement and let SQL Server do all of the work. Low and behold, I ran across a method that turned my original process into a single query statement, which obviously is much simpler!

Example Data Set

To illustrate, I have the following scenario:

Please provide a Customer list by State and include all of the Customer Regions in one column on each state’s row.

Simple request but the issue is that within a state, I have multiple regions depending on a postal code of the address of the customer office.

In my sample database, my data is broken out into three tables: (for the sake of being able to create this locally as an example, I have done everything in tempdb)

create table #tmpCustomer (CustomerId int identity, CustomerName varchar(max))

create table #tmpCustomerAddress (AddressId int identity, CustomerId int, StateAbbreviation varchar(5), PostalCode varchar(10))

create table #tmpPostalCode (PostalCodeId int identity, PostalCode varchar(20), PostalRegion varchar(30))

The relationships are:

A customer can have multiple addresses
For a state, you can have multiple regions based upon the postal code
The postal code belongs to a postal region

These relationships imply that a customer can have multiple regions per state as well, where the postal code of the customer address will map to the region.

For sample data, the following inserts are executed:

insert into #tmpCustomer (CustomerName)

select 'Customer A'

insert into #tmpCustomerAddress(CustomerId, StateAbbreviation, PostalCode)

select 1, 'PA', '19111'

union

select 1, 'PA', '19103'

union

select 1, 'NJ', '08034'

insert into #tmpPostalCode (PostalCode, PostalRegion)

select '19111', 'NE Philly'

union

select '19103', 'Center City'

union

select '08034', 'South Jersey'

Query Solution

Now, if we queried the data using straight joins we would get the following:

select

c.CustomerName,

ca.StateAbbreviation,

pc.PostalRegion

from

#tmpCustomer c

inner join #tmpCustomerAddress ca

on c.CustomerId = ca.CustomerId

inner join #tmpPostalCode pc

on ca.PostalCode = pc.PostalCode

CustomerName	StateAbbreviation	PostalRegion
Customer A	NJ	South Jersey
Customer A	PA	Center City
Customer A	PA	NE Philly

However, the above dataset does not provide the solution, the request is to roll up the State Regions for the customer into one row per state.

The following query gives us the answer:

select distinct

c.CustomerName,

ca.StateAbbreviation,

substring(

(select '; ' + ipc.PostalRegion

from #tmpPostalCode ipc

inner join #tmpCustomerAddress ica

on ipc.PostalCode = ica.PostalCode

where

ica.CustomerId = ca.CustomerId

and ica.StateAbbreviation = ca.StateAbbreviation

for xml path('')), 3, 1000) as Region

from

#tmpCustomer c

inner join #tmpCustomerAddress ca

on c.CustomerId = ca.CustomerId

inner join #tmpPostalCode pc

on ca.PostalCode = pc.PostalCode

Here are the results:

CustomerName	StateAbbreviation	Region
Customer A	NJ	South Jersey
Customer A	PA	Center City; NE Philly

The key of the query is using the for xml path('') syntax as a subquery in the select portion of the field list, as well as using the distinct keyword to remove duplicate entries.

To note, the for xml path('') statement would put in xml tags if the column was named in the subquery but since we left the column name empty when we concatenated the semicolon and the postal region, the xml nodes are empty. The extra substring is just to parse off the preceding delimiter that we start all records with.

The above example could then be used as an inner query tied to other tables within the database for more fields that are also at a single Customer State level.

Conclusion

As the above example showed, the ability to roll up a one to many relationship into a single row set can be be very useful and it would have saved me time in the past if I had come across this method earlier. In addition, having the ability to do this using one T-SQL statement as opposed to having to generate a loop and a procedure to build your dataset is definitely a simpler and in my opinion a better answer.

Database Partitioning with the Entity Framework

2011-10-03T22:57:00.001-04:00

Problem

Requirements on a recent project called for a cloud-based solution and support for a lot of data. Microsoft Windows Azure was selected as the platform and Microsoft SQL Azure was selected for the database. How much is a lot of data? It was expected to be much greater than 50GB. Now we had a problem, since the maximum size of a SQL Azure database was 50GB and SQL Azure Federation has not yet been released.

Solution

An additional technical requirement for the solution was to use the latest Microsoft Entity Framework. A ready-made solution for database partitioning on SQL Azure with the Entity Framework was not to be found, so we developed our own implementation. The key features of the solution included:

1. Minimal impact to the development team, who were already familiar with EF

2. Ability to add additional databases on-the-fly as one or more database approached capacity

3. Self-leveling of the databases: they should effectively grow at the same rate.

Partitioning Strategy

Regardless of the implementation, selecting the appropriate partitioning strategy is critical. We chose a horizontal partition. The Atomic Unit for the partition (or shard) was a Customer. All transactions (Orders) and other data related to a Customer are in the same shard. The benefits of horizontal partitioning in this manner include:

1. Performance: Once a customer was selected, all database activity for that customer was in the same database. There was no spanning of databases to store or retrieve data related to the current customer. Only Customer searches needed to span databases.

2. Self-leveling: The logic for which database to insert the next new customer was simple – always add to the smallest database.

3. Positioned for Growth: Additional databases could be added without having to re-partition or divide existing data.

Implementation Details

The two main areas of the solution that required enhancements to an otherwise ‘standard’ EF implementation were searches and management of connection strings. Searches had to span databases and return which database a customer was found in as part of the result. Likewise, knowing which database a specific customer belonged to had to be easily passed into EF methods and translated to full connection strings when necessary.

Searches

We chose parallel LINQ queries for searches. See below for an example. An array of connections (how many shard databases we currently have) is maintained in a static ConnectionFactory. So, c.Item1 in the final select below contains the shard where the result was found. c.Item2 contains the full connection string passed into the query.Invoke().

public static IEnumerable<TResult> ExecuteAgainstShards<TArg0, TArg1, TResult, TPreShape>(

this Func<TArg0, TArg1, IEnumerable<TPreShape>> query, TArg1 arg1, Func<string, TPreShape, TResult> shapeResult) where TArg0 : ObjectContext, new()

{

var result = from c in ConnectionFactory.Instance.CreateConnections<TArg0>().AsParallel()

from x in ExecuteWithRetry(() => query.Invoke(c.Item2, arg1))

select shapeResult(c.Item1, x);

return result.ToArray();

}

The web.config contains only the connection string for the first shard and there are some helper methods to parse and create connection strings as needed to access the remaining shards. The web.config (or ServiceConfiguration.cscfg in Windows Azure) also contains the number of active shards used by the ConnectionFactory. This provides the mechanism for adding databases without changing code or adding additional connection strings.

Below is an example compiled query and its calling method that leverage the ExecuteAgainstShards parallel execution.

private readonly Func<MyEntities, CustomerSearchTerms, IQueryable<Data.Person>> customerSearch = CompiledQuery.Compile<MyEntities, CustomerSearchTerms, IQueryable<Data.Person>>

(

(es, search) =>

from p in es.People

where

p.Customers.Count() >= 1 && (search.CustomersId == null ||

p.Customers.Count(e => e. CustomerID == search. CustomerId) > 0) &&

(string.IsNullOrEmpty(search.FirstName) || p.FirstName.StartsWith(search.FirstName)) &&

(string.IsNullOrEmpty(search.LastName) || p.LastName.StartsWith(search.LastName)) &&

select p

);

public IEnumerable<CustomerPersonReference> FindCustomer(CustomerSearchTerms terms)

{

var items = customerSearch.ExecuteAgainstShards

(

terms,

(shardName, person) =>

{

var patient = person.Customers.First();

return new CustomerPersonReference

( CommonRoutines.MapHeader(person, shardName),

});

return items.OrderBy(p => p.Item1.LastName).ThenBy(p => p.Item1.FirstName). ToArray();

}

Connection Strings

As mentioned above, once a Customer has been found in a specific shard, all data related to that Customer will be and must be in that shard. In addition to the shard helper methods for generating and parsing connection string, the development team needed to be disciplined to always use the EF constructor that included a connection string. For example:

using (var entities = new MyEntities(ShardUtilities.GetConnectionString(customerShard))

{

// real work goes here

}

where customerShard is likely the shard returned from the search query above.

Summary

Implementation of a database partitioning solution with the Microsoft Entity Framework was necessary to overcome the current SQL Azure database size limitation. But, the solution is certainly not limited to SQL Azure. If you are using a version of Microsoft SQL Server that does not support partitioning or if you want to maintain a database solution that will work with either SQL Azure or on-premise SQL Server, this fairly straightforward implementation might come in handy.

Where is my Dashboard designer?

2011-09-16T08:44:00.006-04:00

Finding SharePoint performance point Dashboard designer tool was a little tricky. It’s a click once download that is not available anywhere in MSDN downloads or any other place. However you can download it from your Business Intelligence Center page in SharePoint.

To make your life easier, if you ever need it and you already have PerformancePoint installed on your SharePoint sever then here is how you can get it:

Open your browser as an admin user
Go to this URL http://<server-name>/sites/<site-name>/pages/ppssample.aspx
Click the “Run Dashboard Designer” and let it download the tool for you.

In highly customized SharePoint 2010 sites this page can be hard to find.

Scale Breaks in SSRS 2008 R2

2011-06-10T14:34:00.004-04:00

Ever came across an issue when in your chart some axis values are so high that rest of the chart items almost look like they do not have any values? I recently came across the same issue and thanks to SQL Reporting Services 2008 R2's new Scale Break feature which made my charts look more meaningful.

To enable scale breaks on the chart
1. Right-click the vertical axis and then open "Axis Properties".
2. Select the "Enable scale breaks" check box.

To change the style of the scale break
1. On the design surface, right-click on the y-axis of the chart. The properties for the y-axis object are displayed in the Properties pane.
2. In the Scale section, expand the ScaleBreakStyle property.
3. Change the values for ScaleBreakStyle properties, such as BreakLineType and Spacing.

Note: You cannot specify where to place a scale break on your chart. The chart uses its own calculations based on the values in your dataset to determine whether there is sufficient separation between data ranges to draw a scale break on the value axis (y-axis) at run time.

File system error: The following error occurred during a file operation: There is not enough space on the disk.

2011-05-27T10:55:00.002-04:00

Since last two days my package which processes 30+ dimensions and a cube was failing with the File system error. Package was complaining that we were running out of space on file system. I checked both SSIS server and SQL Server and space was not an issue at all. After spending some time on troubleshooting I realized that when we were processing all dimensions in one ssas processing task (ssis task), package was throwing "out of space" error but after breaking the dimension processing task into multiple tasks by dividing dimensions equally into three tasks, package started running successfully for me. Now if you come across the same issue then you know what you need to do. break your dimension processing task into multiple tasks by dividing dimensions equally into multiple tasks. I am processing no more than 15 dimensions in one task.

Microsoft BI Labs

2011-05-06T14:48:00.002-04:00

Microsoft recently launched a new site called, "Microsoft BI Labs".
http://www.microsoft.com/bi/en-us/Community/BILabs/Pages/Home.aspx

As shared on their site, "BI Labs is a collection of experimental business intelligence projects and useful applications made available from internal sources across Microsoft. These projects are prototypes and concepts, and there are no current plans to include them in Microsoft products."

Those of you who are interested in learning more about upcoming techonologies in BI world, this might be a great resource for them. They already have some interesting technologies shared on their site e.g.

1- PivotViewer Extension for Microsoft SQL Server Reporting Services

2- Microsoft SQL Server Data Mining for the Cloud

3- Fuzzy Lookup Add-in for Microsoft Excel 2010

4- MDX and DAX Formatter

5- Microsoft SQL Server Reporting Services Log Viewer

6- BI vision in multiple industries (Video Series)

Enjoy!!!

Microsoft SharePoint 2010 Business Intelligence Unleashed Book

2011-04-22T10:32:00.001-04:00

Several members from the RDA BI team have put together an Unleashed Series book covering Business Intelligence with SharePoint 2010 and can be found online at the following link: http://www.informit.com/store/product.aspx?isbn=0132660113.

The release date for the book is May 9th, 2011 and was written by: Chuck Rivel, Ray Barley, Jim Pletscher, Aneel Ismaily and Steve Mann (who was the original editor of the RDA Blog site and former team lead for the RDA BI Team)

The book covers working with the Microsoft BI stack for SQL Server 2008 R2 and SharePoint 2010, which involves the following services and applications: PerformancePoint Services, Reporting Services, PowerPivot, Visio Services and the SharePoint 2010 Business Intelligence Center.

The following is the the official book description as published on the informIT website:

“SharePoint 2010 is rapidly emerging as the preferred presentation and deployment system for Business Intelligence (BI) solutions that use the Microsoft technology stack. This is a complete guide to both SharePoint 2010 and BI. This book reflects the unsurpassed real-world experience of five expert consultants at RDA, a Microsoft Gold Partner specializing in delivering advanced BI solutions with SharePoint. The authors offer practical, how-to guidance for every key task involved in building, configuring, and deploying BI solutions within a SharePoint environment, delivering crucial information that can't be found in any other book. They bridge the gap between SharePoint 2010 and SQL Server 2008, carefully guiding technical professionals on utilizing BI data stored in SQL Server databases. Both a hands-on tutorial and a thorough reference, SharePoint 2010 BI Unleashed will be an indispensable resource for every BI developer, implementer, manager, or integrator working with Microsoft technologies. “

If looking for help on how to integrate the BI stack into your SharePoint 2010 environment, this book provides a thorough reference to the applications listed above as well as provides real world step by step examples on how to integrate several of the BI components to build a complete BI solution.

Setup up a SQL Server 2008 R2 cluster using Virtual Machines

2011-01-13T20:28:00.009-05:00

Sometimes, SQL Server Professionals need to practice setting up a SQL Server 2008 R2 Cluster on their local machines to gain some experience before setting it up for real in Staging or Production environments. In the past, this used to be virtually impossible without specific hardware such as a Storage Area Network (SAN) which is typically used for hosting the Shared Disks. However, with the release of Windows Storage Server 2008 and Windows Storage Server 2008 R2, it’s become much easier to do so using Virtual Machines. Please note that the following configuration is NOT recommended for a real-world Production environment. This configuration should only be used for a Development environment.

The following are the steps on how to go about it:

Setup 3 Virtual Machines running Windows Server 2008 R2 with 2 NICs on each of them. You can use either VMWare Player or Virtual Box. Configure the 1st NIC with a dynamic IP Address that will be used to connect to the host machine. Configure the 2nd NIC with a static IP Address that will be used by the 3 Servers to talk to each other.

Configure the 1st server as your Active Directory Domain Controller. You can call it MainServer.contoso.net.

Add the 2nd and 3rd servers to the Active Directory created on the 1st server. You can call them SQLServer1.contoso.net and SQLServer2.contoso.net respectively.

Configure MainServer.contoso.net as the Storage Server that will host the Shared Disks. You will need to install the iSCSI Target 3.3 software that can be found in the ISO image of Windows Storage Server 2008 R2. For more information on how to install it, please refer to: http://blogs.technet.com/b/josebda/archive/2010/09/27/windows-storage-server-2008-r2-and-the-microsoft-iscsi-software-target-3-3-are-available-on-msdn-technet-here-s-how-to-install-them.aspx. NOTE: You ONLY need the iSCSI Target software. You do NOT need to install all the other bits for Windows Storage Server 2008 R2.

Configure the iSCSI Target and 2 Shared Disks on MainServer.contoso.net i.e. 1 for the quorum and the other for SQL Server data. This blog gives excellent step-by-step instructions on how to do so: http://mohsartawi.wordpress.com/2010/05/25/implementing-a-virtual-machine-failover-cluster-with-windows-storage-server-2008/. Even though it talks about Windows Storage Server 2008 (not R2), the steps are very similar.

Create a Service Account in Active Directory such as SQLService which will be used to run the SQL Server instances on both the servers.

Run the SQL Server setup on SQLServer1.contoso.net and choose the option “New SQL Server failover cluster installation”. Use a generic name such as SQLServer.contoso.net for the SQL Cluster.

Run the SQL Server setup on SQLServer2.contoso.net and choose the option “Add node to a SQL Server failover cluster”.

Create a sample database on SQLServer1 but connect to it from your host machine using SQLServer.contoso.net. Shut down SQLServer1. You should still be able to access the database because SQLServer2 will take over.

SSRS report parameter to show or hide columns

2010-11-04T09:04:00.014-04:00

Sometimes reports are used mostly for data analysis, rather than presentation. In such cases users request many filtering options and flexibility in choosing the data included in a report. So frequently they ask to select which report columns to show.

When resulting data set is large it may be better to filter the data in the query or stored procedure and return only the columns user requests. However, there is a simple way to let a user show or hide many columns. All the column selections can be included as one additional report parameter.

Suppose a tabular report lists a number of projects with several details. There are about 20 columns, such as project name, employee contact information, project address, etc. Let's say users want ability to hide most of the columns.

To give users such ability add a multi-valued parameter to the report that lists all the columns that users can hide. The parameter values are "non-queried" and correspond to the column names. By default the report shows all columns, so all possible parameter values are also included in the default list.
In report design select a column that users can hide and find and expand Visibility property of that column. If the Hidden attribute of Visibility property is true, the column is not shown in the resulting report. Set the value of the Hidden attribute to the following expression:

=NOT Join(Parameters!p_columns.Value).Contains("COLUMNVALUE")

The COLUMNVALUE must match the value of the parameter that identifies the selected column, such as "projectname", "address", etc. The Join function combines all possible parameter values into a single string. Then Contains function tests whether the user has checked that particular value in the list. The negation of expression tells Reporting Services to hide the column if the user has not checked its value in the parameter list. Set the Hidden property to such expression for every column you want to be able to hide.

One note of caution about using this approach with SSRS 2005. Due to the way SSRS 2005 text renderer works, the exported file does not include any column that can be hidden. That affects CSV and XML export. If you export directly to Excel, all selected columns show and have values. SSRS 2008 R2 does not have such a problem: text formats include all columns regardless of visibility settings, and other formats include all columns selected by the parameter values.

2010-07-21T07:38:00.003-04:00

Processing an OLAP cube with a T-SQL Stored Procedure

When processing cubes, you have a couple of different choices about how to process your cube. One method is use an SSIS package with Cube\Dimension Processing tasks.

Another method to process your cube is to use a stored procedure.

Below is a simple SQL Server stored procedure that you can call to process an OLAP cube using T-SQL.

Create Procedure ProcessCube

@Database varchar(100),

@Cube varchar(100),

@Partition varchar(100) = null, -- If NULL, process the entire Cube

@Server varchar(100) = 'localhost'

as

/* variables used to store object handles */

declare @o_svr int, @o_db int, @o_cube int, @o_part int, @o_mds int

declare @hr int

/* different cube processing options. This SP uses "default" */

declare @PROCESS_DEFAULT int

declare @PROCESS_FULL int

declare @PROCESS_REFRESH_DATA int

set @PROCESS_DEFAULT = 0

set @PROCESS_FULL = 1

set @PROCESS_REFRESH_DATA = 2

-- create a DSO.Server object:

exec @hr = sp_OACreate 'DSO.Server', @o_svr out

if @hr <> 0

begin

print 'Error at create server:'

exec sp_OAGetErrorInfo @o_svr

goto cleanup

end

-- Connect to the server:

exec @hr = sp_OAMethod @o_svr, 'Connect', null, @Server

if @hr <> 0

begin

print 'Error at connect to server:'

exec sp_OAGetErrorInfo @o_svr

goto cleanup

end

-- Get the MDStores property from the Server:

exec @hr = sp_OAGetProperty @o_svr,'MDStores', @o_mds OUT

if @hr <> 0

begin

print 'Error at get getting Server MDStores:'

exec sp_OAGetErrorInfo @o_svr

goto cleanup

end

-- Get the database from the MDStores:

exec @hr = sp_OAGetProperty @o_mds,'Item',@o_db OUT,@Database

if @hr <> 0

begin

print 'Error at get database:'

exec sp_OAGetErrorInfo @o_mds

goto cleanup

end

-- get the MDStores property from the database:

exec sp_OADestroy @o_mds

exec @hr = sp_OAGetProperty @o_db,'MDStores', @o_mds OUT

if @hr <> 0

begin

print 'Error at get database MDStores:'

exec sp_OAGetErrorInfo @o_db

goto cleanup

end

-- get the Cube from the MDStores

exec @hr = sp_OAGetProperty @o_mds,'Item',@o_cube OUT, @Cube

if @hr <> 0

begin

print 'Error at get Cube:'

exec sp_OAGetErrorInfo @o_mds

goto cleanup

end

if @Partition is null -- Process the entire Cube, not just a single partition

begin

exec @hr = sp_OAMethod @o_cube, 'Process', null, @PROCESS_DEFAULT

if @hr <> 0

begin

print 'Error at process Cube:'

exec sp_OAGetErrorInfo @o_cube

goto cleanup

end

end

else -- just process the specified Partition

begin

-- Get the MDStores property of the Cube:

exec sp_OADestroy @o_mds

exec @hr = sp_OAGetProperty @o_cube,'MDStores', @o_mds OUT

if @hr <> 0

begin

print 'Error at get Cube MDStores:'

exec sp_OAGetErrorInfo @o_cube

goto cleanup

end

-- Get the partition to process:

exec @hr = sp_OAGetProperty @o_mds,'Item',@o_part OUT, @Partition

if @hr <> 0

begin

print 'Error at get Parition:'

exec sp_OAGetErrorInfo @o_mds

goto cleanup

end

-- Process the partition:

exec @hr = sp_OAMethod @o_part, 'Process', null, @PROCESS_DEFAULT

if @hr <> 0

begin

print 'Error at process Partition:'

exec sp_OAGetErrorInfo @o_part

goto cleanup

end

end

-- And unlock all objects on the server:

exec @hr = sp_OAMethod @o_svr, 'UnlockAllObjects'

if @hr <> 0

begin

print 'Error at unlock all server objects:'

exec sp_OAGetErrorInfo @o_svr

goto cleanup

end

cleanup:

if @o_mds is not null exec sp_OADestroy @o_mds

if @o_Part is not null exec sp_OADestroy @o_Part

if @o_cube is not null exec sp_OADestroy @o_cube

if @o_db is not null exec sp_OADestroy @o_db

if @o_svr is not null exec sp_OADestroy @o_svr

SQL Server 2008 Policy Management: Defining and Exporting your ‘Golden’ Database State

2010-05-04T10:47:00.014-04:00

Policy Management under SQL Server 2008 allows you to define settings for the properties of target objects that control the behavior or characteristics of those objects. One such managed target is a database. Once defined, these polices can then be enforced across all SQL Instances in your enterprise.

In this blog we’ll see how one can create a ‘golden’ copy of a production database with all of the properties set to specific agreed-upon values and then export the current state of this database as a policy. This is a very nice shortcut for creating policies perhaps based on a template and can be a real time saver. It’s like a having a way of saying – “All production databases across our enterprise must look and behave like this database template. Also, if a production database is created without adhering to this policy we need to know!”

The Database Facet
Facets provide us with the properties that model the behavior or characteristics of a managed target. For a database, there are about 60 properties. Facets provide us with a single view of all properties which for the database facet include Name, Collation, Compatibility level, Auto Close, Encryption Enabled, Log and data File Location, etc. Interestingly, most literature dealing with Policy Management designates the feature's start-point in Management Studio as being Management --> Policy Management --> Policies. However, you are able to access the facet dialog from the database context menu by choosing the Facets menu item. (right click on a database and choose the facet menu option) The screen shot below displays the facet dialog for the MaketingLeads database and shows us what properties are exposed by this facet:

Notice there are numerous properties than can be fine-tuned . (Many of these same properties are exposed and can be changed from the Options Tab present under database properties window) Also, notice the Export Current State as Policy button in this screen snapshot. This is the specific feature that allows you to take a snapshot of the current state and save the behavorial characterstics under the protective umbrella of a single policy. The Export as Policy dialog box is captured below:

In conclusion, exporting the current state can be a quick and easy method for creating policies that monitor any changes from an original state. Consider including in your production database ‘golden copy’ conditions that enforce the well-known Microsoft Database Best Practices such as the Data and Log files must reside on different drives. Further, you may consider supplementing these with your own internal organization specific best practices. You may also consider creating other production templates that leverage other facets. For example -- a 'Production Lockdown' policy can be created that is based on settings made against properties exposed within the Surface Area facet.

What's New in SSRS 2008 R2 Data Visualization

2010-04-30T14:06:00.017-04:00

SQL Server Reporting Services (SSRS) 2008 R2 provides three new ways to visualize data in reports: maps, sparklines and data bars, and indicators.

Maps
Report Designer provides a Map Wizard and Map Layer Wizard to add maps and map layers to your report to help visualize data against a geographic background. You can add labels, legends, titles, a color scale, and a distance scale to help your users interpret the map display. You can add interactive features such as tooltips and drillthrough links, or provide parameters that enable a user to interactively control the visibility of each layer.

Sparklines and Data Bars
Sparklines and data bars are simple charts that convey a lot of information in a little space, often inline with text. Sparklines and data bars are often used in tables and matrices. Their impact comes from viewing many of them together and being able to quickly compare them, instead of viewing them singly. This makes it easy to see the outliers. Sparklines and data bars have the same basic chart elements of categories, series, and values, but they have no legend, axis lines, labels, or tick marks.

Indicators
Indicators are minimal gauges that convey the state of a single data value at a glance. The icons that represent indicators and their states are visually effective, even when they are used in small sizes. Indicators can show trends by using directional images such as arrows; ratings by using incremental icons such as stars; and states by using images such as traffic lights or check marks.

Click image to enlarge

Key points on using Filestream attribute

2010-04-18T16:38:00.000-04:00

According to this study from Microsoft Research, SQL Server handles BLOBs smaller than 256KB more efficiently than a file system, while NTFS is more efficient for BLOBS larger than 1MB. SQL Server 2008 introduced the Filestream storage attribute for varbinary(max) data type to store data in files.

Here are the steps to begin using this feature:

Enable Filestream storage at an operating system level using SQL Server Configuration Manager (system administrator). Then enable it on a SQL Server instance level using sp_configure (database administrator). See specific steps here or here.

Define a database filegroup that ties an NTFS file system location to a SQL Server database. It must be a local file system location.

Add a varbinary(max) column to a table definition with the FILESTREAM property. Add a uniqueidentifier column with the ROWGUIDCOL property (required).

Read and write the data through the stream-based APIs to get the performance advantage attributed to the Filestream property. In .NET 3.5 SP1 such support is provided by the SqlFileStream class.

Example of column definitions:


  Photo VARBINARY(max) FILESTREAM NULL,
  RowId UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID()

Reference:
Bob Beauchemin, Programming with FileStreams in SQL Server 2008, MSDN Magazine

SQL Server 2008 R2 Training Kit

2010-04-12T16:42:00.001-04:00

While attending the Philly .NET Code Camp this past Saturday, one of the presentations was around an overview of the SQL 2008 R2 features. From the presentation, I learned that Microsoft is making the new R2 features available through a download that you can configure locally onto a VPC. The training kit will assist you in learning the product as it provides demonstrations, hands on labs, and videos in the kit itself.

The Training Kit can be found on the Microsoft Download Center at the SQL Server 2008 R2 Update for Developers Training Kit page

All up BI Demo – Release 9.2

2010-03-22T17:44:00.002-04:00

Microsoft has developed a comprehensive Business Intelligence solution demo (version 9.2) using SQL Server 2008 R2, Microsoft Office 2010 and SharePoint 2010 technology. This solution enables Business Intelligence for everyone at Contoso (fictional company created by Microsoft) through familiar tools, self–service capabilities, and access to critical business information.

Dan, the CEO of Contoso, is able to get a quick view of his company’s overall performance with scorecards from PerformancePoint services, quickly isolate trouble spots using a custom-built Silverlight application integrated with SQL Server Analysis Services and Bing Maps, and to facilitate a company-wide collaborative initiative to help direct future decision-making using SharePoint 2010.

Toni, the Chief Marketing Officer uses SharePoint 2010 solutions to go behind the numbers to gain insights into the performance of Contoso product sales. With PerformancePoint Services scorecards and dashboards, as well as Reporting Services reports and aggregated SharePoint lists, she is able to perform quick analysis and uncover useful intelligence in fewer clicks. With immediate access to company-wide people and knowledge, she quickly discovers new information about regional marketing strategies that empowers her to make smarter business choices.

Richard, a marketing analyst for Contoso, takes advantage of the self-service analysis tools available from Microsoft to turn data into useful business intelligence. With PowerPivot for Excel and SharePoint, he brings data from several sources to create new comparisons that wouldn’t have been possible before and then publishes these new worksheets to a secure online gallery for others in his company to use. Armed with his new insights, he generates a summary report for widespread distribution using Report Builder 3.0 and makes solid recommendations to benefit the upcoming product plan. Even while Richard performs his own self-service analysis, the Contoso IT department is able to monitor and manage the resources needed to support the online PowerPivot Gallery and the workbooks contained there.

Key Features:
• SharePoint 2010 Insights, PerformancePoint Services, Excel Services, Visio Services, Reporting Services, Search, Social Relevance, Communities, Team Sites, People Profiles, Blogs, Organizational Browser, Workflows
• Silverlight, integrated with Bing Maps for the Enterprise and SQL Server Analysis Services
• Office 2010 Web Applications, Excel Co-editing
• PowerPivot for SharePoint, Excel
• PowerPivot Management Dashboard
• Report Builder 3.0
• SQL Server 2008 R2 DB Engine

How to access “All up BI Demo 9.2”:
There are two ways to access this demo solution, online remote access to server configured by Microsoft or via configuring virtual machine locally.

Online Solution: Microsoft has configured a remote server for general public to get hands on experience with this new BI version 9.2 solution;
a. Type following url in your internet explorer, http://fs.mssalesdemos.com/
b. Sign in with Windows Live id, create one if you do not have one already
c. Click on “Catalog” from top menu
d. Select “All up BI Demo – Release 9.2” from the list and select hours you want to spend with this solution (right side of the screen) and click start. Application will take few minutes to load the actual machine for you at the bottom.
e. While application prepares the machine for you, you can download the demo script from the main page. You will use this script to get the hands on experience with this solution. You also need to enable ActiveX in your browser for this solution to work.
f. Once machine loads at the bottom of the page, double click on it and it will start the remote session. Sometimes you have to try couple of times to connect.

Virtual Machine Configuration: Second option is to download the VM file from Microsoft Partner’s website. It comes as 42 individual files which you need to extract to get your final 80GB virtual machine file. It was not practical for me to configure this on my local machine because my local machine was not able to meet the requirements. I have listed suggested requirements below from Microsoft;

Requirements:
• Server 2008 R2 RTM with Hyper-V
• 200 GB Free Hard Drive space (High performance disks Preferred)
• Dual Core processor VT enabled
• 8 GB RAM
• Internet Connection

SSIS Table Driven Package Configurations with Row Level Filtering

2010-02-22T20:26:00.016-05:00

SSIS Table Driven Package Configurations with Row Level Filtering

Recently we faced a challenge in which we wanted to implement SSIS table-driven package configurations with a twist. We wanted to have a common or shared SSIS package configuration table and have the packages that make up our solution dynamically filter or utilize only specific package configuration rows from this table based on a package variable value. For example – let’s assume our package variable is the customer name with a sample value of: ‘ToscanosHardware.’ What we like to do is to have our SSIS packages look in the User::str_customer_name package variable for the customer-to-process, and then only utilize package configuration rows that apply to this specific customer. (there is a configuration filter column in the SSIS package configuration table) In other words, we’d like to implement table driven package configurations with row level filtering based on a package variable value.

Why can’t we do this with standard package configurations?

Given the current way in which table based package configurations work, we couldn’t find a way to implement row level filtering based on a package variable. Let’s assume we setup standard table based package configuration that use the following table:

CREATE TABLE [dbo].[SSIS Configurations]
( ConfigurationFilter NVARCHAR(255) NOT NULL,
ConfiguredValue NVARCHAR(255) NULL,
PackagePath NVARCHAR(255) NOT NULL,
ConfiguredValueType NVARCHAR(20) NOT NULL)

When, you create a package configuration entry in this table, you are asked to provide a ConfigurationFilter value. Here’s where we’d like very much to use an SSIS Expression and tell SSIS to filter based on what that expression evaluation to! Wouldn’t it be great to be able to provide a filter value that uses a package variable as part of an expression? This does not seem to work because what filter value we provide is actually literally interpreted.
What we’d like to do is capture in the provided screen snapshot:

<---- Here's what we tried!

Notice for the Configuration filter value we provide. We attempt to provide a SSIS expression here. However, the value we provide: User::customer_name + '_Stage_Connection' is not evaluated but is instead literally interpreted.

The Custom SSIS Solution

We ended up creating our own custom SSIS table-driven solution that was both straight-forward and easy to implmenent. Our custom SSIS solution requires several package variables along with SSIS tasks that reference a custom package configuration table that has the same column layout as the standard package configurations, but we appropriately named our table: dbo.ssis_custom_configurations. The SSIS tasks must be placed at the beginning of each package, so we placed them in the OnPreExecute Event Handler area.
In the sections that follow we will dissect each of these components in details so you can fully understand how to implement this solution.

1. Package Variables

There are several variables that are required to drive the custom solution. These are in the table below:

str_customer_name
This package variable provides the customer name we wish to processs and is actually
used to dynamically construct the ConnectString that points to the customer's Staging
Database. For example -- if we have a customer of 'ToscanosHardware' our staging
database will be ToscanosHardware_Stage.

str_configured_object_name
What is the name of the object are we going to focus on? We've pointed to both Connections
here and package variables. For example -- we have a generic Connection String we called
stage.oledb. Using our solution, this default catalog / database is changed leveraging the
str_customer_name.

str_configured_value
This package variable holds the new connection string value or packag variable value that
is obtained from the custom table.

str_config_filter
This package variable actually holds the value we are using as our row level filter. It is an
expression that combines the package name along with the customer name. Here's the
expression: @[System::PackageName]+"_"+ @[User::str_customer_cd] A sample
value could be 'extract_customer_data_ToscanosHardware'

rs_config
Hold the full result set (the rows returned from the package configuration table that whose
filter_name matches the str_config_filter package variable.

2. Required SSIS Tasks

The SSIS tasks below must be copied to the beginning of each package that you wish to participate in the custom package configurations solution. The tasks are below:

2.1 The GET CONFIG VALUES Execute SQL Task

The EXEC SQL Statement selects all rows from the custom package configuration table
using the str_config_filter in the where clause. Let’s assume we are currently working in a
package that extracts sales details data for the ‘ToscanosHardware’ customer. In this case,
our SSIS package name is ‘ext_sales_details’ so our str_config_filter is
‘ext_sales_details_ToscanosHardware’ The actual select statement used by the Execute
SQL Task is below followed by a screen snapshot of the actual task:

SELECT
configured_object_name,
property_name,
configured_value
FROM
ssis_configurations
WHERE
filter_name = ?

The select statement maps the User::str_config_filter package variable to the parameter.
Also, the User::rs_config package variable holds the full result set allowing a For Each SSIS
container to be used.

2.2 The For Each Container

The For Each Container uses the output of the Execute SQL Task which is a result set from
the custom package configuration table. The Collection Tab specifies the Users::rs_config
object source variable as seen below:

Finally, the columns returned are mapped to the following package variables as specified in
the Variable Mappings tab:

Finally, the columns returned are mapped to the following package variables as specified in
the Variable Mappings tab:

2.3 SCR - SETUP CONFIGS Script Task

The Script Task uses the package variables set in the For Each loop and actually changes the values of either the Connection String or the Package Variable Values. It is up to you to add the rows between the Select Case and the End Select. In the example below, these rows reference the connection strings and/or package variables you wish to change via this custom solution. In the example script below we are changing the ConnectionString values for both the Stage.OLEDB connection and the Stage.ADONET connection. Also, we are changing the values of the str_product_input_filename_pattern and the str_input_feed_directory package variables. These connection string rows and package variable rows must exist in the package configuration table.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

_
_
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
Select Case Dts.Variables("str_configured_object_name").Value.ToString
Case "Stage.OLEDB"
If Dts.Variables("str_property_name").Value.ToString = "ConnectionString" Then
Dts.Connections("Stage.OLEDB").ConnectionString =
Dts.Variables("str_configured_value").Value.ToString
End If
Case "Stage.ADONET"
If Dts.Variables("str_property_name").Value.ToString = "ConnectionString" Then
Dts.Connections("Stage.ADONET").ConnectionString =
Dts.Variables("str_configured_value").Value.ToString
End If
Case "str_product_input_filename_pattern"
If Dts.Variables("str_property_name").Value.ToString = "Value" Then
Dts.Variables("str_product_input_filename_pattern").Value =
Dts.Variables("str_configured_value").Value.ToString
End If
Case "str_input_feed_directory"
If Dts.Variables("str_property_name").Value.ToString = "Value" Then
Dts.Variables("str_input_feed_directory").Value =
Dts.Variables("str_configured_value").Value.ToString
End If
End Select

Dts.TaskResult = ScriptResults.Success
Catch ex As Exception
Debug.Print(ex.Message)
Dts.TaskResult = ScriptResults.Failure
End Try
End SubEnd Class

Conclusion

The custom SSIS solution worked like charm. We were able to actually pass the customer name as a parameter to the startup (master) package via a SQL Server Job. The master package that executed the children packages passed this customer name to the children via a Parent / Child package configuration. By doing this, we could have a generic SSIS solution that dynamically processed different customer data simply by having one job per customer.
We also embedded the required SSIS tasks in the OnPreExecute and OnPostExecute Event Handler events. This way we didn’t have to clutter up each package.

Errors locating SSIS XML package configuration files.

2010-02-19T16:45:00.004-05:00

SSIS provides several ways to make a package configurable, including using database configurations, XML file configurations, and environment variables. Of the different methods, XML files are probably the simplest and most dynamic approaches. However, it can be frustrating to figure out why a package generates errors that “The configuration file cannot be found” or that it “Failed to load at least one of the configuration entries for the package.” Why can’t it locate the configuration file? The key is to understand how SSIS sets what I will call the ‘working folder’ (that’s my term, it’s not in SSIS anywhere).

Before discussing that, let me clarify the situation a bit. When you create an XML configuration file, if you specify the path to the file, then that path will be required on all development machines as well as the production machines. On a recent project, the client didn’t want to have a fixed path for the SSIS files, instead they wanted the packages to accept configuration from wherever the package files were located. So, by design we did NOT specify a folder location for the configuration files. Instead, we simply specified the file name so that the configuration file would ‘live’ in the same folder as package. This worked fine, however it revealed an unexpected behavior of SSIS – this notion of a ‘working folder’.

Question: What determines where SSIS will look for its files? Well, that depends on how you opened your project. Some people launch BIDS from the Start menu and then select the project from the ‘Recent Projects’ list or choose ‘File – Open -- Project/Solution’. If you use either of those methods, then the ‘working directory’ will be wherever the BIDS executables reside, most likely something like ‘C:\Program Files\Microsoft Visual Studio 8\Common7\IDE’. I’m guessing that this is NOT where you put your configuration files, so as a result, when you open a package, you get an error message that the configuration files can’t be found. There’s really a simple solution to this and it is to 1) locate your configuration files (dtsconfig files) in the same directory as your solution file (.sln file), and then to 2) ALWAYS open your solution by double-clicking the solution file (.sln file). This will set the ‘working folder’ to be where the solution lives, your configuration file will be read correctly, and the errors about not finding the configuration should be gone.

Now, that works fine when you are developing, because you are specifying where the files open from, but what about production? How do you specify the ‘working folder’ when scheduling the packages using SQL Agent? Well, we ran into that situation as well, and sure enough when SQL Agent tries to execute a package, its ‘working folder’ defaults to the location of the DTEXEC executable. So, a simple workaround was to create a batch file that first changes to the directory where the packages and configuration files live, and then execute DTEXEC with the command line options that we wanted. Then, in SQL Agent, instead of using the ‘SQL Server Integration Services package’ type job, use an ‘Operating System (CmdExec)’ type job and just call the batch file. Worked like a charm. If the location of the packages ever needs to change to a different folder, drive, or even server, it’s a simple change to the batch file to reference that new location.

In closing, having a fixed configuration location or using an environment variable to hard-code the location of configuration files would avoid this situation, but places requirements on server configuration and directory structure. SSIS can support completely dynamic configuration file location, but you need to be aware of how it sets its ‘working folder’.

Creating a SharePoint 2010 Business Intelligence VM Image (using SQL Server 2008 R2 CTP)

2010-02-01T15:17:00.001-05:00

Introduction

SharePoint is becoming more and more prominent as the delivery mechanism for BI solutions within the Microsoft stack. The SharePoint 2010 BETA along with SQL Server 2008 R2 further strengthen the overall business intelligence capabilities and functionality possible in an organization.

I have a VM image to ramp up on SharePoint 2010 itself but in order to expand into all of the new BI features I wanted to create a new one (from scratch) with the latest SQL Server 2008 R2 CTP. So I did! It was a very tedious process but I found a guide to assist me.

I used the wonderful instructions laid out here: http://www.sharepointdevwiki.com/display/spadmin2010/Installing+SharePoint+2010+Public+Beta

However, since I was using SQL Server 2008 R2 and wanted to configure it for full business intelligence usage, I needed to modify the steps slightly. This post will outline those steps and show any updated walkthroughs.

These instructions/steps are needed only when using SQL Server 2008 R2 CTP as there are several installation issues when integrating with SharePoint 2010. Hopefully in the release these will be corrected.

Installation Steps

1) Follow Steps 00, 0b, 0c and 01 from the SharePoint Dev Wiki guide

2) Instead of step 02, we will install SQL Server 2008 R2 CTP but that comes later! DO NOT INSTALL SQL Server 2008 yet.

3) Steps 3-9 can now be skipped as everything there will be (or has been) installed with Windows Server 2008 R2, SQL 2008 R2, and SharePoint Pre-Reqs.

4) Do not even look at Step 10 - skip to 10b and 10c. I happened to disregard the notes about using the Administrator account for everything and ran into an issue at the end. You may want to look at Step 15 and review the supporting links (as well as the supporting links within those pages). Key thing is to not setup that sp_admin account for this VM.

Also, I decided after the fourth time to actually install the hotfix first (10c), reboot, then install the SharePoint Pre-Reqs (10b), and reboot again. Take the time now because if something fails you could waste hours or days going through the same steps again and again.

5) Perform Step 11 - DO NOT RUN THE CONFIGURATION WIZARD - UNCHECK THE CHECKBOX. Click on Close. The next step here will perform the rest of the configuration.

6) Install SQL Server Analysis Services Integration Mode - (see walkthrough below).

7) Perform Steps 13 and 14
It does take a few minutes to start the User Profile service and to run the synchronization. I did not see anything in the Running Jobs (the first time but the second time I did).

8) Perform Step 15 - before you click on the User Profile Service Application, select it first by click to the right of the link. Then on the top ribbon, click on Administrators (see screens below). The Administrator account should be in there automatically but only the "Retrieve People Data..." permission is selected. I selected Full Control as the TechNet links from those supporting links discussed about the account having full control.

I thought the Profile load was finished and found 0 users but it just took some time to complete. The status said it was done but apparently something was running in the background.

9) Now go back to the SQL Server 2008 R2 installation and install the default instance with Reporting Services (Integrated) and Analysis Services (native). See walkthrough below.

10) Configure Reporting Services - Integrated Mode. See walkthrough below.

11) Optional: Install Visual Studio 2010

12) Optional: Install Office 2010 BETA. Include the Visual Studio Tools for Office (VSTO).

SQL Server 2008 R2 - Analysis Services Integrated Mode Installation and Configuration Walkthrough

Click the "Use the same account..." button and enter the Administrator credentials. Click OK.

Click on "Add Current User". I was paranoid the "umpteenth" time and actually added the Local Service, Local System, and Network Service accounts in attempts to prevent the time-out issue (the issue is described below).

VERY IMPORTANT!
Since this is a domain controller, a time out issue will occur as explained here: http://powerpivotgeek.com/2009/11/17/installing-powerpivot-for-sharepoint-on-a-domain-controller/
You need to monitor the services in Server Manager - Services (while the installation is running) and change the SQL Server Analysis Services (GeminiBI) Service back to the administrator account and make sure it is started. This needs to be corrected as soon as it happens.

You do have time however. The service won't even be listed until it gets installed. The switch occurs about midway through the ASSPIInstallFarmAction. When the install is up to this point, you can see the service listed. I just kept hitting the Refresh and I did see it change eventually. Don't forget to Start the service after you change the Log On back to the Administrator account.

Click Install!

If this step does not complete successfully, you might as well delete the VM and start over. It took me three days to get this right. Possible errors are: 1) timeout, 2) Object Not Set to Instance of an Object, and 3) Could not load or find assembly Microsoft.AnaysisServices.SharePoint.Integrated. There could be others but I wouldn't even bother trying to uninstall and re-install - I tried too many times only to find the best way was to just start over from scratch. Believe me, it will save you hours.

After completion. Go to Central Admin and click on the Configuration Wizards:

Select the Farm Configuration.

Click Next

Use the Existing Managed Account and make sure the Administrator is selected. Click Next.

This process already created a root site collection (I was a little upset about that). So we can skip this part. Click Skip.

Click Finish!

Default Instance - SQL Server 2008 R2 Installation Walkthrough (after Gemini Instance Install)
(For Step 9 above)

Select All. Click Next.

SQL Server 2008 R2 - Reporting Services Configuration (Integrated Mode) Walkthrough
(For Step 10 above)

If you selected Integrated mode during installation, many of these settings are already in place. Go through each screen and make sure there is no warning symbol. Click Apply on the screens that show settings that haven't been set. The walkthrough may go through some additional details that aren't needed for your case. These mimic the configuration if a mode (native or integrated) was not selected.

If the database is not set, click on Change Database.

Click Exit.

Download and run the Reporting Services Add-In for SharePoint 2010:

http://www.microsoft.com/downloads/details.aspx?FamilyID=16bb10f9-3acc-4551-bacc-bdd266da1d45&displaylang=en

Click Finish.

Go to Central Admin, and look under General Application Settings:

Click on Reporting Services Integration.

Click Close.

SQL Server 2008 Reporting Services Integration with SharePoint 2010

2010-01-27T15:56:00.003-05:00

I have been working with a Virtual Machine running SharePoint 2010. It has SQL Server 2008 Enterprise Edition but it did not have SSRS installed. I therefore downloaded the 64-Bit version of SQL Server Enterprise to install the extras.

I went through the process and when I saw some options disabled I figured something was wrong:

The SharePoint options were disabled. I couldn't select Native or Integrated mode here. I realized that it was looking for MOSS 2007.

I therefore realized that SQL Server 2008 R2 probably has the SharePoint 2010 awareness. I immediately downloaded the SQL Server 2008 R2 Enterprise Evaluation and installed it on the VM. This time I was able to configure Reporting Services properly:

After downloading and running through the installation of SQL Server 2008 R2, I realized that the process for installing Reporting Services is essentially the same as before (with SQL Server 2008 making it a little easier than SQL Server 2005). This means that you still need to install the SharePoint Add-in as well.

So what you need are both parts:

SQL Server 2008 R2 November CTP

http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx
(I used the download from MSDN but it is publicly available using the above link)

SQL Server 2008 R2 RS Addin for SharePoint

http://www.microsoft.com/downloads/details.aspx?FamilyID=16bb10f9-3acc-4551-bacc-bdd266da1d45&displaylang=en

After you install the Add-In for Sharepoint, new options appear in Central Admin (just like in MOSS 2007):

Clicking on the Reporting Services Integration allows you to complete the integration process:

But it appears now can specify which site collections to deploy and activate the features. I had a few other site collections on the VM but under the /sites/ managed path. I am therefore thinking that it is really asking you to select the Root Site Collection (so if I had a another web application with a site collection, that would be shown in the selection box as well).

After successfully integrating, a Reporting Services option appears under the site collection site settings:

That's about as far as I have gotten so far. I did, however, wanted to check BIDS to see what Data Sources were available. I had a hunch that a SharePoint list would now be an integrated option (instead of having to create an extension as I have done before in SQL 2005). I was right:

I am pretty sure that wasn't in there before I installed R2.

So overall in SharePoint 2010 it's the same process to install Reporting Services in Integrated Mode but with the SQL Server 2008 R2 installs.

SSRS 2008 Pie Chart Formatting

2010-01-18T17:23:00.002-05:00

I was involved with a client where after building the warehouse to host the data, one of user deliverables was delivering a SSRS report that contained many different types of charts. One of the charts that had a wrinkle to it was a Pie Chart that broke down customer dollars by category and then comparing that customer to its industry to see the percentages of where the client's customer income was in relation to the rest of the industry that the customer belonged. For this report, the customer was a parameter and it would display the appropriate information based upon the customer selection by my client.

Building two Pie Charts and having them side by side on the SSRS Report to display these percentages and dollars was a great way to compare the results but it had a couple of formatting issues that I would like to share.

For the requirement, I needed both charts to have the same colors representing the same category in each chart. I could have hard coded the categories but then the report would not have been very flexible if new categories were introduced into the data down the line. To solve this problem, I had to display all of the category types (simple left join) in the legend for the industry and the customer charts. Originally, I only showed those categories that each belonged to but if a customer only was involved in a subset of what the total industry would be involved in, the color coding of the charts would not be in synch. The first requirement was now solved using the left join but now I had a new problem.

Adding the left join now meant that I was retrieving all categories that could have a percentage of 0 since not all customers were involved in every category that the customer's industry was mapped. When displaying that on the chart and trying to show percentages and dollar amounts, if there were multiple categories that a customer did not have, all of the 0 percents and dollars became one garbled mess of characters.

Luckily, in SSRS 2008, one can change the formatting of the Pie Chart's Label Series using Keywords and combining that with a numeric string format. The keywords can be found in the Adding Chart Keywords section in the Formatting Data Points on a Chart article on MSDN, the numeric strings can be found at the Custom Numeric Format String section on MSDN.

To go to the Label formats, right click on the Labels within the chart and select the Series Label Properties:

The properties will display and in the General option, implement the Chart Keywords and a custom numeric format string combination:

For the example, I implemented the following format: #PERCENT{#%;;""} #VALY{$#,###;;""}. Notice, after the keyword, the format is laid out and the Section Separator ";" splits the following types of values into "POSTIVE;NEGATIVE;0" and what format to apply to each type of numeric value.

For the 0 value, I put in an empty string and therefore the combining of empty categories did not display in the Pie Chart itself but the category would still show up in the Legend.

The Pie Chart also has the capability to group percentages together below a certain threshold (percents or dollars) to then allow for a custom title for that category. However, for the customer vs the industry scenario and the color coordination of the legends, this avenue would not apply since I was not guaranteed the same combination of categories for the industry would be setup for the customer, which was the point of the chart in the first place.

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

Include Column Headers in Query Result Export

2009-02-11T13:19:00.014-05:00

Good, bad, or otherwise, I spend a lot of time with my client executing ad-hoc SQL queries against our databases to retrieve data from our databases in order to assist with analysis. Many times this data is queried using SQL Server Management Studio (SSMS), copied\exported to Excel, then sent to appropriate team members for review.

The problem I've been having with this is that the column names (headers) aren't copied with the query results. This means I've been manually typing the column names in for my results. Not that big a deal when there is only a few columns included in my resultset, but when I need to report on 20-30 or so fields. What a waste of time!

I've tried sending the query results to "Text", as opposed to the "Grid", but that doesn't seem to make it much easier on me. After some poking around, I found the "Include column headers when copying or saving the results" option. It's located here:

Tools > Options

Query Results > SQL Server > Results to Grid

With this option selected, I can now copy (or directly save) data from my query results (in Grid) and have the column headers included saving me the effort of typing the column names in for each resultset I need to provide.

As you might have picked up on from the screenshots, I experience this in SSMS 2005 with basically the default settings. I don't have a SQL Server 2008 box readily available to see if this option is still unselected by default, but I sure hope it isn't. I know, going forward, this will be one of the first defaults to be switched on any SSMS installations I own.

Advanced Dimension Data Security with SQL Server 2008

2009-01-12T11:46:00.001-05:00

On a recent project, my client asked if we could secure members of a dimension by Active Directory groups. For example, in a dimension named “Region”, there might be a “Southeast” region. Members of the A/D group named Southeast should only be able to view the “Southeast” member. Below is an example of this, built using the AdventureWorks database. In this example, the Geography dimension is secured.

The Dimensional Model

The goal is that when a client application (like Excel) issues the below query, Analysis Services returns only specific geographies the user is authorized to view.


SELECT
[Measures].[Sales Amount] ON COLUMNS,
[Geography].[City].MEMBERS ON ROWS
FROM [Adventure Works DW2008]

To secure the Geography dimension, two additional tables are added to the model: FactGeographySecurity and DimUser. Here’s the DDL:


CREATE TABLE [dbo].[DimUser](
	[UserKey] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [varchar](50) NULL,
	CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED 
	(
		[UserKey] ASC
	)
)

CREATE TABLE [dbo].[FactGeographySecurity](
	[UserKey] [int] NOT NULL,
	[GeographyKey] [int] NOT NULL,
	CONSTRAINT [PK_FactDistrictSecurity] PRIMARY KEY CLUSTERED 
	(
		[UserKey] ASC,
		[GeographyKey] ASC
	)
)

Now insert some test data into the new tables. The below DML adds my user account, and then grants me access to cities in Great Britain and Canada.
INSERT DimUser (UserName) VALUES('blackd820\black')

INSERT FactGeographySecurity (UserKey, GeographyKey)
	SELECT 
		(SELECT UserKey FROM DimUser WHERE UserName = 'blackd820\black')
		,GeographyKey
	FROM DimGeography
	WHERE CountryRegionCode IN ('GB', 'CA')
In BIDS, add the DimGeography, DimReseller, and FactResellerSales tables to your Data Source View from the AdventureWorksDW database. Also add the two new tables, DimUser and FactGeographySecurity.

Create database/cube dimensions for Geography, Reseller, and User. Create Reseller Sales and Geography Security measure groups. Your cube structure should now look like the below:

Applying Security

In BIDS, create a new role. Mine is named “DimensionSecurity”. On the “Membership” Tab add your user account (I added my “black” account). On the “Dimension Data” Tab, open the “Advanced” sub-tab. Select the “Geography” dimension, and the “City” attribute. Then type the below MDX expression into the “Allowed member set” text box. You can also check “Enable Visual Totals” here if totals should be tallied based on the filtered set, not the entire set.
Exists([Geography].[City].MEMBERS,StrToMember("[User].[User Name].&[" + Username + "]"), "Geography Security")
The exists function returns a set that is based on filtering the first set by members of the second set. And because the “Geography Security” measure group is specified, the second set, [User].[User Name].&[black], must also have associated rows in the FactGeographySecurity fact table. Now it should make sense why we added FactGeographySecurity as a measure group, and DimUser as a dimension.
Note: this only secures the City attribute. For each attribute in the drop-down list, type in an allowed member set similar to the one above. Only the first parameter would change. Ex., for the Country-Region attribute, the first parameter becomes [Geography].[Country-Region].MEMBERS.

Testing in BIDS

In BIDS, go to the cube browser, and click the “Change User” icon . Select the “Roles” option, and choose “DimensionSecurity” from the drop-down list. Expand Geography and drag the City attribute into the rows area. Drag any measure from Reseller Sales into the totals area. You should see, as in the below, that only cities from Great Britain and Canada are displayed.

Testing via MDX

Run the below MDX script in SSMS. Notice how the “Allowed member set” MDX from above is used on the row axis, with the username hardcoded. This might be useful if you just want to verify that the dimension/fact data is setup correctly.

SELECT 
[Measures].[Sales Amount] ON COLUMNS, 
Exists([Geography].[City].MEMBERS,StrToMember("[User].[User Name].&[blackd820\black]"), "Geography Security") ON ROWS 
FROM [Adventure Works DW2008]

Testing in Excel 2007

When connecting via Excel, you might expect it to just work. After all, your account is in the “DimensionSecurity” role. But if you are a server administrator, you will still see all of the City members, not just ones for Great Britain and Canada. But similar to the way you explicitly chose “DimensionSecurity” in the BIDS cube browser, you can test other roles by listing them (comma-delimited if more than one) in the connection string:
Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Data Source=blackd820;Initial Catalog=SSAS Security;Roles=DimensionSecurity
To do this, (I’m using Excel 2007), go to the “Data” tab and select “Properties”. You’ll find the connection string on the “Definition” tab. Add the part highlighted in yellow above.
Note: If you’ve only set security on the “City” attribute, expand “More fields” and select “City” from the PivotTable Field List pane.

Populate the security table from Active Directory

You probably noticed that security is defined in the FactGeographySecurity table at the grain of the individual user, instead of the Active Directory role. This is because there is no IsUserInRole MDX function. So now we have to maintain data in the FactGeographySecurity table to keep it in sync with Active Directory. To do this, we need a way to query an A/D group and return a list of users.

A utility application scheduled to execute nightly could be written to accomplish this. The code snippet below retrieves a list of users in the specified A/D group; this list could be used to insert records into FactGeographySecurity.

        /// 
        /// Gets a list of members in the specified group.
        /// 

        public static List GetGroupMembers(string groupName)
        {
            List users = new List();

            string domain = "somedomain.com";
            string filter = string.Format("(&(ObjectClass={0})(sAMAccountName={1}))", "group", groupName); // principal);
            string[] properties = new string[] { "fullname" };
            DirectoryEntry adRoot = new DirectoryEntry("LDAP://" + domain, null, null, AuthenticationTypes.Secure);
            DirectorySearcher searcher = new DirectorySearcher(adRoot);
            searcher.SearchScope = SearchScope.Subtree;
            searcher.ReferralChasing = ReferralChasingOption.All;
            searcher.PropertiesToLoad.AddRange(properties);
            searcher.Filter = filter;
            SearchResult result = searcher.FindOne();

            if (result != null)
            {

                DirectoryEntry directoryEntry = result.GetDirectoryEntry();
                foreach (object dn in directoryEntry.Properties["member"])
                {
                    DirectoryEntry member = new DirectoryEntry("LDAP://" + dn);

                    //Add users from other groups within this group (only go 1 level deep).
                    if (!IsGroup(member))
                    {
                        users.Add(member);
                    }
                }
            }
            return users;

        }

        /// 
        /// Determine whether the object is a group.
        /// 

        private static bool IsGroup(DirectoryEntry de){
            return de.Properties["objectClass"][1].ToString() == "group";
        }

Finally

Using this approach, each dimension can be secured by creating only a single role. This satisfied my client, who did not want to manually create a role per geography, then go into the role definition and manually select dimensions available to that role.

Hope this helps!

SSIS Error from DTEXEC: Login timeout expired

2009-01-10T08:16:00.004-05:00

When executing an SSIS package that is stored in the MSDB database (i.e. stored in SQL Server) you may experience an error message like this:

Could not load package "\PACKAGENAME" because of error 0xC0014062.Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed.

In my situation the SQL Server database where the SSIS package is deployed is a named instance. There is no default SQL Server instance on the server. The command line that I used to execute the package was:

DTEXEC /SQL "\PACKAGENAME"

The problem is that the above command is trying to load the SSIS package from the MSDB database in the default instance of SQL Server and there isn't one. The solution is quite simple; add the /SERVER command line parameter with the server\instance of the SQL Server; e.g.

DTEXEC /SQL "\ADSECURITYSYNC" /SERVER "SERVERNANE\INSTANCENAME"

Dynamic Set for MDX Calculations

2009-01-09T14:42:00.002-05:00

PROBLEM

I have been running into a problem trying to get the MIN, MAX and MEDIAN MDX calculations to work against a dynamic set as opposed to setting a static set in these Calculated Members in my cube. However, there was a wrinkle to my problem, the dynamic set would go across attributes of the same dimension and this is where I was running into problems.

For simplicity, I have the following tables in my warehouse: LOAN, GEOGRAPHY, LOAN_FACT, TIME

In the LOAN, I have an ID and a LOADING SYS field. The LOADING SYS field describes what source system the Loan came from
In the Geography table, I have a Geography ID and Geography Name (very simplistic for this post)
In the LOAN_FACT, I have the Loan Id, Geography Id, Time Id and EOM BAL.

When I create my cube, the LOAN FACT table is the FACT table and the other tables are the Dimensions of the cube.

So far, so simple.

Now, I want to create calculated members within the cube that gives me the MIN, MAX, and MEDIAN EOM BAL across whatever is selected by the user with the understanding one principal, the LOADING SYS is a very important breakdown of the data.

SOLUTION TAKE 1

I started with the following calculation:

CREATE MEMBER CURRENTCUBE.[MEASURES].[EOM BAL MIN] AS

MIN([LOAN].[LOAN].children, [MEASURES].[EOM BAL])

Then a select statement like the following would be expected to give me my result:

SELECT [MEASURES].[EOM BAL MIN] on COLUMNS,

NON EMPTY(

[GEOGRAPHY].[GEOGRAPHY NAME].CHILDREN *
[LOAN].[LOADING SYS].CHILDREN
) on ROWS

FROM [LOAN CUBE]

However, this did not work as it went through and game me the lowest EOM BAL for every loan within a Geography regardless of the LOADING SYS and then repeated that value for each LOADING SYS even if the LOADING SYS did not exist for a GEOGRAPHY. A Really Bad Answer!

SOLUTION TAKE 2

Taking another stab at the calculation, I updated it to read:

CREATE MEMBER CURRENTCUBE.[MEASURES].[EOM BAL MIN] AS

MIN(DESCENDANTS([LOAN].[LOAN].CURRENTMEMBER,,LEAVES), [MEASURES].[EOM BAL])

This answer came close as it would give me the lowest loan value within a GEOGRAPHY and only present those LOADING SYS values that actually existed. However, it would still only give me the MIN EOM BAL within the GEOGRAPHY regardless of the LOADING SYS, so it still was incorrect.

CORRECT SOLUTION

After a little research, I found that I could setup my CURRENT LOADING SYS as a CALCUALTED MEMBER against the MEASURES Parent Hierarchy. Once I had that, I could setup my LOAN set to use this member. The following two CALCULATED MEMBERS were then created and viola, my answer that I wanted:

CREATE MEMBER CURRENTCUBE.[MEASURES].[CURRENT LOADING SYS] AS

[LOAN].[LOADING SYS].CURRENTMEMBER.NAME

CREATE MEMBER CURRENTCUBE.[MEASURES].[EOM BAL MIN] AS
MIN(
(DESCENDANTS([LOAN].[LOAN].CURRENTMEMBER,,LEAVES), IIF([MEASURES].[CURRENT LOADING SYS] = 'ALL', [MEASURES].[CURRENT LOADING SYS], STRTOMEMBER([MEASURES].[CURRENT LOADING SYS]))),
[MEASURES].[EOM BAL])

Now, if I run the following again:

SELECT [MEASURES].[EOM BAL MIN] on COLUMNS,

NON EMPTY(

[GEOGRAPHY].[GEOGRAPHY NAME].CHILDREN *
[LOAN].[LOADING SYS].CHILDREN
) on ROWS

FROM [LOAN CUBE]

What I return is the MIN EOM BAL within a GEOGRAPHY and LOADING SYS. If I take GEOGRAPHY out of the SELECT, then the query will return the MIN EOM BAL within a LOADING SYS. If I add another dimension to the query, maybe TIME.YEAR for instance, I get the proper results as well.

A couple of things to notice:

I had to check for the ALL member as the STRTOMEMBER function would not return a result so my Grand Total would error out without this check.
I still used the DESCENDANTS function to get to the lowest loan grain within the context of the query

CONCLUSION

One can take advantage of the CURRENTMEMBER of a dimension's query context to obtain a different attribute's value within that DIMENSION. This value can then be used in a later calculation for basically creating a DYNAMIC SET according to the query context and essentially grouping by that value for your calculation.

Using SSIS to store and retrieve SharePoint List Data

2009-01-08T14:56:00.002-05:00

Out of the box, SQL Server Integration Services provides a rich platform for building data integration and transformation solutions. The included SQL Server Business Intelligence Development Studio allows those who might not consider themselves 'coders' to create complex ETL packages that target the heart of most businesses - their data.

However, one thing seems missing from the out of the box SSIS experience; That is the ability to use SharePoint Lists as data flow sources and destinations.

Some might find this puzzling, given the great integration story found built into the Office suite of products - allowing Excel and Access to connect and retrieve data contained in SharePoint lists.

Fortunately, there's a great community that surrounds SharePoint and provides solutions for common scenarios such as this one.

The SharePoint List Source and Destination sample available on the Microsoft SQL Server Community Samples: Integration Services page on Codeplex provides a solution for SSIS Package creators to use SharePoint as a source as well as a destination in their SSIS data flows.

In use, the sample provides SharePoint Data Flow Source and Destination components which allow the user to the SharePoint site URL and list name to use. Once configured, these Data Flow components can be used in conjunction with any other SSIS Data Flow Components.

You can find these components, available for both SQL Server 2005 and SQL Server 2008, as well as sample instructions at the following locations:

SharePoint List Source and Destination sample
MSDN Article

SSAS Calculated Member's Currency Format Displayed in Excel 2007

2008-12-16T22:03:00.002-05:00

Problem

After creating a calculated member in your SSAS cube, you connect to the cube via Excel 2007 and drag the calculation into your Pivot table. However, although the Format is set to Currency in the cube , the calculation appears as an unformatted number in the Pivot table.

Solution

When creating the calculation in the Form View, there is an Format String drop down box that allows for selecting “Currency” as the format. However, the LANGUAGE property of the calculation needs to be set for this to carry over into the Excel pivot table example described above.

To set the LANGUAGE property of the calculation, click on the Script View to show all calculations in the cube. In the Script View, the MDX can be edited for any and all calculations and additional properties can be added. These additional properties are not available via the Form View but the properties are valid MDX properties of a calculated member. One such property is the LANGUAGE property.

The LANGUAGE property accepts the LANGUAGE CODE ID as the value to be set. For example, the value for US Currency is 1033.

The following is an example of a simple calculated member with the LANGUAGE property being set:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Avg Sales Amt]
AS IIF(ISEMPTY([Measures].[SALES Count]), NULL, IIF([Measures].[SALES Count] = 0, 0,
[Measures].[AMOUNT] / [Measures].[SALES Count])),
FORMAT_STRING = "Currency", LANGUAGE=1033,
NON_EMPTY_BEHAVIOR = { [AMOUNT] },
VISIBLE = 1 ;

For more information about the LANGUAGE property, navigate to the following KB article: http://support.microsoft.com/kb/950598

Maintenance Plan SSIS Package fails on the exported server

2008-12-16T21:26:00.003-05:00

by Sarma Musty, Senior Consultant

Problem Statement

Here we tried to solve the problem as mentioned the following link.

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/880fc71e-f214-4f87-85b3-592244764b49/

The developer created a Maintenance Plan using SQL Server 2005 Maintenance Plan Wizard. The tasks included a backup database task. The Maintenance Plan package (SSIS) was exported to a different Server. While trying to edit the Backup Database task, the system threw the following error.

Figure 1 - Error while editing the Back DB Task

Background

In SSIS, Backup Database Task is used to backing databases. The following diagram shows the screen for editing Backup Database Task. User can list the databases that need to be backed up and also provide information about locations, devices etc. The backup can be done on a Tape or Disk.

User can select “Backup databases across one or more files” and manually provide the list of backup files (Manual Option). Or he/she can select “Create a backup file for every database” and give a path for the backups (Auto Option). In this option system will generate backup files automatically. This property is saved in “DestinationCreationType” attribute of Backup Database Task properties.

Figure 2 - Edit Screen on Back DB Task

Figure 3 - Backup DB Task Properties

In the problem, the setting that the developer opted was manual and a backup file location was also provided. The system was not able to recognize this list. Possible reasons could be that system was not able to recognize the location or device that’s defined in the task. Nevertheless, the error message does not give any details about the actual reason.

Solution

As a work around, we changed the DestinationCreationType property from Manual to Auto. After this change the edit screen of the Back Datbase Task is working as expected.

New Technet Post on Installing PerformancePoint Server SP2 with SQL Server 2008

2008-12-12T08:20:00.005-05:00

I was updating a virtual server instance to use SQL Server 2008 with PerformancePoint, specifically with the new SP2. Come to find out unstalling the PerformancePoint 2007 SP2 components with SQL Server 2008 and getting a working configuration is not the most straightforward process. It involves the download and installation of a handful of hot fixes and utilities to get past - among other things -communication issues betwen the Planning components and SQL Server 2008. There's a recent TechNet article that provides the details that helped me get this working:

http://technet.microsoft.com/en-us/library/dd335965.aspx

Resolving update conflicts with Change Data Capture

2008-12-03T13:58:00.016-05:00

Another use of the new SQL Server 2008 feature Change Data Capture (CDC) is to resolve conflicts between multiple updates.

Consider a large online store where multiple business users update product information. Suppose product specialists use a big form to enter the data. Suppose also that one user can only update unit name or size. Another user can change product category. Let's see how we can use CDC to accurately track the changes.

Assume the following table holds the products:

In the Management Studio I opened table properties, selected Change Tracking, and enabled both "Change Tracking" and "Track Columns Updated". Note that a primary key is required for change tracking. This
command can accomplish the same from a script:
ALTER TABLE Products ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

When a user opens a Web form and intends to change the UnitName, application records the current version of the row that contains product data. We can use one of the new SQL 2008 functions to determine the current version of the database row:
DECLARE @prevVersion integer
SELECT @prevVersion = SYS_CHANGE_VERSION
FROM CHANGETABLE(VERSION Products, (ProductID), (1)) CT
The query selects the current version of a product with ID of 1 and saves it in variable @prevVersion.

The user makes desired changes and wants to save the data. To update the row in the database the application runs the following query:
UPDATE Products
SET Name='CDC Power', UnitName='book and CD', Category='Books'
FROM CHANGETABLE(VERSION Products, (ProductID), (1)) CT
JOIN Products P ON P.ProductID = CT.ProductID
WHERE CT.SYS_CHANGE_VERSION = @prevVersion

The WHERE clause condition ensures that the data in the database has not changed since the application last retrieved it.

Suppose that at the same time the first user is making the change, another user is changing category for the same product using the same query. The update executed by the first user increments the current version of the row, so the above query updates no rows for the second user. If the application did not check the version, the second update statement could override UnitName with obsolete data.

Now consider changing the application, so that the query for the second user updates the Category column only. It can execute the following query:
UPDATE Products
SET Category='Programming Guides'
FROM CHANGETABLE(CHANGES Products, 1) CT
JOIN Products P ON P.ProductID = CT.ProductID
WHERE P.ProductID = 1
AND CT.SYS_CHANGE_VERSION = @prevVersion
AND CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('Products'), 'Category', 'ColumnId'), CT.SYS_CHANGE_COLUMNS) = 0

The CHANGE_TRACKING_IS_COLUMN_IN_MASK function tells us whether the Category column has been updated since the last check. Since that is the only column the query updates, we can ignore changes to other columns. Now the query would succeed even if both users initially looked at the same version of the row. By checking which columns have actually changed, the conflicting updates will more likely succeed.

Hand off your change tracking or auditing requirements to SQL Server 2008

2008-11-12T16:39:00.014-05:00

If you are writing additional code to satisfy regulatory compliance or auditing requirements, consider new features of SQL Server 2008. Perhaps your custom applications must support compliance with HIPAA, OFCCP, or other regulations. You may be saving additional copies of your data in the database to satisfy those regulations.

Enter SQL Server 2008, assuming it's easy for you to upgrade to that latest release (and it probably is, if you are already on SQL Server 2005). Its new Change Data Capture (CDC) feature copies the data for the tables you specify. While designed to support ETL processes for efficient loading of data warehouses or marts, it can serve well in scenarios where much of the data changes must be captured and stored in separate tables. Here's a data flow diagram in data warehousing scenario:

Also, a Channel 9 webcast goes into technical details about setting up and using CDC, but this is what's in it for you:

If you use CDC you can forget about writing and maintaining additional code just for compliance purpose.
You stop worrying whether your additional database updates slow down the performance of core functions, as CDC tracks data asynchronously. (If you have doubts about performance check out this case study about Austrian sports broadcasters.)
You don't have to prove to anyone that your compliance code does what it's supposed to, as you can point to the well-defined and published features of your database that you simply turn on.
You can reliably access captured data in a relational format. Already there are tools that help you manage the data from CDC: http://www.codeplex.com/CDCHelper.

On the other hand, you may not need to save detailed history of all updates to your data. Perhaps you only need to know that data in certain columns changed or track the user making the changes. Then consider Change Tracking or Auditing features of SQL Server 2008.

SSIS Package Layout

2008-11-11T08:22:00.001-05:00

If you produce SSIS packages for a customer, even if that "customer" is the company you work for, then this tip is for you.

When you create a new SSIS package and start adding items to the design surface you can quickly create a mess if you're not careful. Even if you are careful it is still quite difficult to precisely place the items so that the connecting lines line up.

For example, I quickly created a package which performs some common tasks:

As you can see, the various items are positioned on the design surface in a haphazard way. You can spend time dragging the items around until you get a layout that looks better, or you can use the power of the "Layout" toolbar to assist you.

By default, the "Layout" toolbar is not displayed in BIDS. To display it, right-click on an empty part of the toolbar and click on Layout (or you can click on "Tools | Customize..." and click the check box next to Layout):

You should see the Layout toolbar displayed:

To fix the layout of my package, I first selected all of the items. You do this either by clicking on the design surface and dragging the selection box around all of the items or by shift-clicking on each of the items until they are all selected. Look closely at each of the items--one item will have white "grab handles" and the rest will have black grab handles. The one with the white handles acts as the anchor for the items in the Layout tool bar that act on multiple items. For example, if you click on the "Align Lefts" button, the left sides of each item will be set to the left position of the anchored item.

I used a combination of "Align Lefts", "Increase Vertical Spacing", and "Make Vertical Spacing Equal" to align my items in a vertical column:

It took my just a handful of clicks and about 20 seconds to perfectly align the items for a professional look.

If you have auto-sized any of your shapes, you are still in luck. Instead of "Align Lefts" you can choose "Align Centers" to keep the arrows straight.

I'm sure that you have your favorite way to lay out your items in your packages, such as a cascading look where the arrows go down, over, and then down again. While that isn't directly supported by the layout toolbar you can still make use of the toolbar to "Make Vertical Space Equal" after moving your items where you want them.

Leave a comment if you have additional tips for using the Layout toolbar with SSIS packages.

Farewell.

Capturing LINQ Output

2008-11-10T11:31:00.003-05:00

If you use LINQ to SQL in your projects, you may find yourself wondering just what actual SQL gets generated and executed against your database. If you're a DBA, you'd probably break out the SQL Profiler. But if you come from a development background, or are just not adept at performing SQL traces, then there is a code-only solution for you.

It turns out that the DataContext class exposes a property called Log, which allows you to specify a System.IO.TextWriter which will receive the SQL commands which are generated.

In a previous post I showed how to call a stored procedure from LINQ. In this post I'll show the SQL calls produced.

The first call in that project was to load all the managers in the database. I defined a manager as a person who had 1 or more "Employees" associated with him or her. As a reminder, the LINQ query looked like this:

   1: var managers = (from m in dc.Employees   2:                 where m.Employees.Count() > 0   3:                 select new EmployeeData   4:                 {   5:                     Name = string.Format("{0}, {1}",   6:                         m.Contact.LastName,   7:                         m.Contact.FirstName),   8:                     ID = m.EmployeeID   9:                 }).Distinct();

I changed the code to write the output to a temporary file by adding one line of code, like so (make sure you have the AutoFlush property set to true, or flush the Log property manually, otherwise you may not see any messages in the file):

   1: dc.Log = new System.IO.StreamWriter(@"C:\Temp\LinqOutput.txt", true, UTF8Encoding.UTF8) { AutoFlush = true };

Now when the code is run the log file in my C:\Temp folder has the following in it:

   1: SELECT DISTINCT [t2].[EmployeeID] AS [ID], [t2].[value], [t2].[LastName] AS [arg0], [t2].[FirstName] AS [arg1]   2: FROM (   3:     SELECT [t0].[EmployeeID], @p0 AS [value], [t1].[LastName], [t1].[FirstName]   4:     FROM [HumanResources].[Employee] AS [t0]   5:     INNER JOIN [Person].[Contact] AS [t1] ON [t1].[ContactID] = [t0].[ContactID]   6:     ) AS [t2]   7: WHERE ((   8:     SELECT COUNT(*)   9:     FROM [HumanResources].[Employee] AS [t3]  10:     WHERE [t3].[ManagerID] = [t2].[EmployeeID]  11:     )) > @p1  12: -- @p0: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [{0}, {1}]  13: -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]  14: -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

Here you can see exactly what gets sent to SQL Server. Let's take a look at how a stored procedure is called (which was the point of my last blog post). After modifying the code to set the Log property, here are the results:

   1: EXEC @RETURN_VALUE = [dbo].[uspGetManagerEmployees] @ManagerID = @p0   2: -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [3]   3: -- @RETURN_VALUE: Output Int (Size = 0; Prec = 0; Scale = 0) [Null]   4: -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

As you can see, LINQ "knows" how to call stored procedures with parameters and can return a return value as well.

If you don't want to write to a file, then anything that derives from a System.IO.TextWriter will do, including your own classes. One class you might find value in implementing is one that overrides the Write method and in turn calls System.Diagnostics.Debug.Write. If an instance of this class is assigned to the Log property of your DataContext class then you can see the SQL directly in the output window of Visual Studio (or other debug viewer programs).

Leave a comment if you have used this functionality in other creative ways!

VSTS Database Edition for SQL 2008 is Available

2008-10-28T20:16:00.001-04:00

Just announced at the PDC: the Visual Studio Team System Database Edition GDR Release Candidate is available. See this link for information, or skip right to the download page.

There are no new features since the September CTP release, just bug fixes and performance enhancements.

You must have Visual Studio 2008 SP1 installed, as well as SQL Server Compact Edition 3.5 SP1. If you have a previous version of the GDR installed, you must uninstall it before installing this version.

According to an earlier post, this version is a "go-live" release, meaning you can use it to develop code that you intend to go live with, though you'll need to wait for the RTM version before doing so. However, there is no mention on the latest blog post as to whether this is actually a "go-live" release, so do your own due diligence before assuming anything. I've pinged the blogger, GertD, about whether this is the case, and I'll update this post when I have an answer.

Passing .NET data types in Parameters to Stored Procedure

2008-10-23T13:48:00.004-04:00

Structured Query Language is very forgiving in its automatic type conversion, sometimes too much so. Recently I encountered a stored procedure, written by a co-worker, that passed a GUID cast as a string to a varchar(255) stored procedure parameter, which was then directly inserted into a table that expected a uniqueidentifier (SQL for GUID). While the team quickly agreed this sort of type coercion was something best avoided, they also asked me for a mapping guide for .NET data types to SQL Server data types. This blog post is the answer to that request.
Generally speaking, most .NET data types have a counterpart in the SQL world and vice versa, although there are some differences and caveats. For example, there is no Unsigned integer in SQL, and SQL DateTimes have different range limitations from their .NET counterparts. More on that in a little while.
Here is the list of .NET data types and their SQL counterparts. This list is adapted from SQL Server documentation and is presented in a .NET-centric way. It is not necessarily inclusive of all defined SQL data types; rather the intention is to provide a data type for each .NET data type.

Boolean: pass to a bit. Note that SQL Server stores up to 8 bits within a given table row into each byte of data on the disk. So storing 8 boolean flags has the same storage cost as a single byte field.
Byte[]: pass to a binary(n) if the size is always the same or a varbinary(n) if the size will vary and not exceed 8000 bytes. For Byte[] buffers > 8000 bytes, pass to a varbinary(max).
byte: pass to a TinyInt.
Byte[1]: pass to a binary(1) or varbinary(1).
char, char[1]: pass to a nchar(1) or nvarchar(1).
char[], string: pass to a nchar(n) if the size is always the same or a nvarchar(n) if the size will vary and not exceed 8000 bytes. For char[] strings > 8000 bytes long, pass to a nvarchar(max).
datetime: Pass to a DateTime or a SmallDateTime, depending on the precision needed or the time range to be stored. The DateTime stores Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds. The SmallDateTime stores Date and time data from January 1, 1900, to December 31, 2079, with an accuracy of one minute. Note that attempting to store the .NET value DateTime.MinValue in an SQL Server database will cause an out-or-range error for both SQL DateTime and SmallDateTime data types.
Decimal: can pass to several data types, depending on the need. Using this type presumes a high degree of a priori knowledge about the values to be stored. If this is not the case, consider using a more general-purpose single-to-real or double-to-float data type instead.
The decimal(p,s) data type allows precision and scale of a decimal number to be specified. The storage size varies depending on the precision and scale values chosen.
The money data type holds values from (–2^63/10000) (–922,337,203,685,477.5808) through (2^63/10000)–1 (922,337,203,685,477.5807), with accuracy to 1/10000 of a monetary unit. Storage size is 8 bytes.
The numeric(p,s) data type holds fixed-precision and scale-numeric data from –10^38+1 through 10^38–1. The p variable specifies precision and can vary between 1 and 38. The s variable specifies scale and can vary between 0 and p. Storage size is 19 bytes.
The smallmoney data type holds values from (–2^31/10000) (–214,748.3648) through (2^31/10000)–1 (214,748.3647), with accuracy to 1/10000 of a monetary unit. Storage size is 4 bytes.
Double: pass to a float (double-precision floating-point number).
guid: pass to a uniqueidentifier.
Int16: pass to a SmallInt.
Int32: pass to a Int.
Int64: pass to a Bigint.
Single: pass to a real (single-precision floating-point number).
Object: pass to an sql_variant.
Unsigned: there is no SQL Server support for Unsigned integers, except for Unsigned char, which can be stored as a tinyint. Larger Unsigned values may be stored in binary(n) fields.
For Unsigned Char or UInt8, use tinyint or binary(1).
For Unsigned Short or UInt16, use binary(2).
For Unsigned Int or UInt32, use binary(4).
For Unsigned Long or UInt64, use binary(8).

For more information, start in SQL Server help at:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/89b43ee9-b9ad-4281-a4bf-c7c8d116daa2.htm