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.
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.
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 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)
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 .
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 .