(scottish) sql bob blog

rss

Some thoughts of your typical data shepherd / data plumber / data dance teacher sort of person.


SQL Grillen 2018
SQL Grillen 2018

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.

Improvements

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.

Next

There are some ideas which are being considered.  Where, when and what who knows, watch this space.


What do I plan to learn in 2018

There is saying “if you want to change some things in your life, you have to change somethings in your life”.  So this blog post is part of me changing something in my life.  Whilst I might think its a good idea to publicly commit to something then work towards that goal, this is not something that I have done for before.  So here goes.  Yes this was inspired by the T-SQL Tuesday topic in December 2018, which I mis-read the date for :-) 

What do I plan to learn this year?

Machine learning - this has been on my agenda for quite sometime now.  This is an area where I see opportunities to apply to my current role in BI

R - Jen Stirrup introduced me to this tool many years ago at SQL Bits, the more I have learned the better it gets.  Now that R is supported by SQL server and PowerBI it's time to go a lot deeper.

Python - having been aware of this language for a while now, whilst looking at looking more closely at machine learning, this is one the languages which is mentioned 

Powershell - having attempted to use this before with limited success, I’ve been inspired by the example of my co-organiser Craig Porteous, Rob Sewell and Chrissy LeMarie.  So I’m going so I’m take this to the next level and see what I can achieve.

BIML - this just seems to have so much potential, finding myself writing more and more SSIS packages that have patterns, now's the time to learn more and do more.

How am I going to learn about these topics?

My preferred method is video training.  This means I am in control of the tempo, if I have 5 mins then can watch a video (or part of).  On the other hand if I have not grasped something it is possible to repeat it till I get it.  One course on Microsoft Virtual Academy (https://mva.microsoft.com/) about the use of the APPLY keyword I had watch several before grasping the concept.  At the moment Udemy is working well for me, just make sure you wait till the one of the regular sales.

In person events is something which has been of massive benefit for me.  My first visit to SQL Bits was without doubt a career and life changer.  Having the opportunity to hear from so many fantastic amazing speakers is a wonderful.  Then there is the networking that is another story, even for a confirmed introvert like me!

Books are still a part of my learning.  Recently I have found myself reading more using electronic books.  There is difference between a physical book and an electronic book.  The later is easier for to take my library with me.  So it doesn't matter if the book is at home or work. 

What I am going to aim to do in 2018 (gulp!!)

Teach - the go to person regarding SQL server in my company is me.  At the moment I am writing some lessons to teach what I know about SQL Server and related skills.  This encourages me to make sure that anything I am teaching that I have a through understanding of the topic.

Blog - writing posts has been patchy, so this is my commitment to write at least one blog post per month.  This year I refuse to allow impostor syndrome to put me off (no excuses).

Submit sessions - on this subject, time to take my own advice / encouragement to speak about what I know,  My success is built on those who have taken the time to speak and teach, and I want to give back.

Stack-overflow - having attempted to answer some questions previously with varying degrees of success.   Rather than waiting, now the strategy is chasing down questions that I can answer / contribute  Who knows chances are that it is myself who will learn more than anyone else :-)

Attend another conference - SQL Bits has been and will be the highlight of my technical year (SQL Geek Christmas / New Year).  In 2018 my minimum  commitment is to attend at least one other event.  If possible as a speaker, volunteer, or at least as an attendee.

There are some other goals I have set myself, they are more personal development that technical development.

Soft skills - ask my partner she will tell you these could do with some work.  As a self concession introvert, its not easy to work with people.  Recently small achievements mean that it inspires me to learn and work harder.

#SQLGLA 2018 - the Glasgow SQL server user group ran, their first event (2107).  This was an amazing learning adventure, so we are getting bigger, watch out Glasgow.

Now that I have committed these goals time to go and start making them happen.  Expect a summary in, December 2018.


#SQLGLA 2017 - User group adventures

On the the 10th of November 2017 the Glasgow SQL server user group ran #SQLGLA.  This was a half day event, primarily for SQL server professionals in Scotland.  Although we did have some visitors for further afield.  Namely our four MVP speakers, William Durkin, Chrissy LeMaire, Andre Kamman and Rob Sewell.

Now the event is completed, we have gathered and compiled the feedback.  Now I feel that there is time to think about the event.  It's been a bit a rollercoaster ride, with the full complement of good bits and scary bits. Much of the thanks for making this entire event a success goes to my co-organiser Craig Porteous.  Without Craig this event would not have come off at all.

It would be fair to say that both Craig and myself had visions of very few people coming.  Other than the speakers, volunteers and ourselves.  People did come and as often happens at these, people you did not expect arrived and those we expected did not arrive.  All part of the learning curve.  My memories of the actual day are, we arrived at the venue, we all ran up and down stairs, realized what we had forgotten to bring, set up the venue ready for our guests.  People arrived, attendees, speakers, sponsors.  Speakers, spoke, people mingled, chatted, had tea, coffee, sandwiches, then drank beer, & wine.  We cleared up, said good bye.  Some people head off to the pub to continue the fun.  It was a fantastic day, all the objectives we had aimed for were achieved.

As we know these events do not run themselves.  There is a dedicated team of selfless people who make these events happen.  Craig Porteous my co-organiser put a massive amount of work into making this event happen.  Even more his selfless decision to have one more drink at the SQL Bits party meant he met William Durkin, and the conversation resulted in the event :-).  We were also very kindly blessed with two very enthusiastic and industrious volunteers Edith and Paul.  Who always seemed to be in the right place at the right time, and just did what needed to be done.

This event was a huge learning experience for Craig and myself.  This event combined with running the user group has taught me more than a few lessons.  Some of which we have learned, some I am sure we are still learning.  Now the event is over, and I have sometime to look back, I will blog about some of the lessons.  About running a user group and a larger event such as #SQLGLA.


We all make mistakes
It happens to us all.  Speaking personally, I do not want to admit to them, or in some cases, I keep making the same ones.  Yes, we all make mistakes its part of life.  In my experience, these can become war stories.  When you talk about the time you <insert horror story here>.  Yes, and I am putting up my hand to say that like anyone else I have made mistakes, some days it feels like that I have made more mistakes than done things right!

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 :-)


Power BI no more

I've been working with my current company for over two years now.  During that time, on my own initiative, I decided to review the BI market to see what tool(s) that the company should be looking at adopting.  There are quite a few restrictions, data privacy, our clients are very cautious about their data.  So must be an on-premise server and yes I have asked lots of questions about this.  Also, our clients are mostly non-profit or charities, the budget is a massive consideration.  

PowerBI has been my tool of choice for reporting.  It is used for a POC (proof of concept) project reporting service desk incidents to our clients.  It is fantastic we had a Pro account, we shared the reports with our clients.  We loved it, the clients loved it. During the next two years, I invested time, energy, effort, working on other POC projects.  At the same time showing the relevant directors why we should look at Power BI for future development.  The deal breaker was an on-prem server, no negotiation on that point.  The start of 2017 exciting news, on-premise server was coming  Which version would get it, how much would it cost, could we use it.  Answers from Microsoft, zero, zilch, nada, nothing, brick wall impression.

So we waited and waited and waited.  Then, Power BI premium.  By the time I had digested the news, it felt like someone had kicked me black and blue.  There is no point in even approaching our Managing Director with a minimum of £3k per month for this project.  Our budget is not even in the same country, let alone same ballpark.  Next, our sharing reports with other free accounts using Power BI Pro, at least for some of our client has gone. Now have it, now you don't.

Your company might be a large enterprise, then these costs are reasonable, we are not a large enterprise.  So in essence over two years investment of my time down the drain, time to start again.  Now I am in the process of contacting clients for the POC project to show them how to access the reports, as the can no longer use their own Power BI accounts.  Disappointed would be a mild word to use to describe my feelings.

