By Bernhard Zehetgruber on 10/22/2008 6:30 AM

I built a lot of new Calculations in a SSAS cube. After finishing my work I was deleting all unused calculations even the CALCULATE statement.

DON'T DO THIS:

The CALCULATE statement is essential for building the cube. This statement aggregates the leaf data and if it is not given no data will be writen in the leaf of the Cube. Therefore the Cube will be processed without any failure but no data is visible when you are browsing the data, because no leaf has got a aggregated value and is empty.

--> never Delete the CALCULATE Statement.

By Bernhard Zehetgruber on 7/4/2008 12:38 AM

Building the SUM, MIN, or MAX .. of over a grouped set of Records this is very easy.

Invenstment | Year  | InterestPrevYear
--------------------------------------
Warehouse_1 | 2004  | 1,05
Warehouse_1 | 2005  | 1,2
Warehouse_1 | 2006  | 1,3

SELECT investment, SUM(InterestPrevYear)
FROM sales.revenue_year
GROUP BY investment

--> Warehouse_1 | 3,55

We want to build the product to charge the interest over the last 3 years, the SUM or any other kown aggregte function is not correct in this case.
We must build the product to charge the interest!

Rememb ... Read More »