Archive

Archive for April, 2012

Excellent SCD2 loading performance by Alex Whittles

April 17, 2012 Leave a comment

Excellent SCD2 loading performance by Alex Whittles. http://lnkd.in/uHZhYX

Now in addition to use CDC or StreamInsight to handle real time loading scenario, T-SQL Merge will be my preferred method to load SCD2 in SSIS if no third-party software is allowed (Although I use extensively the MergeSCD component which is previously called KimballSCD component).

Categories: BI, SQL Integration Services Tags:

Custom Rollup in BISM tabular (PowerPivot)

As we know, the BISM tabular does not support custom rollup. However, I recently read the article here and found it is possible to achieve custom aggregation via DAX. It is not exactly the same as custom rollup with unary operator in MDX. But it at least can perform different calculation at different hierarchy level. Basically, you need nested Summarize statement to specify the table (calcuated, returned by DAX) to loop with. The drawback is that you have to maintain the DAX.

I wonder is it possible to add a few columns to the date dimension to specify the calculation logic for different level. For example, ‘DimDate'[YearCalculationUnaryOperator], ‘DimDate'[QtrCalculationUnaryOperator], ‘DimDate'[MonthCalculationUnaryOperator], ‘DimDate'[DayCalculationUnaryOperator]. And in the measure, use the DAX looks like:

if (
Count(‘Date'[Date])=1,
(    Switch(‘DimDate'[DayCalculationUnaryOperator],
‘+’, <Calculation expression for +>,
‘-‘, <Calculation expression for ->,
‘/’, <Calculation expression for />,
<default Calculation expression >)
),
if    (    Count(‘Date'[Month])=1,
(    Switch(‘DimDate'[MonthCalculationUnaryOperator],
‘+’, <Calculation expression for +>,
‘-‘, <Calculation expression for ->,
‘/’, <Calculation expression for />,
<default Calculation expression >)
)
, <default Calculation expression for other hierarchy>
)
)

Well, time to dig into the BISM