Fun

Community Speaking Analysis with Power BI

Just a short post.

During the SQL Saturday Cambridge speaker meal on Saturday night I was chatting with various people at the dinner table. During this time I got asked the question by an event sponsor “do you speak at many of these things?”. While the general answer I gave at the time was yes. It occurred to me that I didn’t have any accurate means of answering the question with some richer detail. Being a data/business intelligence person this required some serious self reprimanding. I could of referred to my MVP activity log, but that isn’t easily accessible and contains a list of all community involvement. I therefore decided to create a dataset just for speaking within Excel and visualise it with Power BI. Below is what I created.

Nothing to fancy here, just a simple dataset containing 6 fields:

  • Date
  • Event
  • City
  • Postcode (for the map)
  • Attended
  • Subject

What do you think? Click on it to enlarge.

Generally I spend my time architecting data solutions and developing the services to deliver business intelligence to customers. I confess, I don’t spend much time visualising that data and making things look pretty in tools like Power BI, so go easy with the comments.

I of course then published this to PowerBI.com meaning when I get asked the question next time I can simply load the Power BI app on my phone and answer most questions. Being a good developer (I think) I also created a mobile friendly view of the visuals.

If you’d like access to the published version of the dashboard please use this link to request access: https://app.powerbi.com/groups/me/dashboards/91878616-07f4-4f8b-be54-b4df46a2aab7

Many thanks for reading


My Advice for Speaking in the Community

I wanted to do something a little different with this blog post. I’ve certainly not ran out of technical content to blog about. But I wanted to write something to help all those new speakers out there that are about to stand up at the front of an event and share there knowledge, maybe for the first time, or maybe a veteran speaker that wants to see if they agree with my advice. The former is basically where I found myself about 18 months ago now when I did my very first user group talk as a follow up to an IoT solution I demonstrated on the Purple Frog sponsor stand at SQL Saturday Manchester 2016. Thank you Peter Moore for pushing me into speaking 🙂

Since my first talk I’ve now spoken at 40+ events and conferences across the UK, including schools as a mentor (STEM Ambassador). So, in this post I want to share my experience and advice to support you in doing the same. Firstly I would say that I certainly don’t consider myself to be a veteran speaker, but I think I’m a quick learner and my speaker experience now includes several user group MeetUp’s, SQL Saturday’s, SQL Relay and the big one, SQL Bits.

Here’s my top tips, thoughts and advice for speaking in the community. In no particular order.

Take Your Time with the Slides & Tell a Story

At a recent conference a speaker giving general presentation advice asked the question: Is creating a slide deck an art form or a science? Initially I raised my hand to answer as an art form. What proceeded in the conclusion of the talk is that its a bit of both. With the majority of art vs science being dependant on your presentation style. I guess my point for this post is that there isn’t a right or wrong style of presentation, but do take your time when authoring your slides to figure out what works for you. When you’ve figured that out, create a slide deck that flows and tells a story of the topic your speaking about in your style.

Once upon a time there was this developer and his data lake… 🙂

Plenty of Context & Examples

What I mean here is draw on some real world experiences without mentioning customers by name. If this isn’t possible, come up with a ready good hypothetical example that lubricates the understanding for the audience. Often you need to cater for members of the audience that have no idea about the subject you are speaking about so relating the technical content to situations in other areas that they are familiar with will help them massively. Its very easy to fall into the trap of assuming the audience is familiar with the initial steps in a scenario. Assume nothing about the people in front of you, other than that they have taken the time to come and hear you speak. Therefore they are expecting you to make that time they invested worth while and for you to articulate points well.

Max of 50% Slides vs 50% Demos

PowerPoint is boring! Sorry, but it just is. Death by PowerPoint is a real thing. I know someone that died from it! Or they may have just been sleeping. 🙂

Even the most artistic of speaker isn’t going to make PowerPoint slides interesting for a technical audience. As a very maximum please do not have a talk that is more than 50% PowerPoint. Practical demonstrations are far more valuable. Also any slides that you do have should be visual aids to your talking. Do not have slides of bullet points that you read out. I cannot stress that point enough. I will walk out of your talk if you do that and I wouldn’t blame anybody else for doing the same.

AV Connections

This may seem like common sense but all to often I’ve seen speakers show up just expecting the projector or AV kit to magically connect to their laptop. Again, for the second time in this post, assume nothing. VGA is no longer the standard means for connecting to a display. Be prepared with a comprehensive set of adaptors that converts from your laptop to at least VGA, DVI and HDMI. My laptop has a mini display port output so below is what I use. Buy something similar, it won’t break the bank. I also carry my own HDMI cable and keep a 4 way power extension lead in the car, just in case.

Rehearse, Rehearse, Rehearse

Making time in a busy schedule is hard, but trust me, any rehearsal time is a good investment and will set you apart from other speakers. Do it in the car on the way to work going over your slide deck in your head and saying your key points out loud. Draft in your partner to hear it… This is harder that it sounds, firstly because they may find technology boring and secondly, you will never receive criticism as harsh as theirs if something goes wrong. However, this does give you thicker skin for an actual audience and won’t kill you 🙂

Seek Out Feedback

Bigger conferences like SQL Bits collect and provide you with structured feedback reports of your rankings which is great. Don’t view this as critisim. This is the most valuable thing you’ll get to help improve your talk and prepare you for what the next audience is going to ask/expect. If at a smaller event give people your email address or encourage them to reach out on Twitter. Also make sure you ask friends or the organisers what they thought and don’t be disheartened my less positive comments.

Nervousness is Normal

The heading here really says it all. Being nervous is normal, it keeps you sharp and focused. If you aren’t nervous just before you start any talk either something is wrong or you just don’t care. If the latter is true then in my opinion you shouldn’t be on stage in the first place. Enough said here.

Praying to the Demo Gods

As the standard joke goes, pray to the demo gods. Light some candles and sacrifice a modem if you have to! Adding demonstrations to any talk does add an extra layer of pressure but the reward for successful code execution is great and worth it. My thoughts here are simple and quoting ‘Del’ from Only Fools and Horses. “He who dares wins”. I like the pressure and am happy to make a joke of any failings along the way. The key is not to let it cast a shadow over the delivery of any remaining content. Plus, it can’t hurt to manage audiences expectations ahead of a demo with subtle comments about the local WiFi and blocked Azure port etc 🙂

Of course the demo gods may not always be on your side and unexpected things can happen. When they do go wrong have a recording of what you were going to show, it saves face and the audience will respect the extra preparation you’ve done. I use a product called Flashback by Blueberry Software, link below. It offers good quality playback in surprisingly small video files (2mins in 10MB), plus the native recording format includes a mouse tracker and key logger so the viewer knows exactly what’s happening. Be warned though, the key logger element of the recording will display passwords so maybe convert the output to a different video format like AVI before sharing it.

https://www.flashbackrecorder.com/fbhome/

Mic Up if Possible

We can all shout, but doing so for a prolonged period of time will kill your voice box. Even in a room that doesn’t feel that big if a microphone is available then use it. The audience will appreciate it and it will save your voice. Also remember that once you have the microphone on resist the temptation to shout because of the room size. Let the speaker amplifier do the work and get the AV technician to adjust the volume if required.

Keep an Eye on the Time

If you’ve rehersed well this might not be an issue. However, you might get lots of questions from the audience throughout the talk which require you to make some on-the-spot adjustments to content. Be prepared to subtly exclude elements of a demo or leave off a couple of slides at the end. I find this typically comes with experience and knowledge of a talk. As a general rule always leave 5 minutes at the end for questions. Other international speakers have told me that you may need to speak slower if the native language of the audience isn’t English and that American’s always ask lots of questions so be prepared, and as the heading suggests keep an eye on the time. In presentation mode PowerPoint does show a duration stop watch, use it.

Pointer & Clicker

More kit to carry around with you. The below clicker does include a laser pointer so you might not need both.

Depending on the position of your laptop and projector sometimes I find it helpful to use a laser pointer so the audience is drawn to the exact parts of my slide that are being referred to. Especially if your slides are busy with content. What often accompanies the pointer is a means of remotely move onto the next slide. This can be helpful and saves you returning the your laptop for every transition. This brings me nicely onto my next point about movement below.

Body Movement

The most valuable feedback I got from SQL Relay 2016 was from a comment that said something like the speaker frigits a lot which is distracting. Thank you sir or madam for this. You were spot one and please accept my apologies. In my defence I think at the time this was down to nerves as it was my first time speaking at SQL Relay. However, I took the comment and learnt from it, as you should always do. Now I force myself to hold onto something nearby, like the table where my laptop is. Or, cross my legs to stop myself from shifting my weight. Adopt a pose that prevents unconscious movements ideally where all limbs are locked down. If you do have to move then walk slowly and go big. Size matters here 🙂

Er, er, er

Continuous speaking is hard work and requires a lot of concentration. Often the brain cannot keep up with the mouth which results in sentences littered with the word/noise ‘er’. Unless you are a totally cool cucumber this is going to happen. Especially with new content. A trick I try to use to avoid this is by recording myself with the ‘Voice Memos’ app available as standard on the iPhone. Then I listen to myself several times over, maybe during the drive to work or some other private place. I say private place because personally I can’t stand the sound of my own voice, it makes me cringe, so privacy required. However, if you can put up with the sound you’ll hear the ‘ers’ slip into content. My dislike of this when I hear myself on the recording almost unconsciously earses the ‘ers’ when I do the talk for real. I don’t how it works, but it does seem to help. Try it out and make your words flow freely.

Zoom It

As you get older your prefect eye sight will fade. Or your laptop has such a high resolution that text appear small anyway (like mine, 3200 x 1800). For the audience both of these things can be a problem and its your job as a good presenter to overcome them. Increase code text size in query windows, lower the resolution on your screen or get yourself the handy little app called Zoom It. Link below. Its a portal self contained exe that sits in your system tray once started. This tool with a few simple shortcuts gives instant scaling to static screen content and allows annotation and highlighting with various colours. If possible try keep the projector output in the corner of your eye and figure out which menu clicks and parts of your demo require some zooming. I also strongly recommend practicing with the various Zoom It keyboard combinations ahead of a presentation. Crtl + 1 and Ctrl + 2.

https://technet.microsoft.com/en-us/sysinternals/zoomit.aspx

 

That’s it from me on this post. I hope you found the above useful. Back to more technical things next time.

Many thanks for reading


Wearing Four Hats at SQLBits 2017

I know there are lots of great blog posts already out there from great people sharing their experiences from SQLBits. However, I hope none of them can offer an account quite as unique as mine from this years event. Why? Well, most people go to SQLBits as attendee’s to learn, network and socialise. This certainly applies to me. But ‘attendee’ was just 1 of my 4 hats (roles) at SQLBits 2017. In this post I’d like to share with you details of my other hats. Which alone poses a wardrobe problem… Orange with black writing or black with orange writing!

Firstly, let’s label my hats…

  1. Attendee
  2. Helper
  3. Speaker
  4. Exhibitor

Before I share any more information with you its worth saying from the start that this was not an easy task juggling these 4 different roles! Very long days with little sleep, little nourishment, and little time to pause. That said, I wouldn’t change it for the world and would gladly do it all again. The reward and enjoyable of SQLBits greatly outweighs any negativity.

Hat Number One – Attendee

I’ve already mentioned being an attendee is the main reason for going to SQLBits. But just to add some stats to that statement:

  • 88 expert speakers
  • 290 hours of content
  • 120 sessions
  • 13 tracks
  • 4 event days
  • 7500 square meters of conference space
  • 700 square meters of partying

SQLBits really is an awesome event! If you haven’t been before I strongly recommend it. Even if you just go for the Friday party, maybe not dressed as a Rubik’s cube though!… Sadly for a disco themed party this often takes some explaining (invented in 1974 + BI SSAS cube)  🙂

Hat Number Two – Helper

Helping at SQLBits is so much more than wearing a bright orange tshirt and telling people where the toilets are! Its about being part of something bigger. Being part of the #SQLFamily that makes the conference tick. Those early starts and late nights to ensure attendee’s have the best possible experience are returned by being part of this great team.

To give you an idea of what I mean by family, here is a picture we posed for (I’m in the middle), just to congratulate an absent helper and friend on the birth of his son just days before the conference. Congrats Terry aka @SQLShark.

I’m not going to waffle on about helping, instead I’m just going to direct you to the blog of my bearded friend Mr. Rob Sewell about why we volunteer at SQLBits. Great post. Great pictures!

Hat Number Three – Speaker

This was only my second time at SQLBits but I was honoured to get selected to speak on Saturday. I took along the Raspberry Pi’s and showed the audience how to build an IoT solution end to end within the Microsoft stack using Azure.

For those that asked me for my C# UWP app code that I ran on the Pi this is now available on GitHub here:

https://github.com/mrpaulandrew/IoTPi3FezHatUWPApp

If speaking on Saturday wasn’t enough I was also invited to talk in Chris Testa-O’Neill’s session on Friday about Azure Data Factory. I offered our real world experiences of the service from a recent customer project, building a complete business intelligence solution in Azure.

I’ll certainly treasure those 20mins in the limelight. Thanks again Chris.

Hat Number Four – Exhibitor

This year with SQLBits being held on the home turf of Purple Frog Systems we simply had to sponsor the event and make our mark. This was a very proud moment for the company having never sponsored Europe’s biggest data platform before.

I was charged with the setup and collapse of the stand as well as speaking to all the lovely attendee’s during breaks about what we do. The purple glow sticks and balloons were all my idea. You’re welcome!… Did I mention Purple Frog is also recruiting, please send your CV to Jobs@PurpleFrogSystems.com.

Another little known sponsor called Microsoft 🙂 also embraced the spirit of things by having a very purple exhibitor stand. Seen to the right. Total coincidence, but let’s claim it as a win for the frog massive! Ha.

If you won one of our Raspberry Pi starter kit prizes I’d be keen to hear from you about how you’ve used the device. Please reach out.

That’s it from SQLBits this year. Start counting down the days until next years event.

Many thanks for reading.

SQL in Seattle – My PASS Summit 2016

passsummitimg_8236This year was my first time at the PASS summit in Seattle. I’d of course been to plenty of other community events and conferences, but this was the big one! Spanning 3x days with 15x tracks to choose from in 4x rounds of sessions per day. Excluding precons.

Really words don’t do the event or the venue justice. It’s massive. You basically need to see it for yourself. Which leads to the question, why write a blog post about it? Well I was fortunate enough to spend Saturday to Saturday in Seattle, doing the tourist thing for the first half of the trip with the future Mrs Andrew. Then at the PASS Summit for the second half. This blog is therefore going to be a shortened account of my experience, but in the form of tips for other first timers, to both the city and the summit.

Tip 1 – Travelling back in time is horrible. But there’s no avoiding it. So, my advice would be to stay awake as long as possible. Watch 3x films on the plane back to back if you have to, I did. We started our journey from the UK at 2am Saturday morning.

  • 1 hour to get up, finish packing and leave the house.
  • 45mins drive to Birmingham airport.
  • 10mins walk to the terminal.
  • 15mins queue to check in and off load bags.
  • 2 hours waiting around and eating breakfast.
  • 1 hour for everybody to board the plane.
  • 2 hour flight to Amsterdam.
  • 1 hour waiting around in Amsterdam airport.
  • 1 hour for everybody to board another plane.
  • 11 hours flying to Seattle.
  • 30mins getting through customs.
  • 30mins waiting around for luggage.
  • 10mins walk to the train.
  • 30mins train ride to down town Seattle.
100px photo-23-10-2016-17-39-52

Tip 2 – Do not get a taxi from the airport to down town Seattle. It’ll cost around $40. The Light Link Rail costs just $3 per person one way. Follow the signs upstairs in the airport terminal arrivals.

  • 10mins walk to the hotel.
  • 15mins to check in.
  • 5mins to find the room.
  • 45mins to unpack.

Now at this point if you add up the above you will have been awake for around 23hours. Which is no thrill. Espically when you discover the local time in Seattle is now only 2pm in the afternoon. This is where you need to power through. Go for a walk. Do anything to keep your zombie like shell of a body awake until the locals sleep.

Tip 3 – Do not lie down on anything once you’ve past the point of being awake for 24hours 🙂

I survived until around 7pm local time before I crashed. It wasn’t pleasant, but it made the adjustment to US time a little easier.

The follow morning we awoke at a reasonable hour and started our adventure. Having never visited Seattle before we were keen to soak up the sites and sounds.

Tip 4 – Go to the Pike Street Food Market near the water front and sample everything. It’s delicious and a real hive of activity.

Slowly cooked brisket, smoked salmon, clam chowder, Russian pastries to die for. Every colour of fruit and vegetable you can think of. However, what you may not be expecting is the setting. Rather than open air market stalls like you may see in the UK. This market is shoe horned into a rabbit warden of aging industrial water front buildings and spanning several levels. You can easily get disorientated and lost. Its therefore not the most charming place I’ve ever been, but focus on the fresh local produce.

photo-25-10-2016-16-24-17Tip 5 – If you like coffee visit the first ever Starbucks shop and get them to make you a special reserve Pike Place filtered coffee. Lovely!

The shop does get really busy to the point of needing a rope to control the queue down the street so I would suggest getting there early. The strength of the coffee will see you through the rest of the day.

img_8237Tip 6 – Get yourself a City Pass. This is a bulk buy set of vouchers to various attractions. This works out much cheaper than buying tickets separately.

The pass includes the Space Needle during the day, as well as a night time visit. Both provide stunning views of the city scape and surrounding areas. To get to the space needle you can spend 30mins walking or use the monorail on 5th Avenue. It goes back and forth between down town and the needle fairly frequently.photo-23-10-2016-18-19-35cropped

photo-24-10-2016-03-56-39

Tip 7 – The WiFi at the top of the Space Needle is incredible. Ignore the other halve and take your laptop. I measured a 325Mps download speed! #GeekFirst

Once you’ve seen the city from the Space Needle, you’ll also want to see it from a boat cruise around the harbour and pray for good whether.

Tip 8 – Unless you have a particular musical talent or inclination the EMP Museum may seem a little dull. But they do have Super Mario games setup on various consoles to play with.

We also went to the glass art exhibitions in the same area. Not being an arty person, even I found some of the exhibits impressive. Be warned though, even the small pieces retail for around $6k in the gift shop. Other attractions include the aquarium and the zoo.

photo-23-10-2016-16-33-03

Tip 9 – Again if you like coffee visit the Starbucks roastery on Pike Street heading north. It’s a really cool place to hang out and watch the beans being roasted while consuming a beverage.

If you have time make your way north of the city to the Boeing Future of Flight Tour around the factory. This is the worlds largest building in terms of volume. Which you’ll understand when you see full size passenger jets on a production line.

Tip 10 – Book the Boeing factory tour in advance. It does get busy and there are limited places.

For your evening meals you may want to visit the Cheese Cake Factory (yes, like of the Big Bang Theory). Or Daniels Boiler, a steak house just north of down town.

Tip 11 – Get the Uber app setup on your phone. It makes getting a taxi really easy and drivers are everywhere in Seattle.


Enough of the tourist things. Moving on to the PASS Summit.

Tip 12 – Depending on your schedule it’s worth popping into the Washington Convention Centre on Tuesday evening when registration opens to get your badge. This avoids the crowds and gives you chance to socialise with the most enthusiastic attendee’s. As it was my first PASS Summit my levels of enthusiasm were unmatched.

On the first day of the conference make sure you have plenty of sleep and get in nice and early for the breakfast sessions ran by sponsors. Listening to JRJ talk about SQLDW while consuming bacon is a double treat.

Tip 13 – Network, network, network.photo-25-10-2016-22-07-50

Don’t be afraid to talk to people. Everyone it’s friendly and your almost guaranteed to have some professional experience in common.

If speaking to random peeps isn’t for you then try out the SQL Clinic. Normally ran by the Microsoft Cat/Tiger team. Tell them about all your problems and frustrations related to the products you work with and they’ll thank you for the feedback. Seriously, this is the best chance you’ll have to get that bug in Visual Studio that’s been driving you crazy fixed.

Tip 14 – Keep an eye on emails, Twitter and Eventbrite for invitations for evening parties.

Tuesday night, we went to the Amber pub on 3rd Avenue for Karaoke. Hosted by Denny & Cherry Associates.

Wednesday night, we went to the Pike Place Brewery for a private party. Host by Sentry One

Thursday night, we went to 1927 5th Avenue for a party. Hosted by Red Gate.

Tip 15 – Don’t stay out too late and miss the next mornings of content. Especially the keynote talks. You can party anytime. These world class speakers aren’t always around to share their knowledge.

In the community zone make sure you check the schedule for when your country or region is being represented by local chapter leaders and be sure you go along for more networking.

Tip 16 – Take a spare bag with you to carry all the swag the exhibitors will give you. Seriously. T-Shirt, pens, notepadsimg_8238, torches, USB sticks, phone chargers. I even got a 2″ ceramic pig from Solar Winds to remind me about things that may be “hogging” the performance of my infrastructure!

By the end of the week if you’ve done everything right; Your brain will be mashed with knowledge. Your feet will be killing from walking round. You’ll have forgotten all the names of the people you meant. You’ll have about 30 more twitter followers. Finally, you’ll be exhausted and ready for home. But, you’ll still want to come back and do it all again next year.

Many thanks for reading.

Note; all the photos in this post are my own.


My SQL Relay 2016

sqlrelayIn 2015 I attended a SQL Relay day in Birmingham, as a humble attendee with a colleague. I sat on the edge of my seat in every session soaking up all I could from the speakers. I remember being in awe of these technical rock stars that gave their knowledge to the community. A community that was strongly bonded and recognised by many sponsors.

In 2016 the humble attendee was long gone. I was now heavily involved supporting operational elements of the conference. I was now a speaker, giving back my knowledge in every city throughout the week talking about IoT using Raspberry Pi’s and Azure. I believe I completed my initiation into the community (the SQL Family). I accompanied friends and colleagues on the #FunBus each day as we travelled to new locations. I organised other speakers as they arrived at venues and helped with the event in a totally flexible capacity, dealing with whatever was needed. From attending a one day event last year I was now amongst a handful of people that would be at every SQL Relay day throughout the week. As a sponsor, speaker, helper, go to guy.

