Solved

BO Universe Outer Join Problem

Posted on 2008-10-28
16
5,509 Views
Last Modified: 2013-11-15
BOE XI-R2 SP-2

I have a big universe that's built off a Sql 2005 database that is set to Sql 2000 compatibility.  ANSI-92 compatibility for this universe is set to "No".

I wanted to use CLR integration on this database the other day, so I set its compatibility to Sql 2005.  But that broke many reports due to the fact that there are several joins in this universe that use the old-style outer join operators "*=" and "=*".  So I had to set it back to Sql 2000 compatibility.

I've tried setting ANSI-92 to "Yes", which simply removes the "*" from these outer-join operators, making them inner joins (not what I need).  But when I try to replace the old-style operators with LEFT OUTER JOIN or whatever, the BOE universe designer doesn't recognize those.  Parsing the join fails.

I'm thinking--I'm HOPING--this is because I did not have all 3 mojos going at the same time:  db set to 2005 PLUS the universe set to ANSI-92 PLUS the join operator being LEFT OUTER JOIN instead of *=.  I only had the universe set to ANSI-92 with the LEFT OUTER JOIN syntax.  (I had set the DB back to 2005-compatibility because I was killing some production reports).

Can anyone confirm this?  Will it work when I have all 3 of these planets lined up?  Or am I screwed somehow, being stuck at XI-R2 SP-2 indefinitely?

Thanks!
0
Comment
Question by:bamapie
  • 7
  • 7
16 Comments
 
LVL 17

Accepted Solution

by:
MIKE earned 500 total points
ID: 22825479
copy the UNIVERSE to a test area...and TRY IT...????

Or create new Universe with Configuration you are needing..???
0
 
LVL 17

Expert Comment

by:MIKE
ID: 22825487
I shy away, especially with BIG UNIVERSES from making any kind of GLOBAL CHANGES LIKE THIS....
0
 

Author Comment

by:bamapie
ID: 22831838
I've copied and tried it.  Had to copy the DB, too, since this isn't a DB-only change.

Can't get the expression right--or can't get the expression where it's palatable to BO.

Old join expression syntax is:

RSSQL1.dbo.vwShiftRunLineDowntimeTotals.RunShiftKey=*vwDailyProductionReportShiftRunData.RunShiftKey

BOE-UniverseDesign-ChangingNonAN.jpg
0
 
LVL 17

Expert Comment

by:MIKE
ID: 22890940
Recreate a NEW Universe using the correct compatibility for SQL 2005......
0
 

Author Comment

by:bamapie
ID: 22890976
I'm not going to re-create this universe.

I have copied it and changed the compatibility.
0
 
LVL 17

Expert Comment

by:MIKE
ID: 22890982
Does it WORK>...?????
0
 

Author Comment

by:bamapie
ID: 22891033
Did you read my post of 29 Oct?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 17

Expert Comment

by:MIKE
ID: 22891050
Does this WORK / Help...?


".....self-joins in the universe are generated in the FROM clause rather than in the WHERE clause. For reports that don't involve the self-join, the sql may be different but returns the same results. Here is the bug information I got from BO tech support:

ADAPT00754142

Description:

Self-join conditions that are used in query may fail or may reduce performance.

The cause of the problem is that self-join conditions were generated within the WHERE clause of the query by default.



New Behavior:

This problem is resolved.

To solve the problem, previous changes made in response to another issue (ADAPT641525) have been reversed: instead of

generating self-joins in the WHERE clause, by default self-joins will be generated in the FROM clause. However, users have

the option to generate self-joins in the WHERE clause by using this new parameter: "SELFJOINS_IN_WHERE=yes.


(This parameter must be addd to the universe parameter tabs on any universe with self-joins).
0
 

Author Comment

by:bamapie
ID: 22891085
I've read that.  That's BO basically saying they've fixed something.  I don't see evidence of whatever they're describing having fixed my issue.

I'm going to also include a snapshot of the error seen when I attempt to run a query against the BO universe with the ANSI setting change PLUS the sql db with 2005 compatibility ON.
BOE-UniverseDesign-ChangingNonAN.jpg
0
 

Author Comment

by:bamapie
ID: 22891392
It had been stale since my last post Oct 29.  My posting the delete request has caused it to fire up.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22892551
Is the new information helpful?

Please remember all experts are vounteers and work when they have time.  Also there aren't many BO Universe experts so so,etimes a response takes time.

mlmcc
Zone advisor
0
 

Author Comment

by:bamapie
ID: 22896354
I found what it was.  This DB had a couple of non-ANSI views (joins using the *= and =* operators).

I rewrote the queries of these views, and all is well.

I'm awarding points because the notion of copying the universe and DB was a safe route to take...
0
 
LVL 17

Expert Comment

by:MIKE
ID: 22896406
Hum....I thought we identified those "*=" and "=*" join operators early on ....???

Oh well, glad it all worked out.

M
0
 

Author Comment

by:bamapie
ID: 22896434
These weren't joins in the universe.  They were embedded inside the scripts of the views themselves.  Which were then joined (normally, non-outer) to other entities in the universe.
0
 
LVL 17

Expert Comment

by:MIKE
ID: 22896448
Ahhh I see...I see....ok..good.

THANKS for the added info.
M
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now