Grain issue in the fact table

View previous topic View next topic Go down

Grain issue in the fact table

Post  Hemapr on Thu Apr 25, 2013 1:00 am

I need to design litigation loan/case model. The relationship is "a loan can have many cases and a case can have many loans". So, there is a many-many relationship between the loan and case dimension.
So, when i try to identify the grain of fact then it is both " Loan and Case" as the data is so interdependent for both loan and case.
But, the problem comes with counts because i have to use distinct clause to do the Loan count or Case count. Also, with this mixed granularity , will it have any issues with measures?
Is this the right approach to have both loannum and casenum in the fact?

Hemapr

Posts : 12
Join date : 2012-05-15

View user profile

Back to top Go down

Re: Grain issue in the fact table

Post  ngalemmo on Thu Apr 25, 2013 1:51 am

Depends on the fact.

If you have measures that require different grains, then you need multiple fact tables, each with the appropriate grain.

Case grained measures should go into a fact table with case as one of the dimensions. Loan grain measures in a fact with loan as one of the dimensions. Measures at a case/loan level need both 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: Grain issue in the fact table

Post  Hemapr on Thu Apr 25, 2013 10:42 pm

Thanks for the reply!
So, with the approach which you suggested that i will have 3 facts.
1) Loan fact -- with Loan as its dimension and will have measures that are depend only on the Loan.
2) Case Fact -- with Case dimension and will have measures that are depend only on the case.
3) Loan-Case Fact -- with both Loan and Case dimensions and will have measures that are depend on both loan&case

Hemapr

Posts : 12
Join date : 2012-05-15

View user profile

Back to top Go down

Re: Grain issue in the fact table

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