Back to Normal

Goodbye part-time hours, it was nice knowing you

Well, I enjoyed my two weeks of working half-days and going for long walks, but it’s back to regular hours. Interestingly, I was probably more than 50% productive with only 50% of the hours. It’s easier to focus for a shorter day, I think. I’m sure there is some optimal point in that curve.

So, this week’s work was mostly a continuation of all the things I had started on during the previous fortnight. I made some more progress on our Finance data project - moving one of their functions from a big ol’ spreadsheet to tables in our SQL data warehouse - and I made a lot of progress in getting more of our databases into source control. I now have a parent project in TeamCity which can have almost all of the settings and parameters, then it’s simple to create a child project which inherits most values, changing only the name of the database and the Bitbucket repository path. Then a simple empty Bitbucket repo which I fill with the SQL Change Automation objects, and an Octopus project cloned from existing ones. It all works fairly smoothly.

A few other small things - adding some information to our staff data to try to determine whether someone is a “work from home” employee or “part at home, part in office” or “fully in office”. The HR department is interested in those sorts of demographic splits. We were also having some trouble with a very slow dashboard that filtered on survey comment tags - it was a little painful since it used a view that union’d about 12 remote table queries, all involving string concatenation with FOR XML PATH and DISTINCT, and then the filter would like LIKE ‘%tag%’. I helped the analyst see how the new Tableau noodle relationships can help with data sources of different grains, and using direct connections to the secondary servers in Tableau can help with the remote queries.

This is the sort of query we had before (this code hasn’t been checked and is going off my memory):

CREATE VIEW Survey.dbo.CommentsWithTags
AS
SELECT	CommentText,
		(
		SELECT	T.Tag + ','
		FROM	(
				SELECT DISTINCT	Tag
				FROM			[RemoteServer].Survey.dbo.Tags
				WHERE			SurveyID = R.SurveyID
				) AS T
		FOR XML_PATH('')
		) AS Tags
FROM	Survey.dbo.EngageResponse AS R

	UNION ALL
	
SELECT	CommentText,
		(
		SELECT	T.Tag + ','
		FROM	(
				SELECT DISTINCT	Tag
				FROM			[RemoteServer].Survey.dbo.Tags
				WHERE			SurveyID = R.SurveyID
				) AS T
		FOR XML_PATH('')
		) AS Tags
FROM	Survey.dbo.GrowResponse AS R

and then Tableau would query it like so:

SELECT	*
FROM	Survey.dbo.CommentsWithTags
WHERE	Tags LIKE '%remuneration%'

You’ll agree it’s not ideal. Now Tableau directly connects to the Survey.dbo.EngageResponse table and the Survey.dbo.Tags table in [RemoteServer]. Much faster.

I’d like to try something new for my own professional development. I use TeamCity and Octopus for our change automation projects, but there is a lot about them I don’t understand. I’d like to try getting some virtual machines with SQL servers up and running as faux test and development servers, then get TeamCity and Octopus installed and working from scratch so I can better understand the process. Let’s see if I can make the time for it.