Understanding Fact and Dimension table
3 posters
Page 1 of 1
Understanding Fact and Dimension table
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
Re: Understanding Fact and Dimension table
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.
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.
Similar Issue - Understanding Fact and Dimension Table distinction
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!
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
![-](https://2img.net/i/empty.gif)
» joining dimension table to dimension and again fact table
» Joining Type II Dimension Hierarchy directly to Fact versus through a Dimension table
» Large Dimension table compared to fact table?
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Joining Type II Dimension Hierarchy directly to Fact versus through a Dimension table
» Large Dimension table compared to fact table?
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum