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

View previous topic View next topic Go down

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

Post  nelsonriveraw on 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
avatar
nelsonriveraw

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

View user profile

Back to top Go down

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

Post  BoxesAndLines on 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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

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

Post  Jeff Smith on 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

View user profile

Back to top Go down

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

Post  BoxesAndLines on Mon Jan 07, 2013 6:33 pm

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

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

View user profile

Back to top Go down

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

View previous topic View next topic Back to top

- Similar topics

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