June 5, 2008 at 2:30 pm
I'm just starting out with SSIS and I'm not sure what the best approach would be for the following: I need to load a data warehouse fact table which among which for each oder line item shows the list price, the sum of any applicable discounts if any, royalty if any, and one or more fees.
This is coming, on the OLTP side primarily from an order detail table which has a one many relationship to an order pricing detail table. The pricing detail table has at least one record for each line item showing it's list price, and could have another record (of type "royalty", showing a royalty value, and could have none or more records of type "discount" having values for possible discount types, as well as none or more records having values for various fees.
I'm thinking of retrieving the line item data, and then doing lookups for the various pricing elements.. but I'm not sure how to effectively assign default values of 0 for e.g. discounts if there are none. The lookup in these cases would fail and I would presumably need to use the on error branch to assign the default value?
Or is there a better approach to this type of thing?
Any examples of this type of thing that I could look at?
Thanks,
Ilmar
June 6, 2008 at 1:37 am
hey Ilmar,
lookups sound about right.
What you would need to do is redirect the rows not found, which would move into an "error stream", where you could use a column derivation of 0 to assign a discount value after which you merge them with your main stream.
~PD
June 9, 2008 at 12:12 pm
Thanks.. I thought about that, but since I need to do a number of lookups like this, merging them each time seemed like a lot of overhead. What I finally wound up doing was leaving them as null, and then at the end, in a derived column component, replacing all the nulls in the appropriate columns with the default values.. Seems to be working ok..
Ilmar
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply