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.
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.
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:
|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|
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
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:
- A dynamic range covering the headers too for pivot tables
- A dynamic range without headers for vlookups
- 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.
Regular Expressions (RegEx) is a common string processing technique for handling strings that conform to patterns, as opposed to fixed strings.
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.
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/