Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

BO Universe Outer Join Problem

Posted on 2008-10-28
16
Medium Priority
?
5,838 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 2000 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

972 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