XI R2 SQL Problems?

system · January 10, 2006, 9:22pm

I have a perfectly running webi system in the Dev environment BOBJ XI. I migrated the Universe to an XI R2 system and some of the queries are timing out and running for a very long time. When I view the SQL (generated SQL), the XI R2 gives a very WEIRD SQL statement with sub selects and logical operators while the XI system has expected SQL.

Has anyone come across this? I saw some posts in this forum earlier that

geoenvy (BOB member since 2005-10-20)

SebastienGoiffon · January 11, 2006, 12:11pm

Hi,

How did you migrate your reports ? using the import wizard or manually ?

Regards

system · January 11, 2006, 5:02pm

I haven’t seen anything in XI, but I know that 6.5.2 had some SQL issues (not sure if it’s using a similar SQL generating engine or not). I saw objects that should have been in the “WHERE” clause moved up into the “FROM” clause (and generating incorrect results). I’m curious to see what’s going on in your case.

Any database changes along with the upgrade? For example, I know when we went to Oracle 9 from Oracle 8 a few years back we had a couple of queries that we had to tweak.

Maybe compare the SQL being generated in the new environment against the old to see exactly where the differences are.

HTH -

DJ

DJ06482 (BOB member since 2002-11-22)

system · January 12, 2006, 4:41pm

First of all thanks for replying.

We are experienceing problems here in migrating to XI R2 just before production.
Firstly we did not migrate any reports. We just migrated the universe from dev to prod. We used the Import Wizard to migrate the Universe. Universe looks fine in Prod.

We get this problem when we use dimension and measure objects in the Query Panel and use Filters in the filter panel. We do not seem to get the error when we query against just one table but more testing needs to be done to validate this statement. Secondly, we run the same query on both the Dev and Prod boxes. The Dev box (in XI R1) gives correct results while the Prod box with XI R2 gives wrong results

When I hit view SQL the R1 in Dev gives me good SQL which when I copy and paste into the DB2 Query window and run against the Production DB gives me the correct results.

When I view the incorrect result SQL in XI R2, it gives me a weird sub select and the things that should not be in the Where clause.

What am I doing wrong?

Thanks

geoenvy (BOB member since 2005-10-20)

system · January 17, 2006, 2:58pm

I am having the same problem. I migrated a universe and some reports from 6.5.2 to XI Release 2 using the Import Wizard. Most reports worked as expected, but some kept timing out. When I checked to see what was wrong I discovered that some very strange SQL was being generated. Rebuilding the report from scratch in XI R2 generates the same SQL. As far as I can tell, nothing has changed in the universe, all the tables and joins are the same in 6.5.2 and XI R2.

Below you can see the same query as generated by 6.5.2. and XI R2.

6.5.2

SELECT
  ACTUAL_ENDDATE.PERIOD,
  CHANGE.CHANGE_SLA_NAME,
  count(distinct(CHANGE.CHANGE_ID))
FROM
  dbo.DIM_TIME_DAY  ACTUAL_ENDDATE INNER JOIN dbo.DIM_CHANGE  CHANGE ON (CHANGE.CHANGE_ACTUAL_END_DT >= ACTUAL_ENDDATE.CURRENT_DAY AND CHANGE.CHANGE_ACTUAL_END_DT < ACTUAL_ENDDATE.FORWARDDAY_1 AND CHANGE.ETL_CURRENT=1)
   INNER JOIN dbo.FACT_OBJECTCHANGE  LIFECYCLE ON (CHANGE.CHANGE_ID=LIFECYCLE.OBJ_ID AND OBJ_TYPE = 'C' AND CHANGE.ETL_CURRENT =1)
   INNER JOIN dbo.DIM_STATUS  STATUS ON (LIFECYCLE.STATUS_ID=STATUS.STATUS_ID)
  
WHERE
  (
   CHANGE.CHANGE_STATUS_NAME  =  'GESLOTEN'
   AND
   ACTUAL_ENDDATE.PERIOD  BETWEEN  200505  AND  200511
   AND
   STATUS.STATUS_NAME  Not In  ( 'AFGEHANDELD','GEIMPLEMENT','GESLOTEN'  )
  )
