How do I create a lookup that finds the cost for a set of records, not just one.

Hi,

I'm trying to design ETL for a source system that outputs data with an [eventkey] field that is often duplicated. When there is more than one instance of the same [eventkey], it means that a number of procedures were done at one time. I understand that the best design for the fact table is to use the lowest level of granularity, namely the procedure. The cost needs to be added to the fact table at event level, and so I need to look up a set of rows in a lookup to determine total cost for the rows belonging to each event, then split it by the number of rows in that set. But how do I do this? Or do I need to take a different approach?

The sets are costed unambiguously, and there won't be more then one set for a single [eventkey]. Or if there is, that's one for the loading errors report!

Thanks in advance,
Al

I understand that the best design for the fact table is to use the lowest level of granularity, namely the procedure. The cost needs to be added to the fact table at event level

You got the first part correct, but what is the second part about?

If you have a fact table that includes event, procedure and cost, you already have the cost at the event level by simply summing cost by event. Are you trying to create a second aggregate fact table?

No I'm not trying to create a second fact table.

The source data comes without cost. Cost needs to be added to the fact table from a lookup table. I have costs in a separate document, at the level of a combination of types of procedure, not a single type of procedure. If I had a lookup table it would need a many-to-one mapping, with each set of types of procedure mapping to one cost. Then I would have to divide the Event cost by the number of procedures in the event, otherwise the cost wouldn't sum properly. Is this the best structure, and how do you do a lookup like that?

I hope that's clearer!

Al