Blog

  • Leap before you can land

    Leap before you can land… Research scientists at the Southern Illinois University have concluded that primitive frogs learned to jump before they learned to land. The result being a rather inelegant crash landing. I’m sure the frogs were happy that they achieved their goal of getting from A to B, but I do feel sorry for them having to endure the presumably painful landing, not to mention the indignity of having the rest of the pond witness…

    » Read more
  • SQL User Group Session 24 June 2010

    I’m excited to be presenting another session to the South Wales SQL Server User Group. On Thursday 24th June 2010, Eversheds in Cardiff are kindly hosting the event, to run from 18:45 to 21:00. The event is free, and you’ll even get pizza thrown in – what more can you ask for? Oh yes, some BI content… My session will cover data warehouse modelling, including a number of hands on business case studies including…

    » Read more
  • SQL Server 2008 R2 – PowerPivot and Master Data Services

    Purple Frog spent a very interesting day at Microsoft last week, at one of their many events promoting the launch of SQL Server 2008 R2. Rafal Lukewiecki presented an entertaining (as always!) and informative series of talks covering the release, focusing on the enhanced Business Intelligence tools available. The primary changes to note are Power Pivot – An in memory, client side add-in to Excel, that allows users to create virtual cubes on…

    » Read more
  • SQLBits and Microsoft BI conferences

    Following the launch of SQL Server 2008 R2 this week, the ever dedicated SQL Bits team are putting on the next installment tomorrow with SQL Bits VI – ‘the Sixth Sets’. The Purple Froggers will of course be there, hopefully in time for a pre-conference bacon butty! Also worth mentioning is ‘Microsoft Solutions for Business Intelligence’, a seminar that Microsoft are holding at their Reading campus on May 12 2010.…

    » Read more
  • Clearing SSRS Query cache

    When developing SQL Server Reporting Services (SSRS) reports, BIDS caches the query results when you preview the report. This cache is then used next time you run a preview. This has the benefit of speeding up report development, but it does cause a problem when you want to test changing data. A simple way of forcing the cache to refresh is to open the folder containing the .rdl report files, and delete the corresponding .rdl.data files. The…

    » Read more
  • Excel Cube Pivot drillthrough limited to 1000 rows

    When browsing a cube using Excel 2007, you can drillthrough the measures to display up to 1000 rows of the transaction level source data. I often get asked whether this limit of 1000 rows is configurable – well the good news is yes it is. There is an option in the actions tab of the BIDS cube designer which allows you to specify the maximum rows, but helpfully this is ignored by Excel. Instead, you have to set it in Excel when you create a…

    » Read more
  • Loan Amortisation in SQL Server (PMT, FV, IPMT, PPMT)

    Whilst designing a data warehouse for a banking client recently, I needed to calculate projected future loan payments (including breaking this down by interest and capital payments) for every customer throughout the life of the loan. In Excel this is a pretty simple job, as Microsoft helpfully provide a number of functions to do just that (namely PMT, FV, IPMT and PPMT). In SQL Server however we do not have the luxury of having ready made…

    » Read more
  • SQL Bits 6 – Registration Open

    For anyone interested in SQL Server, the unmissable SQL Bits conference is returning for round six. “SQL Bits – The 6th Sets” is being held in central London for the first time, and unlike previous conferences it will be for one day only on Friday 16th April 2010. It’s being held as part of the SQL Server 2008 R2 release, also happening that week in London. Registration is now open, you can find out more at the SQL Bits…

    » Read more
  • MDX Sub select Vs WHERE clause

    I’ve just read an interesting thread on the SQL Server Developer Center forum, regarding how to filter results. Specifically the difference in MDX between using a subselect SELECT x on COLUMNS, y on ROWS FROM ( SELECT z on COLUMNS FROM cube)) or using a where clause SELECT x on COLUMNS, y on ROWS FROM cube WHERE z In a simple query they produce the same results, but what is the actual difference? You can read the full thread here, but to…

    » Read more
  • Data Warehouse Presentation

    Purple Frog is presenting a session on data warehouse design concepts at the South Wales SQL Server User Group on Thursday 25th February 2010. If you’re in the area and want to come along you can register for free here. Eversheds are hosting the event in their Cardiff office. Adam Morton will be demonstrating an ETL control framework in SSIS, and Alex Whittles will be discussing the concepts of data warehousing and the fundamental…

    » Read more