Strange SQL Generation SQL Server XIr2 Migrated Universes

[system]

Hi,

I have noticed some rather strange and rather alarming behaviour during our test migration to XIr2 and was wondering whether anyone had come accross the issue…

Migration using either to upgrading to 6.5 repo or using the all in one upgrade from 6.1b made no difference to the problem, described below.

ODBC or OLEDB connection strings made no difference either.

Using Ansi or Non-Ansi settings also made no difference, results below are using universe parameter ANSI set to ‘Yes’

SQL Server Configuration Details:-

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: ) – Service Pack 3 ODBC Driver – 2000.85.1025.00
OLEDB – Business Objects XIr2

Check that rather funky Oracle syntax, suprisingly does not work too well with our SQL Server… 8)

SQL Generated when ANSI Parameter set to ‘Yes’ – Incorrectly generated SQL.

SELECT
  count(distinct(dbo.MEMBER.ACCT_NUM)),
  dbo.GROUPING_TENURE.TENURE_GROUPING_BY_6,
  dbo.PROMOTION.PRIMARY_PARTNER,
  dbo.CURRENT_PRICE_INDEX.CURRENT_PACKAGE_GROUP
FROM
  dbo.MEMBER INNER JOIN dbo.MEMBER_SNAPSHOT ON (dbo.MEMBER.MEMBER_KEY=dbo.MEMBER_SNAPSHOT.MEMBER_KEY)
   INNER JOIN dbo.PROMOTION ON (dbo.MEMBER_SNAPSHOT.PROMOTION_KEY=dbo.PROMOTION.PROMOTION_KEY)
   INNER JOIN dbo.CURRENT_PRICE_INDEX ON (dbo.MEMBER_SNAPSHOT.CURRENT_PRICE_INDEX_KEY=dbo.CURRENT_PRICE_INDEX.CURRENT_PRICE_INDEX_KEY)
   INNER JOIN dbo.GROUPING_TENURE ON (dbo.MEMBER_SNAPSHOT.REG_CYCLE_AGE=dbo.GROUPING_TENURE.TENURE_GROUP_KEY)
  
WHERE
  ( 
  dbo.GROUPING_TENURE.TENURE_GROUPING_BY_6+' '+dbo.PROMOTION.PRIMARY_PARTNER+' '+dbo.CURRENT_PRICE_INDEX.CURRENT_PACKAGE_GROUP  IN  (SELECT
  dbo.GROUPING_TENURE.TENURE_GROUPING_BY_6+' '+dbo.PROMOTION.PRIMARY_PARTNER+' '+dbo.CURRENT_PRICE_INDEX.CURRENT_PACKAGE_GROUP
FROM
  dbo.MEMBER_FLAG INNER JOIN dbo.MEMBER_SNAPSHOT ON (dbo.MEMBER_SNAPSHOT.MEMBER_FLAG_KEY=dbo.MEMBER_FLAG.MEMBER_FLAG_KEY)
   INNER JOIN dbo.PROMOTION ON (dbo.MEMBER_SNAPSHOT.PROMOTION_KEY=dbo.PROMOTION.PROMOTION_KEY)
   INNER JOIN dbo.CURRENT_PRICE_INDEX ON (dbo.MEMBER_SNAPSHOT.CURRENT_PRICE_INDEX_KEY=dbo.CURRENT_PRICE_INDEX.CURRENT_PRICE_INDEX_KEY)
   INNER JOIN dbo.GROUPING_TENURE ON (dbo.MEMBER_SNAPSHOT.REG_CYCLE_AGE=dbo.GROUPING_TENURE.TENURE_GROUP_KEY)
  
WHERE
  ( 
  dbo.MEMBER_FLAG.MEMBER_STATUS  =  'Active'
  )
)
  AND  dbo.GROUPING_TENURE.TENURE_GROUPING_BY_6+' '+dbo.PROMOTION.PRIMARY_PARTNER+' '+dbo.CURRENT_PRICE_INDEX.CURRENT_PACKAGE_GROUP  IN  (SELECT
  dbo.GROUPING_TENURE.TENURE_GROUPING_BY_6+' '+dbo.PROMOTION.PRIMARY_PARTNER+' '+dbo.CURRENT_PRICE_INDEX.CURRENT_PACKAGE_GROUP FROM
  dbo.PROMOTION INNER JOIN dbo.MEMBER_SNAPSHOT ON (dbo.MEMBER_SNAPSHOT.PROMOTION_KEY=dbo.PROMOTION.PROMOTION_KEY)
   INNER JOIN dbo.CURRENT_PRICE_INDEX ON (dbo.MEMBER_SNAPSHOT.CURRENT_PRICE_INDEX_KEY=dbo.CURRENT_PRICE_INDEX.CURRENT_PRICE_INDEX_KEY)
   INNER JOIN dbo.GROUPING_TENURE ON (dbo.MEMBER_SNAPSHOT.REG_CYCLE_AGE=dbo.GROUPING_TENURE.TENURE_GROUP_KEY)
  
WHERE
  ( 
  CASE WHEN dbo.PROMOTION.CURRENT_BOUNTY_PARTNER = 'CURRENT BNTY PARTNER' THEN dbo.PROMOTION.PRIMARY_PARTNER END  =  'Alba plc'
  )
)
  )
GROUP BY
  dbo.GROUPING_TENURE.TENURE_GROUPING_BY_6, 
  dbo.PROMOTION.PRIMARY_PARTNER, 
  dbo.CURRENT_PRICE_INDEX.CURRENT_PACKAGE_GROUP

Correct SQL is Generated when ANSI Parameter set to ‘Yes’ and Member Status and Current Bounty Partner Objects – that are conditions in the where clause in the example above – are added to Select objects in the query panel.

SELECT
  count(distinct(dbo.MEMBER.ACCT_NUM)),
  dbo.GROUPING_TENURE.TENURE_GROUPING_BY_6,
  dbo.PROMOTION.PRIMARY_PARTNER,
  dbo.CURRENT_PRICE_INDEX.CURRENT_PACKAGE_GROUP,
  dbo.MEMBER_FLAG.MEMBER_STATUS,
  CASE WHEN dbo.PROMOTION.CURRENT_BOUNTY_PARTNER = 'CURRENT BNTY PARTNER' THEN dbo.PROMOTION.PRIMARY_PARTNER END
FROM
  dbo.MEMBER INNER JOIN dbo.MEMBER_SNAPSHOT ON (dbo.MEMBER.MEMBER_KEY=dbo.MEMBER_SNAPSHOT.MEMBER_KEY)
   INNER JOIN dbo.MEMBER_FLAG ON (dbo.MEMBER_SNAPSHOT.MEMBER_FLAG_KEY=dbo.MEMBER_FLAG.MEMBER_FLAG_KEY)
   INNER JOIN dbo.PROMOTION ON (dbo.MEMBER_SNAPSHOT.PROMOTION_KEY=dbo.PROMOTION.PROMOTION_KEY)
   INNER JOIN dbo.CURRENT_PRICE_INDEX ON (dbo.MEMBER_SNAPSHOT.CURRENT_PRICE_INDEX_KEY=dbo.CURRENT_PRICE_INDEX.CURRENT_PRICE_INDEX_KEY)
   INNER JOIN dbo.GROUPING_TENURE ON (dbo.MEMBER_SNAPSHOT.REG_CYCLE_AGE=dbo.GROUPING_TENURE.TENURE_GROUP_KEY)
  
WHERE
  ( 
  dbo.MEMBER_FLAG.MEMBER_STATUS  =  'Active'
  AND  CASE WHEN dbo.PROMOTION.CURRENT_BOUNTY_PARTNER = 'CURRENT BNTY PARTNER' THEN dbo.PROMOTION.PRIMARY_PARTNER END  =  'Alba plc'
  )
GROUP BY
  dbo.GROUPING_TENURE.TENURE_GROUPING_BY_6, 
  dbo.PROMOTION.PRIMARY_PARTNER, 
  dbo.CURRENT_PRICE_INDEX.CURRENT_PACKAGE_GROUP, 
  dbo.MEMBER_FLAG.MEMBER_STATUS, 
  CASE WHEN dbo.PROMOTION.CURRENT_BOUNTY_PARTNER = 'CURRENT BNTY PARTNER' THEN dbo.PROMOTION.PRIMARY_PARTNER END

Other Findings During this Exercise :-

OLEDB set up is poor, if the details are not entered correctly, the first time you attempt to set up the connection, you have to start completely from scratch as Designer seems to not recognise that the details have been amended and refuses to connect to the server.

Sometimes when exporting a universe to the CMS, Designer behaves although it is running a query and goes into ‘Analysing’ phase – like when a user is running a query in Desktop Intelligence. This causes a delay in export, although the universe seems to update OK.

Changing / removing universe cardinalities in some cases also resolved the problem with correct SQL being generated.

Creating Deski queries from scratch also produced the incorrect syntax.

Going to check the above still happens with a SQL Server 2005 test box.

This does not seem to be a problem with Oracle based universes

Have passed on the query to Business Objects, will post here how it goes…

---

Mak 1 (BOB member since 2005-01-06)

[system]

