SAP BI/BW NON - CUMULATIVE KEY FIGURE

Definiton :

    Non - cumulative key figures are Non aggregating key figures which are calculated based on other key figures and characteristics and values are not stored but calculated during query runtime.

Use : Non - cumulative key figures are used in Inventory management or  Banking application where users want to know the stock or account balance as on date.

 If we are working on Banking application we need to provide the transactions including account balance for a particular date.

  Account Balance =  Balance Amount  +  Diposited Amount - Withdrawn Amount.

Suppose customer 'X' wants to know the Balance Amount from his Account on a some date 'Y'.The transactions on date 'Y' may not happen but we have to maintain the Balance Amount for each day irrespective of whether transactions has been done or not.

    We have 2 options for creating Non - Cumulative Key figures

• NCum.value with NCum .value change.
• NCum .value with In - and Out - flow.

The difference is , if we choose 'NCum value with NCum.value change we need to assign one cumulative keyfigure as Non - cum.value change.During run time the value of the Non - Cumulative key figure is calculated by considering Non - cum .value change as the delta change  to the previous value.

Where as in 'Ncum.value with in - and out -flow' the difference between inflow and outflow has been considered as delta change.

Create an infocube (for ex : IC_SL) BY adding Non - Cumulative key figure (TOT_BAL).

When u add  Ncum key figure TOT_BAL ,the cumulative key figures DEP_AMT and WITH_AMT also added automatically.Add 0CALDAY to time characteristics dimension.

Maintain the Non - Cumulative values by choosing Extras menu -> Maintain Non-Cumulative values.

Create Transformations , here Non - Cumulative key figure will not maintain but inflow and outflow cumulative key figures maintained.Make appropriate mappings to load the data.

For Key Figures set appropriate currency in rule details for ex :

 I have a data in infocube(IC_SL) like this

Customer	Account No.	Calendar Day	Amount Deposited	Amount Withdrawn
Thomson	5001	23.07.2015	8000	0
Jack	6004	24.07.2015	9500	1000
Adam	7002	23.07.2015	5000	1000
Thomson	5001	25.07.2015	10000	2000
Jack	6004	25.07.2015	1000	0
Adam	7002	26.07.2015	23000	1000
Jack	6004	26.07.2015	0	1500

Here we can observe Non-Cumulative keyfigure (Tot_bal) Total balance  is not maintained.

Account numbers 5001,7002 have transactions on 23.07.2015.
Account number 6004 has transaction on 24.07.2015.
Account numbers 5001,6004,7002 have transactions on 25.07.2015.
Account numbrs 6004 ,7002 have transactions on 26.07.2015.

Suppose 5001 account holder wants to know the Total balance on 26.07.2015.Even Customer(Thomson) with acc.no 5001 hasn't done any transaction on that day but we are maintaining account balance for each day by making this one as non-cumulative key figure.

Execute the Query

Calendar day	Customer	Account No	Amount Deposited	Amount Withdrawn	Total Balance
23.07.2015	Thomson	5001	8000	0	8000
	Jack	6004	0	0	0
	Adam	7002	5000	1000	4000
24.07.2015	Thomson	5001	0	0	8000
	Jack	6004	9500	1000	8500
	Adam	7002	0	0	4000
25.07.2015	Thomson	5001	10000	2000	16000
	Jack	6004	1000	0	9500
	Adam	7002	0	2000	2000
26.07.2015	Thomson	5001	0	0	16000
	Jack	6004	0	1500	8000
	Adam	7002	23000	5000	20000

Example for calculating total balance for a paticular Account Number.

For Account number 7002 calculate  the total balance on 26th july.

 Account Balance =  Balance Amount on previous day  +  Diposited Amount on current day - Withdrawn Amount on current day.

Account Balance = 2000 + 23000 - 5000.
                            = 20000.(Total balance).

Note : For non-cumulative key figure info cube maintains one extra table is called Validity (L) table.