How to design the flow

[former_member214778]

Hi,

I have a flat file data source which has 2 KPI's loading till now. Now the Business wants to load next 3 months data from those 2 kpi's which means the flat file will have 3*2 times since they want to see the data in separate columns at the report.

For example,

Currently at DS :

Prod   FP            kpi1   kpi2

P11     01/2016      50       100

P2    02/2016       30         200

Proposed changes at DS :

Prod   FP        KPI1 KPI2 KPI11 KPI 22 KPI113  KPI23

P1      01/2016   50    100

P2      02/2016                     30       40

P3       03/2106                                       50        200

How to cope up with this scenario when data is loaded into the cube based upon the fiscal period.

Regards,

Mukesh

[kohesco]

hi,

in cube you need to have FP, KPI1 and KPI2

business can just enter more lines in the flat file, there is no need to create extra kpi's

If Prod means product, then that is your key together with FP.

prod     FP     KPI1    KPI2

P1       01        50        100

P2     02          30          40

P3     03           50        200

on report level you set

                  prod

                  KPI1   KPI2

FP

to have on one line

01             50   100     30     40     50    200

[former_member214778]

Hi Hesters,

thanks for the information. I have thought the same but the catch point is the users want to see the data for every column like separately on fiscal year period basis.

Currently in the report it shows one month per line, but going forward they want to see 3 months in columns separately for each period.

[kohesco]

Hi,

put the periode above the keyfigures

[Former Member]

Hi,

alternatively you can create Restricted keyfigure/selections in columns to create new column for each row.

1) restrict KP1 with FP (restrict FP with user input variable ) "01.206 (user chooses this from selection)

2) restrict KP1 with FP (restrict FP with above variable and apply offset as +1) "02.2016

3) restrict KP1 with FP (restrict FP with above variable and apply offset as  +2) " 03.2016

ex: it looks like this.

        KP1 (01)    KP1 (02)   KP1(03)

P1    50               30           50

Note: if you are restricting period in KPI's , avoid having period in rows.

[former_member214778]

Hi Jyothi/Hesters,

The requirement is like having all the KPI's into one row.

for example:

Product  FYP           KPI1    KPI2   KPI1   KPI2     KPI1       KPI2         KPI1   KPI2

P1           08/2016    20         30      50          40          60          70          50     100

Now, this is how data comes from Flatfile. But  the user says the first KPI1 and KPI12 are for current period  say 08/2016 and then KPI11, KPI22 belongs to 09/2016. like that it goes for next 3 periods.

Now while loading to DSO I have to load like below

Product    FYP            KPI11  KPI12

P1           08/2016       20         30

P1            09/2016      50        40

P1            10/2016     60         70

P1            11/2016     50          100

How to achieve it is almost like making it as key figure based model. Start routine or rule groups will work ?

Regards,

Mukesh

[former_member210321]

Hi,

You can use either Start routine or Rule Groups both will work.

Regards,

[former_member214778]

Hi Asif,

Can you throw some light on how ?

There is no fiscal year period will be mentioned apart from the current period so the period has to be assigned dynamically in the routine.

[former_member210321]

Hi Mukesh,

You can see the document in the link below to get an idea how Rule Groups works:

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/d085fa63-f54c-2c10-b5ba-cc4ac2315...

Regards,

[former_member214778]

Hi Asif,

I have gone thru the document , in that example the source itself is giving fiscal period. In my case the flat file will give me only one current month and based upon that I have to assign other periods.

[kohesco]

Hi,

putting the FP above the key figures will show everything on one line, so you'll meet your goal.  But if you insist in transposing your data: check doc: http://scn.sap.com/docs/DOC-42525, but there are many links to find about how to transpose in abap.

good luck

Grtz

Koen

[D_Chia]

if you are looking for a pure query design level solution and also be able to keep the format of data in the DSO as per your example (see below)

Product    FYP            KPI11  KPI12

P1           08/2016       20         30

P1            09/2016      50        40

P1            10/2016     60         70

P1            11/2016     50          100

you can make use of 'Constant Selection' in your restricted key figure columns for each subsequent period (from ONE chosen period when you run the query eg. From period:  08/2016

1st column is unrestricted but your overall query result is already restricted to the above "From period:" so it will show "current period" amounts.

then for the 2nd and 3rd column ... define restricted kfig ... with Constant Selection on period (with offset against the "from period" variable)

• 

• 

when you execute the query

the above results was based on my sample data below in the InfoProvider (similar to yours in format)

[former_member214778]

Hi Chia/All,

Thanks Chia.

But unfortunately I cannot change the design of the Query because of this, there is already BW Query developed with a business approved format. Hence, I want the phenomena to be developed at the loading time.

Any by the way my Source Flat file will have only one fiscal period. Based upon that I have to derive for other fiscal periods.

It is like this :

Regards,

Mukesh

Message was edited by: mukesh s Source file : Product  FYP           KPI1    KPI2   KPI1   KPI2     KPI1       KPI2         KPI1   KPI2 P1           08/2016    20         30      50          40          60          70          50     100 While loading to DSO: Prod   FYP           KPI1  KPI2 P1      08/2016     20       30 P1    09/2016       50        40 P1     10/2016       60      70 it is like transpose. The source will have only fiscal period which is current period based upon that the other future periods have to be determined.

[former_member210321]

Hello,

In your case as you have only one current month on each group rule of transformation write a field routine for other periods on which the keyfigures are getting mapped. through this way you can obtain your requirement.

Product  FYP           KPI11    KPI12   KPI11   KPI12     KPI11       KPI12         KPI11   KPI12

P1           08/2016    20           30            50         40          60              70              50         100

Product    FYP            KPI11  KPI12      Transformation   Field routine for Period

P1           08/2016       20         30          Group rule 1        (current period)

P1            09/2016      50        40           Group rule 2        (current period + 1)

P1            10/2016     60         70           Group rule 3       (current period +2)

P1            11/2016     50          100        Group rule 4       (current period +3)

etc.....

Above would be an example, for the months like 12 you have to add some more logic so as to obtain the periods correctly.

AM