Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Best practices for a Fact table that contains a row per date/hour/location/patient stay

3 posters

Go down

Best practices for a Fact table that contains a row per date/hour/location/patient stay Empty Best practices for a Fact table that contains a row per date/hour/location/patient stay

Post  nelsonriveraw Mon Jan 07, 2013 12:01 pm

Hi Group

My question is related to data modeling and best practices for a Fact Table that contains information related to our occupancy by location/date/Hour.

My table looks like this:

SELECT [FACT_Enc_Loc_PK]
,[Date_Key]
,[TimeHour_Key]
,[Location_Key]
,[Medical_Service_Key]
,[Person_Key]
,[MinutesIn]
from [FACT_Enc_Loc]

This table stores 3 years of data (300M records).
Does anybody have any recommendation related to make this more efficient in terms of storage? Is there any opportunity for normalization or aggregated tables?

Thanks

Nelson
nelsonriveraw
nelsonriveraw

Posts : 1
Join date : 2013-01-07
Age : 54
Location : Bogota, Colombia

Back to top Go down

Best practices for a Fact table that contains a row per date/hour/location/patient stay Empty Re: Best practices for a Fact table that contains a row per date/hour/location/patient stay

Post  BoxesAndLines Mon Jan 07, 2013 3:39 pm

Por supuesta. The fact looks fine at the lowest level. You can always aggregate based on common user queries by dropping dimensions and summing the facts. For improving performance, I would look to partitioning the fact table based on common data queries.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Best practices for a Fact table that contains a row per date/hour/location/patient stay Empty Re: Best practices for a Fact table that contains a row per date/hour/location/patient stay

Post  Jeff Smith Mon Jan 07, 2013 5:40 pm

Why does the fact table have a primary key?

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

Best practices for a Fact table that contains a row per date/hour/location/patient stay Empty Re: Best practices for a Fact table that contains a row per date/hour/location/patient stay

Post  BoxesAndLines Mon Jan 07, 2013 6:33 pm

Don't underestimate the usefulness of a single column primary key for a fact table.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Best practices for a Fact table that contains a row per date/hour/location/patient stay Empty Re: Best practices for a Fact table that contains a row per date/hour/location/patient stay

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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