Posts Tagged ‘SQL Server 2008’

Mit Microsoft Connect forslag er blevet implementeret

Mit beskedne bidrag til næste version af SSIS er hermed i hus.
I går fik jeg bekræftelse fra Microsoft om, at forslaget har fundet vej til enten næste version eller næste service pack.

Microsoft Conect Mail


Microsoft Connect forklaring







Perculiar Named Calculations

In regards to this article about named calculations in SSAS, I had the perculiar finding today, that NULLs are handled somewhat differently than I would have expected.
In my case I had constructed a named calculation, just as in the article, by concatenating two columns, both of type string and allowing NULLs.
The perculiar part was, that when the second value was NULL, the whole expression would result in a NULL.
So, in order for the concatenation to succeed, the expression should be as follows:

[Marital Status] + CASE WHEN LEN([Has Children]) > 0 THEN  ‘ ‘ + [Has Children] ELSE ” END


MDX Query using other hierarchy in same dimension as in slicer

The other day a coworker of mine presented me with a problem in one of our solutions. His basic pain was, that the front-end of the application only allowed for one dropdown containing whatever dimension was set up to be displayed. This dimension is to act as slicer in a scorecard application. Since his KPIs were aiming different levels of different hierarchies in the date dimension, he was experiencing problems when faced with the choice of only one available date hierarchy.

Luckily we are able to solve this issue by means of MDX.
The following statement should display the solution, by the use of the method EXISTS and the knowledge of the structure of the date dimension hierarchies.

	MEMBER [Measures].[Test] AS 
		( EXISTS([Date].[Calendar Week].Members, [Date].[Calendar].CurrentMember).Item(1), [Measures].[Internet Sales Amount])
	{[Test]} ON COLUMNS
FROM [Adventure Works]
WHERE { [Date].[Calendar].[Date].&[20070201] }

We use the EXISTS function to find the members of the [Date].[Calendar Week] hierarchy with the slicer set to  a member of another hierarchy, in this case [Date].[Calendar].[Date].&[20070201]. Note that we need to get Item(1) of the collection returned, as we get the All member at first index (0).


As mentioned by Hrvoje Piasevoli (blog|twitter) it is a more clean solution to just query by the tuple made by the week hierarchy and the measure, eg.:

([Date].[Calendar Week].CurrrenMember, [Measures].[Internet Sales Amount])

In our case however, we discovered that the attribute relations in the Date dimension was not setup correctly to allow us to “convert” a member in one hierarchy to another. We ended up with the [All] member of that hierarchy.
We solved the issue by making a common root for the two date hierarchies, as seen in the Adventure Works cube.

Setting the maximum allowed memory consumption by Analysis Services

Here follows the step to find where to change the settings for LowMemoryLimit, TotalMemoryLimit and other memory related properties of the SQL Server Analysis Services instance.

Fire up your SQL Server Management Studio, and right-click the server and select ‘properties’

Then check the ‘Show Advanced (All) Properties’ and you’ll see al available properties, like this example:

If values listed are between 0 and 100, they are treated as a percentage of the physical memory on the server. Otherwise they are treated as bytes.

Reporting Services og lokal Domain Controller issue


Jeg havde forleden den førnøjelse, at installere en Domain Controller lokalt på det udviklingsmiljø vi arbejder på. Vi har måtte troppe op med egen server, da kunden ikke kunne afse serverkraft til et udviklingsmiljø. Så maskinen står altså i et domæne vi ikke rigtig kan kontrolere.

Reporting Services, og i særdeleshed rsreportserver.config, håndterer det ikke så elegant som man kunne have ønsket sig. Servicen kører, i vores tilfælde, under kontoen Network Services, som erstattes i det øjeblik Domain Controlleren bliver installeret. Kontoen får altså tildelt en ny SID.

Problemet opstår fordi Reporting Services registrerer SID’en på Network Services, som jo erstattes. Hvorfor den nye konto ikke længere har adgang.


Giv kontoen Network Services rediger/modify rettigheder til mapperne: C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\LogFiles og C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\RSTempFiles

Server og database collation clasher i sikkerhedsindstillingerne for en bruger

Tænkte jeg lige ville dokumentere hvilke skridt der skal til, for at reproducere en lidt underlig fejl i SQL Server Management Studio 2008 (SSMS).

Serveren er sat op med collation: SQL_Latin1_General_CP1_CI_AS
Databasen er oprettet med collation: Danish_Norwegian_CI_AS
Login er oprettet med default sprog: Engelsk
Database bruger er oprettet med collation: Danish_Norwegian_CI_AS

Følgende skridt vil reproducere fejlen:

1. Se properties for server-login:

2. Klik User Mapping

3. Klik Securables

4. Observer fejl:

Cannot resolve the collation conflict between “Danish_Norwegian_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the UNION operation. (Microsoft SQL Server, Error: 468)

MS burde måske nok have været dette scenarie igennem, med en server sat op med én collation og databasen med en anden.





Competing for a free seat on Internals and Performance class by SQLSkills

I’ve been following Paul Randals blog for quite some time now, and it never ceases to amaze me the ideas and information he puts out there. Reading the latest blog post, where is offering a free seat on one of their master classes (, was a new kind of amazement. The opportunity to actually be taught by a super skilled MVP like Paul Randal, is a jaw dropping thought. In Denmark we have a saying that, in Forrest Gump terms, would mean something like: “Life is a huge box of chocolate, with almost no chocolates in it”. Every time someone remotely interesting visits Denmark, the classes are booked almost before they have even been announced. So, good tutoring is really hard to come by around here, SQLSkills kind of tutoring even harder. This fact makes this opportunity even greater than it already is. In other words, this class would classify as a chocolate.
So, why am I to be selected as the winner of this particular competition? To start with the obvious and least important, the change of climate would be more than welcome. A jump from ~35F to ~50F ought to get put a smile on most faces.
To state the more professional aspects, I’d have to mention the impact on my daily routines this class would have, as the first thing. I’ve been waiting a long time for the right class to come my way, but going abroad has not been an option, because of the added costs for flight and accommodation stay. The total cost would be much lower, with the class being free.
Another aspect would be the opportunity for me to improve on the subjects that are taught in class. This would mean a giant skills boost for me. And as I share these areas of responsibility with my co-workers, I haven’t been able to develop or train, so the impact on my day to day work would be huge.
So if I, a professional developer from Denmark, were to be offered a free seat on a class like this, I’d be booking the flight the same minute my arms came down ( and that could take a while )

Did I mention that I once played the guitar in a band called Stoned Sheep 🙂

“I will prepare and some day my chance will come.” – Abraham Lincoln

70-452 PRO: Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008

Jeg tænkte jeg lige vil opsummere hvad jeg kan give videre til andre, der måtte tage denne certificering.

Inden testen startede blev jeg præsenteret for et indledende spørgeskema hvor man skal svare på hvordan man ser sig selv som professionel i barnchen. Her spørges om erfaring med produktet og i branchen generelt. Det har man ca. 15 minutter til at gennemføre hvilket skulle være rigeligt for de fleste. Hvilke valg man foretager skulle efter sigende ikke have indflydelse på hvilke spørgsmål der kommer i selve testen.

Selve testen er overordnet indelt i flere kategorier end jeg lige havde regnet med. Efter at have taget 70-448 havde jeg forventet samme struktur. PRO testen har, ud over de seks traditionelle kategorier: Administrering/Implementering af hhv. SSIS, SSAS og SSRS, også fundet rum til spørgsmål om generelle datawarehouse udfordringer og brug af TFS.

Den test jeg fik, bestod af 50 spørgsmål fordelt på følgende måde:

Datawarehousing: 2 spørgsmål
Ét om dimensionel modelering og ét om SDC (Slowly Changing Dimension)

SSIS: 11 spørgsmål
Konfiguration, logning, transaktioner, dimensioner før facts, roundtrip minimering, export, backup strategi og SQL Server agent rettigheder.

SSAS: 19 spørgsmål
Herunder sikkerhed, sourcecontrol, hukommelsesforbrug, MOSS, processering, datamining (x5), MDX, attributrelationer, snowflake.

SSRS: 18 spørgsmål
Opgradering til native mode, subscriptions, portering til ny server, skalering, drilldown, drillthrough, custom code, ReportViewer, filtrering, Excel og perspektiver.


Det burde give en kort introduktion til hvad der er i vente hvis man melder sig til denne certificering. Der er dog mange områder der ikke er blevet berørt af testen jeg fik, så man må endelig ikke tage min erfaring som facitliste.

HierarchyID datatypen

Denne nye datatype introduceres med SQL Server 2008 og må siges at være en længe savnet type. I hverfald har jeg tidligere kunne have haft brug for en sådanne mulighed.

Læs mere her