0845 643 64 63


SQLBits X Video available

SQLBits X Video Now available

The video of my talk at SQLBits X is now available on the SQLBits website here. The talk was focused on presenting the results of my MSc Business Intelligence dissertation, comparing the performance of different methods of using SSIS to load data warehouse dimensions, specifically type 2 SCDs.

The talk also covers a comparison of the performance between traditional hard disks and solid state storage systems such as Fusion IO.

I then present a method of using the T-SQL Merge statement to automate a significant part of the ETL process.

You can find the code behind the demos on various recent Frog-Blog posts, and there is more to come, so look back soon!


PASS BI Virtual Chapter Talk

For those that would rather hear the talk presented live, or want to ask any questions, please join me at a repeat of this talk over Live Meeting for the PASS BI Virtual Chapter on Thursday 28th June, at 5pm UK time, 12pm EDT (US). You can find the details on the PASS BI chapter website here

SQL Relay Agenda Announced

SQL Relay: Free, Full Day SQL Server Events

If you’ve not heard the news yet, then where have you been hiding?! With SQLBits X such a phenomenal success, but already a distant memory, we’re all looking for the next SQL Server community event to learn, network and enjoy.
The 2012 SQL Relay, following on from the great inaugural events last year, have now been announced and registration is open.

Where are they?

Who’s speaking?

Each event is different, but with a common theme. The awesome line-up of speakers include:

As well as sessions from Microsoft partners and sponsors.

How big are these events?

Each of the 5 venues have different capacities, but in the region of 100-120 attendees per event.

What’s the format?

Each event has a morning session, in which the Microsoft speakers and partners will cover the technical challenges facing IT, and an overview of SQL 2012 and its new features that can help.
The afternoon sessions will be technical deep dives by the selection of World class MVPs.
Some events will also have an evening session, which will extend the technical sessions until 9pm, to cater for those who can’t get time off work!

You can come to 1, 2 or all 3 sessions, just mark your preference during registration.

How can this be free?!

Thanks to the kind generosity of our sponsors, who include:

Gold Sponsor Microsoft
Silver Sponsor Fusion IO
Strategy Companion
Bronze Sponsor Microgen
Purple Frog

Say no more… Sign me up!

Thought you’d say that – register at SQLServerFAQ.com

I’m delighted to be running the Birmingham event, so look forward to seeing you there!

SQLBits X talk – Loading Data Warehouse Dimensions

I’m thrilled to have been asked to present another session at SQLBits X, especially so as it’s set to be the biggest and best Bits yet.

It’s the official UK launch of SQL Server 2012, and as such there’s an impressive collection of Microsoft folk attending and presenting, alongside the MVPs and internationally renowned speakers that we now come to expect at SQL Bits.

The Saturday is now sold out, but (as of writing this) there are still some places available on the Thursday and Friday, but these are unlikely to stay available for long so head to www.SQLBits.com and get your name down asap.

Loading Data Warehouse Dimensions

The session that I’ll be presenting will cover different techniques of using SSIS to load data into data warehouse dimensions. Specifically a performance comparison of the methods when loading Type 2 slowly changing dimensions, as well as showing the impact of using solid state storage such as Fusion IO.

I’ll also be covering the details of how to use the T-SQL Merge statement to take care loading dimensions, and showing why it’s now my preferred approach.

SQL User Group Community

I’ll also be there representing SQLMidlands, a SQL Server user group in Birmingham. User group leaders from around the UK will be presenting a session on Saturday lunchtime, as well as staffing the community corner.

If you’re interested in getting involved with your local user group, if you just want to find out more, or are at your first SQLBits and want a friendly face to chat to – find me & the other UG leaders at the community corner.

Look forward to seeing you there!

SQLBits 9 Session – PowerPivot and QlikView 101

Thank you to the SQLBits committee, speakers, helpers and attendees, for making SQLBits 9 one of the best yet. What a great example SQLBits is of the power of the SQL Server community coming together to help each other, and enjoy a few beers in the process!

QlikView PowerPivotI was delighted to have my “PowerPivot & QlikView 101” session chosen by the committee, which I presented on Saturday afternoon. Thank you to all those who attended, great to see such a good number there. If you haven’t yet submitted your feedback for my session, and the event as a whole, please do take the time to do it. It helps everyone improve the quality of the event for next time.

For those who atttended my session, here are the slides, with a summary of the results and code etc.

Slides: Download the PowerPivot & QlikView 101 slide deck here

QlikView Script: Download the QlikView Script here

PowerPivot Workboook: Download the PowerPivot workbook here

Session Video: The session recording isn’t yet available, I’ll post a link here when it’s ready.

QlikView Expressions

Sales Amount Visual Cue for the green shading
   Upper: (SUM(TOTAL SalesAmount) / COUNT(TOTAL SalesTerritoryRegion))*1.2
   Lower: (SUM(TOTAL SalesAmount) / COUNT(TOTAL SalesTerritoryRegion))*0.8

Sales Amount blue bar Guage maximum
   max(ALL Aggr(SUM(SalesAmount),OrderMonth))

Sales Amount Year to Date

Sales Amount Previous Year
   SUM({<OrderYear={‘$(=Max(OrderYear)-1)’}>} SalesAmount)

PowerPivot DAX Expressions

Sales Amount Year to Date
   =TOTALYTD(SUM(FactInternetSales[SalesAmount]), DimOrderDate[FullDateAlternateKey], ALL(DimOrderDate))

Sales Amount Previous Year
   =CALCULATE(SUM(FactInternetSales[SalesAmount]), SAMEPERIODLASTYEAR(DimOrderDate[FullDateAlternateKey]), ALL(DimOrderDate))


PowerPivot Results

PowerPivot Dashboard


QlikView Results

QlikView Dashboard

If you have any questions about any of this, please get in touch.


New UK SQL Server User Groups

What an exciting few weeks we’ve got to look forward to!

SQL Bits starts on Thursday this week which promises to be another fantastic event. I’m speaking on the Saturday, presenting a session on QlikView and PowerPivot. When I’m not speaking I’ve got a list of duties as long as my arm, from marshelling to room monitoring, and of course representing the Community Corner.

If you’re interested in SQL events local to you, then come and say hi to us on the Community Coener stand – a number of user group leaders in the UK will be on hand to provide any info you want regarding the SQL community, and point you towards events happening in your area.

After SQLBits, we’ve got the SQL Relay to look forward to. Every SQL Server user group in the UK is putting on an event between 3rd and 5th October 2011, with a grand finale event in London at Microsoft’s Cardinal Place on the 6th. Every event has great speakers, from MVPs to Microsoft employees, and in London we have renowned author and blogger Itzik Ben-Gan. We also have a host of fantastic prizes to give away at every event.

What makes all of this even better is that there are a number of new user groups launching during this week, including Maidenhead, Cambridge and Essex. There’s a group near you so head over to www.SQLServerFAQ.com and get registered!

Look forward to seeing you at one of the events!

Midlands SQL Server User Group now a PASS chapter

Back in September 2010 I spent another evening with the Cardiff SQL Server User Group, run by Adam Morton. Having got so much out of the Cardiff group since its inception, both as an attendee and as a regular speaker, it struck me as a shame that there wasn’t a group represting the SQL community in my area of the UK, the Midlands.

Following a few conversations with Adam Morton, Tony Rogerson, Chris Testa-O’Neil and others, Hollie (my patient and supportive Wife and business partner!) and I decided to launch a new SQL Server user group for the Midlands.

With a few months of planning, we invited Allan Mitchell and Neil Hambley to speak at our first event in March 2011, held in a fabulous pub in central Birmingham, the Old Joint Stock. We’re now planning our third event to be held at the start of October as part of the national SQL Relay event, and the group is growing from strength to strength with 30 attendees at the last meeting.

Now, in August 2011, we’re delighted that we’ve been accepted as an official PASS chapter (The Professional Association of SQL Server). This will help provide global recognition of the group, increasing its visiblity and hopefully generating even more awareness and attendees.

To mark the event, we’ve launched a new website for the group www.SQLMidlands.com

We’re incredibly lucky to have such an active, diverse and friendly SQL Server community in the UK. You only have to look at the phenominal success of the SQLBits conference as well as the user groups and SQLSocial. I’ve personally gained a lot from being a part of it, in terms of learning skills, networking, presenting talks and making new friends. I’d like to take this opportunity to thank the community leaders for all their hard work, especially the organisers of SQLBits, who go above and beyond the call of duty every 6 months.

And it’s not all about SQL Server, the SQLSocial event last week was pretty much just an excuse to go to the Great British Beer Festival and celebrate Simon Sabin’s birthday!

If you’ve not got involved before, ask yourself these questions:

  1. Do you want to learn more about SQL Server?
  2. Do you want to meet other SQL Server beginners or professionals, and discuss tech, career, beer, or anything else that springs to mind?
  3. Are you new to this and just want to know what’s out there?
  4. Do you want an opportunity to present a SQL Server based talk to like minded people?

If you answer yes to any of the above, then here are some resources for you

SQLServerFaq – All SQL Server User Groups in the UK

SQL Bits – Europe’s largest and best SQL Server conference, held every 6 months

SQL Social – Does what it says on the tin!

SQL Pass – SQL Server conference held in Seattle, U.S.A.

SQL Rally Nordic – SQL Server conference in Sweeden

The above events are not only top quality, with world leading speakers, some are also free. The user groups, SQLSocial and the community day of SQLBits are totally free – where else can you get that kind of training without paying a fortune?!

You’ll even get to hear me present another talk at SQLBits, I’m talking about PowerPivot and QlikView, with a level 200 introduction into self service BI and how these two products compare.

With so much going on in the UK and abroad, and with such quality, help yourself as well as the community – get yourself registered for some of the events.


SQL User Groups and SQLBits

SQLBits 9

Submit a session for SQLBits

It only seems like yesterday since the SQLBits crew put on their awesome 8th conference in Brighton. The next conference has just been announced, SQLBits will move to Liverpool for SQLBits 9 – “Query Across The Mersey” (…Really?!) between September 29th and October 1st.

Registration’s not yet open, but you can find out more at www.SQLBits.com

I’ve already submitted two sessions to present, “PowerPivot & QlikView 101” and “Advanced data modelling for data warehousing and cubes”. Make sure you vote for them when voting opens! You can read full summaries of all sessions submitted so far here.

SQL User Groups

We’ve also got a busy time coming up with the UK SQL Server user groups. I’ll be running the next Birmingham user group the week after SQLBits, on Monday 3rd October. The agenda and speakers have yet to be set, but it should be a goodun!

I’ll also be speaking at the Southampton user group in 2 weeks time, on Wednesday 6th July. I’ll be presenting my 3 part blog post on automating SSAS cube documentation. Matt Whitfield will also be presenting, so it should be a good night. Now stop reading this and go and register!!

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.


Alex Whittles
Jeet Kainth
Jon Fletcher
Nick Edwards
Joe Billingham
Lewis Prince
Reiss McSporran
Microsoft Gold Partner

Data Platform MVP

Power BI Sentinel
Frog Blog Out