Following one my most epic failures which l shared with someone, who understood. He gave me a card with this quote
“It is not the critic who counts; not the man who points out how the strong man stumbles, or where the doer of deeds could have done them better. The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood; who strives valiantly; who errs, who comes short again and again, because there is no effort without error and shortcoming; but who does actually strive to do the deeds; who knows great enthusiasms, the great devotions; who spends himself in a worthy cause; who at the best knows in the end the triumph of high achievement, and who at the worst, if he fails, at least fails while daring greatly, so that his place shall never be with those cold and timid souls who neither know victory nor defeat.”
It’s not easy to pick yourself up and dust yourself off and keep going. That’s what makes some people that little bit different....
Forgive me whilst I indulge in some nostalgia first. Whilst at SQL Bits 2016, I finally made the decision to start a SQL user group in Glasgow. If I remember correctly Craig Porteous attend the infamous SQL Bits party and met a gentleman dressed as a Wookie (William Durkin). Now Glasgow has a growing SQL server user group (thanks to a dedicated hard working team). Not only that we just had our second SQL conference! (Yes SQL Bits has lot to answer for ;->)
SQLGLA was a fantastic event it has left me with some wonderful feelings. This blog post is part thank you to those involved, also just to express some of my feelings / experiences of the event.
Craig Porteous, Louise Paterson and I are the people who organised this year's event. I felt we were a team (this is a theme, by the way). Each of us contributed to the success of the event, in a different way. The success of the event was not down a single person in my opinion. It was about us all coming together to make it a success. Craig did put a lot of work into making the event what it was.
There are few words to express my gratitude about our team of volunteers. As an organizing team, we have all volunteered before so we knew what is expected of volunteers. There was a lovely mix of those who have done it before and few who have never done any volunteering. What a team though, they all worked really hard. Speaking personally, I am so proud of them, they made this event for me. Not once did I have to worry about if something was done or not being done. They just made it happen. Whilst I have said this before I cannot thank them enough for being such an amazing team, they exceeded all my expectations.
Last year thanks to William Durkin we had such an amazing lineup, and yet this year lineup was even better! A few days before the event I was looking through the list. It would be a fair comment to say I was blown away by the quality of those who choose to speak at our event. The feedback that I have heard so far the attendees also agree 🙂 Looking the depth of experience and knowledge that was shared, was to me awe inspiring. Whats even better is they all came to Scotland to share their knowledge and expertise with us.
It’s a phrase that I've seen often on social media, these are not just words it's something that can be seen and experienced in real life. To experience it is very humbling and gratifying. A few weeks before the event I was contacted by Chris Taylor (@SQLGeordie) one of the speakers. The speakers had decided they wanted to get something for Craig & Karen Porteous who are expecting their second child. It was my privilege and honor to present the gift on their behalf. Hopefully, Craig will forgive me one day for gate-crashing the closing presentation to present the gift? As Craig knew nothing about this gift.
To make the event something that little different we wanted to find a design for the t-shirts that was rather special. Having worked with Emily Chappell over the years on various projects, her quirky designs and sense of style really works for me. So I suggested we should ask Emily for a design for the t-shirts. The first design was not what we wanted. So next we all got together in a coffee shop at lunchtime. We had a chat did some brainstorming, Emily then sent back a design which we all loved (see picture below). Personally, I’ve never been to a technical conference where they sold the t-shirts :-)
First there are few housekeeping duties to take care, which we are just finishing off just now. Followed by some discussions to see what will be the next step 🙂 All I can safely say is keep looking there will be some exciting news soon....
Figure 1 – Three records returned from the temporary table.
There are three records returned, note that the second record the ‘Colour’ field has a NULL value returned. In the next step I am going to add ‘FOR JSON PATH’ after the ‘FROM’ statement.
Figure 2 – Adding ‘JSON PATH’ to the select statement, the results set it outputted as a JSON formatted string
When the SELECT statement with the ‘JSON PATH’ command is run the results are returned as a JSON formatted string. The query was ran in Azure Data Studio, so to see the JSON string formatted. All that was required is to click once with left hand mouse button to open the results in a new window, as show in Figure 2.
Note that for the second record, the ‘Colour’ field has no data returned, since the value for that record is ‘NULL’. Can we write a SELECT statement which includes ‘JSON PATH’ and return records with NULL values?
All we need to is add ‘INCLUDE_NULL_VALUES’ to the query see Figure 3.
Figure 4 - Adding ‘WITHOUT_ARRAY_WRAPPER’ to the query and the square brackets are suppressed.
All the queries shown in the screenshots were ran using Azure Data Studio, which if you click on the Results set returned opens it in another new window and formats the JSON.
That’s enough for just now there is more to come.
This post is just some reflections on my experience on speaking, as part of the newcomer track at SQL Grillen 2018
Pre SQL Grillen
This is the first part of this post is written on Tuesday 19th June 2018 only 3 days to go before the session. My current feelings are rather to say the least nervous. Added to that is a considerable feeling of imposter syndrome. At this time my thoughts and feelings are these -:
Rehearsals - without a doubt, going over the presentation multiple times has helped so much more than anticipated. Whilst at this point the words that are going to be used have been repeated many times. They are in my mind. As each slide comes up, there is little doubt in my mind what I'm going to say or how.
Less is more - the amount of material that came out of my research into the topic, not all of it has made it into the presentation. So, what made it in is just what is required to get my point across. Which has upsides and downsides. It should make the session better, more focused, and if people ask questions after the session then other examples and illustrations will spring to mind. On the downside there is part of me that feels like my audience is not getting all that I want to get across. Then again there are only 60 mins which is more than enough for most people.
Imposter Syndrome - not sure what can be said about this. It seems natural, there are many presenters who feel the same. Right now, the best strategy seems to be to focus on the presentation. The goal of the presentation to help one person take one thing away from the session. Who that person is at this moment I do not know. So if just one person takes one thing away that I will count as a win. That person might just be me, which is also good.
Mentor - SQL Grillen, did an awesome job with the new comer's track. Assigning each person, a mentor, for me, I was very luck and have been assigned Cathrine Wilhelmsen as my mentor. Her insights and attention to detail was invaluable in so many ways. Cathrine made excellent suggestions and helped me to see the presentation from the point of view of a non-native English speaker's. Most importantly just generally very encouraging :->
Post SQL Grillen
Phew! OMG! That was soooo scary! Can I try that again?
Back in Sunny Glasgow. Now looking to see what lessons I can learn, and other thoughts spring into my mind.
Bunny in the headlights - It's easy to forget that as a speaker that I felt front and centre. That is to say, everyone can see you and knows you're a speaker, thats how I saw it. Even better or worse, each of the new speakers was given an orange apron to wear. The other speakers had different colours. For me, it was a strange feeling not in a bad way, more that I am usually part of the audience. On reflection its not a bad thing, all part of the learning experience.
Rehearsals – This really worked for me I was able to sit at the speaker's table check my equipment worked, run over my presentation quickly and that was me ready to go. Doing so many rehearsals (and not having any demos) meant for me that I knew what I was going to say and all the notes I needed were on the slide deck. Sitting at the speaker's desk was scary, with so many people who I have seen speak before. At least I was able to make it to a session before I was due to present. Which allowed me to relax and listen to the awesome trio of Rob Sewell (@sqldbawithbeard), Chrissy LeMaire (@cl) and Cláudio Silva (@ClaudioESSilva) talk about the new dbaChecks module.
The Presentation - nervous? YES! Waiting for the session to start was the worst part. Having seen some advice from Brent Ozar I had some music playing (that only I could hear) only thing was I had to resist dancing around. Knowing the presentation allowed me to concentrate on other things.
Audience – making sure I spoke to the whole audience front row to back, both sides, making eye contact with everyone, looking at their body language, to see if my points hit home
Pace - at some points my pace was a little faster than should be, I felt able to vary according to the material and audience reactions.
Body language - both my own to ensure I got points across. More importantly the body language of the audience. Was the audience looking at the slide, or looking at me, did they react how I expected?
The Audience - Think about this afterwards, there were so many more people than I would have even dared hoped for. My guess was about 30 people, some of the people I recognized, my colleagues from Scotland, Craig Porteous, Paul Broadwith, and of course Cathrine :->, and Grant Fritchey aka "The Scary Dba" (yes really!). Somethings seemed to work really well, like the acronyms game, and my alternative job description, yes you had to be there to get the point.
Feedback - for me this was the hardest part. The best that I had expected something like "Meh".
What I did not expect was people saying how well I had done. Grant Fritchey who attended my session, congratulated me on my presentation, even tweeting about as well. Then Alexander Arvidsson also congratulated me on the presentation, his kind and encouraging words can be found in this blog post. Catherine was very generous with her compliments and encouraged me to review the feedback, which was complimentary and insightful.
Finishing - needs more rehearsing, so that the presentation finishes on more of a high, at least from my point of view.
Timing – instead of using a stopwatch, I used a countdown timer. At several points, I was trying to see how much time had elapsed. As my notes had time elapsed at key points. The countdown timer did not make it easier for me to see the time elapsed.
Hard work – Over the years I have been fortunate enough to see many people speak who make it look so easy. Having done it now, its like a swan look they look graceful and elegant as it glides across the water's surface. Yet hidden away underneath the water are the webbed feet working really hard all the time. That’s my experience of presenting, making it look easy requires a lot of hard work, which remains unseen, the way is should be.
Last point is to thank the SQL Grillen team. William Durkin, who does an amazing job of making everyone feel welcome. Ben Weissman for creating and picking the speakers for the newcomers track. There are as l know so many more in the SQL Grillen team, thank you to all.
There are some ideas which are being considered. Where, when and what who knows, watch this space.
What's so hard about making mistakes for me? The embarrassment of it, maybe I did not know something, or yes that thing that I did well, yes, I did know better. It's not easy for me to admit mistakes. Just ask my long-suffering partner (thank goodness, she does not read my blog!). Yes, I do like to be right and do it the right way. Admitting that I was wrong, or did something stupid, takes it out of me, it's not easy.
Hopefully, in my professional life, I am a little better at dealing with my mistakes. A few years ago, one of my jobs was with a large consultancy company. I was the person responsible for producing reports for the service desk. From time to time there were errors with the reports that I was responsible for producing. During that time, I developed a strategy which I still use to deal with mistakes.
1) Take responsibility
It's not easy to put your hand to say you have made a mistake. On the other hand, how to do you learn from mistakes? For me, part of growing is learning to take the bad with the good. Also personally speaking I have more respect for someone who has what it takes to say when they have made a mistake. Even if you have not made the mistake you find, then take make it your responsibility to fix that mistake. If I do this then my primary focus is to get the issue resolved and move on. Finger pointing or the blaming someone is not part of this.
2) Find the challenge
What when wrong? How did it happen? Be able to explain what happened, in simple non-technical language that anyone can understand. Also be confident that you can explain in technical terms to your peers.
3) Fix it
Get your hands dirty, get involved in fixing the issue. Help find a solution to rectify the challenge or work with the people fixing the challenge if you can. For me, I have and do still learn so much just from fixing mistakes.
4) Prevent it!
Better to have a fence at the cliff edge than a hospital at the bottom. What will stop it happening the mistake happening again? An extra check of something, a checklist of things to do in the same situation.
Is there something I missed, do you have a different strategy. Maybe you disagree? Let me know, every day is school day for me :-)
My latest adventure was speaking at DataRelay in Leeds and Nottingham. As is the way with my submissions the session selected was not the one, that I expected to be chosen. It was titled “How to be awesome at finding your next job”.
Speaking is very much outside of my comfort zone and something that I really enjoy. Why do it then? Well when I see someone in a session, that their body language says they understand what you are trying to say. That feeling is just an absolute joy. As I have learned from others in the community, so I want to pass my knowledge to others. That is one of my primary reasons for speaking. Somehow, somewhere, someone, will be helped to do something. From the few brief conversations that I had during DataRelay I might have achieved that goal. If you came to my session, thank you for coming along, hopefully you got something from it. As I said if you want to chat about the session or ask questions then please do. When I do a session it’s because I am passionate about the topic and have so much to say. I can also listen if you need someone just to talk things over.
What did I get from DataRelay? More than I expected, not in ways I thought that I might. It was possible for me to get to a few sessions. There was time to see how DataRelay do things as an event (very slick BTW). Get to know more about people in the community, make some new friends, meet some old friends. At least one session I attended used Slack / Microsoft Teams in a way I never dreamed of. Yet the possibilities are amazing and mind blowing. Speaking to the sponsors was one of the goals I had. Thank you to the two gentlemen from Microsoft who took time to answer my questions. The time, effort and genuine enthusiasm is much appreciated. It’s nice to speak to a real person who is knowledgeable and passionate about their area of expertise. Over the two days I took quite a few notes which I am excited to share with my team back in Glasgow.
It will take me a little time to recover, there is a long train journey back from DataRelay which will help. Like some of us in the community, it takes energy to be around lots of people. It's worth every single moment of the effort. Especially when I see a room full of people who I can try and show just the one little nugget of information that makes the difference to something in their lives.
This post is being written as the train travels back to Sunny Glasgow. Tomorrow morning, I will be back in the office with my new company Eyecademy. They have supported and encouraged me to go and speak at DataRelay. Which I really appreciate more than anyone in Eyecademy might realise. Thank you for the encouragement and support.
What’s next? Next year I will be speaking at Scottish Summit on the 29th of February 2020 about Soft Skills for Success. Speaking in your home city is both exciting and challenging. So, I will need to put some more work into the session to tailored it to the event and audience. There are a few other submissions to other conferences which I am waiting to hear about. Some other conferences have caught my eye, so I need to consider where and when I would like to speak. Hopefully once I know more, I’ll update the blog.
The core application my current company has built allows people to apply for grants. We track the application through various stages, much like a loan application there are many stages that the application might go through. The status of the application is not linear. So the applications can go back and forth between the same status more than once. In figure 1 below an application .can given a status of ‘under review’ then get a status of ‘submitted’ or ‘Pre-Submission’. So, an application might have the same status more than once with different dates.
Figure 1 –showing the workflow an application might take.
One question which our customers want to know is how long an application takes to move from one status to another. This question could be answered using the existing application database. Using that method is not ideal as the queries are quite complex and slow. This is one of the reasons I decided to build a Datawarehouse as a proof of concept. To see if there was a quicker way to answer that question.
Calculating time difference
Given that an application can be allocated a status more than once during its lifecycle. This has an impact on the method we used for calculating the time difference. The methodology used to get the time difference is defined below
The fact table in the POC Datawarehouse, stores the ApplicationID, Status, and the datetime the application was allocated that status. If an application has been assigned the same status then there would be a record for each time the application has received that status. This has some advantages; the end user can easily understand the data as it is shown in the table. On the other hand, this presented some difficulties with calculating the time period between each status.
After various attempts the following calculated column was created. In this case calculating the days between a grant receiving Pre-Submitted status and Rejected status.
Figure 2 – First version of the calculated column DaysFromSubmittedToRejected
For both values the logic used is very similar, for the variable FirstSubmittedDate the MIN() function is used to get the first date. The FILTER statement only returns records with same ApplicationID and Status = ‘Submitted’. Whereas the variable LastRejectedDate the MAX() function is used to return the last date. Using variables, means the logic can be re-used for other statuses, and the DATEDIFF() function is easier to read. When the results are displayed they were not as expected.
Figure 3 – results from the first version of the DaysFromSubmittedToRejected function see Figure 2
The requirement was to return the number of Days elapsed From Submitted To Rejected with the value returned against the record which is the last time the application received that status. So, another function was created to see if the current record is the LastRejectedDate, which is shown in Figure 4.
With this calculated column (LastRejectedDate) in place the return statement of ‘DaysFromSubmittedToRejected’ function just requires a small change which is as follows
VAR FirstSubmittedDate =
MIN ( 'FactAppStatuses'[DateAddedAt] ),
ALL ( 'FactAppStatuses' ),
'FactAppStatuses'[ApplicationID] = EARLIER ( 'FactAppStatuses'[ApplicationID] )
&& 'FactAppStatuses'[Status] = ‘Submitted’
VAR LastRejectedDate =
MAX ( 'FactAppStatuses'[DateAddedAt] ),
ALL ( 'FactAppStatuses' ),
'FactAppStatuses'[ApplicationID] = EARLIER ( 'FactAppStatuses'[ApplicationID] )
&& 'FactAppStatuses'[Status] = 'Rejected’
'FactGrantStatuses'[LastRejectedDate] = "Y",
DATEDIFF ( FirstSubmittedDate, LastRejectedDate, DAY ),
Figure 5 – Second version of the calculated column DaysFromSubmittedToRejected
When the function is added to the PowerBI report shown in Figure 5 then run then the results can be see in Figure 6 below.
Figure 6 – results from the second version of the DaysFromSubmittedToRejected function (see Figure 5)
The addition of the IF() statement uses the value to check if current row contains the LastRejectedDate. Otherwise it returns a zero. This returns the record set which is expected.
The positive uses of this method are that the same calculation can be re-used for a variety of statuses only requiring a minor change to the formula. On the negative side the more calculated columns there are the longer it would take to generate a report.
No doubt there are other approaches that can be taken to improve this. The time taken to evaluate the calculated column will be slower as the number of rows increases. On the other hand, the current method is significantly faster than doing the same calculations on the application database. Even better with the use of a PowerBI report or a tabular data model. These figures could easily be used by an end user to reports which provide insight into their data. All the better that our clients could do this for themselves, with relative ease.
Hello there,I'm sorry it turned out like that. I've failed massively at things and if I appear successful anywhere, it's because I failed 99% the rest of the time and it got me there eventually. Now I call it 'experience'.Scotland is a notoriously difficult market for community. Having held SQLSat Edinburgh there for a number of years, I recognized eventually that there are a number of generalist IT events which are very popular, and they drowned out the smaller, niche topics and groups. It seems that we like to chat more about broad topics in a big group.Perhaps one thing to look at, as a suggestion, is to look up on meetup etc to see what else is going on, and to see what you are up against. It could be that these larger groups are interested in sql/data and there could be a spin off from that, or opportunities to work with others. It's way harder to do something from scratch, than it is to try and get it off the ground yourself.I hope that helps a bit. Chin up! Lots of people talk about it and don't try. You didn't fail. You got some experience, and you'll use that for next time.Jen