Categories
Personal

New to the UK

A couple of days ago I got off the bus slightly behind a South African couple. A short distance away, near the entrace to Sainsbury’s, stood an elderly eastern European lady selling the Big Issue. I realised the South African girl must have only just arrived in the UK at the point that she decided the Big Issue was a free handout and attempted to liberate one without breaking her step.  Her boyfriend was in fits of laughter and declared his intent to tell all her friends that she tried to steal off a Big Issue seller.

I was amused, but the old lady less so as she never did make the sale.

Categories
Uncategorized

Desired Improvements in Business Intelligence Developer Studio

I’m going to focus on some aspects Calculations in Analysis Services cubes here, and perhaps follow up with more areas later.

1) I’ve already mentioned the strange location for setting the Display Folder property of Calculations in another post, but it deserves inclusion here. Additionally, why is it not possible to set other properties such as Description? Why can we not use the standard Properties window in the shell for all these? Why are Format String, Visible and Non Empty Behavior (sic) on the made expression editing page. (Actually it’s because the Display Folder is in the <Calculation Properties> element and the latter form part of the MdxScript, but this is not a good reason for the UI to split them up.)

2) Intellisense in the Expression box, and real time evaluation of member names. The real time Expression validation in the calculations only deals with basic MDX syntax. I would like a background thread to check member names as well. I accept that it would be difficult to differentiate between the intent to reference a data driven member versus something like a measure name that is baked into the schema. However, it would be useful to at least have mis-spelled measure names underlined. Errors in these are only caught when the calculation is first queried.

3) The Metadata pane in the Calculations tab does not show calculations that have a Visible property of false. This is pretty irritating when you try to drag and drop invisible calculations into the Expression box. It encourages error prone re-typing.

4) The "Calculation Properties" window is too narrow on opening. With reasonable sized calculation names with common prefixes it’s impossible to tell them apart due to the short column width.

5) Comments in calculation expressions cannot be placed at the top of the calculation. If you switch to script view and back to form view they will no longer be visible (but they are still available in the script). They work if placed at the bottom.

 

Categories
Uncategorized

Principle Engineer [sic]

Lately I stumbled upon an email signature in which the author had entitled their job "Principle Engineer". As it has never been explicitly clarified, I suspect this is a homonym of the intended adjective. That is, unless his job was to maintain the moral upstanding of the company.

Of course, a "Principal Engineer" ought to be the first in order of importance [Cambridge Dictionaries Online], which is also not true as there are, possibly, hundreds of employees in his company with this designation.

Categories
SQL Server

Excel Connection Manager and Excel Source in Windows x64

There is no Microsoft.Jet.OLEDB.4.0 provider for 64 bit Windows editions. If you need to use an Excel Source task or Excel Connection Manager in Integration Services on a 64 bit machine (I’m using Vista Ultimate x64), you will need to set your packages, or your project, not to use the 64 bit runtime. This is done by setting the Run64BitRuntime property to false on the project properties’ Debugging tab.

This article on MSDN provides more information about Integration Services running 64 bit editions of SQL Server and Windows.

Also note that if your source file is Excle 2007, the Excel Connection Manager cannot be used and the OLE DB Connection Manager must be used instead. See the note in the linked MSDN article about the Excel Connection Manager.

Technorati tags:

Categories
SQL Server

SSIS Lookup Transformation for SQL Server datetime type and DT_DATE

How do you perform a Lookup transformation using an input column of type DT_DATE (e.g. from a spreadsheet input) and a SQL Server table column that has the datetime type? Dragging the former onto the latter results in the error message "One or more columns do not have supported data types, or their data types do not match".

The answer is to create a Data Conversion transformation earlier in the flow. In this task, convert your input column to the type "database timestamp [DT_DBTIMESTAMP]". Make a note of the output alias you have entered. Now go back to the Lookup transformation and drag the new column (i.e. <output alias> of the Data Conversion transformation) onto the datetime column in the lookup table.

 

Categories
SQL Server

Bugs/Omissions in Visual Studio Team Edition for Database Professionals

 

I’m using Visual Studio Team Edition for Database Professionals and have spotted a couple of niggles with it.

1) If you rename a foreign key field, the schema compare fails to recognize that it needs to update the foreign key constraint. Instead it creates a new one and leaves the old one in place and unchanged, causing an error.

2) Extended properties are behaving strangely. It seems to be dumping them all into a file called “ScriptsIgnoredOnImport.sql”, appending it on to any entry it left in that file last time it ran. As far as I can tell you need to manually copy the extended property creation lines out of this file and into the individual table SQL scripts in the project. Irritating.

 

Categories
SQL Server

Calculated Measures DisplayFolder

In Business Intelligence Studio (AKA Visual Studio 2005), when editing a cube, there is a tab for creating calculations. This tab contains a handy ‘script organizer’ pane showing a list of the names of all your calculations, names sets, etc. Unfortunately if you select an item within the script organizer, the standard properties window does not show "Display Folder" and "Related Measure Group" properties. To get to these properties, there is a "calculation properties" icon near the top of the calculations tab.

 

Categories
SQL Server

SCOPE_IDENTITY()

A technical post for a change. We recently noticed a difference in SQL Server 2005 over 2000 around the use of the SCOPE_IDENTITY function. I’ve been used to "SELECT SCOPE_IDENTITY()" returning INT as its data type, as that was the largest data type that was allowed as an IDENTITY column. However, it now seems to be returning DECIMAL.

A quick investigation shows that DECIMAL can indeed be used as an IDENTITY column. There is a restriction that the scale must be zero, i.e. DECIMAL(n,0), so that the field only contains whole numbers.

Exciting? Maybe not. Interesting? Evidently enough for me to blog it.

Technorati tags: ,

Categories
Uncategorized

Caution! Hot Taps!

The office I’m working in has a notice above each sink:

"Caution! Beware of hot taps. The hot taps have been pasteurised and all water outlets will be very hot."

Surely it is the water that has been pasteurised, not the taps?

Categories
Personal

Polish Bathroom Fitter

We recently had our bathroom stripped bare and refitted. Pink wallpaper became white tiles, the acrylic bath with a hole became a whole enamel bath. That sort of thing.

The company we had in to do the fitting presented us with two faces: Ray, the South African boss (or mere line manager, but I suspect they’re not that huge an operation) and Adam (or ‘Adamski’ as Ray said at one point), a Polish all-rounder in the game of bathroom fitting.

Things were progressing really well, and a nine day job was scheduled to be finished in six and a half. Adam had been very hard working, an amiable and cheery chap, but a little difficult to communicate with. His English was languishing somewhere south of Peter and Jane books. Ray provided telephone and in-person support for the more difficult exchanges. 

On the morning of completion and pursuant to the advice of my father, I started the process of snagging in preparation for winding up the operation. One thing I wouldn’t have thought to do without the paternal advice was to check that the bath overflow worked. I filled the bath and kept the taps running. The overflow was working brilliantly; the water was hardly rising past it. With the test concluded, I headed down to the kitchen for breakfast.

As you will have predicted, that’s when I saw the drips from the ceiling. These were followed shortly afterwards by streams of water coming out from the window frames and pooling inside and outside the house.

There was a knock at the door: Adamski had arrived. I rushed to let him in and raced to think of how I could communicate the issue at hand. I hit on one of the few words he knew, "Problem!", and led him through to the kitchen. On seeing the water works he used one of the other words he knew, "Shit!", and raced upstairs to sort it out.

It seems that the overflow pipe had been loose and had simply detached itself from the bath. The water dribbled out the overflow, down the underside of the bath and into the floor, but it wasn’t a huge amount. The real problem was when I emptied the bath. The overflow pipe had flopped to the ground and was lying lower than the normal waste pipe did. The entire bath full of water had taken the easiest route out of the system and flowed out of the detached overflow pipe and onto the kitchen ceiling.

We were lucky that the flow took it into the wall, and it appears (several days after the event) that we’ve suffered no damage from the incident.

So now we have a new bathroom and an embarassed Pole from whom, to my thinking, we might be able to call in a favour in the future.