Multiple Facts or Single Facts and Status Table?
3 posters
Page 1 of 1
Multiple Facts or Single Facts and Status Table?
We have a Fact Table that captures "Events" from our CRM system.
The Events covers wide range of things from Initial Referral, Telephone Converstaions, Appointments, Being put forward for Interviews etc etc
a simplified version of the Fact table is this
FACT_Event
EventDateSK
EventTypeSK
SrcID
CustomerSK
EmployeeSK
EventCount (this is set to 1 for each record)
One of the issues we have come up against is for example we have customers put forward for Jobs and part of that screen we capture the current status of that submission
ie
Put forward for Interview
Interview Accepted
Interview Declined
Interview Attended
Job Offered
Job Accepted etc etc
The business are keen to know about all the different status of the Job Submissions (ie how many interview declines have we have in the past month that sort of thing) but we're unsure whether its best to capture these all as seperate events as rolling up the events in the cube could give inaccurate figures as basically there is one job submissions but various status'. Would it be better to have a single job submission record in the fact table but then a seperate table capturing job submission status changes?
Or is it possible to create a scoped calcualtion in SSAS for our event count measure that for a particluar set of EventTypes do a DistinctCount rather on the SrcID rather than a SUM of the Event Count?
Cheers as always for any input
The Events covers wide range of things from Initial Referral, Telephone Converstaions, Appointments, Being put forward for Interviews etc etc
a simplified version of the Fact table is this
FACT_Event
EventDateSK
EventTypeSK
SrcID
CustomerSK
EmployeeSK
EventCount (this is set to 1 for each record)
One of the issues we have come up against is for example we have customers put forward for Jobs and part of that screen we capture the current status of that submission
ie
Put forward for Interview
Interview Accepted
Interview Declined
Interview Attended
Job Offered
Job Accepted etc etc
The business are keen to know about all the different status of the Job Submissions (ie how many interview declines have we have in the past month that sort of thing) but we're unsure whether its best to capture these all as seperate events as rolling up the events in the cube could give inaccurate figures as basically there is one job submissions but various status'. Would it be better to have a single job submission record in the fact table but then a seperate table capturing job submission status changes?
Or is it possible to create a scoped calcualtion in SSAS for our event count measure that for a particluar set of EventTypes do a DistinctCount rather on the SrcID rather than a SUM of the Event Count?
Cheers as always for any input
meb97me- Posts : 34
Join date : 2010-07-28
Re: Multiple Facts or Single Facts and Status Table?
Don't confuse or compromise best practice with end-user reporting requirements. When constructing the data warehouse using a dimensional model implemented on a relational database, you should always capture fact at the lowest level of detail possible.
Counting job submissions versus status states is purely a query issue when loading the cubes. You don't need two fact tables, a count distinct is more than sufficient to handle the situation.
Counting job submissions versus status states is purely a query issue when loading the cubes. You don't need two fact tables, a count distinct is more than sufficient to handle the situation.
Re: Multiple Facts or Single Facts and Status Table?
thanks ngalemmo
so capturing the different status' as sepereate events is the way forward
Events
Job Submission
Interview Offered
Interview Accepted
Interview Declined
Interview Successfull
Interview UnSuccessfull
Job Offer Accepted
Job Offer Declined
so capturing the different status' as sepereate events is the way forward
Events
Job Submission
Interview Offered
Interview Accepted
Interview Declined
Interview Successfull
Interview UnSuccessfull
Job Offer Accepted
Job Offer Declined
meb97me- Posts : 34
Join date : 2010-07-28
Re: Multiple Facts or Single Facts and Status Table?
Hi,
Not sure what is captured under the SrcID
but if each interview right from "Put forward for Interview to Job Accepted" can be identified by some kind of case identifier
than that can be used as degenerate dimension or a standard dim in the fact table and then creation unique count measure on that at cube is simple process.
But anyway it will be best to capture in fact table data at its lowest grain .
Not sure what is captured under the SrcID
but if each interview right from "Put forward for Interview to Job Accepted" can be identified by some kind of case identifier
than that can be used as degenerate dimension or a standard dim in the fact table and then creation unique count measure on that at cube is simple process.
But anyway it will be best to capture in fact table data at its lowest grain .
Ashish Mishra- Posts : 11
Join date : 2011-02-22

» Is it a good idea to have a single STATUS table if we have to store multiple status in the data mart?
» Insurance single fact vs multiple facts
» Multiple Facts Mapped to Single Dimension Record
» From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?
» Multiple facts in a fact table
» Insurance single fact vs multiple facts
» Multiple Facts Mapped to Single Dimension Record
» From Enterprise Models to Dimensional Models. Can a single Dimension table be referenced multiple times in a fact table?
» Multiple facts in a fact table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum