join problems between BSEG and CSKS

[rajarshi_muhuri]

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.

[rajarshi_muhuri · Accepted solution]

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

[Former Member · Accepted solution]

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

[rajarshi_muhuri · Accepted solution]

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)

100	DE04	DE04106900	31.10.2009
100	DE04	DE04106900	31.03.2011
100	DE04	DE04106900	31.12.9999
100	DE04	DE04108951	31.03.2011
100	DE04	DE04108951	31.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 .

[Former Member · Accepted solution]

Please do update this thread if you discover a better more elegant way of doing this.

Not with the same client presently but yeah worked closely with Accenture when I was there earlier this year

Thanks,

Anooj

[rajarshi_muhuri · Accepted solution]

Will update this thread  ..

and also need some help on COPA RDS , will open a new thread

[rajarshi_muhuri · Accepted solution]

For a Quick solution , I had done a "Left outer Join" with BSEG as the left table and put a filter on CSKS in such a way that each line is unique based on  MANDT/KOKRS / KOSTL.

But since its time depenedent master data ( slowly changing dimensions) we do need historical master master data.

Thus I will implement variable/parameters to pass the period during run time . parameters for more performance as it alters the way the query is executed ..

I still will ask the CSA if there is any better solution .