Universe Joins 1

[Smithsc]

I have got 3 tables in a universe which I'm trying to link. The first is a 'Company' table which holds customer info. The second is a 'Sales' table which holds all sales for each customer, and the third is a 'Person' table which holds the sales person name connected to the sale.

I'm trying to link these tables together so that I can see all customers, what sales they have had and who was responsible for the sale. I want to see all customers (even if they have had no sales), all sales (even if there wasn't a person connected to it) and all persons.

I know the fields I need to link by. It's just the cardinality I'm struggling with. I've tried every combination of joins I can think of (including inner and outer joins) and they all produce various error messages.

I know the answer is probably very simple but could somebody please help me with this problem as I'm about to throw my desktop out of the window.

Many thanks for any help.

 

[blom0344]

Your requirement suggest having full outer joins between all tables , which isn't going to be supported.
Make sure that you are always sure to have all data returned from the sales fact (which means using an outer join between sales and person)
and use seperate query definitions to fetch customers without sales and all persons.
Cardinality is not the issue here (universe will not act upon it) , but join definitions are..

Ties Blom

 

[Smithsc]

Thanks for your help. I don't exactly understand what you mean by 'seperate query definitions'. Could you please explain.

Thanks

 

[blom0344]

Well, to have list of customers that have no sales for a certain period:

Select customer from table_customer where customer not in 
(select customer from table_customer c,tables_sales s where
c.pkey_cust = s.fkey_cust and s.year = 2008)

would yield all customers that had no sales in 2008

Such a non-correlated subquery is pretty easy to build, though I am not sure if that goes for all BO tools.

What are you working with?

Ties Blom

 

[Smithsc]

I am using Universe designer accessing a SQL database.

What I don't understand is, I have tried to do the same thing using crystal reports and it was very easy to achieve. All I did was link the customer table to the sales table using a 'Left Outer Join' and then link the sales table to the person table using another 'Left Outer Join'.

Why isn't it as easy in Universe Designer.

 

[blom0344]

Hmm, that would still not fetch a resultset that includes all persons I think.
I have no designer available, but if you are on 6.5 or higher you could build a derived table storing the sales and customer (including the outer join) and then use this object to outer join with the person table.
Technically that should work in any case, but there may be a more simple solution. Can't recall from memory though..

Ties Blom

 

[Smithsc]

Sorry, I think I've confused you. I don't want to see all the persons. I want to be able to see the sales even if they haven't got a person assigned to them.

 

[blom0344]

Okay, what sort of error do you run into?

Ties Blom

 

[Smithsc]

When I link the customer to the sales where the cardinality reads:
Each Company has zero or more Sales.
Each Sales has one and only one Company.

and I link the Sales to the Person where the cardinality reads:
Each Sales has zero or one Person.
Each Person has one or more Sales.

I get the error message:

Exception: DBD, [Microsoft][ODBC SQL Server Driver][SQL Server]Query contains an outer-join request that is not permitted.State: 42000

 

[blom0344]

Look into the odbc\sqlsrv.prm file.
There is a setting where you can activate ANSI92 join syntax:

<Parameter Name="OUTERJOINS_GENERATION">ANSI_92</Parameter>
<Parameter Name="OUTERJOINS_COMPLEX">Y</Parameter>.

Then set parameter in designer to ANSI92 = Yes

The problem you are having may be due to SQL being generated with the 'old' SQL join syntax of SQL server

(the asterisk * )

Ties Blom

 

[Smithsc]

I've found the file you mentioned and the line of code reads
<Parameter Name="OUTERJOINS_GENERATION">USUAL</Parameter>

There isn't a "OUTERJOINS_COMPLEX" LINE though.

Do I change the USUAL part to read ANSI_92 and the parameter to be 'Yes'
Does the second line need to be there?

 

[blom0344]

I'd say first make a backup copy of the file

Then change:

<Parameter Name="OUTERJOINS_GENERATION">USUAL</Parameter>

to

<Parameter Name="OUTERJOINS_GENERATION">ANSI_92</Parameter>

Do not yet add the additional line

In Designer properties set parameter ANSI92 = Yes
(you need to search that one, have forgotten where the parameters are hiding)


Ties Blom

 

[Smithsc]

Ties Blom,
Thankyou for all your help and perseverance. It seems to have worked.