Development 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 Microsoft’s Data Analysis eXpressions language, which is very similar to Excel functions. However, DAX is still pretty basic in some places, and while looking for a simple Month Name function (similar to the GetMonthName function in .NET) I ran into a couple of interesting issues.

read more
Manchester City FC Analytics make OPTA data publicly available
Data & analysis 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 community and provide fresh ways of analysing OPTA’s performance data.

read more
Development 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 Task using an OLE DB Source that loaded its data from a Stored Procedure, specified by a package Variable.  Everything was working fine until I identified that I needed to do something in the stored procedure that resulted in my using a temporary table.  As soon as I updated the procedure and attempted to refresh the columns in my OLE DB Source, I was hit with the following error:

Msg 11525, Level 16, State 1, Procedure My_Stored_Procedure_Name, Line 1
  The metadata could not be determined because statement 'My SQL Statement Here;' uses a temp table.

So why does this happen, even when I’ve defined the type of the column in my procedure?

read more
Subscriptions failing after migrating a Native mode SSRS Report Server database
Development 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 files. However, after getting everything up and running on the new server, and successfully testing the reports manually, I discovered that the report Subscriptions were failing.

read more
Development 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 the point where you make the decision that you need to move some filegroups around to distribute the load across several drives. Alternatively, you may just want to reorganise your DB files into a more logical structure.

This is very straightforward to do normally via backup and restore, or simple detach and attach.  However, when your database is under replication, either as a publisher or subscriber, this simple process becomes a little more difficult.  Thankfully, there’s a very simple solution to this, which I came across the other day while spreading our DB across multiple drives.

read more
Ghost Recon Future Soldier audio bug leaves players speechless
Gaming 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 entertaining as ever. However, on the back on numerous complaints about audio issues with the game, I’ve encountered this one which has left me, quite literally, speechless.

read more
How To Connect SSRS Report Builder to an SSAS Cube
Development 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 the GUI route.

read more

About is a personal blog where I post various ideas, thoughts and discoveries through both my day to day work in marketing technology and general hobbies and interests.

Know More