Interestingly enough, another universe query that I had issues with - non ANSI - can be created in Webi, but still generates unecessary syntax - using the convert function - but at least it runs …below:-

SELECT
  dbo.UKDM_BB_ORDERSTATES.ordertype,
  dbo.UKDM_BB_ORDERSTATES.orderstate,
  ORDER_UKDM_BB_STATES.STATE,
  count(dbo.UKDM_BB_MEMBERORDER.Order_id),
  STATE_UKDM_CALENDAR.YR_NUM,
  STATE_UKDM_CALENDAR.WEEK_END_DATE
FROM
  dbo.UKDM_BB_MEMBERORDER,
  dbo.UKDM_LINE_FACT,
  dbo.UKDM_CALENDAR  STATE_UKDM_CALENDAR,
  dbo.UKDM_BB_ORDERSTATES,
  dbo.UKDM_BB_STATES  ORDER_UKDM_BB_STATES,
  dbo.UKDM_BB_PRODUCT
WHERE
  ( dbo.UKDM_LINE_FACT.order_id=dbo.UKDM_BB_MEMBERORDER.Order_id  )
  AND  ( dbo.UKDM_LINE_FACT.UKDM_PRODUCT_ID=dbo.UKDM_BB_PRODUCT.UKDM_PRODUCT_ID  )
  AND  ( dbo.UKDM_BB_MEMBERORDER.UKDM_ORDER_STATE_ID=dbo.UKDM_BB_ORDERSTATES.UKDM_ORDERSTATE_ID  )
  AND  ( dbo.UKDM_LINE_FACT.UKDM_STATE_CHANGE_DATE_ID=STATE_UKDM_CALENDAR.UKDM_CALENDAR_ID  )
  AND  ( dbo.UKDM_LINE_FACT.UKDM_STATE_ID=ORDER_UKDM_BB_STATES.UKDM_STATE_ID  )
  AND  
  (
   dbo.UKDM_BB_ORDERSTATES.ordertype  =  'CEASE'
   AND
   ( dbo.UKDM_BB_PRODUCT.PRODUCT_TYPE_CODE = 'SERVICE'  )
   AND
   dbo.UKDM_BB_ORDERSTATES.ordertype +' '+ dbo.UKDM_BB_ORDERSTATES.orderstate +' '+ ORDER_UKDM_BB_STATES.STATE +' '+ STATE_UKDM_CALENDAR.YR_NUM +' '+ convert(char,STATE_UKDM_CALENDAR.WEEK_END_DATE)  In  
     (
     SELECT
       dbo.UKDM_BB_ORDERSTATES.ordertype +' '+ dbo.UKDM_BB_ORDERSTATES.orderstate +' '+ ORDER_UKDM_BB_STATES.STATE +' '+ STATE_UKDM_CALENDAR.YR_NUM +' '+ convert(char,STATE_UKDM_CALENDAR.WEEK_END_DATE)
     FROM
       dbo.UKDM_BB_MEMBERORDER,
       dbo.UKDM_LINE_FACT,
       dbo.UKDM_CALENDAR  STATE_UKDM_CALENDAR,
       dbo.UKDM_BB_ORDERSTATES,
       dbo.UKDM_BB_STATES  ORDER_UKDM_BB_STATES
     WHERE
       ( dbo.UKDM_LINE_FACT.order_id=dbo.UKDM_BB_MEMBERORDER.Order_id  )
       AND  ( dbo.UKDM_BB_MEMBERORDER.UKDM_ORDER_STATE_ID=dbo.UKDM_BB_ORDERSTATES.UKDM_ORDERSTATE_ID  )
       AND  ( dbo.UKDM_LINE_FACT.UKDM_STATE_CHANGE_DATE_ID=STATE_UKDM_CALENDAR.UKDM_CALENDAR_ID  )
       AND  ( dbo.UKDM_LINE_FACT.UKDM_STATE_ID=ORDER_UKDM_BB_STATES.UKDM_STATE_ID  )
       AND  
       STATE_UKDM_CALENDAR.CAL_DT  >=  (getdate())-30
     )
  )
GROUP BY
  dbo.UKDM_BB_ORDERSTATES.ordertype, 
  dbo.UKDM_BB_ORDERSTATES.orderstate, 
  ORDER_UKDM_BB_STATES.STATE, 
  STATE_UKDM_CALENDAR.YR_NUM, 
  STATE_UKDM_CALENDAR.WEEK_END_DATE 

---

Mak 1 (BOB member since 2005-01-06)

[system]

I’m getting a similar thing, in the BO is adding to_char functions to the sql which SQL server doesn’t recognise, on my migrated docs.I’ll play and let you know how it goes.

---

rjback (BOB member since 2005-10-21)

[system]

Hi,

Thank god, another sufferer… !

One of my other universes also had the TO_CHAR problem that you described…

If you play around with a couple of the work arounds - above - you should find it makes a difference, not good though…

---

Mak 1 (BOB member since 2005-01-06)

[system]

Fixed it by putting the items in the where filter into the select list, and that did the trick. sigh

---

rjback (BOB member since 2005-10-21)

[system]

Yes, thats what I found…see example, above…

I also found that changing one or more of the joined tables from 1:M to 1:1 in the relevant universe and rexporting fixed it without changing the Select objects…

Neither are ‘proper’ solutions though…

---

Mak 1 (BOB member since 2005-01-06)

[system]

rjback, can you let me know what configuration you are running, similar to my previous post…

---

Mak 1 (BOB member since 2005-01-06)

[system]

BO have raised this as a bug, so I guess that you should just hope that your DB is not SQL Server…

Just wondering as to why something as major as this didn’t come out during testing… 8)

---

Mak 1 (BOB member since 2005-01-06)

[system]

Its a new XIr2 installation on a Windows 2003 server, with a SQL Server ODBC connection.
The SQL Server involved doesn’t matter, as my understanding is that the SQL is generated without actually going to the server.

How did you find out they’ve admitted it is a bug?

---

rjback (BOB member since 2005-10-21)

[system]

The SQL Server involved doesn’t matter, as my understanding is that the SQL is generated without actually going to the server.

You are correct, but I was more interested in trying new drivers e.t.c. as Designer has new connectivity for SQL 2005…

I spoke to someone from Business Objects in the States about the issue and provided them with universe examples and reports…

One of their engineers then managed to recreate the issue and I heard from our BI director today that it has definitely been raised as a bug…

It was my understanding that the SQL generation engine had not changed, but, maybe, I have been misinformed… 8)

Unfortunately, unless I hear something soon, I won’t be able to update this as I’m leaving this contract and going away for four months.

I will, however, be catching up with one of my US colleagues towards the end of my trip, so I am sure he will let me know how the issue is resolved…

Cheers,

Mark.

---

Mak 1 (BOB member since 2005-01-06)

[system]

I believe this is related to the issue posted yesterday:

120×120 10.4 KB

XI R2 Designer, Oracle 9i RDBMS We have two tables: customers and orders, with cardinalities set to indicate a customer places many orders. We have an Order Type object using orders.order_type as the select, and Number of Unique Customers using select(distinct customers.cust_id) as the object select. In the Designer Query Panel, DeskI and WebI, when a query is defined with Number of Unique customers as a result object and Order Type = ‘ABC’ as a condition, it throws an “Incompatible combinati…

Trying changing the cardinalities all to 1 to 1, and I bet the SQL generated is much better - without having to include the condition as a result.

---

JMCabot (BOB member since 2005-12-21)

[system]

I also found that changing one or more of the joined tables from 1:M to 1:1 in the relevant universe and rexporting fixed it without changing the Select objects…

Thanks JM, I found this workaround for the issue…its interesting then that there seems to be problems with Oracle also…

I will send this Oracle link to my colleagues in the States…

Cheers,

Mark.

---

Mak 1 (BOB member since 2005-01-06)

[system]

It does look like it’s not a RDBMS specific issue: Oracle, SQL Server, MS Access… perhaps something more globally problematic. Although MS Access doesn’t have a count(distinct table.column) capability, you can test the SQL generation using the Island Resorts Marketing universe. Create an object for ‘Number of Customers’ with count(Customer.cust_id) as the select. Incompatible objects error or weird sub-queries are generated.

---

JMCabot (BOB member since 2005-12-21)

[system]

Business Objects have acknowledged this as a Bug, ID ADAPT00543360 and hot fix is available for those with support contracts.

I narrowed the problem down and it appears to occur when you have 1 to many to many relationship in the universe. Where the measure is not from the fact table and the filter is and the filter is not used in the query panel.

As an example go to the Audit universe that comes with BOXI r2 called “Activity” and put the following objects in the Query Panel:
[Action Name] [Action Year] [Avg Duration]
In the filters add [Action Type ID] = 1
A screenshot example of this can be downloaded.

If you look at the SQL, you will see the use of the IN and the concatenation of fields.

To fix the problem without the hotfix you can either add the object that is used in the filter to the query panel, or remove the cardinalitiy.
ExampleQuery.doc (124.0 KB)

---

toujays (BOB member since 2005-01-06)

[system]

I had the same problem with my DB2 database. I worked with the vendor and found that a generic ODBC connection to the database resolved the issue.

---

BOSleuth (BOB member since 2005-05-05)