Call today 0845 643 6463

SQL Server

  • How to add calculations in SSAS cubes

    January 16th, 2011 in Analysis Services, Blog, Business Intelligence, SQL Server

    One of the most useful aspects of a Business Intelligence system is the ability to add calculations to create new measures. This centralises the logic of the calculation into a single place, ensuring consistency and standardisation across the user base. By way of example, a simple calculation for profit (Income – Expenditure) wouldn’t be provided by the source database and historically would be implemented in each and every report. In a data…

    » Read more

  • OLAP Cube Documentation in SSRS part 3

    September 29th, 2010 in Analysis Services, Blog, Business Intelligence, Reporting Services, SQL Server

    This is the 3rd and final post in this series of blog posts, showing how you can use SQL Server Reporting Services (SSRS), DMVs and spatial data to generate real time automated user guide documentation for your Analysis Services (SSAS) OLAP cube. Part 1 – Creating the DMV stored procs Part 2 – Create the SSRS reports Part 3 – Use spatial data and maps to create a star schema view Download Source Code UPDATE: I presented a 1…

    » Read more

  • OLAP Cube Documentation in SSRS part 2

    September 27th, 2010 in Analysis Services, Blog, Business Intelligence, Reporting Services, SQL Server

    In my previous post I described how to create a number of stored procedures that use Dynamic Management Views (DMVs) to return the metadata structure of an SSAS 2008 OLAP cube, including dimensions, attributes, measure groups, BUS matrix etc. In this post I’m going to use those procs to create a set of SSRS 2008 reports that will serve as the automated documentation of your cube. I’m going to make the following assumptions:…

    » Read more

  • OLAP Cube Documentation in SSRS part 1

    September 25th, 2010 in Analysis Services, Blog, Business Intelligence, Reporting Services, SQL Server

    Being a business intelligence consultant, I like to spend my time designing data warehouses, ETL scripts and OLAP cubes. An unfortunate consequence of this is having to write the documentation that goes with the fun techy work. So it got me thnking, is there a slightly more fun techy way of automating the documentation of OLAP cubes… There are some good tools out there such as BI Documenter, but I wanted a way of having more control over…

    » Read more

  • Leap before you can land

    July 26th, 2010 in Blog, SQL Server

    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

    June 9th, 2010 in Analysis Services, Blog, SQL Server

    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

    May 18th, 2010 in Analysis Services, Blog, Integration Services, Reporting Services, SQL Server

    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

    April 15th, 2010 in Blog, SQL Server

    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

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

    March 17th, 2010 in .Net, Blog, SQL Server

    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

    March 17th, 2010 in Blog, SQL Server

    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