Custom stored procedure for staging load.

Question

Can we create custom stored procedure in MDS database using stg schema and invoke the same from ETL in order to load the entities from staging tables? I tried using the SP which is provided by MDS (stg.udp_<staging entity name>_Leaf). But the problem using this is, it does updates and inserts based on the code attribute. As per my requirement, i have to update and inserts based on a different attribute or set of attributes (not the code or the name). Is this possible? If yes, what are the precautions which has to be taken while writing the SP?

Thanks.

Answer 1

Not sure if I understand the problem correctly. If  you are using EBS, udp_<staging entity name>_Leaf will update the MDS table using the code attributes. However, if you want to update using any other logic, you will have to write an intermediate SP to populate the corresponding records in the staging table. Let's take an example: update the category of all products whose color = 'Blue'. In this scenario, you can enable a subscription view, write an SP that will query the view to get all products whose color = 'Blue', modify the value of category attribute and insert the records to the staging table with importtype=0. Next when you run udp_<staging entity name>_Leaf , it will process all your records that you had earlier picked up using a different column in the search condition. 

Answer 2

Hi,

Yes, i understand that if i am using EBS (upd_<staging entity name>_leaf), the MDS table is updated using the code attribute. But how does it work for an entity which has Code to be set as autogenerated? What should we populate the staging table code attribute with?

Below is my requirement. I have an Entity "Ent1" (Staging table is StagingEnt1) which has code set as autogenerated. I have two sources populating StagingEnt1. I want to udpate the MDS table with Name attribute (Or could be composite key update. SourceKeyAttribute + Name attributes) instead of Code attribute. Is this possible? If my question is not clear, i can provide datasets in order to understand better.

Thanks.

Answer 3

I understand the scenario now. This is what I feel is going to happen: If you hve set the code to autoincrement, if you insert a record (be it new or update) to staging without the code, it will always do an insert. Even if you provide the name field, and the name already exists, it will insert if the code is not present. This will work for you for the 'real' inserts. For updates, you will have to use the approach I mentioned in my prior post. You will first have to join with an existing subscription view to fetch the codes using your unique column (be it name or any other column). And then insert the records (this time you have the code) into staging.