Cumulative data and delta building

[Former Member]

Hi BI experts

Scenario:

Customer	0FISCPER	KYF
1000	003.2012	500
1000	003.2012	550
1000	004.2012	600

In the table above you see the scenario: The data are loaded as flatfile into SAP BW. They have cumulative character.

If 0FISCPER is the same the delta is built correct (overwrite modus).

Let's assume that 0FISCPER is a key characteristic of a DSO.

In period 004.2012 the data come as cumulative figures, which means: key figure is 50 (USD) higher.

Any ideas how to get the 50 USD as delta record for posting into the infocube? Are there any standard means?

Thanks

[Former Member · Accepted solution]

so u want to show the record as 50 instead of 600? or u want 600 and 50 as 2 record on 04/2012?

[abhijitgaikwad · Accepted solution]

Amer 🙂 end-less logic

Fabian, the logic hinted by you is im-practical as the cube data will have to be retroactively corrected. Presuming the ability to perform a full data upload is already available with you, the correction seems practical.

A word of wisdom

Summation, or cumulation, is a reporting capability, be it bex or BI/BusinessObjects, let them do their work. An exception to this is a key figure which is point-in-time type, e.g. balance for the month-end - finding delta in this case still is a reporting task, I reckon.

Should you need them - there are a few ways, two of them comes to my mind, they follow:

1. Source that provides you with the information in form of a flat-file can be amended.
2. Alternate solution is to employ logic though abap or etl program doing the data upload.

-Abhijit

[Former Member · Accepted solution]

Hi Amer

Yes, I need the 50 on 04/2012.

Thanks

[Former Member · Accepted solution]

and do u also need the 600 on 04 as well? or just the 50? and when you get the data it shows 600 not the 50 at all?

[Former Member · Accepted solution]

Loading history:

001.2012  100 USD (first load)

002.2012  150 USD (delta: 50)

003.2012  550 USD (delta: 400)

004.2012  600 USD (delta: 50)

I need only the delta records for each month.

This flatfile source is not the only source, there are also sources feeding the cube with delta information. Therefore I need the delta records.

cube after loading:

2012   001.2012    100 USD

2012   002.2012      50 USD

2012   003.2012    400 USD

2012   004.2012      50 USD

Reporting on 004.2012 it shows 50 USD. Reporting only on 2012 (0FISCYEAR), it shows 600 USD.

[Former Member · Accepted solution]

1) if you are getting Delta in your flat file but not in the cube:

you cant do that in the cube as cube will archive or SUM it all.. so if the user enter FY 2012? he should see 600 and if they select fy004.2012 it should show $50?i think that should already work with that you have in your report. or you want user to pull a report that only shows them delta records. on w/e time frame they put in?

you can create a query on write optimize DSO but make your fiscal month year mandatory,write optimize will not over write keys, will hold both records and every time the user enter the month, year it should pnly pull that record for the month..

2) if you are not loading delta in the cube as in your flat file is not delta then you will need to create another DSO to hold all your current information. you will need to bring in your month/ fiscal year field with key to the amount field (where you are storing $600) then compare last month to this month amount and get the difference).

if you got a good abaper with u , write the code to a DSO , you will get it n a table you could write a code on it to get the first record of the key  and keep on extracting new record with the subtraction of the original( like sy-tabix) to get your rows number.

good luck

[Former Member · Accepted solution]

Thanks

I made a mistake, I accidently assigned 0FISCPER to the data fields of the DSO. It should be key field.

Nevertheless, ABAP code is essential between DSO to DSO or DSO to cube to get the delta records.

Users would like to report from month to month, e.g. 001.2012 to 003.2012, result should be 550 USD, without delta logic the cube would summarize the full loads of each month, in other words from 001.2012 to 003.2012: instead of 550 USD it would report 800 USD which is incorrect.

By the way, it is a monthly once load, not daily.

Your proposal number 2 will work.

Thanks