Income and expense: same or separate fact table?

tormi · ‎11-11-2022

Hi all.

Spending alot of time on videos and training, here and on Google. Still I have issues on deciding on my fact, dimension and relationships planning. My main issue is understanding wether using separate fact tables for income and expense. I have not found any understandable (for me) templates or examples to try and follow.

Background:

This is a rental company, so far renting out 5 properties. I would love to be able to visualize data like income/expense per month, and ability to break it down to which income channel is providing what income, and also what expenses we have

5 rental properties

6 income streams (called income channel)

15 expense types

They all vary so there is no point of adding set number on these income/expenses.

I would like to show visualizations like this (and of course more):

Inntekt=Income

Utgift=Expense

(Later I would like to add information about loans/interest and also and overview of who does the cleaning per property.)

As far as I have come I have considered using two fact tables, but I think this will more complicated then necessary. This is what I have on my drawing board now:

First of all: Would this work at all?

In my head I think it would make sense for such a small company to just keep all from the two fact tables in 1 single table. But this would mean that Fields like "ExpenseID", "ExpenseName", "IncomeChannleID", "IncomeChanelName" and so on would be empty in part of the table. Does this pose an issue?

Bringing all into one single Fact table poses a concern to me because of my own overview.

Any help, would be greatly apreciated.