3

My fact table consists of complaints with typical dimensions of who, what and when. We have a target that the complaint should be responded to within a certain time period.

I'm not sure on how I best to model whether the complaint had a response within that time period.

I can store both the target value and the result in the fact table as integers with the result as a measure. Or I could use a dimension which has values of yes/no instead of representing this as a measure, I could then use a calculated measure in my cube. Or I could use a combination of both.

Are there any pros, cons and gotchas modelling the facts in the ways described above?

I anticipate that this fact table will be used to get the total number of complaints, the number of complaints responded to in time, the percentage responded in time and also identify individual complaints which were not responded to in time.

  • 1
    I'm pretty sure in the future somebody will want to know "by how much we miss the target on average". One more thing - I don't understand why target and result are integers in your case. Shouldn't they be of type date? In summary, you should definitely store "registration date", "closing date" and "target date". And then you can have calculated dimension (if target date < closing date then 'miss' else 'on time'). That way you'll cover yourself in the future. – Vladimir S. Aug 7 '17 at 11:17
  • Target could be represented as a date and/or an integer as the number of days. Result wouldn't be a date - that would be the response date. Result was suggested as an integer so it could be summed without the need of an extra dimension. – mheptinstall Aug 7 '17 at 11:43
  • Other questions: Are these SLAs metrics consider only working hours (8 hr days) and not calendar hours (24 hrs)? How big are these tables? Are you doing a full load, incremental, or updating the fact table? – codedawg82 Aug 7 '17 at 13:19
  • It is days only. Can do a full load or update the fact table, it is small in size. My question is more on how the comparison of a date against a target should be modelled. – mheptinstall Aug 7 '17 at 13:30
3

Here is a flexible way to store it that should support changes in requirements in the future.

  • Store the target in a dimension like dim_complaint_type. That way if in the future you have different types of complaints – e.g. Billing, Technical, ... – each of them can be categorized and have different targets. By applying slowly changing dimensions type 2 on the target attribute, you can also change your target from one year to another without changing the result for past complaints. You can also do "what-if" analysis by changing the target in one dimensions and see how much more/less are now on-time.
  • Store the response time in the fact table, as the elapsed_time (integer) or as the registration_date and closing_date as suggested in the comments. My preference goes for registration_date and closing_date with a calculated column elapsed_time which is registration_date - closing_date.

For the result, I wouldn't store it as a flag (yes/no) but as a number. If you call the column on_time and put a 1 for each complaint that meets the target, you can easily SUM on that column to have the total of on_time complaints over a period.

I see three ways of getting the result :

  • Not storing it, but compute it on the fly in your reporting by having the same formula everywhere to check if fact_complaint.elapsed_time is lower than or equal to dim_complaint_type.target or not. It might be dangerous if different persons use different formulas. If you have a tool with a metadata layer (e.g. OBIEE, SAP BO, ...), you can define that result measure once for all.
  • Create a view that joins fact_complaint and dim_complaint_type and add that result measure in the select list. It would be defined once for all. But you need to be careful with that approach as some reporting tool might not act in the same way with a database of a view. The database optimizer might also not be able to do all its magic the same way.
  • Actually store it in your fact table (i.e. compute it at ETL time). This is probably the best approach in term of performance. But it requires to update that column if you change the target for some past complaints.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.