Narrow the Fact table?
Page 1 of 1
Narrow the Fact table?
Hi,
I have a couple of questions on Fact table design:
1. One of the transactions we need to 'measure' is related to release of funds process. This process has in addition to amount, when, who and to whom, it has details like Bill Number, Advice Number and Payment settlement related details as well. I was thinking of creating a Fact table which has the below details:
DateReleased, ReleasedBy, ReleaseTo, AmountReleased, BillNumbereleased, BillGeneratedDate, AdviceNumber, AdviceNumberDate, PaymentSettlementNumber, PaymentSettlementDate
Of the above, the columns in bold are the ones most frequently used for reporting purposes, whereas the remaining columns are used quite rarely and required only when user drill though to the lowest level. With this info, i am thinking of creating Fact table with just the most frequently used columns and another table with the rarely used ones, and create a Foreign Key relation between them.
This way i can improve the performance on the most commonly used data. I know this is not the way to about a dimension model, but wanted to know everyone else's thoughts.
2. For the same example above, there are a lot of remarks columns at each stage which needs to be displayed when the user does a drill through to the lowest level. Now, where should this data be stored?
Please share your thoughts. Thanks.
I have a couple of questions on Fact table design:
1. One of the transactions we need to 'measure' is related to release of funds process. This process has in addition to amount, when, who and to whom, it has details like Bill Number, Advice Number and Payment settlement related details as well. I was thinking of creating a Fact table which has the below details:
DateReleased, ReleasedBy, ReleaseTo, AmountReleased, BillNumbereleased, BillGeneratedDate, AdviceNumber, AdviceNumberDate, PaymentSettlementNumber, PaymentSettlementDate
Of the above, the columns in bold are the ones most frequently used for reporting purposes, whereas the remaining columns are used quite rarely and required only when user drill though to the lowest level. With this info, i am thinking of creating Fact table with just the most frequently used columns and another table with the rarely used ones, and create a Foreign Key relation between them.
This way i can improve the performance on the most commonly used data. I know this is not the way to about a dimension model, but wanted to know everyone else's thoughts.
2. For the same example above, there are a lot of remarks columns at each stage which needs to be displayed when the user does a drill through to the lowest level. Now, where should this data be stored?
Please share your thoughts. Thanks.
Straightdrive- Posts : 2
Join date : 2012-06-29
Similar topics
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» How to create fact table with measures derived from comparing two fact table rows
» Healthcare Hospital Stay fact table - transaction fact table - a row for In and a row for Out?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» How to create fact table with measures derived from comparing two fact table rows
» Healthcare Hospital Stay fact table - transaction fact table - a row for In and a row for Out?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum