A Picture is Worth a Thousand Words or in This Case a Picture of a Thousand Words

Recently I've been working on a roadmap for decision support and business intelligence tools within our business. While I had little trouble pulling this together for most scenarios and tools, when I came to look at visualisation tools I was a little stumped. Most of what I've done in the past has been GIS centric - mashing up data with various layers from a GIS tool to speed business decision making or expose things that might otherwise be obscured in a sea of data. I had a passing familiarity with some other visualisation tools such a Tableau but went hunting for others. One of the less obvious choices was actually introduced to me in passing during last year's Gartner Symposium. It's called Wordle (http://www.wordle.net/) and can be used to create word clouds, visually highlighting the words that appear more frequently in a given body of text. As a test I pointed it at my blog and got the word cloud below back (Click here to go to a bigger version of this on Wordle's site.).

Funky? Cool? Yes! (Well at least in my opinion). But, does this sort of technology have an application in the corporate word? Perhaps? I've been mulling over a few ideas:

• Feeding Wordle long documents that are given to me to read. If I see enough prominent words in the resultant word cloud that interest me I read it. If not I file it!
• As a means of mining semi-structured data. A colleague of mine talks about finding the gold amongst all of the documents we have scattered across local drives, file shares and document management systems. Perhaps this is a way of speeding the process of finding that gold. 
• As a type of dashboard to sit over internal social media feeds (Yammer, etc) or other internal collaboration tools - drill down to the source discussions only if you see key words that matter to you.

Drop me a line if you're already doing something with a tool like this, I'm keen to hear what's working for you.

So How Does This Blogging Thing Work Again?

OK, I’ll admit it. Things have been a little quite on my blog recently. Eighteen months is quite a time between posts. Surprisingly, my lack of writing is not due to a lack of time – although my second child did enter the world not long after my last blog entry. It’s more a reflection of a change in circumstances for me, a move from hands on designing and building business intelligence solutions to a role which involves strategizing, positioning, promoting concepts and attempting to envision future ways of working with information.

It’s amazing how not having specific, tangible and concrete problems to solve, issues to deal with or systems to build seems at first to rob you of anything worthy of sharing with the community. But now I’m hoping that some of the concepts I’m working with and the bigger issues that I’m chipping away at over time will carry interest for at least some of you. I doubt I’m the only person facing these challenges and a little sharing and exchange of opinions might be helpful.

Among other things, in the coming months I expect to be trying to establish and build traction around data governance, shape several data migration activities and find ways of better using metadata in our business. If all goes well I’ll also get the opportunity to kick around some data mining based R&D. I’ll endeavor to share what I can where I think it might be of interest. Hopefully there’ll be a bit of two way communication so please do drop me a line if you’ve tackled the problems I’m writing about or have a view on the concepts I’m discussing. I’m a big believer in leveraging multiple points of view where I can!

The first cab off the rank will be a blog entry that combines the concept of a single source of truth, the band Nirvana and my morning run. Keep an eye out for that post in the next day or so.

When Identity Matters

In a perfect world identity columns should have no real-world meaning and should never be exposed to the user. However, this is not always the case. I've seen systems which use identity columns for booking numbers (which are then exposed to customers) and other systems where records in dimension tables start to be commonly refered to by their surrogate key values, rather than by natural key or name.



As soon as such values start to matter then a potential problem looms if you need to introduce a row with a particular value for the identity column. You can't just go ahead and issue a simple T-SQL INSERT statement, doing so will result in an error.



In order to successfully perform this type of insert you'll need to turn IDENTITY_INSERT on for the table in question. Like so,



SET IDENTITY_INSERT myTable ON



GO



INSERT myTable(ID, Name, Level)

VALUES (2315, 'A new test value', 5)



GO



Once you've finished inserting rows be sure to toggle IDENTITY_INSERT off for the table.



SET IDENTITY_INSERT myTable OFF



GO



Incidently if you are using SSIS to insert data using a Data Flow then take a look at the Advanced options for the SQL Destintion. Ticking the Keep Identity option on this page will allow you to insert values into an identity column.

SSRS Page Sizes and Orientations

One of the things I found odd about Reporting Services when I first used it was the lack of an option to set the paper/page size to common paper sizes such as Letter, A4, A3, etc. This is common in some other reporting tools such as Crystal Reports.

In SSRS page sizes are set by PageSize property of the Report object. You can get to this property from the Report Properties option in the Report menu. From here just look on the Layout tab and you'll find what you need. The measurements will either be in cm or inches (depending upon the Regional Settings of your computer). The most common paper sizes you will need to print to are Letter and A4. The values for these two (in potrait orientation) are

Letter: 8.5in, Height 11in
A4: Width 21cm, Height 29.7cm

If you need to use Landscape orientation simply swap the width and height values.

You can also set the size of the four margins here as well.

In order to maximise the amount of report real estate you have to work with you should also set the Size property of the [Report] Body object to the following:

[Body] Width = [Report] Width - Left Margin - Right Margin
[Body] Height = [Report] Height - Top Margin - Bottom Margin

Accurately setting these values will allow you to make the most of the available space without getting print overruns on to the next page or finding every second page blank due to a page size which has been set slightly too wide.

Querying Data for Report Parameters

Often there's a need to dynamically get data to populate the list of options for a report parameter. There are a couple of ways to go about this. One I often see is to query a transaction table to get a distinct list of items to appear in a drop down parameter. Something along the lines of

SELECT DISTINCT city FROM orders
ORDER BY city

In my opinion this is a bad idea for two reasons:

1. It places unneccessary load on the database. Querying thousands, maybe even millions, of rows to return a handful of rows might not be the best idea if there is a reference table (perhaps just listing cities or even listing all customers - with city information as part of the customer's address). The situation is even worse if the column being queried from the transaction table isn't included in a suitable index.
2. Looking at a transaction table for this information will only return a resultset for those items which have been involved in transactions. If it's important for the report user to be able to query against any city then there's a problem!

So, whereever possible I advocate querying small reference tables to populate report parameters. Look at the data model as a whole to try and find suitable tables and ask questions of key super-users, DBAs, application developers if need be - you might be suprised to find there's a table you can use - it just may be that it was named in a way that didn't make its purpose obvious.

The Logical Operator Less Used

Most people who need to work with SQL Server databases will be familiar with the basic logical operators such as AND, OR, BETWEEN, IN. A good number might also be across the use of the EXISTS operator. Perhaps less known are three others ALL, ANY and SOME.

ALL will return true if all of a set of comparisons are true.
ANY will return true if any of a set of comparisons are true.
SOME will return true if some of a set of comparisons are true.

There are numerous areas where these operators can be used. One case is where there is the need to compare a value against a range of other values from . It can take away the need for a string of ANDs and ORs in WHERE clause.

The syntax is along the lines of:

IF @valueToCheck <> ALL (SELECT referenceValue FROM referenceTable)

-- do something

ELSE

-- do something else

In this case the first code branch will be executed only if the valueToCheck is less than all of the values in the reference table.

New Behaviour When Modifying SQL Server tables from Management Studio

Today I struck some behaviour I'd not seen before. I was attempting to change the nullability of a column from inside Management Studio when up popped a message with the following text:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that requie the table to be re-created.

Hmm, now I realise that this type of change needs the drop and re-create of the table, but I'm sure in earlier versions of SQL Server this type of change was possible from the designer with the extra work being taken care of silently in the background. Now, in 2008, it seems to be blocked entirely - ideally I'd have liked to be able to make the change in the designer and then generate the change SQL script from there rather than having to write the script myself from scratch.

So, I did a little digging looking for the option mentioned in the error mesage. I found it under the Tools->Options menu in the Designers section.

Simply uncheck the box and you'll be able to make these types of table changes and / or generate the change scripts from Management Studio.