The fact table also has many to 1 relationship to another table which has its own relationships to other tables but the problematic area is here.
I wanted to change this relationship in the business model to be fact table will have many to one relationship to all the table (including T1 and T2) to resemblance a star schema, removing the relationship between T1 to T2 and T2 to T3 (the complete design is much more complicated so im trying to do some muscling to reduce the complexity).
In the business model and mapping layer, i do:
1. Created 2 new column in factTable T1key and T2 key. (obviously no T3 key column needed since fact table already contains that)
2. In the logical source table of the factTable, i added T2 and T3, making the relationship there such as:
factTable - T3: inner join
T3 - T2 : inner join
3. Navigate to the column mapping tab:
- map T1key to t1key (foreign key of T1 table that T2 table contains) in T2 table
- map T2key to t2key (foreign key of T2 table that T3 table contains) in T3 table
4. Navigate to the foreign key tab:
- adding foreign key from factTable to T2 on factTable.T2Key = T2.t2Key
- adding foreign key from factTable to T3 on factTable.T3Key = T3.t3Key
I already has the "flag allow logical foreign key join creation" checked
=> when i try to create new analysis with a column from t1,t2,t3,t4 => error: No fact table exists at the requested level of detail.
This has happened to me before when i dont create the foreign key in the physical layer and doing it on the business model layer..... Im thinking the foreign key i created at step 4 also somehow ignored by BI thus the problem... What am i missing??