Blog

  • 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…

    » Read more
  • 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? Monday 21st May –…

    » Read more
  • Automating T-SQL Merge to load Dimensions (SCD)

    This is the 3rd post in the Frog-Blog series on the awesomeness of T-SQL Merge. Post 1: Introduction to T-SQL merge basics Post 2: Using T-SQL merge to load data warehouse dimensions In this post we’ll be looking at how we can automate the creation of the merge statement to reduce development time and improve reliability and flexibility of the ETL process. I discussed this in the 2nd half of a talk I gave at the UK technical launch of SQL…

    » Read more
  • Map Postcodes in SSRS Reporting Services

    I was asked the other day for some help on how to plot data by postcode on an SSRS spatial map. I’ve done this a few times, initially for a presentation I gave back in 2010 and most recently a couple of weeks ago when I wanted to analyse the SQLMidlands membership. It occurred to me that despite posting a number of blogs using spatial data, I’ve not done one on the basics of plotting postcode data. So here we go… Scenario You…

    » Read more
  • 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)…

    » Read more
  • Using T-SQL Merge to load Data Warehouse dimensions

    In my last blog post I showed the basic concepts of using the T-SQL Merge statement, available in SQL Server 2008 onwards. In this post we’ll take it a step further and show how we can use it for loading data warehouse dimensions, and managing the SCD (slowly changing dimension) process. Before we start, let’s have a quick catch up on what an SCD is… What is a Slowly Changing Dimension (SCD)? If you want a full explanation of…

    » Read more
  • Introduction to T-SQL Merge Basics

    A number of Frog-Blog posts over the next couple of months are going to make heavy use of the awesome SQL Server MERGE statement, introduced in SQL Server 2008. I thought it best to write an introduction post to provide the basics and groundwork for future posts. So what does the Merge statement do? Merge upserts data into a table. If, like me, you find the word ‘upsert’ more than a little ridiculous, we can scrap it in favour of a slightly…

    » Read more
  • Fast record count in SQL Server using sp_spaceused

    Just a quick tidbit today, but a very useful one. Ever need to do SELECT Count(*) on a very large table? It can take ages. Instead, use sp_spaceused EXEC sp_spaceused 'MyTable' It returns you a useful result set like name rows reserved data index_size unused MyTable 19690290 13637488 KB 3001688 KB 9986416 KB 649384 KB Not only does it also give you extra useful information about the table, but it comes back virtually instantly. Columns:…

    » Read more
  • PowerPivot Vs QlikView 101 – SQLBits Video

    PowerPivot Vs QlikView 101 – SQLBits Video The video is now available from my PowerPivot and QlikView talk at SQLBits 9 in Liverpool in September 2011. You can download or watch the video here. In this 1 hour session I create interactive dashboards from scratch in both PowerPivot and QlikView, showing how to set up the data model, overcome common pitfalls and build the dashboards. I create almost identical dashboards in both systems and…

    » Read more
  • 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! I 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…

    » Read more