linked universes joins not working properly in WEBI

[Former Member]

Hi everybody,

I'm facing a problem with a fact universe (containing only fact tables) linked to another universe (containing only LOV tables): joins between one fact table and one LOV table don't appear in webi queries (It gives me a cartesian product...), but they appear in deski (and the results are correct).

I have already tried setting up a new context, but without results.

Everything works fine if I import the LOV universe into the FACT universe. but I would like to let them separated.

Thanks in advance.

Lorenzo Brambilla

[Former Member]

Hi Lorenzo,

Could you try including that join in to that context.

Regards,

Chaitanya Deshpande

[Former Member]

Hi Chaitanya,

please consider that I have only two joins in the universe (I'm trying to solve this problem starting from a blank universe...) and I have add them both to the context.

I have a lot of other tables in the LOV universe, but they are isolated, because LOV of no interest in this analysis.

And adding the join to the context didn't solve my problem.

Regards.

Lorenzo Brambilla

[Former Member]

Lorenzo,

If it is working fine in DeskI properly but cartesian in WebI, then something sounds really weird. Is it possible for you to "inspect" the SQL from both DeskI and WebI and detect anything that is different between the two?

Thanks,

John

[Former Member]

Hi John,

I've already inspected the SQL from both and, as I told, the problem is that in WEBI joins are not present in the SQL.

Here are the two queries:

(DESKI - with the join in the where clause)

SELECT

DW.SLS_SLS_DUE_DATES_LOG_VIE.OS_USR_TXT,

DW.SLS_SLS_DUE_DATES_LOG_VIE.PREVIOUS_DUE_DATE_DTE,

DW.SLS_SLS_DUE_DATES_LOG_VIE.NEW_DUE_DATE_DTE,

DWH.ANAG_CLI_FULL.COD_CLIENTE,

concat(concat (DWH.ANAG_CLI_FULL.RAG_SOC_CLI_1,' '),DWH.ANAG_CLI_FULL.RAG_SOC_CLI_2)

FROM

DW.SLS_SLS_DUE_DATES_LOG_VIE,

DWH.ANAG_CLI_FULL

WHERE

( DW.SLS_SLS_DUE_DATES_LOG_VIE.COMP_COD=DWH.ANAG_CLI_FULL.COD_SOC and DW.SLS_SLS_DUE_DATES_LOG_VIE.CUST_SUPP_COD=DWH.ANAG_CLI_FULL.COD_CLIENTE )

(WEBI - without the where clause)

SELECT

DW.SLS_SLS_DUE_DATES_LOG_VIE.OS_USR_TXT,

DW.SLS_SLS_DUE_DATES_LOG_VIE.PREVIOUS_DUE_DATE_DTE,

DW.SLS_SLS_DUE_DATES_LOG_VIE.NEW_DUE_DATE_DTE,

DWH.ANAG_CLI_FULL.COD_CLIENTE,

concat(concat (DWH.ANAG_CLI_FULL.RAG_SOC_CLI_1,' '),DWH.ANAG_CLI_FULL.RAG_SOC_CLI_2)

FROM

DW.SLS_SLS_DUE_DATES_LOG_VIE,

DWH.ANAG_CLI_FULL

These two queries are generated without any context. If I add a new context, Deski remains the same, WEBI generates two different queries, one for each table.

Obvisuly, the universe these two queries are based on is the same.

I have replicated this problem also on a new virtual machine where I installed BO XI R2 SP4 FixPack 4.5 from scratch only for testing purpose.

Regards.

Lorenzo

[WMuellner]

Hello,

which one is the fact table in your universe in the case of the example SQL?

I'd expect you did not define the "measure" objects properly...

a few more questions:

Did you define the join cardinalities?

Did you check your universe before exporting?

How many contexts do you have?

try to remove the contexts and add an aggregation function to the measure (e.g. "sum(facttab.measure))

which universe parameters did you set?

make sure you did not use "shortcut joins"

hth,

Walter

[Former Member]

Hi Walter,

the fact table in the examples is DW.SLS_SLS_DUE_DATES_LOG_VIE.

The other table contains to the customer's information.

When I check the universe, Designer tells me that there are some isolated tables (they belong to the linked universe and are LOV not related to this analysis, so I suppose this is correct and doesn't matter)

Join cardinalities are all set (manually) and no shortcut joins are used. But during the check, it tells me that two cardinalities are unknown (one of this is the join between the tables in the query, and this may be the problem).

Context and loop check are successfull.

I have tried to reset the cardinality (manually and automatically), but with no success.

No contextes are defined at the moment: one I set one, WEBI generates two different queries, without any join between them.

My measures are dates, and I can't set an aggregation function on them.

I didn't change any parameters of the universe, but the linked universe.

Regards

Lorenzo

Edited by: Lorenzo Brambilla on Nov 25, 2008 9:25 AM

[WMuellner]

Lorenzo,

try the following:

1. set the join cardinalitites manually (fact table has "n"-side and LOV table has "1"-side of join)

(automatic setting tries to find the cardinalities based on the actual table content, DO NOT USE in case of large or partly filled tables)

2. remove all contexts

3. try the query again, if still wrong -> most probably a bug in webi, open a case with support.

4. check universe "SQL" settings in "Univers Parameters" dialog,

in the SQL tab, there are some check boxes (like, "generate multiple SQL..."). what is the status of these boxes? Try to check at least the following: generate multiple sql "for each measure" and "for each context")

5. try again, if still wrong: --> bug... see above.

hth,

Walter

[Former Member]

Walter,

I've already tried all your suggestions, but with no success.. I'm going to open a new case with support.

Thanks to all for your help.

Lorenzo

[Former Member]

Hi Lorenzo,

I encountered the same issue and didn't find a way to solve it so far. Did you got a solution from support by now or how did you solved this issue finally?

Thank you very much for your advice!

Frank

[Former Member]

Hi Lorenzo,

Could you please try changing ANSI92 parameter from Yes to No or from No to Yes in the Universe designer >Parameters> Parameter tab.

Hope this will help.

Regards,

Chaitanya Deshpande

[Former Member]

Thanks Chaitanya,

I tried it, but I still have the same problem.

One more info is that if I add a context, webi query panel generates two queries (one on the fact table and one on the LOV table) without join them.

Regards,

Lorenzo Brambilla