Natural Key in the fact table for ETL purpose ?

View previous topic View next topic Go down

Natural Key in the fact table for ETL purpose ?

Post  VTK on Thu Aug 15, 2013 4:27 pm

I am just wondering how common is to use a Natural Key of a grain in the fact table ? For Ex: If a grain of the fact table is 1 row per policy then I would like to have Policy Number in the fact table along with Policy Dimesion ID. I understand that Policy Number would be in the Policy Dimension but having this in the fact table makes the ETL much more efficient. Idea is to hide these keys from the users but use it for ETL.

One main reason is that when we don't have a Policy in the Policy Dimension for whatever reason but we got it from the Fact extract then we have to default the record to say -1 and then when we have that
Policy in Policy Dimension, we need to go back and update the fact record with the Policy number. If we don't have the Policy Number then we can't do that as there could be multiple -1's are there.

I guess another option is to just omit the records if the main dimension is missing it but is that the standard practice ?

How have you handled this scneario ?

Thanks

VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: Natural Key in the fact table for ETL purpose ?

Post  ngalemmo on Fri Aug 16, 2013 10:40 am

No.  Storing extraneous columns in a fact table significantly degrades the performance of the table in queries.  A typical query almost always winds up doing a scan of the fact table.  Extra columns means less rows retrieved per block meaning more reads and longer query times.  Disk I/O is the slowest operation in any query.

If you receive a fact with a policy number that does not exist, infer a new policy dimension row using that number and reference that row.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Natural Key in the fact table for ETL purpose ?

Post  VTK on Fri Aug 16, 2013 1:39 pm

Are you saying that we need to maintain a cross reference table where we store the Natural key and an ID and use the ID in the fact table as a SK.
Later, when we have that in the dimension, we have to lookup to this table and then take the assigned SK and replace it with actual SK.

Thanks

VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: Natural Key in the fact table for ETL purpose ?

Post  ngalemmo on Fri Aug 16, 2013 1:55 pm

The dimension is the cross reference as it contains both the natural key and the surrogate key. When you infer a dimension you create a new dimension row with a surrogate key and the natural key. When the dimension information finally shows up in the normal load, it updates the other attributes.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Natural Key in the fact table for ETL purpose ?

Post  svmayor on Fri Oct 11, 2013 1:49 pm

Another thing to consider if using the -1 value as the surrogate key to connect fact records to dimensions is, if that SK is used in the fact table as part of the primary key for those records, then you will eventually get a unique constraint violation error (database level) and then the process will fail. By assigning (infering) a dimension record, you avoid that.
avatar
svmayor

Posts : 3
Join date : 2013-03-06

View user profile

Back to top Go down

Re: Natural Key in the fact table for ETL purpose ?

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