Building Next Gen Datacenter – The Pelicase Portable Datacenter

I found this crazy post about a guy who build himself a portable datacenter, which is awesome! 😀

More pics and the shopping list can be found here:

Building Next Gen Datacenter – The Pelicase Portable Datacenter


SQL Server Management Data Warehouse (MDW)

The SQL Server Management Data Warehouse (MDW) is one of the most underappreciated features of SQL Server. But it’s a really handy feature which logs WaitStats and more, with almost no overhead (< 1% CPU). And…it does generate some nice reports which you could use to monitor your server (out of the box):

SQL Server 2012 Master DWH

How to enable it
For more info (and how to enable it), please visit:

SSRS: How to use a date/time parameter as an input for an MDX query (instead of a list of text values)

I guess we’ve all seen it before, VS/SSDT/BIDS automatically generates datasets for input parameters (which is fine to start with).

But for Date/Time parameters it always (by default) selects the ‘Text’-datatype (drop-down list), instead of the ‘Date/Time’-datatype (nice calendar).

What we want to achieve
So this is what we want to achieve (in red):

SSRS datetime input parameter instead of text for mdx

Start a clean slate
First step is to drop the generated date/time parameter(s) we’re not going to use and create a new one:

SSRS datetime input parameter instead of text for mdx - properties

Make sure to select the ‘Date/Time’ as the datatype, which enables the nice calender-type input. Leave the ‘Available Values’-section unchanged/empty.

Hussle the Date-value in the correct format (YYYY-MM-DD)
A difference between both datatypes is that the ‘Date/Time’-datetype will return values formatted based on your local/regional settings (e.g. YYYY-M-D or in my case D-M-YYYY).
Where I need it to be YYYY-MM-DD (including 0’s), so something like:

To achieve this, we need to cast/convert a little bit and include a ‘0’ when we need it:

=IIF(LEN(CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))) = 2,

Turn it into a MDX value
The final step is to get it into the desired value for MDX:
[Dim Creation Date].[Date].&[2015-12-05T00:00:00]

Here’s the full code expression which you can use as a parameter value (expression):

="[Dim Creation Date].[Date].&["
IIF(LEN(CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))) = 2,
IIF(LEN(CSTR(DATEPART(DateInterval.Day,CDate(Parameters!DateFrom.Value)))) = 2,

Use the expression as an input value for your dataset
The final step is to use the expression:

SSRS datetime input parameter instead of text for mdx - dataset properties

That’s it! Bye bye ugly drop-down list 🙂

SSRS datetime input parameter instead of text for mdx - final result

SSRS Drill-through: passing multiple values to another report (MDX list of values)

When I build large dashboards, I often fall back to basic stored procedures to combine datasets, but also to be able to manipulate parameter values (month, first week, previous week, previous year totals, etc.).
An annoying thing is that when you want to create a drill-though action to another report which uses MDX, you’ll need to magically join these values.

Well…here’s how you could achieve it.
Let’s say I have these three values (keys): XXX, YYY and ZZZ

MDX expects a prefix and a suffix (according to the dimension properties/levels).
So here’s what MDX expects:

[Dim Customer].[Customer Number].&[XXX]
[Dim Customer].[Customer Number].&[YYY]
[Dim Customer].[Customer Number].&[ZZZ]

Important: MDX expects different objects/rows, like you need to use separate rows in VS/SSDT for the default parameter values.

Your dataset returns the basic key values, first step is to join these (including the prefix and suffix):

="[Dim Customer].[Customer Number].&["+JOIN(Parameters!Customer.Value,"], [Dim Customer].[Customer Number].&[")+"]"

The results is:

[Dim Customer].[Customer Number].&[XXX],[Dim Customer].[Customer Number].&[YYY],[Dim Customer].[Customer Number].&[ZZZ]

Which seems correct, but MDX isn’t expecting a multi-valued string (compared to SP’s).

Final thing, you’ll need to do is to split it again in order to provide a record/row per value:

=Split("[Dim Customer].[Customer Number].&["+JOIN(Parameters!Customer.Value,"], [Dim Customer].[Customer Number].&[")+"]",",")

SSIS The package contains two objects with the duplicate name of output column “ErrorCode”

Grrr…this one can be annoying!
But when you know how to cope with it, it’s an easy fix.

The problem: The SSIS package is failing validation with a message to the effect of “The package contains two objects with the duplicate name of “output column “ErrorCode” (123)” and “output column “ErrorCode” (456)”.

The explanation: The Data Flow source (for at least OLE DB Sources) will have a Error Output that by default will contain all the columns of the Source with two additional columns: ErrorCode and ErrorColumn.

The solution: The source column can be renamed as a last resort. This is often not practical or possible, the better solution is to rename the Error Output column. With the data flow open:

1) Choose the Data Flow Source
2) Choose Properties
3) Click ‘Show Advanced Editor’
4) Navigate to ‘Input and Output Properties’
5) Open the Source Error Output
6) Scroll to the bottom of the list and rename the last ‘ErrorCode’ column.

SSIS advanced editor errorcode errorcolumn

Thanks to: