Archive for the ‘SQL / BI’ Category

Shrinking a fixed-size VHD file

June 18, 2012 Leave a comment

In playing around with the new Azure Virtual Machines I wanted to upload an existing demo VM. It was a dynamically expanding VHD capped at 250Gb, but Azure only accepts fixed-size disks, and I certainly didn’t want to upload anything larger than was necessary. So to convert and shrink the drive I had to go through a slightly convoluted process:

1. Compact the file through Hyper-V, and then Convert it to a Fixed size (use the Inspect Disk option in the right-hand pane of Hyper-V management console)


2. Boot into a Win PE environment in the VM, so you can get to the DiskPart utility. You need to select the relevant volume (SELECT VOLUME x), then run SHRINK to take the volume down to the size required. Bear in mind you probably want some extra overhead for growth again! After this you can turn the VM off again.

3. Download VHDResizer from, this allows you to resize the VHD:


Categories: SQL / BI

Microsoft Project Barcelona

October 20, 2011 Leave a comment

One of the internal projects I’ve been following for some time has gone public and are now blogging about their development process. It’s a really interesting read from a dev point of view, but the product itself is also very clever. It’s addressing the ‘impact analysis’ and ‘data lineage’ world.

Imagine you’re a DBA, and you want to make a change to a column in a table. What views are connected to that table? What ETL packages refer to that view? What data source does that package load? Are there cubes built from that warehouse? Are there reports using the cube? Are their Excel spreadsheets connected to that report?

This chain of data is complex and for most organisations ever-changing. Project Barcelona aims to address this by crawling all these objects and building a node graph showing the movement of data throughout. They explain it much better than I can: take a look at their blog here:

I love that they’re publishing previews of the user experience online through Azure as well, at


Cool stuff.

Categories: SQL / BI

Combining SSAS’s DefaultMember and Roles

June 16, 2011 Leave a comment

I’ve been asked if a cube could be exposed to users so everyone gets ‘their’ view of data by default, but is able to compare with others if they wish. a combination of setting the DefaultMember on a per-role basis seems to be the answer. Take an AdventureWorks example: if I’m a sales manager responsible for the UK, most of the time I only want to report on that. But, come annual review time I probably want to see how I compared to my peers. If there’s a ‘UK Manager’ role set up in the cube, to which I belong, when I build a pivot table I see just values for the UK by default:


But, if I choose to include Countries in the query, everything shows up:


To set up the role, choose the appropriate MDX statement in BIDS:


Make sure you’ve got the corresponding Attribute chosen in the drop-down and specified in the MDX (Country in this example).

Categories: SQL / BI

BI Sessions at TechEd/BI Conference

June 24, 2010 Leave a comment

A couple of weeks ago Microsoft hosted TechEd 2010, which included the BI Conference. Over 55 hours of content has been made available, and Kevin Lief, one of the chaps responsible for BI channel development in the UK, has collated a great document with links to all the most relevant BI sessions. I’ve shared it on my SkyDrive here. One particularly good session is from Donald Farmer, giving a 101 overview of what BI is and why it’s of use.

Categories: SQL / BI

When graphs go wrong!

As always, Stephen Few has picked up on a fantastic mis-representation of the facts through data visualisation, in this blog post it’s levelled at BP and their efforts to plug up the oil slick disaster. Misleading graph is misleading!

Categories: SQL / BI

Information Delivery • Reg Whitepapers

Last week I took part in a really interesting discussion, hosted by The Register, around the changing world of Business Intelligence. We discussed some of the challenges of traditional ‘Big BI’ and the shift towards more agile and user-focused BI to support front-line decision making. I tried to bring up as many real-world examples and tidbits of good practice I’ve come across. It was filmed in their London studios and you can view it online here: Information Delivery • Reg Whitepapers. (signup required)

Categories: SQL / BI

SQL Server 2008 R2 TechDays Presentations

On April 21st, my colleague Matt Stephen and I presented at the SQL Server 2008 R2 TechDays event in London. We covered PowerPivot and Reporting Services, looking at how you can build complex data analytics and produce rich reports with some very simple tools. We’ve had really good feedback from the event, and recordings of the presentations/demos (along with excellent sessions from the other speakers on the day) are now online. Go to and click on the ‘Resources’ tab to view all the videos, or go direct to ours here: PowerPivot and Reporting Services.

Categories: SQL / BI