when I bring in the calulation defined in frame work manager. native sql is breaken up in to two pieces, however the native sql should have only one piece. there is a join between two fact tables in FM and the join breaks only when i bring in the calculated field in report studio.
fact1 and fact2 joined as 1 to 1 in framework manager and the calculation is defined in one of the columns in fact 1 table. When I query fact 1 and fact2 with out the calculated field report runs fine. when I query fact 1 and fact 2 with calculated field, report hangs.
any thoughts?
Check the expression of the calculated field. If it is using something from some other Query subject, it might create an additional join at the run time.
Moreover, it is better to join 2 facts with one dimension in-between both of them rather than joining directly.