7th March 2012. The attention of the world was focussed on a new technology launch. I'm quite happy with my iPad 2 though thank you very much so my attention was with the official launch of SQL Server 2012. I'd imagine this is a product that will still be available and deployed long after the latest iPad has fallen from consumer favour. I suppose the other main difference is that there's nothing in the SQL Server 2012 release that hasn't been well trailed in advance. No unfounded rumours about what this new data platform would do or what the core themes were going to be. "Mission Critical Confidence", "Cloud" and "Breakthrough Insights" are the three key points drummed into the market long before this product launch. Perhaps the most interesting innovation at the SQL Server 2012 Launch then is the manner of the global launch. No big arena with whooping fans for Microsoft (well, not this time anyway). Instead they've chosen to promote their most important corporate product of 2012 through a three month virtual launch event. You can register for free to this launch at this site and I'd encourage you to do so to catch all the key notes and breakouts at your own leisure. I'd certainly encourage you to do so but thought I'd use this post to explain what Maxima are doing to help our clients understand SQL Server 2012.

I've been using BI reporting tools from numerous vendors for over fifteen years now but I'm still keen to see a new one whenever it comes along. The most recent 'new' tool for me is Microsoft Power View which is one of the more exciting new features from a user perspective in the upcoming SQL Server 2012 release. Power View is really pretty neat when it comes to getting reports up and running. Arguably it's just a cool front end on top of traditional pivot style analysis but there is a whole untapped community of users out there who really don't get pivoting in Excel and just want to get to the data. Power View is for those users. I think the immediacy of reporting that Power View delivers is really impressive. So much so that I've decided a written, screenshot driven blog entry about it's use just won't do. Instead, I've prepared a YouTube video of what happened the first time I built a report in Power View – just click here to view. Enjoy!

UPDATE - My friends at Microsoft have been in touch to recommend the sample Power View reports at this MSDN blog - worth a look as you can start interacting with the tool yourself.

Over the past year we've been working very closely with Microsoft to develop and refine our services and capabilities around their Business Intelligence solution. In the coming months we're going to be talking quite a bit about this as SQL Server 2012 is released to the market. There'll be a lot more posts on this blog around topics such as the reporting capabilities of PowerView, the multi dimensional and tabular options in the new Business Intelligence Semantic Layer (BISM), the business value added by Data Quality Services and general implementation methodologies. We'll also be releasing a number of Case Studies and are in the detailed planning stages for a number of events around the UK. To kick all this off, I'm delighted to announce a complementary Microsoft Business Intelligence Webinar I'll be running with Microsoft on 22nd February 2012. You can register for this webinar here and I'm using the rest of this post to suggest three good reasons why you should.

An interesting enhancement to functionality in the latest PowerPivot release is Sort by Column. In PowerPivot you could sort your data alphabetically, in ascending/descending order and this worked perfectly. That is until you came to things like dates. I would like to show my data in order of Month Name, Day of the Week. Members in a slicer treat this data as a string value to be sorted in alphabetical order (like the example below).

In a previous post I gave a high level summary of a number of the new features in Microsoft PowerPivot v2. One of the simplest but perhaps most powerful is the ability to view your relationships in a Diagrammatic View (much like our standard Entity-Relationship Software).

After sitting in on a recent webinar looking at new features for Microsoft PowerPivot 2012 (v2) and a new product called PowerView I could not help but be impressed. PowerPivot has been around for a while now but PowerView is a new feature part of Microsoft SQL Server 2012 that must also run with SharePoint Enterprise Server. Although I'll not be covering PowerView in this blog (I'll discuss that in my next one) it's clear to say that this is a highly intuitive and interactive reporting tool that allows you to create rich and dynamic visualisations over the web.

One of the most compelling features of the entire Microsoft BI suite is the fact that it mostly uses the tools most users already have access to. Excel is a clear case in point but a lesser known example is Visio. Traditionally the preserve of IT network designers, in the Microsoft BI environment it really comes to life as a tool to build widely customisable dashboard components. Sure, SharePoint 2010 Insights has some nice scorecards, etc... but they do all look kind of similar and sometime users want to express their own corporate identity through a dashboard. If nothing else, it helps with the user acceptance. I've been doing just that for one of Maxima's current Microsoft BI clients and thought I'd blog about the techniques I've been using to generate a specific scorecard requirement where the format required not only conditional formatting of the colour in each KPI but the position on a grid as well.

A common request for many BI deployments is to have a standard report template created in Microsoft Word which can every month, with a flick of a button, be refreshed against the latest data. This is ideal if it's a regular reporting cycle and all the user needs is to see the latest figures in a familiar format against which they can then add their interpretation, analysis, etc... and distribute out to their audience as a straightforward document. Most top tier BI tools have an 'Office Add-In' product that allows you to take content from their environment and embed it in Word, PowerPoint, etc... but, curiously enough, it's not so straightforward to embed SQL Server 2008 Reporting Services (SSRS) reports within Word in a manner that allows them to be refreshed.

There are three basic options (and a number of 3rd party products out there which I'm not able to pass comment on) so let's look at them in my reverse order of preference:

I recently stumbled across strange behaviour when analysing and producing datasets from SAS. Essentially, we were analysing a range of Customer IDs and providing these to our Customer for additional review. These Customer IDs happened to be stored as numeric fields. When the data was exported to txt/excel the Customer ID was rounded to the nearest ten. After review it turned out that this was caused by a numeric precision issue (which is quite common as it turns out).

SharePoint 2010 contains some impressive new BI functionality, combining eye-catching visualisations and interactivity with SharePoint's integrated security and ability to collaborate and share. However, the initial set up of external data connections within Excel Services and PerformancePoint Services can be tricky.

I've prepared another YouTube video for the Maxima Channel this week inviting current, past and future clients to the Microsoft Customer Immersion Experience in the heart of Edinburgh.  You can view it here - think I'm getting better at these but need some proper lighting next time!

 

In my last blog I wrote about using SQL to build comma separated lists of values within groups of data. I'm going to continue on a similar theme here.

Very often, when being asked for data outputs, I get supplied with a list which I can use to restrict the rows returned in a SQL query. That may be a list of accounts, manager codes, asset class values, category ids or some other such list of values. They will usually be sent via email either as an excel spreadsheet or as a simple text list (each value on a new line rather than being comma separated). To make it useable, I need to have that data, either in a table or as a comma separated list. For just a few values, I'll manually add commas and remove carriage returns but as the lists increase in size, this becomes a tedious job and subject to error. There is a variety of ways to solve this, but the solution that I tend to use is a little bit of Visual basic for Applications (VBA) within Microsoft Word.  This blog is going to review four options.

A nice quick tip, I had lots of cell values in a mixture of cases that should have been uppercase.

Microsoft Excel is the mostly widely used BI tool in the world, but while it's excellent as an analytical tool and has come on leaps and bounds in terms of report presentation in the 2010 release, it remains less than ideal as a data source.

This post details a few common pitfalls I've come across recently while importing Excel-based data into an SQL reporting database using SQL Server Integration Services (SSIS), and some tips on how you can deal with these.

Last year Maxima offered me the opportunity to develop my skill sets further by choosing a training course to attend. As an existing qualified Oracle DBA I was really looking forward to adding SQL Server 2008 to my skill set and so chose to pursue my MCITP certification.