Forum Discussion
Helper IIncome and expense: same or separate fact table?
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.
In my opinion keeping the fact tables separate (and not directly linking between them) is the cleanest approach. Alternatively you could have a single fact table where you add a "fact type" column with the values of "Income" and "Expense" and then use the appropriate filters. But that will get messy quickly with larger datasets, especially when the facts are updated with different cadence.
I think your approach is sound and you should continue with it.
3 Replies
- lbendlin
Super User
In my opinion keeping the fact tables separate (and not directly linking between them) is the cleanest approach. Alternatively you could have a single fact table where you add a "fact type" column with the values of "Income" and "Expense" and then use the appropriate filters. But that will get messy quickly with larger datasets, especially when the facts are updated with different cadence.
I think your approach is sound and you should continue with it.
tormiThanks for answering lbendlin . I have gone back and forth again a couple of times, and no I am not so afraid of multiple fact tables. I have also decided to add mortage info into my dahsboard. I might be overcomplicating things, but I am getting a better grasp of the logic. Hence my question, would this combination of Fact and Dimensions work? Any ideas to simplify or make the structure better?
- tormi
Thanks for the answer.
As I was continuing my learning I did conduct some tests. I also got an understanding of the value of cleaning up my data as early in the process as possible. Since I have all data in Excel, and this is a small project compared to what a lot of you guys are doing I ended up with the following:
I have a set number of income streams/expenses, there won't be any adding of much except updating it monthly, so I think I will be able to keep it all under control. I created a few measures and charts and it seems to work fine.
Since I am new to this, and am learning PowerBi just because I want to, not because I have a clear business need I have found that a real hard part of PowerBI for a beginner like me is getting a clear grasp on how the initial data best should be structured so it fits into a nice relationship table.
Most of what I read is to avoid two fact tables, but very nice for me to know it is possible and that it would work the way I initially planned it.
Thanks for your insight.