Click here to Skip to main content
16,016,925 members
Please Sign up or sign in to vote.
1.00/5 (1 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
Posted
Updated 25-Jan-16 11:02am
v2

1 solution

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.
 
Share this answer
 
Comments
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, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900