SSAS 2012 and 3 SSDT Quirks I’ve Encountered

Just a quick post here about some quirks in SSDT for SSAS 2012.

I’m reasonably new to the whole BI thing and so I’m really learning as I go with SSAS at the moment. I’ve tinkered with it in the past but I’m now our company’s data analyst so I’m working with it on a daily basis.

I did play with SSAS in SQL Server 2008 and found the cube browser in BIDS to be quite good. You drop things on columns and rows, filters were easy to set and you could really analyse the data right in BIDS. Obviously this is not how my end users analyse the cubes I construct but hey I was learning back then, I had no idea really what BI was about.

So… 4 years later I take a job as a data analyst. This is some way away from my more familiar role as a DBA. So… I quickly learn that the tool most commonly used to analyse data in cubes is Excel. Brilliant! A tool I’m familiar with! So, I’m running SQL Server 2012 BI Edition on a nice shiny new server, I’ve built a data warehouse/mart, and I’ve got my first cube. Things are going well, until I get to the cube browser in SSDT.

Quirk #1 – The cube browser

The cube browser is different! I no longer have the ability to drop things on columns and rows! I can’t do any analysis straight in SSDT.

CubeBrowser

Quirk #2 – The format strings

What’s even more annoying is that I’ve set a format string on my measure and it’s not displaying in the cube browser. Oh well, that’s ok I’ll hit the Excel icon and take this cube right into Excel.

Quirk #3 – The whole reason for writing this blog entry to be honest…

I’m not sure if this happened in previous versions but the cube still wasn’t showing my measure like the format string I’d specified. A lot of googling led me to a post (which I’ve since lost, so apologies to the author for not crediting him here) that stated you need to escape the £ signs in the format string if you want them to appear in Excel.

FormatString

So having applied that I’ve gone back into Excel and refreshed my data connection! And…

Excel

Hey presto! £ signs appearing.

I hope this helps someone out because #3 had me going for hours!