I'm modeling for a lending company and I'm modeling our loan approval process. I have some basic dimensions relative to our business figured out:
Time(day) Account Opportunity Channel
And I and getting to the modeling of fact table(s) around the loan approval and decisioning process. An opportunity will get one or more "Credit Reviews" which have a series of steps they go through before arriving at a "Credit Decision". My instinct is to model the fact grain at the credit review step level or maybe the credit review level (or have one fact for each).
I'm working with some analysts who are used to using a canned report that is at the opportunity level, and finds a "best credit review", and do their reporting at this granularity. I'm trying to explain my credit approval fact and how it would work and they think it's "overly complicated" and seem to want a fact table that is one-to-one with the opportunity, which is a dimension. And contains information on only the "best" credit review. In my mind that seems odd. I haven't run across a fact table that is at the same granularity (one-to-one) with a dimension. Have you ever heard of this? It feels like they are just trying to reproduce what they are used to, a non-dimensionally modeled report at the opportunity level. I'm trying to explain the utility of having a fact table that is at a higher granularity than they are used to in order to support many questions about the data rather than one, but I seem to be running into a wall getting this concept across.
My question is this. Is it every appropriate to model a fact that has the same cardinality as a dimension? What would you recommend for this situation?