Hi,
I have a Report fact that can either be linked to a Assessment fact, Referral Fact or and admin Assessment fact using a common dimminsion of Report.
Report dim 1.1 to 1.n Report Fact
Report Dim 1.1 to 0.n Assessment Fact
Report Dim 1.1 to 0.n Referral Fact
Report Dim 1.1 to 0.N Admin Assessment Fact
What is the best way to create the bridge table for these Facts? Should I have 3 seperate bridge tables, i.e. Report SK with Assessment SK, Report SK with Referral SK, Report SK and Admin Assessment SK? Or should I create one bridge table with the SK for all 4 facts?
Thanks.
Cognos will try to resolve a multi-fact query by splitting the query, performing seperate aggregations and then stitching the resultsets over the shared 'non-facts'. This is standard behavior when multiple facts converge upon a dimension. It is triggered by setting the cardinalities of the defined joins. It will do so without any sort of 'bridge-ing'