Design Question - Multiple Fact Tables at the same Grain

I am working with a transactional system where pretty much everything is related to one key, LoanId. There are logical breaks like Loan Origination, Loan Funded, Loan Sold. There are measures that are related to each one of these events. My thought was to create the following:

FactLoanOrigination – One row per Originated Loan
FactLoanFunded – One row per Funded Loan
FactLoanSold – One row per Sold Loan

The design problem that I am having is that each of these Fact Tables would have the same business key and grain, LoanID. Is this an acceptable design? I thought about adding the Date of each event as part of the key, but this scares me because if the date changes, I would have the same loan represented twice. I could leave everything in one Fact table at the LoanID grain but this would make one very large fact table.

In the past, I have always had good transactional Keys that I would use for the Business Key and grain of the fact table like LoanID, OriginationID for the first fact table or LoanId, FundedID for the second Fact Table. I just don’t have that in the current system I’m working with.

Any thoughts or ideas would be greatly appreciated

Having separate atomic fact tables is the correct way to do this. Each fact represents a different business process that occurs at different times with different potential outcomes. Putting everything into one table only complicates matters.

Should you need to present a lifecycle view of the business, queries can easily combine the facts you need and, if necessary, it is easy to create an aggregate from the atomic facts.

My thought is
Assuming table have fields
LoanId -- represents natural key from source
EventId -- loan events information

You need to have two more fields (Make FctTranId, EffDt , LoanId as part of composite primary key.
FctTranId -- which is running number generates based on LoanId and EventId.
EffDt -- Effective date reprents when the event occured or u can aslo load with date when it came to table.

With above combination you can track history records as well as get the latest record quickly.

Looks like you want to present loan life cycle in fact table. Is that what accumulating snapshot fact is supposed to deliver? In this type of fact, the grain is one row per prospective loan borrower. In your example, you would have 3 role playing date keys in the fact to indicate when, whether or not a loan has been originated, funded and sold. I imagine you could have other dates, like loan close date, to fit into this fact table. You would also have other dimension keys in the fact, like loan key and applicant key etc. Part from some obvious measures like loan amount, you should have a number of other useful derived measures, like durations between two important dates, to indicate how long it takes to reach a certain milestone in the lifecycle.

In your periodical ETL load, you need to revisit the the loan record in the fact based on your source and update those date keys and perhaps measures (eg. durations) accordingly, as they might have original unknown values ( not happened yet) and the source has got the dates for the current load. The updates continue until the loan has been closed. Here is another post about accumulating snapshot fact:http://forum.kimballgroup.com/dimensional-modeling-and-data-architecture-f6/conformed-dimension-for-transaction-fact-and-accumulating-snapshot-fact-table-t694.htm .