Very recently Tableau announced a price change.  Long story short, my line manager saw the new pricing structure, complete with on-premise server, per user cost of $35 per month, PowerBI cannot compete with that deal.  What will happen now is my company most likely to become a Tableau customer, Microsoft's loss.  The tools released at the data summit (June 2017) now places PowerBI toe to toe with Tableau.  Sadly I believe that Power BI will likely loose in the long run due to the pricing currently in place.  Whilst I understand the business logic and reasoning pursuing this model.  Microsoft has also demonstrated very clearly they do not understand the market in the way that Tableau seems to, which is reflected in their pricing structure.  Great for me, another toolset to add to my CV.  As I see it, Tableau leaves PowerBI dead in the water for customers like my company.  There is NO competition, Tableau has this market to themselves.  Which is bad news for me as a customer. 

Microsoft has got it right before, yes I will stand up, shout, cheerlead, and applaud when they do get it right.  As my tweet to James Phillps / Power BI team expressed.  When Microsoft get it wrong I need to be just as vocal, and I believe they have got it wrong, with the pricing in a big way, at least from where I am standing.  Yes I will continue to let people know about PowerBI, it not be with the same enthusiasm, that makes me sad :-(

Last but not least a more personal public apology to Chris Web (@Technitrain) was on the end of my rant via Twitter regarding pricing, sorry Chris, my bad.


Speaking the same language

We all communicate with each other, some more than most, in our house if my partner is not talking to me there is something wrong.  It's not usual for the misunderstanding to be something I might have said (or done).  Communicating with each other verbally is something that we learn to do from an early age.  We learn what words mean, their power, what they can do for us, what to say, and what not to say.


There have been times I have had the pleasure of going to the local garage and speaking to the mechanic regarding whatever challenge is with the car.  The mechanic would explain the issue to me, using words which I have to say that wished I understood. It is entirely possible that there is a "big end" in our car just do not ask me where it is or what it does.  Or that the timing belt is very important to make the engine run properly.


Every industry, profession, hobby, has their own language.  This often makes it easy for professionals to communicate with each other often in a form of shorthand which can sound foreign to someone else even if they speak the same language.  Working in the IT industry this is something I am very aware of.  If someone asks me what I do for a job what do I say?  I might say that I am a BI professional, working primarily with the MS SQL server stack, sometimes using SSIS, and SSRS.  I write a variety of CRUD scripts in TSQL and I do some query optimisation.  If the person asking is not an IT professional who works in my specific area of expertise, most of my explanation would have sounded like I had spoken in a different language.

What I now say is my job involves three things, data shepherding, data grooming, and data dressage.  I might expand a little on these to explain that l move data from one place to another ensuring none of the data gets lost as we move it.  Some of the data might need to be polished or groomed to fit in its new home.  Then I train data to perform and dance in a way that others can understand it better.  What I try to do is use words which people who are do not work with databases can understand and relate to.


When I speak to clients one thing I try to remember is to use words that anyone can understand.  If I introduce technical concepts or acronyms in the conversation I will try to make time to explain them.  Or use analogies that are simple and easy to understand.  This is not an easy thing to do, it is our job to make IT simple and easy to use.  There might be lots of complicated moving parts behind the scenes.  Like a car, we have a simple dashboard, underneath are lots of complicated moving parts that just work.  The hard work of maintaining and fixing those parts I happily leave to the experts.


Personally, I see our job is to make our customers task as simple as it can be.  We should present challenges, technical details in a language our customers can easily understand.  As Einstein is quoted as saying “make everything as simple as possible but no simpler”.  One excellent example of this is by Brent Ozar when explaining implications of RTO & RPO here -> https://www.brentozar.com/archive/2014/05/new-high-availability-planning-worksheet/.  The worksheet sets out the terms in language everyone can understand, even better by drawing attention to the targets, so everyone knows what to expect.

Is this easy, or simple?  No, it is not.  Having said that which expert do you feel most comfortable with? would happily go back to time and again?  The one that speaks to you in words and terms you can easily understand.  Or one that uses language and words that are sometimes not easy to follow or understand?  The choice for me is easy and simple, which is why I work that little bit hard to make it as easy as possible for my customers to understand me.


Microsoft Certification Pros and Cons
This post is a follow-up to the Glasgow SQL server user group meeting on 17th January 2017.

Microsoft offer a very wide variety of certification you can get an overview here on this page,  There really is something for everyone. Like many things in life, there are good points and bad points to everything.  What l intend to do is share some experiences and maybe some tips picked up along the way.

Why should you study for a certificate?

There are as many different reasons as people taking the exams.  Many people have found it helps them to carry out their day job better.  When l started to study for Querying Microsoft SQL Server 2012/2014.  I was fairly certain that l had fairly good T-SQL skills, studying the course caused me to explore and learn in much greater depth topics l had just looked at yet not studied in depth. The next exam Administering Microsoft SQL Server 2012/2014 Databases has been of great benefit to helping me understand the internals of SQL server.  Allowing me to talk with more confidence to technical staff.  Having had to study and master various skills, this has given me more confidence to expand and increase my knowledge.

Whilst nothing can beat experience having a certificate can also improve the chances when looking for a new job.  If an interviewer is looking at two evenly matched candidates, one without a certificate one with a certificate.  Personally speaking, if l was the person making the decision then the person with the certificate would the one whom l would recommend getting the job.  By studying and passing the exam the candidate has demonstrated, initiative, self-motivation, and a genuine desire for technology.  At least that's my opinion.  In one case l know of a colleague whom l work with, who said one the reasons they were offered a job was due to the fact that had studied and passed two exams, which were relevant to the role they applied for.

Craig Porteous spoke to some recruiters he knows and asked them some questions about their view of certification.  So these thoughts are directly from those who make hiring decisions.

What weight do you put on certifications when hiring? 
1) A lot. With two otherwise comparable candidates, the one with certifications wins in my mind.
2) A lot, think it shows that candidates are focused in developing their career.
3) For a technical role I see it as essential 

Do you encourage the pursuit of certifications by your team?
1) Yes
2) Yes
3) Yes

Do you see any downsides/negative aspects to certifications?
1) No, none at all. 
2) Some of the accreditation's could be more hands on focused. 
3) Cost (retaining skilled up workers)

With staff who have completed certifications, do you see any differences in working practice etc to those who haven’t? 
1) Yes. People working towards certifications are more engaged with technology and tend to apply their learning in the work environment, sharing their knowledge and improving the overall team dynamic leading to improved productivity.
2) It really depends on the individual so don’t think it’s a fair comparison. Better way to look at it is how doing accreditations adds value to that person in regards technical ability and confidence.
3) Last example was a infrastructure type. Stuck him on a sccm course. A,month later our sccm world upgraded. A year later he left and now heads up sccm at dell secure works in the US . For me a benefit – I get a sccm upgrade from a capable engineer. They get a badge they can use to get their career upgraded. Win win.

One reason l have heard for not doing any certification is the cost.  My personal point of view on this is that l am investing in myself.  If l learn a new skill gain or learn some new techniques.  Yes, the company l am working for will benefit which is excellent news for them.  If l choose to move to another role with a different company, then those skills transfer with me.  Those skills l have invested both my time and (more importantly very often) my money in they are mine.  So my choice is to invest in myself as l believe that the return on investment (ROI) is excellent.

Resources
 
So you have decided to study for certification, what resources are there available?  The following list is just suggestions, based on largely on my experience and some others.

Books

 If you are planning to take the data platform exams then l would strongly suggest investing in the books for the relevant exam. For example, this one is for the 70-461 exam. The book covers all the topics that could be questioned in the exam. There is an accompanying CD has an electronic copy of the book and practice exam questions.   One series of books which I used whilst studying for 70-461 exam Querying Microsoft SQL Server 2012/14 was series titled 2 Joes.  The purpose of the books is to take a complete beginner through all the skills required to pass the exam.  Personally l found this is the best explanation of how to query xml data using TSQL.  They where for me worth the investment. 

Training Sites

CBT Nuggets - an excellent resource, the videos l viewed for my 70-462 exam were really helpful.  As part of the training package l signed up for included exam questions which l also found where excellent.  More expensive that other sites, you have access to all the courses.  Really worth considering if want to maximize your study time.

