(scottish) sql bob blog


Some thoughts of your typical data shepard / data groomer / data dance teacher sort of person.

“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

    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.