modeling multiple fact tables

Hi I am new to dimensional modeling looking for some advice. here is my scenario...

I have 3 fact tables loans- defect and notes. relation is a loan can have multiple defects(defect events) and a defect can have zero or more notes (note events). Loan has its own dimensions that may be used with defect facts and so on ex: number of defects for a given loan type (loan type being dimension of loan).
My initial thought was to use loan as dimension rather than fact but then there are facts on loan like when loan is created and what is type of loan and etc, so it needs to be a fact

Is it a good idea to use loan table for both fact and dimension or use it as a junk dimension for defects fact table and have defect as junk dimension for notes fact table?. Or is there any better way that i am missing. Any help is appreciated.

Thanks

Facts contain measures and FKs to dimension tables. Facts represent business events, loan origination is one such event. Is a dimension ever a fact? No. Is a fact ever a dimension? No. Can you have a loan dimension, maybe. Can you have a loan origination fact? Sure.

Good dimensions are sharable across fact tables. Build fact tables one at a time.

I understand that its bad idea to have multiple fact tables, in my situation i cant have one , since the grain between loan, defect and note event is different. Its like multi level hierarchy. Loan is parent to defect and defect is parent to note event. Also a loan can have zero or more defects and a defect can have zero or more notes. That being said our usual queries will be based on all these three facts. Do you suggest that i implement them separately?.

My queries will look like give me list of all loans and count of defects that are of type 'A' defect, in this case loan number needs to be listed which is acting as a dimension (possibly a degenerate dimension) or give me a count of all notes that contain defect of type 'B'.

I am looking for a way to implement this in single model so i can traverse from loan all the way to the defects

Where did you hear that? Certainly grain is one reason you need different fact tables, but also the nature of the event, timing and a lot of other reasons (such as having a model that makes sense) necessitate implementing multiple fact tables.

As far as what fact tables you need, I have no idea, because you are not describing what happens (what are the events) but rather what the relationships are. A fact has a relation to all context (dimensions) pertinent to the fact. Facts do not have relationships to each other, other than in queries and only along conforming dimensions.

as far as nature of events goes.. A new loan record is created when a loan is approved, from approval to closing there may be several events happening to a loan which are tracked by loan event transaction table (events include assigning to a specific department to process etc).

Now when loan is being processed it can trigger a deficiency which is some thing missing from a particular loan and these are tracked by separate transaction table and these deficiencies can be be of different types which is in a look up table.

A deficiency can in turn trigger multiple actions or notes which are tracked n separate transaction tables and like deficiency note has different types in lookup table.

here is my model so far
facts (same a sthree transaction tables i have in relational model)
i have loan fact (has loan number as degenrate dim and other facts like balance, loan amount etc), defi (factless fact as they are just events that are triggered by loan) and note (fact less fact as they are events that are triggered by defi)

dimensions:
i have loantype, property, channel, loan date and closing date etc as conformed dimensions i used them as conformed they apply to all three facts

for defi
i have defi type, defi date, analyst, defi closing date which apply only to defi

for note
i have note type, note closing date which apply only to note


I have been able to cover about 50% of requirements with this model

however the thing that missing are for example i need number of loans and amount for which defis are closed in last month, in this particular query i cannot answer this from loan fact as it is using defi specific dimension which is defi date. I cannot include defi date in loan fact because there can be multiple defis. One solution i can think of is to include all loan facts in defi and count distinct , i dont like this idea because of performance and i might need to do same for note as well.

Any idea on how to implement this. Thank you

I think your dimensional design is sound. You probably can answer your questions by referencing information from multiple fact tables. It's very often the case to use the factless fact tables as a filter/source of information to your transactional/accumulating snapshot fact tables.

thank you for the reply, but here is an example that might not work
i have a loan let say loan number 1 which has defis 1 , 2 and defi type 'a' and loan amount 200

now if i want to get all loans and amount of defi type 'a', currently i get loans 2 and amount 400 which is incorrect, because i use defi fact table (since i have to use dim specific to defi in where clause) whcih has two defi's with same loan number.

How can i address this issue? do i need setup a bridge table many to many between loan fact and defi dim?

Do you have a diagram handy that can help us in this discussion? I just want to make sure we're looking/talking about the same thing

added image, i apologize if its messy