Conformed Dimension not being Used in Stitch Query Join

[dawells]

All,

I am building an enterprise FM model of off a Netezza Data Warehouse and I have run into an issue. See I have created last year Fiscal and Calendar year of year query subjects to allow users to quickly do analysis on Sales, Purchase Order, and Product Assortment.

This modeling is working fine except for when I filter the current timeframe data by the various flags that exist in the corresponding fact tables the filter is only applied to the CY Query Subject and not the LY Query Subject.

For instance, I have Sale Detail fact table that has a Return Flag on every record that identifies record as a return or sale transaction. Now I have created a table in the database called DIM_BOOLEAN_FLAG that I join to dimensionalize the Return Flag as its own Query Subject.

My understanding of how FM should handle this Return Flag dimension is that it will be treated as a conformed when a user pulls in a date value from one of the rollplayed date dimensions and a fact measure from the Current Year Fact Query Subject as well as a fact measure from either of Last Year Fact Query Subjects.

Thus if I am to filter the Return Flag dimension in anyway (or use just as an attribute of a sale) that filter should get applied to the CY data and via the joib be applied to the LY data.

However, this filter I described is only getting applied to the Current Year Fact and not the LY Year Fact. This behavior doesn't make sense to me and I am hoping someone else has faced a similiar issue. Please advise. Thanks.

Dustin

moderator's note: added some new lines to improve readability

[cognostechie]

Cognos does not force the joins and hence will not join both the Facts even if there is a comformed dimension between them. In order to apply the filter to  both the Facts, atleast one item should be used from the confirmed dimension and one item from each Facts.

Consider the following join :

Sales Fact   ---------    Customer Dim   ----------  Returns Fact

Ex 1 - Report contains:

1> Sales $      -   From the Sales Fact
2> Returns $  -   From the Returns Fact
3> Customer ID -  From the Sales Fact

If you apply a filter to select only one Customer, the Returns Fact may not be filtered but the Sales Fact will be.

Ex 2 - Report contains:

1> Sales $      -   From the Sales Fact
2> Returns $  -   From the Returns Fact
3> Customer ID -  From the Customer Dim

If you apply a filter to select only one Customer, both the Facts will be filtered.

Your post is not very clear but the above might help you to understand.