Statement Cycle versus MTD, Financial/Banking Industry
2 posters
Page 1 of 1
Statement Cycle versus MTD, Financial/Banking Industry
Banking industry reporting requirements often call for viewing monthly data both on a calendar month and on a statement cycle. What are some best modeling practices for dealing with a snapshot of MTD transactions and Statement-Cycle-to-Date transactions? Is it common to have two types of snapshot fact tables, one rolled up at the monthly and one rolled up from previous statment date to current statement date?
I assume that for a transaction grained fact table, (one row per transaction per day) a user can view data from both perspectives using the date table: For monthly views, join from the Date table using the 1st through last day of a given month in the WHERE clause. For statement cycle, the WHERE clause date range is that of a particular statement cycle. The user would have to know what cycle date they are looking for. The Account table stores last cycle date and next cycle date, and a Statement Cycle Description column (3rd of the month, 18th of the month, etc...).
Business requirements are that users can view transaction types; Fees Charged, Fees Waived, Point-of-Sale credits and debits, Interest earned, etc...
My question involves snapshot fact tables. MTD snapshots are easy enough, just sum each of the targeted transaction types for a rollup of each Fee Charged, Fee Waived and Point-of-Sale debit, and join to a truncated date table (DimDateMonthly). Result example: Fees Waived for June 2010 for BankXYZ = $1580
To fit the same pattern for Statement Cycle, would you require a Cycle Date table? Most financial institutions have several "cycle dates" per month, large institutions may have up to 28 cycle days per month (or more considering quartely statement cycles). This would mean 28 x 12 columns for each year in the Statement Cycle Table, (cardinality would resemble DimDateDaily table). Result example: Fees Waived for Cycle Date May 13th through June 12th, 2010 for BankXYZ= $340
Please share your experiences and recommendations.
Thank you.
I assume that for a transaction grained fact table, (one row per transaction per day) a user can view data from both perspectives using the date table: For monthly views, join from the Date table using the 1st through last day of a given month in the WHERE clause. For statement cycle, the WHERE clause date range is that of a particular statement cycle. The user would have to know what cycle date they are looking for. The Account table stores last cycle date and next cycle date, and a Statement Cycle Description column (3rd of the month, 18th of the month, etc...).
Business requirements are that users can view transaction types; Fees Charged, Fees Waived, Point-of-Sale credits and debits, Interest earned, etc...
My question involves snapshot fact tables. MTD snapshots are easy enough, just sum each of the targeted transaction types for a rollup of each Fee Charged, Fee Waived and Point-of-Sale debit, and join to a truncated date table (DimDateMonthly). Result example: Fees Waived for June 2010 for BankXYZ = $1580
To fit the same pattern for Statement Cycle, would you require a Cycle Date table? Most financial institutions have several "cycle dates" per month, large institutions may have up to 28 cycle days per month (or more considering quartely statement cycles). This would mean 28 x 12 columns for each year in the Statement Cycle Table, (cardinality would resemble DimDateDaily table). Result example: Fees Waived for Cycle Date May 13th through June 12th, 2010 for BankXYZ= $340
Please share your experiences and recommendations.
Thank you.
parbie- Posts : 11
Join date : 2010-04-06
Re: Statement Cycle versus MTD, Financial/Banking Industry
You would have two snapshot tables, one for month, the other for cycle.
I don't understand the rest of your problem. I don't see the need for another date dimension for month or cycle, just use the date dimension and reference the appropriate month end date or cycle end date for the particular snapshot.
I don't understand the rest of your problem. I don't see the need for another date dimension for month or cycle, just use the date dimension and reference the appropriate month end date or cycle end date for the particular snapshot.
Re: Statement Cycle versus MTD, Financial/Banking Industry
Thanks for the reply. Follow up question: I understand that the full Date Dim can be used for the MTD snapshot. Do you not like the idea of a "shruken" dimension for time? Is it not simpler (and probably better performance)?
Thanks.
Thanks.
parbie- Posts : 11
Join date : 2010-04-06
Re: Statement Cycle versus MTD, Financial/Banking Industry
No, I don't like it. It doesn't really save anything, has negligible impact on performance, and can make integration with detailed facts more difficult for BI tools. (If you are really worried about performance, you can always permanently cache the date dimension in memory and be done with it).
Tools, such as Business Objects and others, recognise the underlying tables and columns. You may have different views of the date dimension (in the tool), but the attributes like month and year all map to the same columns in the same table. If you have a 'month' table, this is not the case. If you query a detailed fact and a summarised fact by month and year, most tools, if you share the same attributes in the same table will automatically aggregate and join the facts on those attributes. If they are attributes in different tables, you may need to do a bit more work to combine the information (such as explicitly merging sub-queries).
Tools, such as Business Objects and others, recognise the underlying tables and columns. You may have different views of the date dimension (in the tool), but the attributes like month and year all map to the same columns in the same table. If you have a 'month' table, this is not the case. If you query a detailed fact and a summarised fact by month and year, most tools, if you share the same attributes in the same table will automatically aggregate and join the facts on those attributes. If they are attributes in different tables, you may need to do a bit more work to combine the information (such as explicitly merging sub-queries).
Month End Column
I can see how a month end indicator column can be used to join a monthly fact table. Question: If my monthly fact table also serves as month-to-date for the most current month (i.e, each day throughout a current month is updated by overwriting the previous record until the last day of that month) what key do you assign then, same month end idicator column I take it?
parbie- Posts : 11
Join date : 2010-04-06
![-](https://2img.net/i/empty.gif)
» Kimball Architecture in Oil and Gas industry
» Bill Cycle Modeling
» Multiple Life cycle entities in One dimension table...
» Travel industry example
» Gaming Industry
» Bill Cycle Modeling
» Multiple Life cycle entities in One dimension table...
» Travel industry example
» Gaming Industry
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum