Punchlines and Context

the-persistence-of-memory.jpgIt was 7 o’clock, a fine Sunday evening in the early Autumn with the sun now behind the horizon. I remember looking out of the window of the strange house that I found myself in. Well, not that strange really. I knew the house, it was directly opposite from my own house…. except I didn’t live there any more.  I digress, daylight gave in deceptively quickly to a clear bright starry night with no sign of a moon. There was however, something out there and each time I passed the window something in my consciousness jarred, prompted by events tantalisingly always on the edge of my vision.

Without knowing why I had an uncomfortable knot developing in my stomach. After a busy weekend at the various local cornish beaches there was a definite feeling of ‘end of term’ although it was in reality still mid school term. The girls were all at home and preparing themselves for school the next day, by preparing I mean generally anything other than what they needed to be doing; laughing, singing, playing guitar, making videos, offering endless cups of tea and such other acts of intended bribery. I felt that we were all in a place that was special, warm and fuzzy, a place called family. It was seconds later that the bottom of my world dropped away, spinning merrily into a nose dive with no sign of saving. I saw the lights tracking across the the sky and I knew it was no small thing. I knew that they had come for us all. Within what seemed like seconds and an aeon simultaneously an unfathomable amount of light burst out from the earth in the valley to the west of our village.

There was no panic, no screaming or shouting, and no tears. The figures appeared suddenly and without any drama, they were just like the lights, beyond the edge of vision, despite being in full vision to each and every one of us. I just remember that they were grey and very thin, I guess like all of our current conceptions. There was one figure, just one, for each house. The figures said nothing but we knew that we were all to leave the house and follow them, we did so as a family. It felt for all the world like we were making our way into the fields around our house for a picnic. The younger girls were skipping and singing, as they do, without a trace of fear; to them it seemed natural. Although the natural light had abandoned us the walk was well lit by the strange sh0ws that seemed to emanate from every valley in every direction, it felt like we were in broad daylight. I felt the fear in me as we marched to wherever we were being led. As we walked I grabbed my fiancée Zoe’s hand, It was this action that forced ‘our being’ who was just on the edge of belief to turn on me. I don’t remember what it said but the gist was clear to me as was the venom in the delivery; he  no longer existed merely as a splinter in my consciousness. Whatever it was, it regarded Zoe as a spoil of war and would not have me, a mere human, laying claim to her. Along with the gist I will always remember that the face was contorted into the classic Edvard Munch ‘scream’ shape though there was no evidence that it was angry. It was almost as if this face was its only face but that it had only now let me truly glimpse it, in a moment of intimacy between conqueror and condemned. At any rate, I don’t remember letting go of her hand….

 

Time passed imperceptibly, the next I knew we were all in a field, picnic blankets out and it seemed like the world and its dog was there, thousands of people; except in my village the only way you’d ever get more than 200 local people together would be with a mass resurrection of the graveyards to the  rear of my house. There was a definite festival vibe with a centralised point from which huge images and light shows were projected. They were devoid of content but fabulously showy, the colours were strong, clean, intensely vibrant and the lines of the holographic images were so sharp that they appeared to be more real than anything we had ever seen in our lives. But we were all aware that this was just filler, we were here for a reason. Mankind was assembled across the whole globe for this great reveal.

And then, as quickly as they had all arrived earlier their light-show ceased and an ominous silence and darkness enveloped the entire valley. The remnants of the now dying light show seemed to fizzle in our minds and fill the descending darkness until finally our brains caught up with the total black that consumed us all. The silence that enveloped us all was the kind of noise that can only follow a full scale assault of the ear drums and then just as the silence started to itself die away the music started and the holographs pierced the darkness with increased ferocity. It quickly became apparent that the main feature was itself an advert, an advert for a car. At first it seemed just like any other car but then with a deep and final feeling of dread we all knew what was coming and we all knew what it meant. The vivid green of the holographic car slowly morphed, losing its colour and it seemed that the entire fabric of the car became all together more….. organic. One thing was clear to me at this point, this was no advertising campaign nor was it an even thinly veiled threat; it was a clear statement of intent and was being delivered in much the same way that a Bond villain spills his carefully laid out plan to James moments before he is to die. It became clear that we were looking at our future, quite literally. But still no panic, I think we were all frozen with fear and the coolness of the night.

And then…. the swimming skies started to interweave themselves with reality, my now conscious Neanderthal control centres were fighting the sleep chemicals  and asking for full control of whatever remained of my mind after what had gone down earlier…..It was at this point that I remember waking from this, probably the most memorable dream, that I have ever had; scared beyond belief and breathless. That dream stayed with me constantly for days, not dying with the morning as dreams normally do and here I am nearly two years later still recounting the entire dream.

pic78.jpgSo why, what’s the point I hear you say? Well its a conversation all about context. I understand everything that I remember and I have a view of what I saw in this dream. None of it is so weird that its off the scale of my ability to process it but here’s the point. Without the context of my mind at that point in time the whole scene makes no sense at all. Without knowing the running state of the machine on which the dream was played out we cannot hope to understand the context of even our own thoughts and we certainly will never get the punchline (that is assuming that we ever get there as dreams just seem to dissipate with no warning or conclusion). We don’t even understand with any degree of certainty the purpose of dreams. My mum believes that they are a way of backing up the data of the day to ‘long term storage’ , a nightly back up if you like. I feel that there is a little more than that to it, I believe that we are archiving the data for sure but at the same time we are trying to form some world view of the events of the day, hence why we seem to make these large creative mental leaps during the night. I think that we are not only storing these memories but are building up an index of life events incorporating everything that seems to fit in with our general world view. I think that this explains why some heavily traumatised individuals can’t last the night without the dreams becoming so horrific that they waken in a state of severe anxiety, They just cant make sense of the reality, even in a totally abstracted manner. My dog? She thinks that dreams are where the sea goes to at night but she is far cleverer than I!

For probably the finest example of context and punchlines look no further than the Biscuit Story attributed to the great Douglas Adams

That there is one other person in the world who experienced the exact same events but missed the punchline as DNA saw it and instead has his own view of what happened at that train station is the clearest indication that context is everything. Without that clear context the joke is not just not funny, its not even a joke.

searching_stick_figure_800_clr_1813We have a parallel too  in the world of software, issues within software normally exhibit themselves as some weird unexpected output much like my dream. If you were to try and understand why these events occurred with out any context you would be forgiven for coming up with all sorts of weird and wonderful explanations. Bereft of context dreams could indeed be where the sea goes to at night!  Luckily, unlike my real dreams, by extensive use of logging we can give context to ‘these dreams’ and take the mystery out of the situation. With carefully designed logging we need never stumble in the dark for that elusive clue as to what a dream really means. If only real life was like that, then again…. maybe not.

Man of Distinct(ion)

yudIts not often that I learn something new about the fundamentals of SQL. Sure they add to the syntax all the time and you learn something  about what you can do with these addition but fundamentals, that’s basic stuff – the kind of stuff you rattle off a thousand times a day without even thinking about it . I’m talking about  stuff I should have known though it seems that I am not alone so I thought I would share this tidbit. This knowledge  is to do with the DISTINCT keyword of all things.

If I run the following piece of SQL I get a large no of results, covering all of the intakes available across all courses for all campuses:-

SELECT  C.[Description],O.Description,O.TotalIntake FROM Campus AS C  INNER JOIN Intake AS I
    INNER JOIN Courses AS O ON I.IntakeID = O.Id
    ON C.CampusID=I.CampusID

The results look a little like this:-

Capture

But what if I want to return a count of all of the course types at a campus and the number of seats available over the whole intake.The obvious thing is group by campus and then count the number of Courses like this

SELECT C.[Description],COUNT(O.Description),SUM(O.TotalIntake) FROM Campus AS C  INNER JOIN Intake AS I
    INNER JOIN Courses AS O ON I.IntakeID = O.Id
    ON C.CampusID=I.CampusID
GROUP BY C.[Description]

CaptureWait a minute, that’s not right though is it. I don’t have  84 different course types at my Shrewsbury Campus. Turns out that  getting a straight count of the course types is not quite as simple as that , that is unless you know what I now know…

That DISTINCT may be used within a COUNT function.

Its obvious, sure but I really had no idea that this was supported by SQL. So I refactor the SQL to look like this

SELECT C.[Description],COUNT(DISTINCT O.Description),SUM(O.TotalIntake) FROM Campus AS C  INNER JOIN Intake AS I
    INNER JOIN Courses AS O ON I.IntakeID = O.Id
    ON C.CampusID=I.CampusID
GROUP BY C.[Description]

 

CaptureAnd there it is, We have distinct course types at each Campus. I guess I’ve probably really over engineered solutions  in the past because I never knew about this little nugget of amazingness. Go forth and be awesome.

Lookup Date Values in DTS package

calendarI recently came to extend some DTS packages that we have implemented for a client to include a LOT of new Date styled dimensions. You know the kind of thing… We have a date table with all of the possible dates over the last 400 years which we all figure should suffice. If I’m still coding beyond this date I’m not entirely sure that DTS will be the framework of choice, Hell its already VERY superceded even as we speak! What I did not appreciate was that date look ups would be problematical in DTS.

To state the problem we have the ‘DimCalendarDates’ Dimension table defined thus:-

  • Sid (int) – Surrogate ID
  • CalendarDate (date) – The date that this record pertains to.
  • FirstDayInMonth (date) – The first date within this month.
  • Year (int) – The year that this record appears within

For the purposes of this Blog we are only really interested in the CalendarDate which is the field that we join to and the SID field which is the surrogate ID field that we populate the Facts Table with instead of a date. Our example fact table ‘DataFacts’ is defined thus:-

  • SID (int)- Surrogate ID Field
  • DimStartDateFK (int) – Links to the DimCalendarDates table on the SID field
  • DimEndDateFK (int) – Links to the DimCalendarDates table on the SID field
  • DimArchiveDateFK (int) – Links to the DimCalendarDates table on the SID field

I tend to populate my tables in these easy to follow steps:-

  1. Load data from source tables into staging table.
  2. Populate dimension tables with new dimension data found in staging table.
  3. Load staging data into dataset.
  4. Using a Lookup DTS component look up the SIDs for each dimension field required and persist it in the dataset
  5. Update/Insert the facts table data.

OK, job 1 in this instance we are not bothered about as this is irrelevant to the blog, in addition as we are using a predefined and pre-populated calendar table we also need not worry about step 2. Step 3 is where we load our data so in the instance above we would use a Get Component as shown:-

Capture

And issue the following SQL statement within it:-

SELECT SID, StartDate, EndDate, ArchiveDate 
FROM DataStaging

Simple, Now we perform the lookup We configure the component thus:-

Capture

Capture

And then we merge this data into the dataset taking all of the data from the incoming Staged data and appending the lookup value as the image below shows:-

Capture

Our finished transformation looks a little like this:-

Capture

Brilliant, except for one small thing. It doesn’t work… At least not correctly Quite a few BUT crucially not all of the Look up matches will fail to find a corresponding date even though the value is in the underlying DimCalendarDates table. The problem is to do with Siberian winds I think, that is to say… I don’t know what the issue is and I care even less. Basically it seems that DTS lookups work best against textual data and so the answer is indeed simple, treat the date fields as string. But no-one wants to store dates fields as text right? So here’s the two fold approach I took:-

  1. Add a new formula field to the DimCalendarDates table to express the data as a string value
  2. Change the look up expression to convert the look up value to a string representation of the data
  3. In both instances I needed to take care of nulls as well (i have a NULL date within my DimCalendarDates table)

OK, So first off we create the formula to express the date field as a string value. I thus applied the following update to the DimCalendarTable:-

ALTER TABLE DimCalendarDates ADD
 [CalendarDateAsString]  AS (CONVERT([nvarchar],[CalendarDate],(101))) PERSISTED

Nice and simple and for performance reasons I also persist the string field so that it is worked out only on updates not on ALL reads.

The conversion of the look ups are a little more involved:-

Capture

Capture

Capture

In essence what we have done is changed the query to compare the CalendarDateAsString formula expression to a textual representation of the date that we are looking up (represented in this instance by a question mark). Note that the Inner SQL query on the advanced tab must match exactly the source as defined on the Connection tab. Now the look ups should behave exactly as one would have expected.

You live and learn, well…. you live anyway.

Trust Issues

TrustEarlier this week I came across an issue with regards to one of our sites that integrates with SAP Business objects. We utilise single sign on in order to authenticate against the BO Server but I came up against the issue whereby authentication just would not work. A co worker then suggested using Internet Explorer as we are aware of its tight integration to the OS,and to nobodies surprise this worked. Being a developer i was not satisfied with ‘Just use IE’. In fact the day that I say this strike me down with an Iron Girder! Chrome, Maybe… Never IE.  I then decided to take a look into why the other browsers did not work and more importantly how to make them behave correctly. Firstly what you need to understand that you are attempting to trust the machine on which the IIS that exposes your website is installed. In my instance I am trying to trust an IIS installation on my localhost.

Internet Explorer

It just works in my experience.

Firefox

Firefox is probably the most complex to get right, but not that complex at all.

  1. Enter the URL about:config into your browser window. You will be presented with the following:-

    beCareful

  2. Elect to be careful!
  3. You will then be presented with a ton of settings! You need to change the following ones ONLY (You can seperate multiple values with a comma)
    • network.automatic-ntlm-auth.trusted-uris :-  localhost
    • network.automatic-ntlm-auth.allow-proxies :- true
    • network.negotiate-auth.trusted-uris :- localhost
    • network.negotiate-auth.allow-proxies :- true
  4. That’s it.

Opera + Chrome

Actually requires very little setting up at all. In fact it uses the standard Internet Options Dialog which is nice to know:-

  1. Load the ‘Internet Options’ system dialog
  2. On the ‘Security’ tab select ‘Trusted Sites’
  3. Press the ‘Sites’ button and add your site to the list

    trusted

  4. The elect to use ‘Custom Level’ security settings using the button on the same screen
  5. Right at the bottom of the list select ‘Automatic Log on with current username and password’

    logon

  6. Dismiss the dialogs using OK and your changes should be applied immediately. I would restart your browser just to be sure

Edge

Forget it. It just plain doesn’t support it at the time of writing.