cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Generation Error after converting eFashion.unv to eFashion.unx

dallas_marks
Active Participant
0 Kudos

One of the first things I tried to do with SAP BusinessObjects Business Intelligence 4.0 was convert the built-in eFashion universe. Unfortunately, the UNX generates unresolvable outer joins, even though the data foundation layer does not contain any. I am using BI 4.0 SP02 Fix 4. Any ideas?

Here is what a query on the original eFashion.unv looks like for Year, State, Store name, and Revenue.

SELECT
Calendar_year_lookup.Yr,
Outlet_Lookup.State,
Outlet_Lookup.Shop_name,
sum(Shop_facts.Amount_sold)
FROM
Calendar_year_lookup,
Outlet_Lookup,
Shop_facts
WHERE
( Outlet_Lookup.Shop_id=Shop_facts.Shop_id )
AND
( Shop_facts.Week_id=Calendar_year_lookup.Week_id )
GROUP BY
Calendar_year_lookup.Yr,
Outlet_Lookup.State,
Outlet_Lookup.Shop_name

And here's the SQL generated by the converted eFashion.UNX. Notice the outer joins in the FROM clause even though the universe doesn't contain outer joins.

SELECT
Calendar_year_lookup.Yr,
Outlet_Lookup.State, Outlet_Lookup.
Shop_name,
sum(Shop_facts.Amount_sold)
FROM Calendar_year_lookup,
Outlet_Lookup,
Shop_facts,
{ oj Outlet_Lookup LEFT OUTER JOIN Shop_facts ON Outlet_Lookup.Shop_id=Shop_facts.Shop_id },
{ oj Shop_facts LEFT OUTER JOIN Calendar_year_lookup ON Shop_facts.Week_id=Calendar_year_lookup.Week_id }
GROUP BY Calendar_year_lookup.Yr, Outlet_Lookup.State, Outlet_Lookup.Shop_name

How should I resolve the issue so correct SQL is generated by the Information Design Tool 4.0?

Accepted Solutions (0)

Answers (1)

Answers (1)

0 Kudos

Hi Dallas,

This is a known issue, the only problem is with the legacy unv, it does not affect rel unv's. We've reported this problem to the Sample Report team so they are aware of the problem. Not sure if they plan to update or fix the issue.

Is there a reason why you are using it?

Thank you

Don

dallas_marks
Active Participant
0 Kudos

Thanks, Don. Just to clarify, you're saying that the issue is with the eFashion.unv universe itself, not with the Information Design Tool or the SQL generator for MS Access? Has a "solution" been posted for fixing the eFashion universe?

0 Kudos

Correct,

Miguel has opened a dialog with the Sample Report Team and not sure what the answer was. Currently there are no samples shipped with BOE 4.0 so technically there is no issue...

All I can suggest is you use your own Universe or try to fix it your self if that's possible. I don't think they are planning on shipping samples with the GA release. They may eventually but no sure at this time.

Don