Stitch Query Issue

[wyconian]

Hey everybody, I'd really appreciate it if anyone could help with a problem I've got.

I'm trying to model mulit fact, multi grain facts for a client in FM. 

My client has a order header fact and a order line fact.  The requirement is to be able to report against both facts in one report.  Order line is at a lower grain than order header.  There is no physical conformed dimension so I've created a QS which has order header reference and order line key from the 2 fact tables.  The order header fact joins to the conformed dimension on order header reference and the order line fact joins on order header reference and order line key.  I've set determinants on the conformed dimension so that order header reference is a group with attributes order line key and order line key is the unique identifier.

I was expecting FM to create 2 queries one for each fact/conformed dimension and then to outer join them on the common key and to aggregate the line values up to the order header reference level.

FM did have queries for each fact/conformed dimension but the sticth query was RSUM(1 FOR XXX) where xxx is either the header or the line.  This is used to stitch the queries together and is returing incorrect results.

Nothing I've tried has worked, does anyone have any ideas?  is this a bug?

I'm using F 10.1.1

This is the query FM generates, any ideas how to get rid of the RSUM?

with
D as
    (select
           F_ORDER_LINE.ORDER_HEADER_REFERENCE  as  ORDER_HEADER_REFERENCE,
           RSUM(1  order by F_ORDER_LINE.ORDER_HEADER_REFERENCE asc  local)  as  sc
     from
           RC_RMBC_PRE.RC_RMBC_PRE.dbo.F_ORDER_LINE F_ORDER_LINE
     order by
           ORDER_HEADER_REFERENCE asc
    ),
D3 as
    (select
           F_ORDER_HEADER.ORDER_HEADER_REFERENCE  as  ORDER_HEADER_REFERENCE1,
           RSUM(1  order by F_ORDER_HEADER.ORDER_HEADER_REFERENCE asc  local)  as  sc
     from
           RC_RMBC_PRE.RC_RMBC_PRE.dbo.F_ORDER_HEADER F_ORDER_HEADER
     order by
           ORDER_HEADER_REFERENCE1 asc
    )
select
       D.ORDER_HEADER_REFERENCE  as  ORDER_HEADER_REFERENCE,
       D3.ORDER_HEADER_REFERENCE1  as  ORDER_HEADER_REFERENCE1
from
       D
        full outer join
       D3
        on (D.sc = D3.sc

Thanks for your help

[MFGF]

I've set determinants on the conformed dimension so that order header reference is a group with attributes order line key and order line key is the unique identifier.

Hi,

I think I see the issue right there.

You should have two determinants in your conformed dimension as follows:

1. Order Header Reference as the ID and no attributes - this one having Group By checked
2. Order Line Key as the the ID and having "Unique" checked - this will put both items in as attributes.

I assume that your real Order Line table contains both the Order Header Reference and the Order Line Key also? Is the Order Line Key unique across all Order Headers or should it be concatenated with the Order header Reference to make it unique? If the latter, then the ID of the second determinant would need to contain both Order Header Reference and Order Line Key.

I'd also take the Order Header Reference and Order Line Key items from just the Order Line table to make your conformed dimension, too - that way they are sourced from the same underlying query subject. Hide both in the Order Line Fact query subject (ie set the "Is Hidden" property to True) and do the same with Order Header Reference in Order Header Fact. That way you will be forced to use the conformed dimension to bring in Order Header Reference in your report.

Cheers!

MF.

[wyconian]

Thanks MF great advice as always.

I've made those changes so I now have a conformed dimension which has order header reference and order line bkey both sourced from the order line table. 

I've added a couple of new query subjects based on the facts but with the order header reference sourced from the conformed
dimension.  That has removed the RSUM stictch column FM was generating and seems to be joining the queries together correctly now.

My problem is now the aggregation. Instead of aggregating the ORDER LINE measures to the ORDER HEADER REFERENCE level they are just being listed (see attached).  I'm expecting to see one record for each ORDER HEADER REFERENCE.

Would it show like that in FM?

Anybody got any ideas what I'm missing.

Thanks

[wyconian]

Thanks for your help MF.  Turns out the above is correct, I was expecting it to aggregate in FM but apparently that happens in report studio.  I've now been able to correctly report against 3 fact tables.

Thanks again.

[MFGF]

Thanks MF great advice as always.

I've made those changes so I now have a conformed dimension which has order header reference and order line bkey both sourced from the order line table. 

I've added a couple of new query subjects based on the facts but with the order header reference sourced from the conformed
dimension.  That has removed the RSUM stictch column FM was generating and seems to be joining the queries together correctly now.

My problem is now the aggregation. Instead of aggregating the ORDER LINE measures to the ORDER HEADER REFERENCE level they are just being listed (see attached).  I'm expecting to see one record for each ORDER HEADER REFERENCE.

Would it show like that in FM?

Anybody got any ideas what I'm missing.

Thanks

Did you check the "Auto sum" option in the top right corner when testing? Generally this works like the "auto group and aggregate" property in the query of a report.

Cheers!

MF.

[wyconian]

Thanks that's a big help.