# Boolean as measure in fact table or dimension/attribute value

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.

• 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. Commented Aug 7, 2017 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.
– mhep
Commented Aug 7, 2017 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? Commented Aug 7, 2017 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.
– mhep
Commented Aug 7, 2017 at 13:30

• 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.
• 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.