Overlapped master data frm 2 src systems & concat source system prefix prob

[Former Member]

Dear SDN fellows,

Currently in my project I encountered the case where 1 BW system is shared by 2 source systems (R/3 and ECC6) and the master data from the 2 source systems are overlapped (eg. same material no from R/3 and ECC6 have different meaning).

One of the suggestions is to concat master data value with source system ID as prefix. For example:

Material 4711 from ECC6 Source System = AB_4711

Material 4711 from R/3 Source System = CD_4711

However, I found that some master data having data type NUMC (eg. 0EMPLOYEE) that:

u2022 We need to use numeric value so that the prefix is consistent to all master data, hence we can no

longer use symbol like dash, underscore to separate prefix and master data value. This requires

thorough observation to all master data values, for example if we use prefix '99', whether this is safe and

doesn't overlap with existing master data that is not prefixed (we might only prefix master data for the

newly added R/3 source system, and if there is other new source system added in future; so that the

existing master & transaction data that have been live for 2 years using ECC6 source system are

not interrupted).

u2022 Some master data may have been populated with values with maximum length, thus to add the prefix

we need to extend the length of InfoObject (eg. 0COMP_CODE is CHAR(4) and normally uses

values: 1000, 1100, 1200, etc.). To be able to extend the length we have to delete all records in master

data, and consequently we have to delete transaction data in cubes that use master data as well, thus it

still impacts existing data.

Thanks alot.

regards,

arie

[thomas_rinneberg]

Hi!

So you are sure, that you do not have different material numbers for same material (eg. material no 4711 in R/3 is "Material A" and material no 1234 in ECC6 is "Material A" as well)? Because if you did, then you need matching and merging (i.e. introducing a common number and check whether a given material is already present by its attributes rather than its number).

Personally, I would use a common number range resp. name space even then and include two additional attributes u201Csource systemu201D and u201Coriginal numberu201D to the InfoObject. But I would not prefix the material number, but instead, when loading, I would look up the common number via these two attributes (but beware performance! No single selects!). If the material is not yet present (loaded first time), you pick just a new number (e.g. from a number range). By this, original number might even be different data type (e.g. char) than common number (e.g. numc).

So you would need a run to initially fill the two new attributes for the present master data, and afterwards you handle both sources the same way, so it may happen that the common number is not the same like the original number even for the old source system (if new materials are created in the old source system).

If you ever would have the need to introduce matching, this solution would be easiest extended to fit in. Drawback is of course, that from the number itself, you cannot guess neither where it comes from nor what material it might be. So you might like to change your queries such that they only display the u201Coriginal numberu201D and u201Csource systemu201D.

Best Regards, Thomas

[Former Member]

Thanks alot Thomas for great, unique workaround that I'm not able to think of. However it may not be working in our case, because we already have our BI system live for 2 years, there are existing master data that are already referenced by transaction data in cubes, and we don't want to interrupt this.

If we switch to this workaround, the common number range may conflict with existing master data that has data type NUMC.

Also when looking up 2 attributes (source system and original number) to determine new number, in addition to checking existing master data, as data is loaded package by package within the load request, we may need to check earlier package loaded (whether a record in latter package has same attributes or not), maybe this can be done using static internal table, or checking in master data view table (/BI0/M...) where field OBJVERS="N" (New) as these records are not activated yet.

[thomas_rinneberg]

Hi!

I did not mean to change your existing master data key values. Just fill the two new attributes (in initial run: Source system = the old one you had before / original number = common number (i.e. the current key)).

Regarding different requests / data packages: You should activate the master data before you load the next request. And if you do not have double master data keys coming from the source (or even then, if you use semantic groups in the DTP), you should not have the same record in different packages.

Best Regards, Thomas

[Former Member]

Hi Thomas,

Thanks for the reply. Yes, double master data keys is rare case and using semantic group in DTP will help.

Do you mean for all master data with data type NUMC, we still preserve existing master data with their existing key values? and new records after that will get the highest existing number + 1?

eg.

highest existing 0EMPLOYEE (NUMC(8)) = 00123456

new 0EMPLOYEE = 00123457

I'm afraid sometimes there might be special number range arrangement, for example:

0EMPLOYEE from Dept A, will use prefix 1 = 10000000 - 19999999

0EMPLOYEE from Dept B, will use prefix 2 = 20000000 - 29999999

...

0EMPLOYEE from Dept I, will use prefix 9 = 90000000 - 99999999

In this case might be difficult to use this method. Also in case the length is limited (eg. NUMC(4)), there may not be sufficient numbers to be used, especially if the master data by nature is huge and yet will be shared by multiple ECC or R/3 source systems. Sorry I don't know yet real example of master data falls into this case, but the limitation does exist.

How about re-creating all the master data InfoObjects (eg. 0MATERIAL is re-created as CMATERIAL), do you think it's viable to use this approach? about the complexity to re-create all the transfer/update rules (particularly the ABAP routines inside), is there an efficient, robust way to re-create it, perhaps programmatically?

Thanks alot Thomas.

[thomas_rinneberg]

Hi!

Valid objection. On copying: With BW 7.30, there is the data flow copy wizard.

Best Regards, Thomas

[Former Member]

Hi Thomas,

Thanks for the reply. Yes I heard of data flow copy feature in BW 7.3, but if we re-create the master data InfoObjects (eg. 0MATERIAL is re-created as CMATERIAL) we're not able to copy paste the data flow from BI Content as the fieldname is now different, also all the ABAP routines inside transformation rule need to be re-created as well for the same reason.

[Former Member]

Hi,

You can try compounding the infoobject with the source system. Please see the below for the same.

http://help.sap.com/saphelp_nw04/helpdata/en/80/1a6399e07211d2acb80000e829fbfe/content.htm

[Former Member]

Thanks Rahul for your info. We are aware with 0SOURSYSTEM compounding method, but we consider not to use it, because our BW system has been live for 2 years, and adding 0SOURSYSTEM compounding will require records in all master data InfoObjects to be deleted first (and consequently transaction data as well) and reloaded later on, which we try to avoid.

[Former Member]

Even if the values come from 2 different sources , it will not impact your report output as in masterdata values are overwritten .

As, a result there is no need to give any prefix for the data coming from another source system .

There are instances where master data gets data from different source systems , it does not effect your output.

[Former Member]

Thanks Amrita. In our case we don't want master data from 2 different source systems to be overwritten, because we need both of them.