Transactions with Mixed Grain

[aecobalt]

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.

[ngalemmo]

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.

[aecobalt]

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.

[ngalemmo]

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.

[Sponsored content]