Why use a Fact table?

Question

• Greetings. My organization has many DB's that have nothing to do with financials, etc. In fact we can have lots of tables in a DB that have very little numeric data. However, folks are asking me to create a dimensional model for a data warehouse for this data. I have some dimensional modeling experience, not a lot for sure. My knowledge is of creating Fact tables for the Integer data. That being said I'm not sure how/ why I would do this in this scenario. Any ideas?
TIA, ChrisRDBA
Monday, May 23, 2011 8:23 PM

• While facts commonly have numeric measures, it is quite possible to have a 'factless' fact. Often a fact represents an event such as an order, a deposit or helpdesk call. These will generally happen to an individual or entity and have a date associated but they do not necessarily have to have anything numerically measurable, orders clearly have quantities and values but consider an admission to hospital. An admission will happen to a Patient on a particular day and will be to a certain ward but there is no obvious numeric data associated with the admission. When we query the data warehouse we might ask how many admissions happened a simple row count? or How many different patients were admitted (a distinct count on the surrogate key of the patient dimension)?

In this simple example the fact still gives us 2 useful measures that we can slice and dice without containing any actual measurement on the fact.

It is also possible to have many numeric measures without them being necessarily financial data. To take another example from healthcare data (because that is what I work with). We might decide to create a 'snapshot' fact which contains the count of appointments that a patient had with a doctor in a particular month. The grain of this fact will be by patient, by month and then we may have a count of appointments as a numeric measure on the fact, a further measure might be the count of appointments that the patient did not turn up to.

Hopefully they are two examples of how data that is not heavily numeric still lends itself to dimensional modelling.

• Marked as answer by Wednesday, May 25, 2011 5:01 PM
Monday, May 23, 2011 8:55 PM

All replies

• While facts commonly have numeric measures, it is quite possible to have a 'factless' fact. Often a fact represents an event such as an order, a deposit or helpdesk call. These will generally happen to an individual or entity and have a date associated but they do not necessarily have to have anything numerically measurable, orders clearly have quantities and values but consider an admission to hospital. An admission will happen to a Patient on a particular day and will be to a certain ward but there is no obvious numeric data associated with the admission. When we query the data warehouse we might ask how many admissions happened a simple row count? or How many different patients were admitted (a distinct count on the surrogate key of the patient dimension)?

In this simple example the fact still gives us 2 useful measures that we can slice and dice without containing any actual measurement on the fact.

It is also possible to have many numeric measures without them being necessarily financial data. To take another example from healthcare data (because that is what I work with). We might decide to create a 'snapshot' fact which contains the count of appointments that a patient had with a doctor in a particular month. The grain of this fact will be by patient, by month and then we may have a count of appointments as a numeric measure on the fact, a further measure might be the count of appointments that the patient did not turn up to.

Hopefully they are two examples of how data that is not heavily numeric still lends itself to dimensional modelling.

• Marked as answer by Wednesday, May 25, 2011 5:01 PM
Monday, May 23, 2011 8:55 PM
• You would probably benefit from posting this question on the SQL Server Analysis Services forum http://social.msdn.microsoft.com/Forums/en-AU/sqlanalysisservices/threads  or the Data Mining Forum http://social.msdn.microsoft.com/Forums/en-AU/sqldatamining/threads
Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
Monday, May 23, 2011 10:58 PM
• You would probably benefit from posting this question on the SQL Server Analysis Services forum http://social.msdn.microsoft.com/Forums/en-AU/sqlanalysisservices/threads  or the Data Mining Forum http://social.msdn.microsoft.com/Forums/en-AU/sqldatamining/threads
Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

I would, but we may not be using AS -- we may create a dimensional model, but then not create Cubes.

TIA, ChrisRDBA
Wednesday, May 25, 2011 5:02 PM
• While facts commonly have numeric measures, it is quite possible to have a 'factless' fact. Often a fact represents an event such as an order, a deposit or helpdesk call. These will generally happen to an individual or entity and have a date associated but they do not necessarily have to have anything numerically measurable, orders clearly have quantities and values but consider an admission to hospital. An admission will happen to a Patient on a particular day and will be to a certain ward but there is no obvious numeric data associated with the admission. When we query the data warehouse we might ask how many admissions happened a simple row count? or How many different patients were admitted (a distinct count on the surrogate key of the patient dimension)?

In this simple example the fact still gives us 2 useful measures that we can slice and dice without containing any actual measurement on the fact.

It is also possible to have many numeric measures without them being necessarily financial data. To take another example from healthcare data (because that is what I work with). We might decide to create a 'snapshot' fact which contains the count of appointments that a patient had with a doctor in a particular month. The grain of this fact will be by patient, by month and then we may have a count of appointments as a numeric measure on the fact, a further measure might be the count of appointments that the patient did not turn up to.

Hopefully they are two examples of how data that is not heavily numeric still lends itself to dimensional modelling.

Thanks!
TIA, ChrisRDBA
Wednesday, May 25, 2011 5:02 PM