Blog

  • Find first order date in MDX

    I had an interesting question the other day; “how do I find the date of the first order/activity for a given customer/employee/product etc in MDX”? Well you’ll be pleased to know that it’s pretty simple. The sample code bellow will work with the AdventureWorks DW 2008R2 cube. First of all we need to filter the date dimension to contain only those with orders. Then take the first item of the resulting set, then find its…

    » Read more
  • Still time to register for SQL Saturday 162 in Cambridge

    Just a quick reminder, if you haven’t registered already then get yourself to the SQL Saturday website and register for the UK’s first ever SQL Saturday, in Cambridge on Sept 8th 2012. The presenter/session line-up is awesome – check out the schedule here. I’ll be presenting a talk at 3:50pm on a better way to load data warehouse dimensions. I’m also proud to say that Purple Frog are sponsoring the event. There…

    » Read more
  • Speaking at SQL Saturday 162 in Cambridge

    I’m thrilled to have been selected to speak at the UK’s first SQL Saturday event – A big thank you to the organisers, I’ll already buzzing about it! SQL Saturday #162 is being held in the fabulous city of Cambridge, with two days of awesome content. Pre-Con Training Day: Friday 7th September 2012 Free SQL Saturday: Saturday 8th September 2012 Full details on the SQL Saturday website here. I’ll be reviving my talk…

    » Read more
  • MSc Dissertation – Performance of Loading SCDs in SSIS

    Well after 3.5 years, I’ve finally completed my MSc Business Intelligence – hoorah! And to reward the time, effort and increased grey hair, they saw fit to give me a merit as well. During the last year I’ve been writing a thesis investigating the performance characteristics of loading data into data warehouse dimensions. Specifically loading Type 2 SCDs using SSIS. For those who have followed the previous posts and my…

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