Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Change grain of a fact to facilitate the drill across

2 posters

Go down

Change grain of a fact to facilitate the drill across Empty Change grain of a fact to facilitate the drill across

Post  veskojl 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

Change grain of a fact to facilitate the drill across Loans_12

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

Back to top Go down

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

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

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

Post  veskojl 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

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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