Category Archives: Steff

Permalink to single post

Organising SQLRelaySpeaking at SQLRelay
After dipping Cardiff’s collective toes into the world of local SQL Server conferences, we’re doing it again in November.  We’re taking registrations, and volunteers are always welcome.  If an all day conference is a bit too much, why not try out a lunchtime or evening event?

There are 9 other events around the country which you can attend as well as / instead of Cardiff, including Bristol where I’ve the privilege of speaking along with some excellent folk like Klaus Aschenbrenner.

These are awesome events which people have worked hard to deliver for FREE, whilst having world-renowned speakers delivering top notch content, so whether you attend or not, please spread the word and encourage people to take their learning and personal development into their own hands.

Permalink to single post

R for database and Excel people

What is R?

R is a statistical language for doing all sorts of analytics based on many different types of data and it’s also an open source platform that allows people to extend the base functionality.  More details are available from the horse’s mouth.

How can I give it a go?

Download R and RStudio an awesome development environment for R.  There is also an excellent online R learning site.  I do not recommend sticking with just R – we’re used to a lot more convenience and good development bits and bobs like IntelliSense and Rstudio really delivers.

» Read more

Permalink to single post

Marketing for SQLRelay – two weeks in

eventbrite results

Further to the last post introducing my trials and tribulations, and a hectic week or two we’ve made excellent progress on the Relay.  I’ve enlisted Mark (@tsqltidy) the chair for the Relay and others to assist with the twittering and other activities which has really held me reduce my workload substantially.

All ten venues are going ahead:

Location Date
Reading Monday 11th Nov 2013
Southampton Tuesday 12th Nov 2013
Cardiff Wednesday 13th Nov 2013
Birmingham Thursday 14th Nov 2013
Hertfordshire Friday 15th Nov 2013
Newcastle Monday 25th Nov 2013
Manchester Tuesday 26th Nov 2013
Norwich Wednesday 27th Nov 2013
Bristol Thursday 28th Nov 2013
London Friday 29th Nov 2013

» Read more

Permalink to single post

It’s a nightmare when I’m trying to find out what’s clogging up my hard drive, particularly now that I have an SSD and can no longer be quite so lazy and sprawling with myriad files and downloads.  This is the case even after moving most contents to Dropbox and putting this on my slow 1Tb harddrive. It can get really tiresome to be running out of space and having to trawl through, right-clicking on different folders.  It was boring but it was how you did it, well now I am enlightened, and now I don’t have to pour my time down the drain.  

(more…)

Permalink to single post

Marketing for SQLRelay – In the beginning

After organising SQLRelay for June 24th in Cardiff, as part of the national series of 8 events.  We’re gearing up for November with the aim of being able to capitalise on the growing knowledge of SQL Server 2014 CTP and pushing the Relay into a less busy part of the UK community schedule.  The difficulty is that where we had more than 6 months to prep for the previous Relay, this time round we had less than 5.  What this means for me, is not only do I want to run a bigger and better Cardiff event, but I also (being a glutton for punishment) took on spearheading the marketing efforts for the whole shebang.

Details will be released next week on the launch, but given my lack of knowledge about anything social media this has already been a major undertaking for me, and I thought it might be of value for me, future me, and my dear readers to compile information and learnings as I go along so that it’s easier to implement in future for other marketing endeavours.  It also provides an area for discussion. » Read more

Permalink to single post

Dynamic named range generator

Why do I use dynamic named ranges?

Where I work, most reports are exposed via a web front-end and Excel can create an external connection and retrieve the information.  This is much safer than using direct database connections in workbooks.  A problem with web queries though is that they cannot be converted to Tables in order for referencing columns and the dataset as a whole to be made easier.  As a result, dynamic named ranges are a necessity for producing easy to develop and manage spreadsheets since the volumes in the raw data can change over time.

How I save myself time

A raw data table with 20 columns will take a long time to create the named ranges for, given that I want:

  1. A dynamic range covering the headers too for pivot tables
  2. A dynamic range without headers for vlookups
  3. A dynamic range for each column without headers

I use a macro, assigned to a nice button on my ribbon, to generate all the relevant ranges.

What are the special considerations?

Structure – raw data tables should ALWAYS be set up in a specific way – with the Primary Key on the left hand side and always filled in, with no empty rows or columns

Special characters – range names can’t contain special characters.  The VBA uses the RegEx functionality to strip these out.

Numbers – range names can’t have numbers either.  We can’t just strip out the numbers like we would special characters because they might be important like Grade1, Grade2 and Grade3 and collapsing them all to the name Grade would be a problem.  Instead, the macro converts all numbers to the corresponding letter in the alphabet.

How much the data will grow?  By default I set the macro to use 10 times the number of records present when I run the macro – if it’s already bigger than 25k rows, the number will need to be reduced, and if I don’t think 10 times the number will be adequate, I’ll increase the number.

» Read more

Permalink to single post

RegEx functions in VBA

Regular Expressions (RegEx) is a common string processing technique for handling strings that conform to patterns, as opposed to fixed strings.

Perl Problems

XKCD Perl Problems

It is an excellent set of functionality that is available in most programming languages, and even in SQL.  It is however not readily available in Excel or VBA.  This has it downsides if you’re trying to complex string matching and extraction, so in my personal workbook, I include the RegEx functions available at http://www.tmehta.com/regexp/ .  These are utilised in my dynamic named range generating macro and have been a real life saver when faced with three different tabs of manual-entry logs that need the finding and identification of file references used in various places.

 

Summary:

1) Learn about RegEx at http://www.regular-expressions.info/

2) Use it in your coding language of choice, and feel frustrated

3) Bring the frustrations and the benefits into VBA and Excel with http://www.tmehta.com/regexp/

Permalink to single post


Follow up resources / places to go for way more detail:

Stairway to SSIS

MSFT SSIS tutorial package 1

MSFT SSIS tutorial package 2

MSFT SSIS tutorial package 3

SQLCat SSIS best practices

Bob Duffy SSIS best practices

Connection Strings

The BOL for SSIS 

Design Patterns book

Design patterns 24HOP vid

Permalink to single post

Dynamic named ranges – the basics

Whoah nelly, what’s a named range first of all let alone a dynamic one?

A named range is a shorthand or alias for a set of cells in Excel.  These can be created easily by simply selecting one or more cells and using the name box to give it whatever name you feel relevant.  This alias can then be used in formula to make something much more insightful like =A1*VAT as opposed to =A1*0.2

Excel nav bar

Excel name box

For more info on these, I can highly recommend the Contextures blog and video:

Ok, so what’s a dynamic named range?

A dynamic named range is one that can grow or shrink dependant how much data there is, instead of a fixed size.

What’s the point?

This allows you to build pivot table connections that always select the relevant amount of data, it allows you create dropdown list for validation that can be easily updated, and it allows you to produce cleaner formula.  There are many other uses including producing charts with the last X entries showing only.

So, how do I do it?

You need two functions COUNTA and OFFSET

  • COUNTA (as opposed to just COUNT which will only count numbers) will work out how many rows (or columns) need to be included in the range
  • OFFSET will build the range of cells needed based on a starting cell and some info how the range needs to be built

A dynamic named range formula looks like

=OFFSET(Sheet1!$A$1, 0,0, COUNTA(Sheet1!$A$1:$A$1000))

The start point

How many rows and columns it needs to move

How many rows it needs to extend – no need to use $A:$A since this uses unnecessary processing power

Included to allow the formula to be hardcoded to a specific range in a sheet

 

 

« Older Entries
%d bloggers like this: