Solved

BO Universe Outer Join Problem

Posted on 2008-10-28
16
5,654 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

734 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