Data Warehouse Sizing – Getting the Length of All Your Source Character Columns

Commissioning an architecture to host a data warehouse can be tricky, and computing the storage requirements is probably my least favourite task.  A full guide is probably best saved for another post, but when you have character (char, varchar, etc.) columns, you want to find the average length of the data stored in them in your source.  There’s a simple piece of SQL for this:

select avg(len([ColumnName])) from [TableName]

Easy to execute a few time, but if you have to run it against a few hundred columns it’ll get painful to do manually.  Here’s a script to do it all for you and return a nice table of results.  It relies on creating a table which has the table and column names in it, as below.

(Hint – your source to target map spreadsheet has this already in it, doesn’t it?)

With this in place, just run the following script:

declare @table varchar(20), @column varchar(50)

declare @results table (TableName varchar(20), ColumnName varchar(50), ColumnLength int)

declare @sql varchar(1000)

declare c1 cursor read_only

for select TableName, ColumnName from CharacterColumns

open c1

fetch next from c1 into @table, @column

while @@fetch_status = 0

begin

set @sql = 

    'select ''' + @table + ''', ''' + @column + ''',  avg(len(' + @column + ')) from ' + @table

fetch next from c1 into @table, @column

insert into @results exec (@sql)

end

close c1

deallocate c1

select * from @results

Go and make a cup of tea, or lunch, or whatever it takes depending on the number of columns you are working with and the number of rows in each table.  When you get back, you’ve got a result set ready to be pasted into your sizing document.

Sparklines in Excel 2010

Continuing the content delivered at March’s UG…

According to Wikipedia, a sparkline is “a type of information graphic characterised by its small size and data density.”  More simply put, it is a very small line chart, generally small enough to be included inline with text.

This sounds too small to be of any use, but their inventor Edward Tufte and data visualisation theorists such as Stephen Few make the point that charts should really be used to give an overview of a data series which can easily be consumed  – they are not usually good for delivering really accurate understanding of numbers, but for a quick visual comparison of data.  Sparklines can therefore be used in tandem with a table of figures to combine both detail and overview in a single place.

There’s an excellent addin available for earlier versions of Excel which actually creates better and more flexible sparklines in my opinion, available direct from the developer.  But in 2010 there is native functionality for them.

Sparklines are added very simply by selecting one of the types from the Sparklines section of the Insert ribbon.  You then choose the cell range on which the sparkline is to be based and cell it will occupy – a sparkline fills just one cell.

Once added, the sparkline gets its own ribbon with additional formatting options.  These are mostly cosmetic but there are a couple of options you may find enhance them a little. such as adding various point types.

Because a sparkline is contained within a cell, changing the horizontal or vertical size of the cell will stretch or compress the sparkline accordingly.  You can also place them in a range of merged cells if you find you need a little extra space for them.

Of course, Sparklines aren’t part of the pivot table itself, so if a user drills into the pivot table or changes its setup in any way, the sparklines are likely to lose their validity somewhat.  They will work well for less interactive reports, though.

So, after conditional formatting, slicers and sparklines, what sort of dashboard have we come up with?  Something like the image below.

We could add as many more elements to this as we wished, of course – pivot tables and pivot charts, KPI lists, dials and charts (if you really have to!).  All this functionality will work fine when published to Excel Web Services in SharePoint 2010.