How to solve the matrix kind of scenarios with multiple Dimensions and KeyFigures.?.

[Former Member]

Hello HANA Gurus,

         Please explain me to how to achieve the following scenarios,

        We are having 10-12 different dates dimenions like effective date, effective year, effective period, period/year and 10-15 calculated Key figures.. How to calculate the KF's with each the date dimension columns. Is therea way that we could able to achieve without doing the repititive calculation for each of the KF's with date dimensions?.

For eg,

      Date Dimension 1 with KF 1

...... Date dimenion 1 with KF 10 and so on...

same way the loop continues. for different KF's with date dimension.

Appreciate your response.

Regards

Suresh

[lbreddemann]

As the key figure values depend on the combination of dimension values you can of course calculate all key figure values for a given combination of dimension values in one go.

So that's easy.

More effort typically is to get through generating all or most of the combinations of dimensions.

Often you want to keep one or more dimensions fixed and see the key figure values for the remaining dimensions.

For stuff like this, SQL provides the GROUP BY GROUPS or BY CUBE option (check the documentation for the SELECT command):

create column table multidatefacts (eff_date date, plan_date date, some_date date, any_date date, kf1 decimal (10,2), kf2 bigint, kf3 boolean);

insert into multidatefacts (

select add_days (current_date, gen.rno) as eff_date,

       add_days (current_date, mod (gen.rno, 10)) as plan_date,

       add_days (current_date, mod (rand()*100, 10)) as some_date,

       add_days (current_date, mod (rand()*100, 3)) as some_date,

       to_decimal ((rand()*10000), 10, 2) as kf1,

       to_bigint  ((rand()*1000000)) as kf2,

       to_boolean (mod(gen.rno,2)) as kf3

from ( select top 100000 row_number() over () as rno

    from objects cross join objects) gen);

select eff_date, plan_date, some_date, any_date,   sum (kf1) as sum_kf1, sum (kf2) as sum_kf2

from multidatefacts

group by grouping sets LIMIT 5 WITH TOTAL

     (eff_date, plan_date, some_date, any_date);

EFF_DATE	PLAN_DATE	SOME_DATE	ANY_DATE	SUM_KF1	SUM_KF2
11/11/2015	?	?	?	592.46	360,441
12/11/2015	?	?	?	7,730.46	539,385
13/11/2015	?	?	?	5,808.74	108,557
14/11/2015	?	?	?	6,224.69	113,680
15/11/2015	?	?	?	9,995.38	751,745
?	?	?	?	499,946,968.79	49,999,491,526
?	10/11/2015	?	?	49,673,805.61	5,041,625,484
?	11/11/2015	?	?	49,790,037.68	4,949,927,791
?	12/11/2015	?	?	49,895,346.25	5,004,267,751
?	13/11/2015	?	?	49,591,138.38	5,017,878,840
?	14/11/2015	?	?	50,525,341.34	4,992,236,194
?	?	?	?	499,946,968.79	49,999,491,526
?	?	10/11/2015	?	50,569,998.16	5,106,850,774
?	?	11/11/2015	?	50,220,936.9	5,039,195,045
?	?	12/11/2015	?	50,254,591.69	5,052,036,579
?	?	13/11/2015	?	49,406,776.59	4,945,725,531
?	?	14/11/2015	?	50,412,571.27	5,016,524,250