Udemy - with this provider you purchase one course at a time which allows you "lifetime access" to the course.  When the courses are on sale, the prices are low.  The quality of the courses can vary, so have a look a the reviews on the course before purchasing.  Personally, l found the 70-463 covered the basics well, on the other hand, it did not go into sufficient detail for the exam questions.

Pluralsight - this is a well know training site (ok l have heard the name in quite a few place) there are lists of videos for specific certifications.  The quaility of the courses l have watched were of a very high quality.

Microsoft Virtual Academy - its Free, which is not always a recommendation.  That said the quality of the courses that l have viewed have been excellent.   For the exams search for jump start videos, which are a really good jumping off point to start your studying.  So excellent hints and tips in the videos from people who passed and training people for the exams.

SQL Bits - apart from being one of the best SQL conferences in Europe.  The organizers have given back to the SQL community by recording some of the sessions and making the available for FREE on their website.  To find what you are looking for might take a bit of searching, most topics will have at least one video on them.

YouTube - there are a lot of videos uploaded on a wide variety of topics.  There will be some searching to find the topic you are looking.  On the downside the quality of content is variable.   Ranging from the excellent to the not so good.

Blog posts - again this will require some searching.  It has in my experience, been worth the time and energy required.  One author whilst studying for the 70-463 exam blogged about what she was learning as they went along.

Passing the exams

What is required to pass the exams?  Practice and lots of it!  One of the keys that are borne out by other people who passed the exams is the practice exams.  These exam questions will not be exactly like the exam questions.  What they will do is get in the way of thinking when doing the exams.  Reading exam questions to see the question, examining multiple choice questions for the correct answer.  Best way to get practice is to get hold of practice exam questions and take the exam.  There are a number of providers which will allow you to purchase them, MeasureUp, CBTNuggets and others. They will not be exactly the same as the exam questions you take when you go into the exam room. On the other hand they will give you practice at answering the questions.  If you pay attention to the score at the end of the practice exam you will also be able to see where you need to improve.

The questions are designed quite deliberately to test your knowledge, well you would not want them to be too easy ?  This article from Pluralsight has some excellent examples of the format of the type of question you will be answering in the exam.  If you are really interested in how the question are constructed and methodology behind them, this video from Pluralsight has an interview with someone who designs the exams.

All that remains to say if you have decided to study for a certification, good luck and happy studying.


“I get knocked down, But l get up again You are never gonna to keep me down…”
First, be warned there will be some spelling and grammatically errors.  This post is rough and ready as it comes.  Ok what is this about, l want to document and relate some of the challenges l will have overcome as the person who has started the Glasgow SQL server group.  Like most things l have attempted in my life, l have failed, that is not stopped me.  So l am going to try and share my experiences and lessons on starting / running an SQL server user group.  The sole reason is hopefully someone, somewhere will be helped by reading about my challenges (mistakes)

Ok first the bad news.  This evening was the first group meeting, time 7pm, location a coffee bar in Glasgow.  Attendees, me and my shadow (as in nobody).  Not the best start l agree, after waiting for 30 mins with my sign on the table, l decided to call it a night.  Not feeling in the best of moods l was pondering what next.  When some song lyrics popped in my head, “I get knocked down, But l get up again You are never gonna to keep me down…” the chorus from Tubthumping by Chumbawamba. So that’s what l listened to repeatedly on the way home.

So how did l get here, and what could l have done better?

Take action more quickly
- the idea occurred to me whilst at my first SQL bits conference three years ago.  It took until last years SQL Bits (2016) conference to tweet about it to see what interest there was, it was re-tweeted however no signs of interest.  On the upside, l met two people from Glasgow who hopefully will be involved the user group.

Do not wait – the longer l waited bigger the challenge became!

Make use of all the opportunities you can find – l did set up an event on Meetup.com that has been really positive (more of that in another post).  What l have not done is to contact all my professional contacts in Linked in to spread the word.  I could have used twitter more, to date l have not set up a facebook group, or set up an event on Eventbrite.com.

Positive’s – the two people whom l met at the 2016 SQL bits conference will hopefully be involved in future events and yes they let me know they could not make tonight.   The Trello board l have set up to record ideas and suggestions has worked really well.  There a wealth of ideas and suggestions of what we can do and suggestions on item to be actioned.  Whilst waiting for people come tonight l have made a list of actions l am going to take next.  That can wait till my next post.

Next steps – meetings will be arranged, scheduled and publicised in as many ways and places are possible.  I shall be making use of my contacts to spread the word of this event.
Parting thoughts.  

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.”
Theodore Roosevelt

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....


Thank you

This post is just a quick thank you to everyone who re-tweeted about the Glasgow SQL server group. Also to those who tweeted words of encouragement. It has been my privilege to see what a unique group the SQL server community is watching and hearing the encouragement and care passed on by this SQL server community. To receive, it is very humbling. So thank you to everyone it was and is much appreciated.


Dynamic date variables using M (Power Query Formula Language)

Whilst working on developing some reports for our Service desk l was asked if we could just return a dataset containing the last 6 and 12 months of data.  This is the type of request l would normally use a TSQL script to resolve.  In this case one of the purposes of this project was to allow us to dog food both PowerBI and also self service BI more generally.

The challenge was quite simple find a way to filter data in a dataset based on specific datetime. to this with a specific date-time using the UI is very simple.  In Figure 1 below l will filter the column titled Issue_Created, which contains date-time values.


Figure 1 - The field 'Issue_created' selected showing the filter icon

From the list l can use this to filter by certain dates as shown below in Figure 2.  This lead me to wonder if it was possible to filter by date ranges.


Figure 2 - Filter by specific date

Below is a video showing how to filter records that either equal or occur after specific date, see Figure 3.

Figure 3 - Creating a filter to show record on or after a specific date.

Once l had set up the filter l then opened the 'Advanced Editor' to look at the M Code.   The code that does the filtering looks like this 

#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Created] >= #datetime(2015, 7, 7, 5, 16, 52))

The syntax looked relatively simple and straightforward, one of the main challenges is that in the snippet above the datetime is static not dynamic.  At this point there was a tweak to the specification, my internal clients l have every faith in me :-).  What they required was based on today's date, get the first day of the month and then return first day of the month 6 months and 12 month before.  This is a pattern which l have done many times in TSQL, this was going to be a bit more of an adventure.

Long story short, after some experimenting l arrived at the following formulas

= Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()),-6)
First day of the month 6 months ago based on current date

= Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()),-12)
First day of the month 12 months ago based on current date.

Whats the next step?  Create a variable which will contain the DAX formulas that have created above.  The first step to open PowerBI desktop and go to 'Edit Queries', this will open the 'Query Editor' window.  In the 'Query Editor' window click on the 'New Source' button and select 'Blank Query' from the pull down list see Figure 4.


Figure 4 - Creating a new Blank Query to act as a parameter

Now the blank query has been created the next step is to name the parameter, the name that l use in the example is Previous_FDM_6Months.  Next l type in the DAX formula -> = Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()),-6).  Once thats done, then click the green tick to update and evaluate the parameter.


Figure 5 - Setting the parameter name and inserting the DAX formula

So now l have parameter which returns a datetime value based on current date and time.  The last step was to amend the part of the M code l had grabbed earlier to filter the dataset.  Below is the line from the M code l use in production

let
    Source = Sql.Database("A_Cloud_Database","Some_Data"),
    dbo_Some_Data = Source{[Schema="dbo",Item="SomeRandomData"]}[Data],
    #"Filtered_Rows_Last12_Months" = Table.SelectRows(dbo_Some_Data, each [Issue_Created] >= Previous_FDM_12Months),
    .........

The data is extracted from the database and filter only return data where the Issue_Created is equal to or greater than the datetime value returned by the parameter Previous_FDM_12Months.

A complete reference of all the datetime functions in power query lanague aka M can be found here https://msdn.microsoft.com/en-us/library/mt296608.aspx.  If you want to download a PowerBI desktop file with the functions you can download it here.