This blog post is my brief and informal account of SQL Relay 2016 from an insiders’ perspective.

Before Monday

photo-12-10-2016-13-13-20As Purple Frog Systems were a silver sponsor this year and heavily involved in the overall setup of the SQL Relay event, time before the first day with delegates was spent sorting everything out from graphics to sponsor swag, bags, leaflets, transport, hotels and everything in between. Organising a conference in five cities in five days with zero time and little budget takes a phenomenal amount of effort. The picture to the right was the scene in the Purple Frog conference room leading up to Relay.

This little lot had to be sorted, condensed, part sent to Nottingham for the second half of the Relay week and most importantly Tetris’d  into the back of a 17 seat minibus aka the #FunBus. Not just once, but five times in a repeatable way.

Monday 3rd Oct – Birmingham

I was up and out the house for 6:15am, yes, this time does exist! Much to the disgust of my other half who reluctantly gave me a lift to Stafford train station, I bound for Birmingham New Street. Upon arrival I filled up with coffee and met up with Microsoft’s Andrew Fryer, David Postlethwaite and Purple Frog’s Hollie Whittles. We then a short car journey to the Midlands Art Centre (MAC) this was the calm before the storm.

Upon arrival an army of helpers, speakers, sponsors and volunteers unloaded the mini bus and setup for the first day of the conference. Spirits were high and enthusiasm in abundance. The doors opened on time and delegates poured in.

At 12pm I delivered my first talk of the week on building an IoT solution end to end in the MAC’s Hexagon theatre, this went well and I received positive feedback. This was a great start to SQL Relay for me as I am a fairly new community speaker.

photo-04-10-2016-14-12-43 photo-04-10-2016-14-12-31 photo-04-10-2016-14-12-53

Thanks Terry (@SQLShark) for the terrible picture of me 🙂

After the prize giving, a select few loaded up the #FunBus and we set off for Cardiff. Alex Yates from DLM Consultants had turned 30 the day before, so as a SQL Family it was necessary to have a party aboard the #FunBus between Birmingham and Cardiff. This included party hats, balloons, banners, singing and birthday cake! The atmosphere aboard the mini bus was brilliant and perfectly fitting of the SQL Family.

photo-03-10-2016-17-55-01The terrible picture was my fault this time!… But you get the idea.

We arrived in Cardiff, with the first leg of the journey complete. We checked into our respective hotels and met up with the Cardiff massive for a beer or three led my Steph Locke.

Tuesday 4th Oct – Cardiff

photo-05-10-2016-06-31-55A slightly more civilised start today, we met at 7am in the hotel for breakfast before taking a short walk across the road to the Cardiff Motorpoint Arena. The oiled machine of helpers sprung into action again, setting up the event with time to spare before delegates arrived.

The Cardiff peeps really spoilt us at lunch time. We were given lamb tagine, couscous and salad. It was lovely, especially the second time round 😉

The demo gods were once again kind and my IoT talk went without a hitch.

Later in the day we learned that Jean Michel Jarre had also come to the Motorpoint Arena for SQL Relay, providing a great backing track whilst we packed away.

The force was strong in Cardiff so rather than hitting the road straight away we stayed for another SQL Family gathering at Five Guys. It was a great end to the second day. Spirits and enthusiasm were still high.
photo-05-10-2016-06-33-59

Left to right the people I know, sorry if I missed you; David Postlethwaite, Alex Yates, Steph Locke, Ryan Yates, Rob Sewell, Alex Whittles, Richard Douglas, Stuart Moore, Me, John Martin and Andre Melancia. Photo by Andrew Fryer.

After burgers and chips most of us we were aboard the #FunBus again heading for Reading. After getting lost in the Reading traffic, we dropped people off at there hotels and had a relatively early night.

Wednesday 5th Oct – Reading

Sadly there was no breakfast provided in the minimal Reading digs, but I didn’t mind as todays SQL Relay was being hosted at Microsoft’s shiny UK offices at Thames Valley Park, complete with in-house Costa Coffee and Starbucks! SQL Sentry’s John Martin welcomed us into the building and provided the caffeine while we waited in the lobby… It was massive and luxuriously furnished. The Microsoft buildings were superb, even though the SQL Relay conference only occupied a small corner in building 3.

Data_Cul_MFSTHQ

12pm came around again and I presented my talk in a state of the art theatre. The venue made the buzz of speaking even more exhilarating.

photo-06-10-2016-06-57-24 photo-06-10-2016-06-57-32

In the afternoon we were greeted by Claire Smyth, Microsoft’s MVP Programme Organiser. Claire spent a lot of time learning about the wider community and brought us another round of coffee, while we discussed how else Microsoft could help support local user groups.

Sadly, the day came to a close and we were kicked out of the pretty office. But we were over the hump of the week.

We boarded the #FunBus bound for Nottingham this time. This was the longest leg of our journey and travelling up the motorway, limited to 62mph drained a lot of my positivity. The new co-pilot/DJ didn’t help either :-). Fortunately, we had more cake on-board and conversation flowed freely which helped pass the time. Also a new game was created to get me more twitter followers, this offered much amusement discovering how many twitter accounts one person can have!

Upon arrival in Nottingham we made our usual rounds of local hotel drop offs and arranged to meet up at The Round House Pub at 9:30pm. This later resulted in 10 of us all going for curry at The Cumin. The restaurant owner was surprised and happy to accommodate our party on this Wednesday evening. With full stomachs SQL Relay paid the bill (thank you!) and we headed off to count sheep.

1
SELECT COUNT(0) FROM [Brain] WHERE [Thoughts] = 'Sheep'

Thursday 6th Oct – Nottingham

Another early start, the SQL Family met for breakfast at 7am. The St James Hotel was great, but the coffee machine was broken! Let’s just take a moment to reflect of the gravity of this issue. Two third party coffee vendors in house at the Microsoft offices to instant coffee today! Trip advisor shall hear about this! 🙂

Reflection over. Today we were at the Albert Hall! Not Thee Albert Hall, but good enough if anyone asks. An aged building with a beautiful main hall, complete with a massive pipe organ. Sadly, our conference suite was downstairs.

By day four, things were pretty automatic with Relay setup and the day flew by. My IoT talk went well and I even went the extra mile to setup the demo again in the venue lobby for delegates who missed my session.

photo-06-10-2016-14-30-45 photo-06-10-2016-09-44-10

Purple Frog’s Alex Whittles presenting on the right, as nobody took my picture today, well not that I’m aware of.

photo-06-10-2016-22-49-16We packed up and loaded the #FunBus heading for Leeds. We arrived to be greeted by a city light show with music and stunning visuals projected onto the side of our hotel. In addition, Microsoft’s Mike Boswell had booked us a private room at the local Fazenda – a Brazilian restaurant where huge amounts of meat get delivered to your table by gentlemen holding swords and very sharp knives. Yum yum! Wine flowed and the variety of meat were gorgeous.

By 10:30pm I was struggling to keep my eyes open so admitted defeat and headed back to the hotel. I was gone as soon as my head hit the pillow.

Friday 7th Oct – Leeds

The 7am breakfast routine continued, but none of us really had an appetite after the meat feast from the night before.

thankrayToday we were at the Thackray Medical Museum for the last day of SQL Relay. The venue was in a lovely old hospital building complete with exhibitions on how leg amputation used to be performed!

The local SQL community helpers sprang into action once more providing fresh hands to make light work of the conference setup. I confess by day 5 I wasn’t feeling very fresh or enthusiastic. Despite this I was honoured with a midway clap from the audience in my IoT session after the first Raspberry Pi demo worked like a dream, feeding its sensor values into Azure in real-time. It was a proud moment. I may have even cracked a smile myself, which a member of the audience caught on camera 🙂 … because I’m not a grumpy person *cough*

photo-07-10-2016-14-26-13

The last prize giving was done and SQL Relay was closed to delegates for another year.

After Friday

Now it’s back to normality for most people however the SQL Relay drum beats on.

This morning was spent sorting out the Purple Frog conference room again after all the SQL Relay stuff was unloaded on Saturday. Then it’s a case of finishing this blog post and sorting out sponsor attendee slips before we can get things closed off.

It’s been brilliant being part of SQL Relay this year. The people that give their time to make the event run are gods amongst men. In each city, having that fresh wave of helpers really made the difference. Thank you all.

In short; it was an incredible, but exhausting week. One which I hope I’ll be lucky enough to repeat next year.

Many thanks for reading.

Edited by the future Mrs Andrew.


SQL Battleships

When Battleships Met SQL Server, like Bailys meeting Coffee, but better.

Do you have an equal love of the classic two player board game Battleship and also SQL Server database development? If the answer is yes (which it obviously should be) then this is the blog post for you!

The next question that I’m more than happy to answer for you is; how can I combine these 2x loves?… Well my friend, please continue reading to find out.

SQL BattleShips

So, let’s create ourselves a SQL Server database to play a game of Battleships using tables as our grid and ship layout, then let’s use stored procedures as our weapons in which to fire on your opponent (executing statements against their tables). The other unique advantage of playing SQL BattleShips is that both players don’t need to be in the same room verbally querying each other’s grid references and board. In other words, why use English to query this data from a person when you can use SQL to query a database table.

Getting Setup

If you haven’t already done so, please download the zip file from the Downloads page in the database backups section labelled BattleShips.zip. It contains a SQL Server 2014 database backup file of the database called BattleShips. Please restore the database to a suitable SQL instance (and please check the assumption section below). I say a suitable SQL instance because apparently my previous employer didn’t think we needed it on every production server in the organisation!

A Few Assumptions

  • You or the person creating the new game, which doesn’t have to be one of the players has sysadmin access to the SQL Server instance where the Battleships database has been restored

Why? The new game procedure alters various database objects and setups up SQL logins for each player.

  • The SQL instance has database mail setup and is working.

Why? For ease of automation and sending each player details of what to do next. Including login details.

  • The SQL instance is setup for mixed mode authentication.

Why? Each player will have a SQL authenticated login created on the SQL instance in order to be able to connect to the BattleShips database and play with their respective objects. The database users then have a set of permissions defined which prevents each player for seeing each other’s tables (ship board).

  • You and your opponent are aware of how to edit/query database table data, plus views and execute stored procedures with required parameters in SQL Server Management Studio (SSMS).

Why? Because this is SQL BattleShips and it can’t be played using English queries.

Creating A New Game

To create a new game, execute the following procedure adding an email address for each player as required.

1
2
3
4
5
6
USE [BattleShips]
GO
 
EXEC [dbo].[NewGame]
	@Player1EmailAddress = N'bobby.tables@somewhere.com',
	@Player2EmailAddress = N'chuck.norris@somewhere.com'

Using database mail players will then receive details instructions on what to do and where to connect to using a set of SQL credentials provided. For information the body of this email is sorted in the [dbo].[EmailText].

To isolate players from each other as part of the game SQL authenticated user accounts are created for each player which only grants the necessary permissions to the respective player’s databases objects. This is handled using database schema’s and execute only permissions to certain procedures. Players will not have access to any other database on the SQL instance.

If you are a sysadmin you can of course very easily cheat, but this is not in the spirit of the game and it is expected that each player only uses their SQL account provided in the email sent out.

How To Play

Following the instructions provided by email, once you’ve connected to the BattleShips database in SSMS, edit your ‘board’ table. [playerX].[board]. Add all your available ships and close the table editor once complete.

BattleShipsBoardSetupWhen you are happy with the strategic planning (layout) execute the following stored procedure. This will validate your board layout and provide feedback if a ship is the incorrect length or has been missed. If everything is correct your status will be set to ready.

1
EXEC [playerX].[ImReadyToPlay]

Once both players have completed this operation can battle commence! Query the table players if you are unsure of the other player’s status.

1
SELECT * FROM [dbo].[Players]

They may also be ready to play or still setting up their board. If you’re playing in the same room feel free to speak to each other, it’s not against the rules, just not in the sprite of SQL BattleShips.

When both players are ready start taking shots at each other’s ship grids in turn, with player 1 going first. This can be done by executing the following procedure. You’ll of course want to update the gird reference parameter value once you’ve eliminated A01.

1
2
EXEC [player1].[TakeShot]
	@GridReference = 'A01'

Following execution feedback will be returned about the success of your shot and the states of both your board and a grid of which coordinates you have already tried. If you use the same grid reference twice you’ll be warned so your shot isn’t wasted.
BattleShipsShots
After a winner has been established by an opponent sinking all of their rival’s ships (or updating all of their ships with an X for hit). The players table will be BattleShipsPlayersupdated with the winner’s status and the loser will no longer be able to take any more shot.

Happy hunting my friends and please give me your feedback on any epic battles, and maybe the coding!

Many thanks for reading.


Paul’s Frog Blog

Paul is a Microsoft Data Platform MVP with 10+ years’ experience working with the complete on premises SQL Server stack in a variety of roles and industries. Now as the Business Intelligence Consultant at Purple Frog Systems has turned his keyboard to big data solutions in the Microsoft cloud. Specialising in Azure Data Lake Analytics, Azure Data Factory, Azure Stream Analytics, Event Hubs and IoT. Paul is also a STEM Ambassador for the networking education in schools’ programme, PASS chapter leader for the Microsoft Data Platform Group – Birmingham, SQL Bits, SQL Relay, SQL Saturday speaker and helper. Currently the Stack Overflow top user for Azure Data Factory. As well as very active member of the technical community.
Thanks for visiting.
@mrpaulandrew