February 2011 - Posts

Just what SQL Azure needed? A compelling business feature–Reporting
22 February 11 10:36 AM | GavinPayneUK | with no comments

Those of us who let ourselves be the target of Microsoft’s marketing will be very aware that SQL Azure has been around for a year now and that a (very) small number of people are using it for their cloud based apps and a few more as a development test bed.

In summary, the reason for this is because what we’ve yet to see for SQL Azure is:

  • mass adoption by developers therefore taking the service into production
  • it become a default deployment option for commercial shrink wrapped applications
  • a list of compelling quick win features which makes SQL Azure a no-brainer for businesses to migrate to

As with all new technologies and versions there are generally three major reasons why people generally adopt them:

  • They have to – for example Microsoft announce the end of support for the current version or the only version you can buy is the latest
  • They want to – the developers on a project might, given the chance,  want to use the latest version with the newest features because they can
  • They need to – a feature the business must have might only be available in the latest version

So looking at the two sets of three points above we can see that SQL Azure has yet to have a compelling ‘must have’ feature which has driven noticeable adoption, although to be fair its still a developing product.

SQL Azure Reporting

However, recently we’ve seen talk about the CTP of SQL Azure Reporting.  According to hearsay, the most requested feature of SQL Azure was a  cloud version of the on-premise Reporting Services.  And when you consider what a business  user has to go through to get Report Designer and web based reports available to them you can see why a black-box cloud based reporting service is big ask from the data community.

In the future, data analysts won’t need to worry about servers, installations or configurations, they’ll all be done in bulk by Microsoft in the Azure cloud.  It’ll also remove the dependency the business analysts have on the IT department.  Could life be any better for those people?

A big but

But, and its a big but, the CTP of SQL Azure Reporting lists “SQL Azure databases” as its only supported data source, unlike on-premise installations of SSRS which can pull data from almost anywhere.  This will be a big hurdle if the requirement for your reporting data to be in the cloud stays.  The question is, will businesses be willing to jump that hurdle?

Will SQL Azure Reporting’s requirement to have your reporting data in the cloud be the compelling event which kickstarts the migration of on-premise data to the cloud?  The tools are already there,  for example SQL Azure data sync, however the data businesses are most likely to want to report on will be their most sensitive data, so they will also have to ask themselves sooner than they expected about how much they trust Microsoft’s cloud services.  That isn’t a technology driven decision. 

So what next?

If adoption of SQL Azure Reporting is to go beyond the CTP businesses need to answer these questions:

  • Am I happy with all of my reporting data being in the cloud?
  • Am I happy I can get my data, on a regular basis, to the cloud? (Let’s hope its' only MBs a day not GBs)
  • Does my business understand the consequences of the two questions above?
The changing shape of the Business Intelligence marketplace: Applications vs. Platforms
15 February 11 02:28 PM | GavinPayneUK | with no comments

I recently read the latest Gartner Magic Quadrant for Business Intelligence (link) which put Microsoft as a leader.  However, what was more interesting for me than Microsoft’s success was how as an industry we see BI as a single marketplace, business requirement and vision, despite in my view it now being two separate areas: BI applications and BI platforms.

As this article will discuss in more depth we now have two communities with differing requirements, our IT departments and our business users, each with their own success criteria and their own levels of technical interest in the solution.

The early days

When I first started implementing specific BI services in about 2002 the only tools Microsoft gave us was Analysis Server in SQL Server 7 and PivotTables in Excel. We used Excel to pull data out of our cubes and let the finance team slice and dice the data on their desktops, as much as they could with a 32-bit memory address space. Everything BI related was clunky, resource intensive and second-place to the transactional systems. And while the finance team broke process by finding their own IT solution they were still slaves to the IT team when it came to getting it working.


From a marketplace perspective the solutions were predominantly from large global organisations such as Microsoft, Business Objects, Hyperion and Cognos. They involved heavyweight deployments which required partners to scope, consultants to implement, developers to develop and servers to host, just to report on data which we already had, it felt crazy in a medium sized business.

The teenage years

The next phase of the BI market development I saw was when a world of Excel add-ins matured. We used a business planning tool for Excel from a company called OutlookSoft (they were bought by SAP in 2007). These add-ins depended on their own application services which hosted their own web portals, sat on their SSAS cubes and compiled their own metadata databases in parallel. The scale of the platform was overkill for the functionality business users actually got. However, they did what the business users wanted and more importantly the business users got to steer their own boat and became less dependent on the IT department.



The modern era

Fast-forward to 2010 and we saw a whole new generation of user-oriented BI tools returned in our Google searches. Microsoft’s PowerPivot and SSRS, and Tableau are two of the most prevalent as we enter 2011 and they show how the end user is now truly in the driving seat. Our end users want easy to use, cool and trendy applications that they understand and control. They want them to be working within an hour of installation with no coding. They want them to look nice and be controlled by a mouse rather than code. It’s these reasons that have seen PowerPivot, SSRS and Tableau become so popular.

In parallel to these end user tools we also have SSAS, SSIS, SSRS and Visual Studio to give us our BI platform. Defining success for one of these products is very different to how we’d define success for PowerPivot. They are designed to provide very different services to our end users, whether directly or more importantly in-directly.


Conclusion :Applications and Platforms

So, what is my point? It still seems very strange that Gartner still group BI applications together with BI services. An end-user is unlikely to directly interact with SSAS while a Visual Studio and BIDS developer may use nothing else.

We should also consider the differences and similarities when we consider where BI is owned within a business. Do we upskill our business users to appreciate database queries, ETL processes or even MDX, or do we put business users in our IT departments where they immerse themselves of T-SQL developers day in day out? The reality has to be two business areas, one owning applications, one owning platforms, but with a common vision for success. Perhaps then we’ll be able to use the one-size-fits-all approach to BI the market is still portraying.

Southampton SQL Server User Group–February 2011
11 February 11 09:17 AM | GavinPayneUK | with no comments

Earlier this week I attended the second sitting of the Southampton SQL Server user group, one of the regional events which compliments the larger SQL Server events we put in our diaries.

The event is very well organised by Mark Pryce-Maher (twitter), Matt Whitfield (twitter) and Adrian Hills (twitter) in an easy to find Southampton church hall room with drinks and pizza supplied in addition to quality time with fellow SQL Server peers.  Thanks must also go to another of the event’s supporters Red Gate. (link)

SSRS with John Martin

The main speaker of the evening was John Martin (twitter) presenting about SQL Server Reporting Services.  John took us on a demo-rich journey through SSRS’s features and abilities based on his own experiences of using the services.  What was equally important for me was not only that John was sharing his knowledge of SSRS with us but that the event was giving John a platform to develop his presenting skills on.

Presenting at regional events

Regional user group events are key for giving people the confidence to make their first steps into community presenting and I know that John will have taken as much away from the evening as we did in the audience, albeit very different things.  I spoke to a few other people there who were keen to try presenting at a user group.  My suggestion is to just do it.  Ask for a 10 minute presenting slot and I bet at the end of minute 10 you’ll be wishing you asked for 20 or 30 minutes instead. 

Mark makes the evening very welcoming and the second half of the evening allowed us all to chat with each other.  I met a lot of names I’d seen on Twitter but never had the pleasure of meeting until now.

The next Southampton event

The next session of the Southampton SQL Server user group is on the 9th of March where I’ll be talking about SQL Server’s replication features.

PAL–Performance Analysis of Logs
02 February 11 08:22 PM | GavinPayneUK | with no comments

I was doing some research earlier this week on SQL Server related troubleshooting tools and was surprised I’d forgotten about Microsoft’s PAL tool – Performance Analysis of Logs.

PAL is a free PowerShell UI based tool from Microsoft that creates a perfmon template which can then be used to capture counters most relevant to a high-level performance review PAL will them give for specific Microsoft server deployments, SQL Server being one of them. 

Everyone knows what perfmon does, probably too well, to the extent that we sometimes overlook using it to help identify problem components we can then drill down into.  If you know a server’s IO bound do you know for sure which drives are having problems and whether they’re having IOPS or bandwidth starvation or high service times?  PAL will at least identify that IO is the only problem you have, or perhaps identify other areas for concern, perhaps when the server’s not IO bound its CPU bound for example.

Below is a link to the tool itself and to a SQLBits video with Justin Langford which demonstrates its ease of use and value:

Microsoft PAL home page (link)
SQLBits video (link)