fact table Synthetic

[#1]

Hi Experts,

I have two fact tables

Payment:

OrderID,PayTypeID, Amount,

Refund:

OrderID,PayTypeID,RefundAmount

And one DimPayType dimension table:

PayTypeID, PayName.

My concern is if I load them into QVW directly, then that two fact tables will be linked with the OrderID and PayTypeID, buf if I rename PayTypeID field, then it will not be linked to dimenstion DimPayType , so how can I do this, could you give me a detail solution?

Thanks!!!

[Anonymous]

Hi,

If your fact tables are aggregated at the same level I would join them togethe (use left join or join between your load statements.....or alternativley explore the CONCATENATE function to create a single fact table and then hang your DIM table off of that.

Cheers.

Ad.

[#3]

Hi,

try this---

----------------------------------------------------------------------------------------------------------------

payment:

load orderid, paytypeid as one, amount

from payment.qvd;

dimpaytype1:

load paytypeid as one, payname

from dimpaytype.qvd;

-----------------------------------------------------------------------------------------------------------------

Refund:

load orderid, paytypeid as two, amount

from refund.qvd;

dimpaytype2:

load paytypeid as two, payname

from dimpaytype.qvd;

------------------------------------------------------------------------------------------------------------------

[#4]

Hi,

But will this load dimpaytype twice and increase double memory usage for dimpaytype records?

[jonathandienst]

Hi

I would concatenate the two fact tables like this:

Payment:
LOAD
 OrderID,
 PayTypeID,
 Amount,
 'Payment' AS Source...
CONCATENATE
LOAD
 OrderID,
 PayTypeID,
 RefundAmount AS Amount,
 'Refund' AS Source...

You can differentiate the values using the Source field.

Jonathan

[johnw]

Jonathan's suggestion of concatenation is good. You could alternatively consider an outer join:

PaymentsAndRefunds:
LOAD OrderID,PayTypeID,Amount as PaymentAmount
FROM Wherever;
OUTER JOIN (PaymentsAndRefunds)
LOAD OrderID,PayTypeID,RefundAmount
FROM SomewhereElse;

Which is better would depend on what you want to do with the data. The outer join makes it easier to deal with payments and refunds as separate things - no need to refer to a Source field. But it also makes it harder to treat payments and refunds as the same sort of thing, as simply financial transactions with an amount associated with them. In the very global sense of "I'm doing financial processing" I'd want the concatenate solution, but the outer join may be better for specific problems.

I'll also point out that technically, you could probably just do nothing. Having two tables keyed by the same two fields is cluttered and not something I would personally do, but it shouldn't actually cause problems for QlikView. QlikView should be able to sort that kind of thing out just fine. I just think other data models are better.

[#7]

Hi John Witherspoon,

Thanks for you answer, and another question, how about the concatenation, is it will remove and combine the same rows from the different tables?

For example:

Payment

OrderID PayTypeID Amount

1 1 $30

OrderID PayTypeID RefundAmount

1 2 $19

How is the concatenation result?

Is it looks like:

1 1 $30 null

1 2 null $19

How's the difference between Union ALL and concatenation?

Thanks.

[johnw]

Let's try data that's has some keys in common and some not:

Payment:
OrderID, PayTypeID, Amount
1,1,$30
1,2,$40

Refund:
OrderID, PayTypeID, RefundAmount
1,1,$10
1,3, $5

The concatenation suggested by Jonathan would do this (if we sorted the results by OrderID and PayTypeID):

ResultingTable:
OrderID, PayTypeID, Amount, Source
1,1,$30,Payment
1,1,$10,Refund
1,2,$40,Payment
1,3, $5,Refund

The outer join I proposed would do this:

ResultingTable:
OrderID, PayTypeID, PaymentAmount, RefundAmount
1,1, $30,$10
1,2, $40,null
1,3,null, $5

Concatenation IS union all. But in this case, Jonathan combined two different fields into one and added another field to show where the data came from. That's why his suggestion is NOT the resulting table you show, even though that would be the result of concatenation had he not done some additional manipulation.

[jonathandienst]

Hi

The two proposals each have some advantages, and which you choose depends on your needs.

My suggestion (as John said in a previous post) treats payments and refunds as transactions, and allows you to display a single transaction chart with a selection list box. If nothing is selected, the chart will display net values (assuming refunds are negative and amounts positive) as per the top chart in the attached demo model. This is simpler if both Amounts and RefundAmounts are both transactions, but less so if the Amount and RefundAmount need to be treated in materially different ways. In whuch case you will need Sum(If...)s or set expressions.

John's suggestion has the advantage that it results in a slightly more compact model, and flips the advantages and disadvantages mentioned above.

The attached demo does both options so you can play with them and decide which best meets your needs.

Jonathan