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

[nelsonriveraw]

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

[BoxesAndLines]

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.

[Jeff Smith]

Why does the fact table have a primary key?

[BoxesAndLines]

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

[Sponsored content]