I need help on how to present or report from multiple fact tables:
1. Orders (has all items that were ordered)
1. Sales (has all sold items, spanning multiple years)
2. Revenue (has revenue from items sold in current year)
As you can see Sales amount <> Revenue amount.
We have separated the facts because they are for different departments but also they have different dimensions, and other reasons.
I am being asked questions like "... we want to know how much was ordered, how much sold, and how much of the sales were paid (revenue)..."
So I am not sure if I should create a view on top of the facts?
is the accumulating fact for this type of scenario?
what is the best solution to this type of question?
I am using Microsoft BI stack. So I would like to model in tabular model.