GROUP BY
  ACTUAL_ENDDATE.PERIOD, 
  CHANGE.CHANGE_SLA_NAME

XI R2

SELECT
  ACTUAL_ENDDATE.PERIOD,
  CHANGE.CHANGE_SLA_NAME,
  count(distinct(CHANGE.CHANGE_ID))
FROM
  dbo.DIM_CHANGE  CHANGE INNER JOIN dbo.DIM_TIME_DAY  ACTUAL_ENDDATE ON (CHANGE.CHANGE_ACTUAL_END_DT >= ACTUAL_ENDDATE.CURRENT_DAY AND CHANGE.CHANGE_ACTUAL_END_DT < ACTUAL_ENDDATE.FORWARDDAY_1 AND CHANGE.ETL_CURRENT=1)
  
WHERE
  (
   CHANGE.CHANGE_STATUS_NAME  =  'GESLOTEN'
   AND
   ACTUAL_ENDDATE.PERIOD  BETWEEN  200505  AND  200511
   AND
   convert(char,ACTUAL_ENDDATE.PERIOD) +' '+ CHANGE.CHANGE_SLA_NAME  In  
     (
     SELECT
       convert(char,ACTUAL_ENDDATE.PERIOD) +' '+ CHANGE.CHANGE_SLA_NAME
     FROM
       dbo.DIM_STATUS  STATUS INNER JOIN dbo.FACT_OBJECTCHANGE  LIFECYCLE ON (LIFECYCLE.STATUS_ID=STATUS.STATUS_ID)
        INNER JOIN dbo.DIM_CHANGE  CHANGE ON (CHANGE.CHANGE_ID=LIFECYCLE.OBJ_ID AND OBJ_TYPE = 'C' AND CHANGE.ETL_CURRENT =1)
        INNER JOIN dbo.DIM_TIME_DAY  ACTUAL_ENDDATE ON (CHANGE.CHANGE_ACTUAL_END_DT >= ACTUAL_ENDDATE.CURRENT_DAY AND CHANGE.CHANGE_ACTUAL_END_DT < ACTUAL_ENDDATE.FORWARDDAY_1 AND CHANGE.ETL_CURRENT=1)
       
     WHERE
       STATUS.STATUS_NAME  Not In  ( 'AFGEHANDELD','GEIMPLEMENT','GESLOTEN'  )
     )
  )
GROUP BY
  ACTUAL_ENDDATE.PERIOD, 
  CHANGE.CHANGE_SLA_NAME

It looks as if the query is being optimized in some nonsensical way…

McAroni (BOB member since 2006-01-17)

system · January 17, 2006, 4:25pm

I’m glad I am not alone. I found this a few days ago and have raised this with Tech support… So far they have asked for bith pieces of SQL and both universes.

No response as yet.

Grizzly-Law (BOB member since 2004-07-01)

SebastienGoiffon · January 17, 2006, 11:26pm

Hi,

I remember now that indeed SQL is optimized ! Did you use Oracle ?

Regards

Senio BO consultant since 98 and version 3

system · January 18, 2006, 10:27am

I’m using SQL Server 20000.

McAroni (BOB member since 2006-01-17)

system · January 18, 2006, 11:24am

So am I

Grizzly-Law (BOB member since 2004-07-01)

system · January 18, 2006, 11:34am

Another thread (https://bobj-board.org/t/52392) mentions a workaround; change the 1:N joins to 1:1 in the universe. I’ve tried it and, it seems to work. It’s not a proper solution though

McAroni (BOB member since 2006-01-17)

system · January 18, 2006, 12:15pm

Interesting suggestion. As far as I know, the only need to specify cardinalities is to assist in the auto detection of Contexts.

After you change the cardinalities to 1:1 I assume you do not re detect the contexts ??

Grizzly-Law (BOB member since 2004-07-01)

system · January 18, 2006, 12:20pm

[quote:8e1b61a6f5=“Grizzly-Law”]After you change the cardinalities to 1:1 I assume you do not re detect the contexts ??
[/quote]

Correct, the only thing I changed were the cardinilaties, the contexts remain the same.

McAroni (BOB member since 2006-01-17)

system · January 18, 2006, 12:40pm

Well to my joy … it works. I no longer get a spurious sub select in my sql.

Thanks for the suggestion.

But this surely is a bug that needs addressing fast.

Grizzly-Law (BOB member since 2004-07-01)

system · January 18, 2006, 8:06pm

My situation is the same. Opened a case with prod support but they say they are “still testing”

geoenvy (BOB member since 2005-10-20)

system · January 20, 2006, 3:51pm

Hi All,

Interested by your comments on SQL Oddities in BOXI R2; I’ve also experienced some strange phenomena and was wondering if anyone else has had similar issues…

If I modify the “SQL generated by your query” in WebI (which obviously works fine) by adding a single space before a line, it breaks the code…

So, if the “SQL generated by your query” is:


SELECT
  vpdb_Person.person_id,
  vpdb_Person.surname,
  vpdb_Person.forename,
  vpdb_Person.doj,
  vpdb_Person.sex,
  vpdb_Person.cost_centre_name,
  vpdb_Person.function_nam,
  vpdb_Person.empl_class_descr,
FROM
  vpdb_Person
WHERE
  (
   vpdb_Person.doj  BETWEEN  @prompt('Enter Doj (Start):','D','Vpdb Person\Doj',Mono,Free,Persistent,,User:0)  AND  @prompt('Enter Doj (End):','D','Vpdb Person\Doj',Mono,Free,Persistent,,User:1)
   AND
   (
   vpdb_Person.function_nam In @prompt('Enter value(s) for Function Name (% for ALL):','A','Vpdb Person\Function Nam LOV ALL',Multi,Free,Persistent,,User:2)
  )

And I change it to:


SELECT
  vpdb_Person.person_id,
  vpdb_Person.surname,
  vpdb_Person.forename,
  vpdb_Person.doj,
  vpdb_Person.sex,
  vpdb_Person.cost_centre_name,
  vpdb_Person.function_nam,
  vpdb_Person.empl_class_descr,
FROM
  vpdb_Person
WHERE
  (
   vpdb_Person.doj  BETWEEN  @prompt('Enter Doj (Start):','D','Vpdb Person\Doj',Mono,Free,Persistent,,User:0)  AND  @prompt('Enter Doj (End):','D','Vpdb Person\Doj',Mono,Free,Persistent,,User:1)
   AND
   (
    vpdb_Person.function_nam In @prompt('Enter value(s) for Function Name (% for ALL):','A','Vpdb Person\Function Nam LOV ALL',Multi,Free,Persistent,,User:2)
  )

Note the single space added before the “vpdb_Person”, 3 lines from the bottom

It Fails…? This seems like a minor issue, but has massive ramifications when altering the default code more significantly

Any one got any ideas? - Or is it just me?

Cheers

Alaric

cartwrightjepson (BOB member since 2005-10-18)

system · May 9, 2006, 2:49pm

[quote:938777ecf0=“Grizzly-Law”]I’m glad I am not alone. I found this a few days ago and have raised this with Tech support… So far they have asked for bith pieces of SQL and both universes.

No response as yet.
[/quote]

same here. just spent last few days pulling my hair trying to figure why some reports that were migrated to XIr2 suddenly have v. poor performance. And this is what was happening in my case.

FYI As a workaround (for some reports) making sure that all objects that are used in conditions are in the select clause will prevent this weird SQL from beign generated.

agulland (BOB member since 2004-03-17)

system · May 9, 2006, 3:20pm

Hi all,

From what I hear this is a common bug that arises from using contexts in R2… something about R2 not managing contexts properly and applying somekind of union all and then crapping out on performance…

cheers…

David_Dadoun (BOB member since 2006-04-21)