Different SQL generated in Desktop Intelligence and Webi. Why?

[fratto]

I'm working with BOXIr2.
I've created my universe and then a Desktop intelligence report. Everythings ok!
When I've tried to replicate this report in Webi, error WIS 10901 appears. So, I've controlled SQL generated and I've seen missing tables in FROM clause. How could I solve this problem?
Thanks.

 

[skrandel]

WOW! How can this be? Is the universe properly stored? Are you sure you don't have a second copy?

Have you talked to BO about this? This is such basic functionality that I'm shocked it's a problem.

Steve Krandel
Symantec

 

[fratto]

Hi skrandel, thanks you for your reply.
I've followed your advice: I've resaved my *.unv as AAAEnginfo.unv and named the related Universe name in Parameters as AAAEnginfo.
Exported the universe, I've created a Desktop intelligence report containing 2 dimension and 1 metric, each one from differnt tables joined together.

SQL generated in Desktop intelligence:
SELECT
TABAGE1.DESAGE,
datepart("YYYY",ORCMOV_M1.DATREG),
Sum(ORCMOV1.IMPORTO*(ORCMOV1.QTAMOV-ORCMOV1.QTAEVA)/ORCMOV1.QTAMOV)
FROM
TABAGE1 RIGHT OUTER JOIN ORCMOV_M1 ON (ORCMOV_M1.TIPOCF='C' AND ORCMOV_M1.CODAGE=TABAGE1.CODAGE)
INNER JOIN ORCMOV1 ON (ORCMOV_M1.NUMORC=ORCMOV1.NUMORC AND ORCMOV_M1.TIPOCF='C' AND ORCMOV1.FLAGSA<>'S' and ORCMOV1.FLAGCOM='' and ORCMOV1.QTAMOV<>0)

GROUP BY
TABAGE1.DESAGE,
datepart("YYYY",ORCMOV_M1.DATREG)

Then I've replicated in Webi, and this is the result:
SELECT
TABAGE1.DESAGE,
datepart("YYYY",ORCMOV_M1.DATREG),
Sum(ORCMOV1.IMPORTO*(ORCMOV1.QTAMOV-ORCMOV1.QTAEVA)/ORCMOV1.QTAMOV)
FROM
TABAGE1
GROUP BY
TABAGE1.DESAGE,
datepart("YYYY",ORCMOV_M1.DATREG)

As you can see, two tables have been completely ignored.

Maybe are there some Webi setting parameters in order to lead a correct generation of SQL? Is it possible I've corrupted some file? Or what else?
Thanks

 

[skrandel]

Change your universe parameters so that it's not doing ANSI joins.

I bet it works.

Steve Krandel
Symantec

 

[fratto]

You're right!
But this means that in Webi is Ansi92 not available?
Have you ever met such a issue working with Sql Server 2000 and Webi?

Thanks.
Francesco Frattini

 

[skrandel]

I think there are a great many problems with XI that don't make sense. You should read the release notes for the latest hotfix and see if it isn't repaired.

Steve Krandel
Symantec