Change grain of a fact to facilitate the drill across
2 posters
Page 1 of 1
Change grain of a fact to facilitate the drill across
I'm designing a loan related DWH and I came across following problem. I have two fact tables:
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!
- "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
Re: Change grain of a fact to facilitate the drill across
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.
Re: Change grain of a fact to facilitate the drill across
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?!
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
Similar topics
» How do I connect fact tables for drill down
» Drill Across Fact Tables with Report Builder
» Create fact table from a dimension (detect change)
» Fact Measures that don't change for every Dimension value
» Modelling Fact Tables That Change
» Drill Across Fact Tables with Report Builder
» Create fact table from a dimension (detect change)
» Fact Measures that don't change for every Dimension value
» Modelling Fact Tables That Change
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum