Loading Master and Transaction Data

[Former Member]

Hi Eperts,

I have doubt about data extraction from external source.

My External source (flat file) contains some mater data as well as transaction data in one table. I want to load transaction data from that file to my fact table and (If any amendment ) master data to my master data table.

is it possible through single extraction process.

suggestion required.

Regards,

Aniruddha

[Former Member]

hi folks......me too working with anirudda

so our problem is that.....

see i guess a flat file extraction in BIW terms may have assumed this flat file as generated by somw dbms.(right??)

But in our case we are capturing data from flat files which are filled manually by corresponding persons (as according to the user roles assumed by us).

so as our requirement is we prepared these templates in MS excel and it happened that to make the template meaningful......it included some master data (apart from just ID's)

so our question is ...can't we just load data from flat file into BIW (may be into PSA) ...and then feed this data to various data targets (may be master data & info cubes containing transaction data).

any way of separating master and transaction in BIW after loading it?????

hope this makes our situation clear to u all??

[Ashwin]

Hi!

I explained the procedure to load the master data and transaction data from a single source in my post .. you can follow thhe procedure explained there but.. it has to be done sequentially first master data and then the use the same source to load to cube. But do this only it its unavaoidable i.e you can not have 2 separate files for masterdata and transaction data.

with regads

ashwin

[Former Member]

mmmhh...

I'm wondering if you really need to load these masterdata info...now I understood that for masterdata you are meaning descriptions (other than ID that, however, belongs to the transactional record...), because you need to add them in order to have a file more legible (if you have customer code '100' you want to add a column with 'Mr.Smith'...

But the question is:

do you have already these masterdata in your BW or there is something new ?!

[Former Member]

may be and may not be.....

user may enter a whole new record altogether....(i assure u...we have all the master information in the file he is entering ...and not just name)...or user may change a single attribute of the master (like a changed customer phone number)

now is there a way to detect whether the master information is new or changed after loading it into biw??

[Ashwin]

Hi Sudhir!

you can do one thing.

1) Remove all the attribute values from thecube and just load the key values in the cube. ( this ensurese that if there are new keys loading in to the cube the the key values will automatically be inserted in to the masterdata of the info object)

2)Then map the key fields of masterdata and other transactional data fields in the infosource to the charecterestics in the cube.

3)map the masterdata fields same info source to the masterdata info object with a new set of update rules where you can use the start routine to filter all the changed records(Hope you are not loading a very large data)

4) you can run first uplaod the data from the same file in to the masterdata infoobject which loads all the new and changed records in to master data.

5) then you can load the transactional data into info cube as usaually.

with regards

ashwin

[Ashwin]

For example if the file looks like this

<b><i>Customer ID ;Customername ; quantity ; Rate

1001 ; ABC ;25 ;31.30

1002 ; xyz ;26 ;41.30

1003 ; KJH ;28 ;51.30</i></b>

Then create an info source with the same fields.

<b>Customer _ID Customer name quantity Rate</b>

and then map it to the flat file data source. It must be simple.

Then map the fields <i><b>Customer ID, CustomerName </b></i> to your master data info object. Here you can use a <b><i>start routine </i></b> to find the new or changed records.( the code can be found in many posts which were answered earlier)

Then you map the fields <i><b>Customer _ID, quantity, Rate</b></i> to your cube.

Then you can load the data in to both from a single source.

hope it will be more clear

with regards

ashwin

[former_member184494]

Aniruddha Mohanpurkar ,

Load the master data first if you want any validation / other attributes to be included.

And always load the master data first separately whenever possible.

Arun

[Former Member]

Hi Anirudda,

does your file contain record like this one ?

0DOC_DATE 0DOC_NUM 0PLANT 0MATERIAL 0AMOUNT

25/05/05 12345789 BLABLA 12398329 34.000

If you consider material and plant as 'master data' you need to load them by dedicated infosource (0PLANT_ATTR, 0MATERIAL_ATTR...) and then you can load the above mentioned record without problem.

If you want to load a transactional record, you have to load (as for the example) master data, since a transactional record is always a mix with Key figure and master data elements (material, plant and so on...)..

But in order to have your master data as reference (inside your system) you have to load master data (attributes) and then your transactional records!

Hope now is clearer...

Bye,

Roberto

[Ashwin]

Hi Roberto!

i have a doubt about this isue.. can you explain me what are the problems if we implement this senario with following approach. (I am assuming the file to be in the structure you have suggested)

1 ) first create a info source simmilar to the structure of this file

2) create the transfer rules to map this fields to the file datasource

3) then map this flexible info source to masterdata info object 0material (only 1 field in this case) with update rules where we filter only the unique records or any other records for master data for the 0material .

4) then load the master data infopackage and load the masterdata first

5) use the same infosource in upload rules for any other infoprovide to load the data from all fields.

6)Load compleat transaction data into the infoprovider.

I think this should work for the given problem, although not a good way of handling the dataload.

with regards

ashwin

Message was edited by: Ashwin Kumar Gadi

[Former Member]

Hi Roberto,

My Exel file contain data in follwing manner.

Customer_name quantity Rate

ABC 25 31.30

xyz 26 41.30

KJH 28 51.30

From above case you might have understood that Master data Attribute Customer_name is coming in table instead of Customer id.

In my situation if there any amendment in customer name that should be reflected in master data table as new customer and automatically creat id for newly added customer. in no amendment in the data then data should be updated at intented place.

How should I proceed regarding this.

Required Help and suggestion.

Regards,

Aniruddh.

[Former Member]

Hi Ashwin,

your post already contains the answer: "although not a good way of handling the dataload"..you are right, but sometimes this can be useful if you want to integrate your master data with no OLTP elements (coming from other legacy system!)...

Anirudda,

if you load with master data check, you need to load your customer_name with 0CUSTOMER_ATTR before loading your trasactional record containing it !

Otherwise SID will be automatically created...

Bye,

Roberto

[Ashwin]

Hi Aniruddh!

how do you deal with multiple customers with the same name.. i mean how do you find the key for different customers who has same name.

with regards

ashwin

[Former Member]

I Hope that Aniruddha means customer number for customer_name and not only the 'description' !!!

[Ashwin]

Hi!

If that is the case then it works as explained by Roberto.

But.... from anirudh's post it is given as "Master data Attribute Customer_name is coming in table instead of Customer id" . this is why i needed more information from him.

with regards

ashwin

[Former Member]

Hi Aniruddha,

Not clear with your question.

You have Master data and Transaction data in a File or table.

If you have both the data in a single file and suggested you to separate that and always recommanded to load master data first to avoid loading performance and many more reasons.

If you have both in tables than simply create a Generic datasource and use the datasource for loading that.

Hope this helps.

Please let me know if you have any other issue or your question is something else.

Regards,

Ashish Goel

(Reward with points if this helps)