Statement Cycle versus MTD, Financial/Banking Industry

View previous topic View next topic Go down

Statement Cycle versus MTD, Financial/Banking Industry

Post  parbie on Tue Sep 07, 2010 12:42 pm

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.
avatar
parbie

Posts : 11
Join date : 2010-04-06

View user profile

Back to top Go down

Re: Statement Cycle versus MTD, Financial/Banking Industry

Post  ngalemmo on Tue Sep 07, 2010 1:32 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Statement Cycle versus MTD, Financial/Banking Industry

Post  parbie on Tue Sep 07, 2010 3:50 pm

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.
avatar
parbie

Posts : 11
Join date : 2010-04-06

View user profile

Back to top Go down

Re: Statement Cycle versus MTD, Financial/Banking Industry

Post  ngalemmo on Tue Sep 07, 2010 4:34 pm

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).
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Month End Column

Post  parbie on Wed Jun 01, 2011 3:20 pm

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?
avatar
parbie

Posts : 11
Join date : 2010-04-06

View user profile

Back to top Go down

Re: Statement Cycle versus MTD, Financial/Banking Industry

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