Conformed dimension with two fact issue

[cognos05]

Hi ,

I have a simple use case where i have customer dimension and then two facts table ,

one table which has sales of customer and other table which has the rebate amount earned by that customer

so my relation in my FM is

Customer - sales  ( 1 to N)
Customer - rebates( 1:1 to 0:1)

Now i create a report with customer , sales amount and rebate amount


customer 1

sales
1  400
1  300
1  300
1  200

Rebates
1 250


Cognos ouput

customer sales rebates
1            1200   1000

its always mutlplying my rebate amount by number of rows on sales fact .

Any idea why cognos will not do a stitch query where it take the results of two tables and then joins

I expect output like

Customer sales rebates
1             1200    250


Thanks,

[bus_pass_man]

Let's put aside the outer join, which I don't think should be necessary, have you tried making the relationship between your customer table and your rebate 1 to many?

I was able to use your data to reproduce the problem and I was able to get the correct results when the relationship was 1.N.

1.1
SELECT
    Sheet10.Customer AS Customer,
    SUM(sales0.sales) AS sales,
    SUM(rebates0.rebate_value) AS rebate_value
FROM
    130256853...Sheet1 Sheet10
        INNER JOIN 1505442408...rebates rebates0
        ON Sheet10.Custno = rebates0.Custno
            INNER JOIN 1581931391...sales sales0
            ON sales0.customer = Sheet10.Customer
GROUP BY
    Sheet10.Customer

1.N
WITH
FS1 AS
    (
    SELECT
        Sheet10.Customer AS Customer,
        SUM(rebates0.rebate_value) AS rebate_value
    FROM
        130256853...Sheet1 Sheet10
            INNER JOIN 1505442408...rebates rebates0
            ON Sheet10.Custno = rebates0.Custno
    GROUP BY
        Sheet10.Customer
    ),
FS2 AS
    (
    SELECT
        Sheet10.Customer AS Customer,
        SUM(sales0.sales) AS sales
    FROM
        1581931391...sales sales0
            INNER JOIN 130256853...Sheet1 Sheet10
            ON sales0.customer = Sheet10.Customer
    GROUP BY
        Sheet10.Customer
    )
SELECT
    COALESCE(
        FS1.Customer,
        FS2.Customer) AS Customer,
    FS2.sales AS sales,
    FS1.rebate_value AS rebate_value
FROM
    FS1
        FULL OUTER JOIN FS2
        ON FS1.Customer IS NOT DISTINCT FROM FS2.Customer

[cognos05]

Hi ,

Thanks for the suggestion , changing to 1 to many worked fine, instead of 0 to 1 .

Should always the relation to fact be 1 to many ,

so here my case was 1 customer can have 0 or 1 rebates.

I am assuming cognos likes to have 1 to many between dimension and fact so it can differentiate it was 2 facts?

Thanks for your help , really appreciate it .