Converting eFashion from UNV to UNX – Part One

One of the first things I’ve tried to do with SAP BusinessObjects Business Intelligence 4.0 is convert the sample eFashion universe (actually, there are two sample eFashion universes, but I digress) from a traditional UNV universe (Universe Design Tool) to a new UNX universe (Information Design Tool).  The process for converting universes in the Information Design Tool is simple and straightforward (see official product tutorials on SCN) but the results, at least with Microsoft Access universes like eFashion, are not.

Here is what a query on the original eFashion.unv looks like for Year, State, Store name, and Revenue.  I’m using SAP BusinessObjects Business Intelligence 4.0 SP02 Patch 4 (the most current release).

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.

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

The outer joins are ambiguous, so the SQL statement cannot execute.  Plus, the ANSI92 parameter is turned off by default, so I’m confused how the joins ended up in the FROM clause.

I can only speculate what the root cause is (bad settings in the PRN files?), but I’ve posted the question in the SCN Forums (click here to access).  In the meantime, I’ve used the free Microsoft SQL Server Migration Assistant (SSMA) (click here to download) to move eFashion from Microsoft Access to Microsoft SQL Server.  My immediate concern is to get some party pants demos put together for my upcoming presentation at the SAP BusinessObjects User Conference.

So far, my BI 4.0 experience has been in the lab.  But Michael Welter indicates that he’s had success converting real production universes.

And let’s face it, most production universes don’t use Microsoft Access.  Especially the production universes of ramp up customers.  But it seemed to me that my little eFashion test was the “Hello, world” of the new Information Design Tool.

Thanks to Pierre LeRoux at SAP for passing along my dilemma.

Although eFashion.unx has difficulties generating SQL, there are some good experiences with both the Upgrade Management Tool and the Information Design Tool in their handling of universe restriction sets.  I will share that news in an upcoming blog post and next month at the ASUG SAP BusinessObjects User Conference.

I’ll keep everyone up-to-date regarding what becomes of my eFashion SQL question in the SCN Forum.