Combining Fact Tables for Enterprise Reporting

View previous topic View next topic Go down

Combining Fact Tables for Enterprise Reporting

Post  pcs on Wed Oct 03, 2012 12:40 pm

Hello All,
I work for a loan origination company.

We have transaction fact tables for each step of the loan origination process:
FactProspect captures information for each prospective loan origination:
- ProspectID
- Prospect Date
- Customer
- Count
FactLead captures information for each prospect that becomes an active lead:
- ProspectID
- Count
- Lead create date
- Lead assigned date
- Credit pull date
- Loan Amount Requested
- Reported Property Value
FactLoan captures the next step in the process where the lead becomes a loan:
- ProspectID
- Count
- Loan Origination date
- Loan To Process date
- Loan to Underwriting date
- Loan Amount
- Appraised Property Value
FactLoanFunded captures information about the funding of a loan:
- ProspectID
- Count
- Funded Date
- Loan Amount Funded
FactLoanSold captures information when we sell off the loan
- ProspectID
- Count
- Purchaser
- Sold Date
This is a typical sales funnel (more prospects than leads, more leads than loans, more loans than loans funded and a lag between funding and sale)

My question is how to consolidate the data for reporting from reporting services and an Analysis Services cube from all processes?

We currently have one view for each fact table. Over time these views have expanded to include measures and dates from other processes. (For example the FactLoanFunded view includes ProspectCreateDate, LeadCreateDate, and LoanOriginationDate; as well as LoanSoldDate)

I was thinking of creating a single view that consolidates all of the measures and dimensions in one large logical fact table:
- ProspectID
- Customer
- Purchaser
- Prospect Count
- Prospect Date
- Lead Count
- Lead create date
- Lead assigned date
- Credit pull date
- Loan Amount Requested
- Reported Property Value
- Appraised Property Value
- Loan Count
- Loan Origination date
- Loan To Process date
- Loan to Underwriting date
- Loan Amount
- Appraised Property Value
- Loan Funded Count
- Funded Date
- Loan Amount Funded
- Loan Sold Count
- Purchaser
- Loan Sold Date
Pro:
This view would simplify things from a management standpoint (one view vs. several we'd have to keep in sync),

Cons:
Performance (has to resolve all data joins even if all we want is data from one process)
The fact table would get wider as we add attributes and measures to each process.

I also considered a complementary accumulating snapshot, but have not gone much past the conceptual phase of planning that. (and isn't that pretty much the same thing as the contemplated view?)

Are there preferred alternatives to combining this data?

Thank you,


pcs

Posts : 20
Join date : 2009-02-03

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum