Change grain of a fact to facilitate the drill across

View previous topic View next topic Go down

Change grain of a fact to facilitate the drill across

Post  veskojl on Thu Apr 23, 2015 10:57 am

I'm designing a loan related DWH and I came across following problem. I have two fact tables:

  • "LoanAccumShaphot" that tracks loans life cycle
  • "LoanPayements" that holds the actual financial payments



Naturally, by the OLTP data, the only common dimensions between the two are "DimCustomer" and "DimLoanAccount", but I have KPIs that depends on metrics from both fact tables and I want these KPIs to work when slicing by Office/Product. The solution I see is to "enrich" the "LoanPayements" data by getting all the missing keys from the LoanAccumShapshot data, using the LoandAccountSK.

The question is:  Is this a good/common practice, or I'm doing it wrong?

Thanks in advance!

veskojl

Posts : 11
Join date : 2011-07-21

View user profile

Back to top Go down

Re: Change grain of a fact to facilitate the drill across

Post  ngalemmo on Thu Apr 23, 2015 4:50 pm

Loan payments should have as much dimensionality as possible for that event. Each fact table should stand alone. You can then combine the facts based on common conformed dimensions.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Change grain of a fact to facilitate the drill across

Post  veskojl on Fri Apr 24, 2015 12:08 am

Thanks, ngalemmo.
I'm also leaning toward this approach, but I'm worried not to go extreme and overdo the design.
Strictly speaking, when someone makes a loan payment, the LoanProductSK is not important/tracked, cause it's no part of the repayment process, but indirectly that payment is related to the loan product.
If I follow this "indirect relation" logic I could equalize almost all my fact tables and their granularity will differ only by 1-2 dimensions.
So this is my concern, whether the drill across functionality could be a leading factor during the design?!

veskojl

Posts : 11
Join date : 2011-07-21

View user profile

Back to top Go down

Re: Change grain of a fact to facilitate the drill across

Post  Sponsored content


Sponsored content


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