Multiple Facts or Single Facts and Status Table?

View previous topic View next topic Go down

Multiple Facts or Single Facts and Status Table?

Post  meb97me on Tue Mar 08, 2011 10:18 am

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

meb97me

Posts : 34
Join date : 2010-07-28

View user profile

Back to top Go down

Re: Multiple Facts or Single Facts and Status Table?

Post  ngalemmo on Tue Mar 08, 2011 1:37 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Multiple Facts or Single Facts and Status Table?

Post  meb97me on Wed Mar 09, 2011 5:05 am

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

meb97me

Posts : 34
Join date : 2010-07-28

View user profile

Back to top Go down

Re: Multiple Facts or Single Facts and Status Table?

Post  Ashish Mishra on Wed Mar 09, 2011 8:01 am

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 .



Ashish Mishra

Posts : 11
Join date : 2011-02-22

View user profile

Back to top Go down

Re: Multiple Facts or Single Facts and Status 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