Graham

Business Intelligence and so-called "Big Data" Architect at Indicia. Primarily working with the Microsoft BI stack and Hortonworks Data Platform, but love to expand my horizons with different tech. Football daft, and also an NFL fan. Partial to a spot of golf and Xbox, too.

All articles by Graham

 

Windows Phone, meet Cortana (just watch out for Halo spoilers)

×Warning: This post contains spoilers regarding the ending of Halo 4.  You’ve been warned.   I’ve been spending some time with a special lady lately.  She’s always with me, and has been helping me organise my life.  She’s not all work though, and has shown that she knows a joke or two, she’s sassy, and
Continue reading...  

Mapping C# DateTime to SQL Server datetime2 via SSIS

With SQL Server 2008, Microsoft introduced the new, improved datetime2 format.  This newer time storage format is great, because it takes up less storage space, plus you have control over precision and can define your field to the exact specification required.  Database columns defined as datetime2 can be mapped in SSIS by using the DT_DBTIMESTAMP2
Continue reading...  

Case sensitivity in SSIS Lookup Transformation

Something that catches out a lot of new SSIS developers.  The caching mode used for a Lookup Transformation may affect case sensitivity. The Full Cache option is case sensitive by default.  Partial and No-cache options use the Collation setting of the database (or table) to handle case. Of course, using Partial or No-caching results in
Continue reading...  

Visualising a football match as a Network Graph using Gephi

Ever since getting my hands on some Opta data, courtesy of Manchester City’s Analytics challenge all the way back in August 2012, I’ve been wanting to try something different with the data.  Although it’s taken me over a year to get around to doing it, I’d initially thought of the idea of doing some kind
Continue reading...  

Building a Raspberry Pi NAS: Enclosure

Lately I’ve been experimenting with the Raspberry Pi, the credit-card sized budget computer that took the world by storm back in 2012.  I posted the other day about the hardware I’m using to create my own, Raspberry Pi based NAS (Network Attached Storage) slash backup server slash media centre.  I mentioned at the end of
Continue reading...  

Building a Raspberry Pi NAS: Hardware

The Raspberry Pi has been a huge hit since its launch in 2012, grabbing the attention of hobbyists and professionals alike.  The option to buy a fully functional, credit-card sized computer for less than £30 has opened up a slew of possibilities for experimentation and creativity, regardless of budget.  I’d been meaning to pick one
Continue reading...  

Power BI for Office 365 first thoughts

I’ve been meaning to write something on Power BI for a long time now, and I’m a little late in getting round to writing this, as most of the dust has already settled after Microsoft sent out the first round of invites to the Power BI for Office 365 preview, and a lot of people
Continue reading...  

Optimising SSIS to read from a view using OLE DB Source

I’ve really been neglecting the blog of late and have been taking a bit of a break from a lot of extra curricular business intelligence and data reading.  I figured it was about time to get back to posting though, and as luck would have it, my colleague Stephen came to me with an interesting SSIS
Continue reading...  

Fix “login failed” error while editing Table Properties in SSAS Tabular via Visual Studio

Just a quick post regarding a strange problem I encountered while working on an SSAS Tabular model project.  Built on the same Vertipaq technology as Power Pivot, it’s very easy to get started and produce quick, efficient data models.  Unlike Power Pivot, which runs as an Excel add-in, SSAS Tabular models are developed in Visual
Continue reading...  

Microsoft announces Power BI for Office 365

After launching a number of really neat self-service business intelligence plugins and components over the past couple of years, Microsoft has finally announced their complete self-service BI package: Power BI for Office 365.  Incorporating Microsoft’s four big self-service BI components of the past year or so; PowerPivot, PowerView, Data Explorer and GeoFlow, the Power BI
Continue reading...  

5 Reasons why PowerView can’t replace Reporting Services

The other week I wrote a post discussing how PowerView was the future of SQL Server Reporting Services, and the killer features that made it a compelling choice.  Despite the numerous positive advances that PowerView brings to Microsoft/SQL-based reporting, there are of course a number of counter arguments.  I deliberately left these out in order
Continue reading...  

6 Reasons why PowerView is the Future of Reporting Services

Since its introduction with SQL Server 2012, PowerView has started to become the familiar face of Microsoft’s self service business intelligence offering.  Its inclusion in Excel 2013 has only reinforced its position as the premier tool for quick, interactive  visualisation of data, in conjunction of course with the magnificent PowerPivot.  But ask any “traditional” BI
Continue reading...  

SQL Server Management Studio standalone download

Something I see online all the time, is people trying to find a standalone download for SQL Server Management Studio.  Until recently, it seems, Microsoft certainly didn’t make it easy to just install the client tools without having to install the entire SQL Server database engine as well.  The are plenty of reasons why you
Continue reading...  

SQL Saturday 202 in Edinburgh

Last year I had the fantastic opportunity to attend SQL Saturday #105 over in Dublin on the company dime.  A fantastic, free training event, SQL Saturday brings together a number of industry leaders and enthusiasts to deliver talks on a number of topics related to Microsoft SQL Server.  Well, my colleagues and I are thoroughly
Continue reading...  

GeoFlow brings 3D geographical visualisation to Excel 2013

The other week, Microsoft announced GeoFlow for Excel 2013 at the SQL PASS Business Analytics conference in Chicago.  While it’s not exactly new, it is at least, a pretty impressive looking addition to the data visualisation toolkit. However, while GeoFlow finally brings 3D geographical visualisation to Microsoft’s self-service BI utility belt (in your face, Batman),
Continue reading...  

Installing SQL Server Data Tools – Business Intelligence for Visual Studio 2012

As if renaming the accurately titled Business Intelligence Development Studio (BIDS) to the rather ambiguous SQL Server Data Tools (SSDT) wasn’t bad enough, in December, Microsoft’s latest SSDT release only brought half the expected capabilities to Visual Studio 2012.  Yep, the December 2012 SSDT download was missing a key component: the project and item templates
Continue reading...  

Is Hadoop the right tool for the job?

I recently posted some thoughts regarding Microsoft’s Windows-compatible Hadoop implementation, HDInsight.  I was investigating it for a project that I figured would benefit from a distributed processing approach, although ultimately decided to pursue other alternatives.  It led our team to make some quite interesting discoveries about Hadoop, and some scenarios of when current distributed processing
Continue reading...  

Hands-on with Hadoop and HDInsight

Hadoop.  Everyone and their dog is talking about it.  That and “Big Data”.  There was an excellent post on Brent Ozar’s DBA Reactions Tumblr blog recently that encapsulated it perfectly, titled “When the executives ask if we’re Hadooping”.  It’s a valid point though, Hadoop is mentioned in just about every article these days, along with the
Continue reading...  

Notes from building a Custom ForEach Enumerator in SSIS

I recently posted about a quandary in which I found myself that led to me building my own extended ForEach File Enumerator in SSIS.  All things considered, it was a reasonably straightforward experience, with most of my issues stemming from a relative unfamiliarity with Windows Forms development (I was always an ASP.NET man).  The whole
Continue reading...  

Re-downloading Microsoft Office 2010 using an existing Product Key

Just a quick one for now, as I’ve still not got around to finishing up the several posts I currently have in the offing, due to playing about with HDInsight on Windows Azure.  I did however, spend a rather frustrating couple of hours at the weekend trying to find a way to re-download Office 2010
Continue reading...  

Extending the ForEach File Enumerator in SSIS

One of the primary reasons for using SSIS is to process multiple files, ETL (Extract, Transform, Load) data, and feed it into a destination, for example, a data warehouse. Built-in components such as the For Loop Container and the ForEach Loop Container make it very simple to enumerate a collection and perform some processing tasks
Continue reading...  

Microsoft BI 2012: A Year In Review

With the year drawing to a close, I thought it would be the perfect time to recap the major developments in Microsoft Business Intelligence throughout 2012.  Unsurprisingly, the launch of SQL Server 2012 proved to be the focal point of this year’s releases, although we had a few extra surprises along the way:
Continue reading...  

Time Format on non-Date Dimensions in SSAS

I ran into a rather strange situation recently while attempting to set up some new calculated measures in an SSAS cube.  I’m still new to MDX, so I ended up chasing my tail around for a while (and crying out for help on both StackOverflow and MSDN) before I eventually tracked down the culprit and
Continue reading...  

10 Years of Xbox LIVE Infographic

Love them or hate them, there’s no escaping infographics these days.  They are EVERYWHERE.  Personally, I’m a fan.  I’ve always been interested in visualisation techniques and better ways to represent data visually.  After all, the human brain can process images and patterns much faster than it can text.  The best visualisations are the ones that
Continue reading...  

Microsoft’s Mobile BI “Project Helix” Unveiled

Some pretty interesting information leaked out of last week’s SharePoint Conference 2012 regarding Microsoft’s upcoming Mobile BI (Business Intelligence) solution.  There’s been surprisingly little reaction to the unveiling of the solution, apparently codenamed “Project Helix”, perhaps due to the fact that it was unveiled at a SharePoint event and not a SQL Server one.  However,
Continue reading...  

Editing a PowerView RDLX file in Sharepoint 2010

Since I’ve not managed to get a post written up in about a month, I figured it was well past time to pull my finger out and get something posted.  As part of my recent work with PowerPivot and Sharepoint 2010, I’ve also been playing about with PowerView as a quick UI.  For anyone who
Continue reading...  

Calculate the difference between two dates in DAX

Another quick Data Analysis eXpressions (DAX) update, given that I’ve been playing about with PowerPivot again this week.  Following on from my exertions trying to work out how to return a month name in DAX, I found myself looking for a DAX equivalent of SQL Server’s DATEDIFF function.  Fortunately, this is reasonably straightforward.  All we
Continue reading...  

Calculating Month Name from a Date Integer in DAX

I’ve been playing about with PowerView and PowerPivot recently, while also getting used to the Analysis Services (SSAS) Tabular model in  SQL Server 2012 (Denali).  The tabular model provides a high-compression, in-memory store for easy data model construction and analysis.  Ad-hoc calculated fields can be defined within PowerPivot (built on the same xVelocity technology) using
Continue reading...  

Manchester City FC Analytics make OPTA data publicly available

In a move to stimulate the analytics community, Manchester City Football Club have teamed up with English Premier League statistics provider OPTAPro to release a full data set of Premier League players for the 2011-2012 season.  The MCFC Analytics project has been created to harness some of the amazing talent out there in the analytics
Continue reading...  

Metadata Discovery in SSIS 2012 not working with temp tables

SQL Server Integration Services is a tidy bit of kit and an absolute must have on any SQL Server developer or administrator’s toolbelt.  However, it is as frustrating as it is sublime, with many obfuscated error messages, quirky behaviours and downright oddness.  I ran into one such quirk recently while setting up a Data Flow
Continue reading...  

Subscriptions failing after migrating a Native mode SSRS Report Server database

I was recently involved with the migration of a Native mode SQL Server Reporting Services (SSRS) database from a non-domain server to a domain-based server.  The migration itself was fairly straightforward (as it usually is) thanks to the inherent portability of the Report Server database and required configurations, such as encryption keys and .NET config
Continue reading...  

Is Battlefield 3 Premium worth it?

Last month saw the launch of Battlefield Premium, DICE and EA’s answer to Call Of Duty’s ELITE system for their seminal online shooter, Battlefield 3.  So far, 800,000 people have signed up for Premium, launched on 4th June for PS3 and 12th June for Xbox 360 and PC.  Operating using the ever more common “Season
Continue reading...  

Moving Filegroups on a SQL Server Database under Replication

Sooner or later, with any SQL Server database of a suitably large scale, you’re going to run into a situation where you start hitting hardware limitations.  It may be disc space, it could be IO issues caused by concurrent access of multiple filegroups and databases on the same controller.  Either way, you’re likely to hit
Continue reading...  

Ghost Recon Future Soldier audio bug leaves players speechless

Being a huge fan of the Tom Clancy games ever since the original Rainbow Six broke onto PC way back in 1998, I picked up Ghost Recon: Future Soldier for the Xbox 360 at the weekend.  True to form for the series, the game’s mix of 3rd-person shooting, high-tech equipment and squad mechanics is as
Continue reading...  

How To Connect SSRS Report Builder to an SSAS Cube

I was asked recently how one could use SQL Server Reporting Services (SSRS) Report Builder construct a report using a SQL Server Analysis Services (SSAS) cube as it’s data source.  The good news is that is just as simple as connecting to a SQL Server database, there’s really very little difference, at least if you using
Continue reading...  

Calling external DLLs from a Script Task in SSIS 2012

The 2012 version of SQL Server has brought with it a number of new features, not least the new, Visual Studio 2010 shell-based Management Studio. However, the changes are more than just aesthetic, as I found out when I came to try and upgrade some SQL Server Integration Studio (SSIS) packages from SQL server 2008
Continue reading...  

How To Define an XSD file for an SSIS XML Source using an expression

I’ve been looking at migrating some SQL Server Integreation Services (SSIS) packages lately, and as part of the move, am parameterising a lot of settings to improve the ease of future configuration changes.  One of these changes involved a package that reads from an XML source and uses an XML Schema Definition (XSD) file to
Continue reading...  

Skype app available for download on Windows Phone 7.5

Somehow I completely missed the boat on this one, but I found out today that an official Skype app has finally been released for Windows Phone.  I’ve been waiting for a WP7 Skype app since I first got my HTC Trophy back in November 2010, so I was surprised to see that the app quietly
Continue reading...  

How To Enable Remote Errors in SQL Server Reporting Services (SSRS)

Just a quick one for a Wednesday afternoon.  I was recently asked if it was possible to enable remote errors in SQL Server Reporting Services (SSRS), so that people (and applications) could return full error details from RDL execution, without having to consult the local log files.  It’s an incredibly easy task to enable this.
Continue reading...  

How To Connect SSRS Report Builder to a PowerPivot Excel file (Sharepoint)

So, you’ve finally got around to setting up that SQL Server 2012 environment and you’re playing about with PowerPivot and SQL Server Analysis Services.  You’ve set up your sharepoint site and you’ve created and uploaded a new spreadsheet using PowerPivot that contains all that tasty cube data.  However, in your haste, you haven’t set up
Continue reading...  

SQL Saturday #105 Dublin Review

As I mentioned in my last post, I was recently over in Dublin attending SQL Saturday 105, a SQL Server conference and networking event hosted by PASS.  For anyone considering attending a future SQL Saturday event, I can definitely recommend it for developers, Database Administrators (DBA) and Business Intelligence (BI) developers of any level, from
Continue reading...  

SQL Saturday #105 In Dublin

As it stands, I’m currently getting organised to head to SQL Saturday #105 which takes place tomorrow (March 24th) in Dublin.  This is the closest SQL Saturday event to Edinburgh so far, so I jumped at the chance to go.  SQL Saturday is a regular event, created by the Professional Association for SQL Server (PASS) to
Continue reading...  

GAME cancels Mass Effect 3 pre-orders alongside other EA releases

GAME Group look to be in some trouble today after the news broke that they would not be stocking any EA published titles after Friday’s release of SSX.  The game retailer had some trouble with Metal Gear Solid at the start of February and had to stop taking pre-orders.  Things seem to have gone from
Continue reading...  

Microsoft Releases Visual Studio 2011 Beta and Windows 8 Consumer Preview

Double the fun for developers and consumers today, as Microsoft launched the consumer preview of their new tablet-friendly Windows 8 OS, alongside the first beta of Visual Studio 2011, which will provide all the necessary tools for developers to get to grips with Win 8’s new “Metro” styling.
Continue reading...  

SQL Server Job Agent fails SSIS package with error: Option “/CALLERINFO” is not valid

I was editing some pre-existing SQL Server Agent jobs the other day, adding some interconnectivity and changing steps around as I’ve done hundreds of times before.  These job steps are used to execute SSIS packages (SQL Server Integration Services) on the server and have been running for months (years?) without fail.  However, after I made
Continue reading...  

Reverse the order of words in a string in SQL Server

I recently found myself with a situation in SQL Server where I had a string consisting of a series of values (constructed using the CLR function for concatenating values that I wrote about last week) that I needed to reverse.  Unfortunately, it’s not as simple as using SQL Server’s built-in REVERSE function.  That actually reverses
Continue reading...  

How to open your NAT settings for Xbox LIVE on O2 Wireless Box II

I’ve had my fair share of issues with my broadband connection since signing up with O2 (provided by Be There) 3 years ago. Fortunately, it’s improved a great deal over that time and is now extremely stable. The biggest problem I had was playing online via Xbox LIVE, especially getting my Network Address Translation (NAT)
Continue reading...  

Concatenate multiple row values in SQL Server with CLR

One of the good things about SQL Server (as with most other Microsoft products) is that there are often multiple ways to achieve a single result.  One such example is the concatenation of cell values from multiple rows into a single, delimited string.
Continue reading...  

Yahoo Site Explorer Closes Tomorrow

For those out there who like to use Yahoo’s Site Explorer to investigate the Yahoo index and mange their sites, the day you’ve been dreading has arrived.  Yahoo have announced the final closure date for the service (after announcing earlier in the year that it would close “sometime this year”) and that day is tomorrow,
Continue reading...  

Workaround for the Battlefield 3 “no weapon” glitch

In my free time lately I’ve been getting into the superb online multi-player in EA and DICE’s Call of Duty baiter, Battlefield 3 on Xbox 360.  For anyone who’s not tried the series, it’s a large scale multi-player military shooter with wide open maps and a variety of land, sea and air vehicles with which
Continue reading...