Un-stitched multi fact query question

[cwmoorexx]

Hi,

I'm a newbie to reportnet, so please forgive my ignorance.

I have 2 fact tables (call them "Department Facts" and "Cost Sharing Facts") each with it's own set of dimensions and sharing a single conformed dimension in common (call it "ID Number Dim").  Department Facts has an n-1 relationship to ID Number Dim (an ID can have 1 or many department facts),  and Cost Sharing Facts has an n-0 relationship to ID Number Dim (an ID can have 0 or many cost share facts).   

The trouble is that when I bring into a report a column from one of the other dimensions related to the Department Fact (the Department Name in this case) and one of the dollar columns from the Cost Sharing Fact table, the query doesn't stitch.  When I look at the generated sql, reportnet is just querying the 2 columns separately without joining them through the ID Number conformed dimension.  This produces a report with two columns, one listing all of the department names, the other where each cell contains the sum of the dollars from the whole fact table.  How do I get reportnet to join the two queries through the ID Number dimension, so that it gives the sum of the dollars for each department, instead of the sum of the dollars for all of the departments?

Any help you can offer is appreciated!

[CoginAustin]

Your fact table should have 1:1 with dimension
Each fact has one and only one dimension value

Each dimension should most likely be 1..n
Each dimension can have 1 or more facts

Mine is made this way and it works fine. I struggled with all these relationships myselfÃ,  ;)

[MFGF]

My guess is that you have not defined Dimension Information for your common dimension in Framework Manager.Ã,  Am I right in thinking that you get stitching if you bring in the ID Number from the dimension (ie the joining key) but not if you bring in a different dimension column instead?

If that guess is correct, you need to define the logical hierarchy and different level(s) in use in your dimension, identifying the key of each level, and any other attributes in use on that level.Ã,  All of this is done by using the 'Specify Dimension Information' dialog for your dimension query subject in Framework Manager.Ã,  Re-publish your package once you have done this, and hopefully that will give you valid, consistent results in your reports.

Best regards,

MF