Understanding Fact and Dimension table

View previous topic View next topic Go down

Understanding Fact and Dimension table

Post  tadikamalla on Wed Dec 19, 2012 1:58 pm

Hi, I have one simple scenario can you please confirm if this should be a fact table or dimension table. A patient went to a hospital for a visit and doctor tracks diagnosis for that visit and suggests a procedure. So in this scenario I have to create some main table like Patient, Visits, Procedures, Event, Medication and Diagnosis table. Among these tables which one should be a fact and which one should be a dimension table. Medication would defiantly be a Fact table because it has the quantity sold but all others does not have any numeric data. But business requirement would be what kind of visits a patient has taken and what are the different procedures a patient has taken. Please confirm.

tadikamalla

Posts : 1
Join date : 2012-12-19

View user profile

Back to top Go down

Re: Understanding Fact and Dimension table

Post  ngalemmo on Wed Dec 19, 2012 7:49 pm

A fact represents a business event or state (such as membership status). Dimensions provide context for the events and states.

Visits, procedures, consumption of drugs and materials are all business events. What procedure, what diagnosis, when, which drug, who was the patient, who was the physician, at all contexts for those events.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Similar Issue - Understanding Fact and Dimension Table distinction

Post  at710 on Mon Jan 07, 2013 1:25 pm

Hello, I'm designing a DWH for a company specialised on temporary employment and secondment. Currently I have one dimension table for employees. Most of our employees work (for one of our customers) some days in succession every few weeks. Therefore my dimension table also contains an entry_date and leaving_date column holding dates that indicate that this employee had a contract with our company during this time. Which means that all the other time this person is not considered as an active eployee. So i have a separate row in my employee table each time an employee works for some days (see example).

simplified employee table looks like:
surrogate_key, business_key, name, entry_date, leaving_date
1, 1, alice, 01.01.2012, 10.01.2012
2, 1, alice, 02.02.2012, 20.02.2012
3, 2, bob, 01.01.2012, 10.01.2012
4, 2, bob, 02.02.2012, null

However for my final report i'm calculating the number of employees (entered, active & leaving) for one week as a measure. These measures are calculated based on the entry- / leaving_date columns of my employee dimension.
I somehow feel like having things mixed up. Is it common to have measures calculated based on values residing in a dimension table or is this a no-go? Isn't my employee table updated far to often (every few days for most of our employees) to be treated as a SCD (type2) dimension (there are also other attributes like "weekly hours of work" which can change on a daily basis during emloyment, so splitting out the entry- / leaving_date columns won't save me from proper historization)? Would you recommend to split the information regarding the contract from my employee table?

Thank you for any advice!

at710

Posts : 10
Join date : 2012-11-02

View user profile

Back to top Go down

Re: Understanding Fact and Dimension table

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