Uploading data file with only a partial Master Data (ID)

[former_member196865]

Hi Gurus,

We have a data file with a field that only has the last four digits of the Master Data ID. The Master Data ID has 7 digits and was created when the actuals data was being uploaded. But now the budget data flat file seems to have only the last 4 digits in them. Is there a way we can change the transformation file so that it will accept the last 4 digits of the flat file and write it to the 7 digit ID in the Master Data?

Note: I also have a field in the Master Data with the 4 digits but that is just a property. I am not sure if we can use the property to write the data. I have suggested maintaining a conversion file bu the user doesn't want to maintain that. Is there a work around for this?

[Former Member]

David...absolutely dint understand the requirements! Can you please explain it with some example and screenshot?

JP

[former_member196865]

Hi Priyanka,

Please find the screen shots.

As you can see the dimensionX on the transformation file expects the entire ID as is shown in the Master Data but the Data input file only has partial ID data(last four digits of dimensionx). I have suggested a conversion file where we can maintain the masterdata IDs and incoming data ids, but the user doesn't want to maintain that. I am thinking if we can do some sort of conversion in the conversion file to accept the partial data ..or if we can write something in the transformation file to accept the partial id (last four digits) for dimensionX.

[Former Member]

David,

What your customer is asking is not gonna happen! We cannot accept partial ID's anyhow through conversion or transformation! We will need full ID to save the data on server! Since its MS we cant even have routines to change the ID's while loading!

I guess you have only 2 options...

1. Use conversion file to convert the ID's

2. Otherwise change the ID's of Dimension X to only those last 4 digits...as it is your description has those starting alphabets with it. Then it will be straight forward.

Regards,

JP

[former_member196865]

Hi Priyanka,

Can't we use something like

dimensionx.property1=*col(1)

or

write a script logic to convert the values in column 1 into dimensionx's id and run it in the data manager package?

or

import it into a temporary table and push it into the fact table?

[Former Member]

dimensionx.property1=*col(1) - Does your flat file has property column? Even if it did then....dimensionx.property will be 1600 only where are the alphabets? and honestly that syntax doesnt work!

write a script logic to convert the values in column 1 into dimensionx's id and run it in the data manager package? - I dont think this is possible!

import it into a temporary table and push it into the fact table? - I have very little knowledge of MS version, so I really dont know how this can be done here....pushing in the fact table will not help it during the loading of flat file!

JP