I have a cube, called metric_cube
, with a measure of Metric
and a dimension Week
with the given output on SSAS from SQL Server 2014.
Current Output:
Week Metric
---- ------
1 6
2 5
3 4
4 3
5 2
I want to get the cube to output the sum of the Metric
for all combinations of a from Week
and to Week
, where the from Week
<= the to Week
.
Desired Output:
Week(F) Week(T) Metric
------- ------- ------
1 1 6
1 2 11
1 3 15
1 4 18
1 5 20
2 2 5
2 3 9
2 4 12
2 5 14
3 3 4
3 4 7
3 5 9
4 4 3
4 5 5
5 5 2
I have tried using the same dimension twice, but I get a cross join error. I have also tried creating two dimensions and applying both of them, but all that does is duplicate the Metric
value from the current output for all to Week
s without consideration of the desired constraint for the from Week
<= the to Week
Is it possible to use a data cube to achieve the desired output? If so, how?