SSAS: Vague Processing Errors About Connection Strings Are Not My Friend

Oh Visual Studio, you crazy cat, you! I've just spent the past hour or so going stark raving mad. courtesy of a DBA who's less than 'expert' and a teeny little Visual Studio property I neglected to set properly.


When deploying my Analysis Services database, the processing kept failing with a vague error along the lines of "connection string is invalid" and "login timeout". Now, I know there's nothing wrong with the connection string cos it works fine in VS, right? Wrong!


When deploying a cube to the Integration environment, my DBA told me that I couldn't just deploy it (outrageous suggestion), no I had to give them the project, they would deploy it, then I had to reimport it from the new environment into Visual Studio. WTF? Ok, I'll play. I'm new here after all so who am I to question their processes?


So when I later needed to redeploy some changes I'd since made, the DBA told me I had to do the same again. At this juncture, I put up a big red stop sign and start asking some questions. Turns out they do that so the Impersonation works correctly. Why wasn't it working as expected is the real question they neglected to ask and solve. Instead of fixing the problem, the treated a short-term workaround as the solution, and stopped there, purely because they didn't know any better.


Having rarely used impersonation before I didn't have an immediate answer. I've always just use the service account as my credentials because I'm lazy and frankly I can't really see why, on a DEV box mind you, you'd bother? Whatever, I don't care so much, if that's how they want to do it I'm easy.


So the problem is that after redeploying the project, it wouldn't reprocess. As I mentioned earlier, I was getting these vague errors about logins and connections. Turns out the solution was really freakin' simple.

See that little "Remove Passwords" property? Yeah that's the sucker. That cheeky tyke was set to TRUE. Set it to false and whaddya know? Everything works a treat.

Aaaah... Friday 4pm... and time for a brewskie.

Weighing in on Project Gemini

My reception to Project Gemini to date has been lukewarm. Sure, self-service cubes in Excel sounds sexy and looks sexy... but... we don't let our users have free reign with data for a reason. These controls didn't just happen because we want to keep ourselves in a job (I hope!).

I'm gonna take a stab and suggest that every BI pro has walked into a job and found an environment where there are 2000 reports on a single system and some of them consume all the machine resources and take 10 minutes to execute. That sort of malarky. So we did the hard slog, got the BA's back up and whipped everyone back into line. The users were happier because the reports execute quickly and were easy to find, you were happier because there are far fewer reports to maintain, the server team are happier because the box didn't have to be rebooted every day and the BA's are happier because everyone else is happy (one hopes!).

This is why we put controls in place.

So. Doesn't this leave Project Gemini dead in the water in the real world?

Well, I read an interesting perspective from Patrick Husting which made me re-think my thoughts (non-pun intended).

"People are always going to do self service reporting in some way whether we like it or not, no matter how good the underlying data is, so why not do it in a controlled manner where everything is audited and logged and the IT team has full visibility of what is going"

Ok so, to quote from a really crappy movie I saw recently "just because it is, doesn't mean it should be". But I think he might actually be right. To a point.

I really do hate to be a naysayer. And I'm not just saying that. But, I have a big BUT (please no jokes!).

Where is all this magical data that is being analysed coming from in the first place? From systems. Well guess, what, we don't let users have hollus bollus access to our databases to avoid the suituations caused by our predecessors.

Actually that's not my only issue... users do NOT have a big picture view of the world. They don't care what other parts of the business are doing and they are not interested in how data aggregates upwards beyond their little fish pond. They are not interested in whether there is a single source of truth or if their version of the truth is different to someone else's. They are not interested in network bandwidth or performance at other sites.

Nor should they care about this stuff, that's not their job. It's ours.

It's all very well to say "everything is audited and logged" but aren't we supposed to operate preventatively not reactively?

So I have to ask, isn't that what something like ProClarity is for? To allow users to slice and dice data however they choose while ensuring that the structures and models have been put in place to consider the big picture?

You know what, I think I've just talked myself back to my original stance. It's sexy and I like it but I can't envision practical applications in the real world yet.

That said, I'm always happy to be proven wrong.