Transactions with Mixed Grain

View previous topic View next topic Go down

Transactions with Mixed Grain

Post  aecobalt on Wed Oct 02, 2013 7:38 am

Hi All,
Please help, I have an issue identifying the grain of transactions and not sure if I need to split transactions into two or three different Fact Tables.

Currently the source system stores all transactions (rent payments) in one table, these payments/transactions are received daily but each transaction is related to an specific payment frequency  i.e. When tenants sign the letting agreement they choose to make payments either weekly, monthly or annually. In other words the business operates/manages tenancies on weekly rents, monthly rents and annual rents.

As far as I can tell, when payments transactions are received these are already at their lowest atomic level (regardless of the frequency of payment, weekly,monthly or annually)
Converting annual/monthly payments into weekly payments can be get very complex due to apportioning of specific amounts to specific weeks and handling of different financial calendars, plus is not really the way the business operates.

I need some reassurance/advice on this please, should I break up transactions into three different TRANSACTIONAL Fact Tables (WeeklyRents, MonthlyRents, AnnualRents) linking back to a single Date Dimension (using role playing date dimensions one for each specific period) i.e. AccountingPeriod_Week ( pk = week commencing date) / AccountingPeriod_Month(pk = first day of the month) / AccountingPeriod_Year (pk = first day of the year).

Then followed by three different Summary Snapshot Tables ( WeeklyRentsSummarySnapshot, MonthlyRentsSummarySnapshot, AnnualRentsSummarySnapshot)

The only difficulty I can see is when reporting by weekly period? (I will be missing monthly and annual transactions)

Many thanks in advance, your advice/comments are very much appreciated.



aecobalt

Posts : 3
Join date : 2013-08-29

View user profile

Back to top Go down

Re: Transactions with Mixed Grain

Post  ngalemmo on Wed Oct 02, 2013 9:21 am

If I was doing a retail data warehouse an I received sales transactions where in some cases I sold 1 container of milk while in others I sold 2 containers, would I need to build 2 different fact tables? Of course not. Why would rentals be any different?

A renter is paying for time... it is a matter of quantity and units.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Transactions with Mixed Grain

Post  aecobalt on Thu Oct 03, 2013 7:40 am

Many thanks for your reply, your example makes perfect sense, I can see the grain for the transactional fact should be the 'transaction ID + line number' and not the 'transaction unit of measure' (week, month or year).
On the Snapshot side, is it correct that 'units of measure' are mixed? or do I need to convert months or years into weeks?  one of the requirements is to be able to analyze weekly balances. Please see example below, many thanks again for your help.


aecobalt

Posts : 3
Join date : 2013-08-29

View user profile

Back to top Go down

Re: Transactions with Mixed Grain

Post  ngalemmo on Thu Oct 03, 2013 9:35 am

You can do both. Have a column with the rent paid and another with the number of weeks, from which you can derive the weekly value.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Transactions with Mixed Grain

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