6 Replies Latest reply: Jun 21, 2012 6:51 PM by Rajarshi Muhuri RSS

join problems between BSEG and CSKS

Rajarshi Muhuri
Currently Being Moderated

Hi

 

I am joining BSEG table with CSKS  (Cost Center Master Data). The CSKS is unique with the combination  KOKRS (Controlling Area) / KOSTL ( Cost Center ) and / DATBI (Valid To Date) .

 

BSEG has KOKRS and KOSTL , but does not seem to have a "Valid to Date"  . 

 

 

How do I join BSEG and CSKS ? is there any other table that I can bring in to get the "Valid to Date" in the data foundation , so that I can still connect CSKS as an attribute view .

 

 

My BSEG is also connected to BKPF and CE4xxxx_ACCT in the data foundation.

  • Re: join problems between BSEG and CSKS
    Rajarshi Muhuri
    Currently Being Moderated

    Right Outer Join between BSEG and CSKS solves the problem ofcourse , would would it be logically correct / meaningful ?

    • Re: join problems between BSEG and CSKS
      Anooj Behanan
      Currently Being Moderated

      Hi Rajarshi,

       

      No expert in this ECC application area, but just looking at it with my BW hat on:

       

      Assuming you have multiple records in CSKS for a controlling area/cost center combination with different validity periods but no overlap, I think ideally you should be joining your data foundation with CSKS attribute view on a transaction date column for .e.g account posting date, document date, document creation date etc (depending on your reporting requirement) with the join only bringing back the master data record where the date on the transaction falls between Valid From and Valid To Date on the master data record in CSKS.

       

      However, I am not sure if you can do such a join within Analytical views.

       

      With a right outer join and if CSKS is your right operand, you also run the risk of bringing back cost center records that have no accounting documents against them in addition to of course multiple records where a valid join happens.

       

      Options I can think of:

       

      Have a calculation view (with SQL or CE functions) that selects data from your joined analytical view & then filter out a) records that have null values on the key transaction data columns (e.g. accounting document number) and b) records where posting date (or the date you have chosen for the join) is not within the cost center master data validity period.

       

      You could may be do the above directly on the reporting tool as well (for e.g. in an IDT/Universe, a Webi query on the Universe etc) but doing it within HANA will ensure better performance.

       

      OR

       

      May be join the analytical view and the attribute view directly within a calculaton view with the right join conditions.

       

      Also, If my first assumption isn't true, ie, if the data in your CSKS table does not have more than one record for every controlling area/cost center combo, then you simply can join it with your data foundation on these two columns only without bothering about the valid to date column.

       

      Thanks,

      Anooj

      • Re: join problems between BSEG and CSKS
        Rajarshi Muhuri
        Currently Being Moderated

        Thanks Anooj for your helpful insight. And outer join would do exactly what you professed ( bring redundant empty line items ) . In universe one can do a join called theta joins ... which can do a join between validity dates .

         

        The CSKS has these data (MANDT/KOKRS / KOSTL / DATBI)

         

        100DE04DE0410690031.10.2009
        100DE04DE0410690031.03.2011
        100DE04DE0410690031.12.9999
        100DE04DE0410895131.03.2011
        100DE04DE0410895131.12.9999

         

        so you see that DATBI is bring in the uniqueness . So doing an outer join first and then putting a filter in the SQL scripted calc view would be a solution in the worst case ( i guess)

         

        Anyway I will ask the SAP CSa of the client on this .

         

        I looked at your linkedin  profile...... We are supporting the same UK client .

Actions