Implementing Drill Through Actions in SSAS (2020-03-09)

The Panjandrum Conundrum

This editorial was originally published on July 10, 2017. It is being re-run as Steve is on holiday.

I once asked a Microsoft employee, in some exasperation at the absence from T-SQL of a much-needed function, how the decision was made to add one. He described a byzantine struggle to convince a panel of sage panjandrums within Microsoft, long on academic status but, I imagine, short on experience in the stark reality of developing database applications under pressure.

I suspect that success in swaying the "judgement of the wise" depended more on your persuasive powers in extolling the virtues of the feature, than on the its real value to the end user. The rich variety of ObjectPropertyEx(), for example, seems to be more about the convenience of 'internal' use rather than for the benefit to us.

By contrast, it has taken twelve years of persuasion to get the expedient String_Agg() aggregate function and Translate() into TSQL (SQL Server 2017). We get a trickle of useful functions. The SQL Server 2012 functions for converting between datatypes, Try_Convert(), Try_Cast() and Try_Parse() were very handy, and everyone loves the String_Split() function in SQL Server 2016. All these have been easy to implement, but have taken a long time to get through the decision-making process.

When you look through the new crop of functions that appear in every release, it typically seems to be a mixture of the sublime and gorblimey. I’m sure that some of you use the IIF function rather than the CASE construct it is translated into, but it isn’t exactly exciting. The Choose() function seems to be a lifebelt thrown to a struggling procedural programmer. The Trim() function still doesn’t behave the same way as in any other language.

Who are the sage mandarins of Microsoft that for so long frowned on String_Agg() yet smiled on IIF? I sometimes wonder if they look at the world through some distorting mirror, as they gaze on the misbegotten implementation of JSON (‘where are the lists?’), and smile at a job well done.

We have, in PASS, a professional association of users of SQL Server, with a mission to spread knowledge about the use of the Microsoft Data Platform. Surely, amongst the worthy dignitaries of this august society, there are many who are uniquely qualified to assign a value to a proposed extension to TSQL. To be sure, we aren’t all professors, but we are mostly graduates of the school of hard knocks, with a life at the hard end of SQL Server development, with impossible deadlines, implacable mangers and improbable developers. There is, surely, the necessary talent that we can elect to listen to a pitch for a new function or feature in Transact SQL, and to ace the decision every time.

Phil Factor.

Phil Factor

Join the debate, and respond to today's editorial on the forums