Grain issue in the fact table
2 posters
Page 1 of 1
Grain issue in the fact table
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?
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
Re: Grain issue in the fact table
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.
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.
Re: Grain issue in the fact table
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
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
Similar topics
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» separate fact table/different grain - do I need a bridge table
» switching grain of snapshot fact table
» Unusual(?) parent/child Fact table design issue
» Grain of measure in fact table
» separate fact table/different grain - do I need a bridge table
» switching grain of snapshot fact table
» Unusual(?) parent/child Fact table design issue
» Grain of measure in fact table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum