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

View previous topic View next topic Go down

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

Post  Al Wood on Wed Dec 08, 2010 12:59 pm

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

Al Wood

Posts : 46
Join date : 2010-12-08

View user profile

Back to top Go down

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

Post  ngalemmo on Wed Dec 08, 2010 3:45 pm

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?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Sorry, I wasn't clear

Post  Al Wood on Wed Dec 08, 2010 4:52 pm

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


Last edited by Al Wood on Wed Dec 08, 2010 5:34 pm; edited 5 times in total (Reason for editing : Further clarification)

Al Wood

Posts : 46
Join date : 2010-12-08

View user profile

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum