Hello all,
I’m working with BI4.2. We have created a universe with a star modelisation (no snowflake or anything else).
All the joins are outer join on the fact table:
The Fact table is the “main” table and has obviously the most amount of rows than in any other table of the data model.
When we create a report on Webi based on this universe, we encounter an issue that slow down a lot the query refresh. When we mix data from dimensions and fact tables, the From condition starts with a dimension table with a RIGHT OUTER JOIN on the Fact table, the other joins are LEFT OUTER with the Fact table as the LEFT table. I would like to have all joins as LEFT JOIN with the Fact as the LEFT table:
final.F_Fact.mt_amount_conso
FROM
final.D_Date D_Date_Period RIGHT OUTER JOIN final.F_Fact ON (final.F_Fact.cd_exercice_yyyymm=D_Date_Period.cd_yyyymm)
LEFT OUTER JOIN final.D_Entity D_Tiers1 ON (final.F_Fact.id_tiers1_fk=D_Tiers1.id_entity)
LEFT OUTER JOIN final.D_Account ON (final.F_Fact.id_account_fk=final.D_Account.id_account)
On this example, I would like to have:
final.F_Fact.mt_amount_conso
FROM
final.F_Fact LEFT OUTER JOIN final.D_Date D_Date_Period ON (final.F_Fact.cd_period_yyyymm=D_Date_Period.cd_yyyymm)
LEFT OUTER JOIN final.D_Entity D_Tiers1 ON (final.F_Fact.id_tiers1_fk=D_Tiers1.id_entity)
LEFT OUTER JOIN final.D_Account ON (final.F_Fact.id_account_fk=final.D_Account.id_account)
I could do it manually with a manual query script (what I currently do), but then the modifications we make on the Universe are not automatically detected by the webi report.
Do you have any tips or any solutions to solve this issue?
I already tried to reverse the outer join and the cardinality on IDT but with no success. I don’t have any clue how to try to solve this problem…
Thank you.