Click here to Skip to main content
14,428,023 members
Rate this:
Please Sign up or sign in to vote.
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.

Thank you,
Bin Sheikh
Updated 25-Jan-16 12:02pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Based on your data volume, you can decide.

if it is less, you can create view on top of this and have the total sum of order, sale and revenue corresponding to dimensions.

or, you can create tabular model.
Member 12286600 26-Jan-16 10:06am
Hi Purna,

Thank you for the reply.

I have followup question. If I create new tabular model, how do I connect the fact tables? would the common dimensions serve as link between the facts tables? or do i use common column between the fact table? For example there is a itemID that is unique and in all the 3 fact tables, should I link by itemID? Please note that since some itemID are in one fact but not in the another fact. Like item was ordered and sold but payment was not received yet. Or item was ordered then cancelled.
Thank you.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100