Data warehouse: Orders, Sales and Revenue facts

Question

1.00/5 (1 vote)

See more:

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 25-Jan-16 8:33am

Member 12286600

Updated 25-Jan-16 11:02am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Purna Chandra Sasmal · Accepted Answer · 2016-01-25T11:20:00

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.

Posted 25-Jan-16 11:20am

Purna Chandra Sasmal

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.