Best practices for a Fact table that contains a row per date/hour/location/patient stay
3 posters
Page 1 of 1
Best practices for a Fact table that contains a row per date/hour/location/patient stay
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
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- Posts : 1
Join date : 2013-01-07
Age : 56
Location : Bogota, Colombia
Re: Best practices for a Fact table that contains a row per date/hour/location/patient stay
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- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Best practices for a Fact table that contains a row per date/hour/location/patient stay
Why does the fact table have a primary key?
Jeff Smith- Posts : 471
Join date : 2009-02-03
Re: Best practices for a Fact table that contains a row per date/hour/location/patient stay
Don't underestimate the usefulness of a single column primary key for a fact table.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Healthcare Hospital Stay fact table - transaction fact table - a row for In and a row for Out?
» Transaction Hour in Fact table or Separate Time Dimension?
» Adding a fact table with start date and end date to a (SSAS) multidimensional cube
» Actual Date vs Date Key in Fact table
» how to design? Vehicle location status fact table
» Transaction Hour in Fact table or Separate Time Dimension?
» Adding a fact table with start date and end date to a (SSAS) multidimensional cube
» Actual Date vs Date Key in Fact table
» how to design? Vehicle location status fact table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum