Negentiennulvier.nl

 Voor ict-website Webwereld stond oktober in het teken van lektober, en werd dagelijks een digitaal privacylek geopenbaard. Sindsdien staan de kranten er vol van—de zogenaamde mediahype. De Volkskrant berichtte deze week over een alarmerend aantal cyberaanvallen op de Britse regering en Verenigde Staten.^1,2

 Omdat die media maar oppervlakkig ingaan op de uitwerking van cyberoorlogvoering is bovenstaande Vimeo (zoals YouTube) over ’s werelds meest beruchte computerworm Stuxnet een aanrader. Zeer verbeeldend laat Patrick Clair zien welke dreiging er uit gaat van zulke virussen, wormen en dergelijke malware. Én wat voor een gigantische bedragen er omgaan in de handel in zero days (‘onbekende’ toegangspoorten om beveiligingen te omzeilen). Dit alleen al impliceert de betrokkenheid van grootmachten, of erger, opnieuw een wapenwedloop tussen Oost en West. Zo schrijft Wired, dat de vs wachten tot een “catastrofale aanval” en dan met excessief (cyber)geweld zullen terugslaan.³

 Was hun Stuxnet dan een eerste steek onder water. Thomas Rid bepleit gelukkig, dat een cyberoorloog nóóit zal plaatsvinden en de huidige gebeurtenissen slechts uitingen zijn van sabotage, spionage en ontwrichting.⁴ Niets bijzonders, it’s all in the name.

---

An illustration of the desired result: concatenated related row values. [© Joris Korbeeck].

 I stumbled upon the problem of reporting the categories in which products are classified, like illustrated in the picture above. It comes down to the breakdown of the relational properties of records in a table: several rows with a common identifier (e.g. product id) should be grouped together resulting in a less normalized representation. Although the string manipulation capabilities of Transact-SQL (T-SQL) are somewhat limited, Anith Sen has succeeded in describing some neat solutions to this problem.¹

 I like the option of writing a user-defined aggregate library which will be deployed as a Common Language Runtime (CLR) most. Especially because I’m already familiar with programming C# and the .NET Framework, and that the SQL-side of the implementation makes use of standard syntax (

GROUP BY

) which is thereby extremely reusable. During implementation the following additions presented themselves.

 Instead of duplicated values in the concatenated string for duplicate row values I require an (alphabetically) sorted string with unique values. Therefore I made the following changes to the

strconcat

object:

private void Add(SqlString value)
{
    if (!this.values.Contains(value.Value))
    {
        this.values.Add(value.Value);
    }
}
 
public SqlString Terminate()
{
    this.values.Sort();
    return new SqlString(string.Join(", ", this.values.ToArray()));
}

 This introduces a check before adding each value to the

List<string>

values and sorts it before it’s returned to SQL. The complete C# source file is available for download:

  strconcat.cs (1 kB)

 Furthermore one has to deal with the deployment of the library to SQL Server, Niels Berglund wrote a superb set of tools to get this job done. Unfortunately the home (page) of SQLCLRProject looks abandoned to me, nevertheless one should be able to download the installation files via Google. It supplies project templates for Microsoft Visual C# (supports Express Editions too) and simplifies deployment with the Deployment Properties tool.

 Independent of its

MaxByteSize

attribute Deployment Properties creates an user-defined aggregate with both input and output byte size set to 50:

CREATE AGGREGATE [strconcat](@value nvarchar(50))
RETURNS nvarchar(50)
EXTERNAL NAME [strconcat].[strconcat]

 A workaround for this shortcoming is re-executing the statement above using the correct values (the maximum allowed is 4000).

---

¹ Larson, A.S., “Concatenating Row Values in Transact-SQL”, Simple-Talk, link, 31 July 2008.

 Voorbereidend op weer een groots avontuur hoor ik van het overlijden van een, nee hét “icoon van de Nederlandse bergsport”. Bergbeklimmer en vooral avonturier Ronald Naar is zondag op 56 jarige leeftijd overleden. Uitdagingen zoals hij ze aanging, vereisen een drive van jewelste; ze kunnen aan het begin staan van persoonlijke groei tot change management. Ikzelf en mijn vrienden zijn hoogstens bescheiden bergsporters, maar hebben des te meer de lessen-van-de-buitensport geleerd.

 Dit blog beschrijft de ervaringen, die ons teambuildings-, coachings- en leiderschapskwaliteiten hebben gebracht. Ronald was een pionier op dat gebied. Omstreden was zijn beklimming van de Nanga Parbat (1981) waarover hij jarenlang een tweestrijd voerde. Vol zelfvertrouwen—wat hem eerder onsympathiek deed voorkomen—trad hij de media tegemoet ( “Frank Moll en Ronald Naar”). Diezelfde Moll beschrijft hem nu in de Volkskrant¹ als een “ontzettend gedreven, gepassioneerd, succesvol en controversieel klimmer”. Eervol.

 „Negentiennulvier.nl is met u.” Niet in de laatste plaats, omdat wij onze verjaardag delen. R.I.P.

---

¹ “Ronald Naar: icoon van de bergsport en een gedreven pionier”, de Volkskrant, link, 23 mei 2011.

A comparison of dynamic pivoting and Excel export features of different SQL Server technologies. [© Joris Korbeeck].

 Companies are converting to a data warehouse for their business intelligence (BI) solutions, like the medium-sized enterprise where I am part of a two-man data management team. Weighing the pros and cons of technology x with respect to technology y makes an important—but often underestimated*—part of the design and development process. In the (Microsoft) SQL Server section of my weblog Negentiennulvier.nl I write about my experiences with this matter; I invite you to join the discussion.

 Last week I had to automate the monthly delivery of an information request and stumbled upon a common phenomenon: pivoting to create a less normalised representation of the data. Although common, and a standard feature of the Transact-SQL (T-SQL), Integration Services (SSIS) and Reporting Services (SSRS) technologies, pivoting a dataset with ‘dynamic’ columns could be challenging. That is when the number of pivoted columns is unknown at design-time. Apart from scheduled (e-mail) delivery I have made an overview of two major requisites, dynamic pivoting and export to Microsoft Excel file format capabilities (in the diagram above).

 András Belokosztolszki describes in detail how this can be down using dynamic SQL; I recommend reading his post², but it comes down to:

DECLARE @cols nvarchar(2000)
SELECT @cols =
  STUFF((
    SELECT DISTINCT '], [' + ColName
    FROM Table1
    ORDER BY '], [' + ColName
    FOR XML PATH('')
  ), 1, 3, '') + ']'

 Unfortunately Excel export via distributed queries (not enabled by default³) has the same problem regarding to variable column headings, and therefore regarded as too rigid. SSIS simple does not support dynamic columns in Data Flows and its Pivot transformation, because output column metadata has to be entered—thus known—during design-time; needless to say its file export feature is excellent. SSRS’ Matrix component to aggregate data in a grid that has variable columns and/or rows does the job, together with its advanced Excel rendering extension this seems the right approach.

 One has to note however that this layout renderer causes merged cells when encountering slightly different column widths (e.g. when converting grids, fields en labels to Excel cells). On MSDN Blogs Chris Baldwin hints to use points instead of centimeters to prevent internal rounding inaccuracies and workaround this inconvenience to end-users.¹ Nevertheless I found out that this should be done by editing the XML source code. Only when you enter the point measures directly, Visual Studio—often called BIDS—sticks to those values (instead of converting it to centimeters or inches).

---

¹ Baldwin, C., “Merge Ahead”, MSDN Blogs, link, 7 July 2006.
* Probably subject of a future post.
² Belokosztolszki, A., “Pivots with Dynamic Columns in SQL Server 2005”, Simple-Talk, link, 14 September 2007.
³ “ad hoc distributed queries Option”, MSDN Library, link, 12 January 2010.