I was wondering if anyone had any suggestions on fact table design for a business process that is very similar to order header/order detail information. We capture customer feedback (distinct calls to a call center) and subjects associated with the feedback. One of the measures of interest will be distinct feedbacks (so for example, total number of calls) as well as the total number of subjects (so number of complaints for example that comes in through the subject).
The following is an over-simplified example of the OLTP and how the data comes in. We have our model down for designing the dimensions. My key question is how we would measure the number of distinct feedbacks as well as the total number of subjects. The issue is that we're allowing measurements at different grains in the fact table (count(distinct feedbackid) and count(subjectid)). I've been reviewing the Kimball Tips #95 that talks about this at some length. At any rate, thanks in advance for your suggestions. Here is some underlying code. I realize the dimension keys are not here and this isn't a strict representation of a normalized data source. We would have a customer dimension, a feedbacktype_subtype dimension, dim_date role dimension views etc.
DECLARE @Feedback TABLE (FeedbackId INT, DateLogged DATE, CustomerID INT) INSERT INTO @Feedback (FeedbackId, DateLogged, CustomerID) VALUES (1, '20180101', 52) ,(2, '20180101', 77) ,(3, '20180101', 32) DECLARE @Subject TABLE (SubjectId INT, FeedbackId INT, FeedbackType VARCHAR(10), FeedbackSubType VARCHAR(10), IncidentDate DATE, ClosedDate DATE) INSERT INTO @Subject (SubjectId, FeedbackId, FeedbackType, FeedbackSubType, IncidentDate, ClosedDate) VALUES (1, 1, 'Complaint', 'Employee', '20171231', NULL) ,(2, 1, 'Complaint', 'Vehicle', '20180101', NULL) ,(3, 2, 'Compliment', 'Employee', '20171220', '20180101') ,(4, 3, 'Suggestion', 'Service', '20180101', '20180101') -- SELECT CustomerId --actual dim_Customers_key will come from a dim_Customers dimension ,FeedbackType --Combination of feedback type and feedback subtype will come from a dim_FeedbackType_SubType dimension ,FeedbackSubType ,DateLogged ,IncidentDate ,ClosedDate ,F.FeedbackId --Leave as a degenerate in the fact table ,S.SubjectId --Suggest leaving as a degenerate as well? FROM @Feedback F JOIN @Subject S ON S.FeedbackId=F.FeedbackId