Solved

BO Universe Outer Join Problem

Posted on 2008-10-28
16
5,711 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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
 
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 101

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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

632 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