Webi SQL - "Join x,y"

[misdeveloper]

I am trying to diagnose a problem with one of my Webi reports which returns two complex measures (each containing decode statements), grouped by one dimension. When each of the measures is used on its own, they work perfectly. However, when they are used in conjunction they return the error "no data found".

When I view the SQL generated by this report, it has the format "Join (x), " where (x) and are the individual SELECT statements for the two measures.

To my knowledge "Join..." is not valid SQL syntax, so I am left to assume this is Webi's way of doing a UNION, or some other standard SQL function?

Can anyone shed any light on this 'Join' syntax, and how Webi returns distinct queries into adjacent columns, but grouped by the same dimension?

 

[skrandel]

This is due to you choosing measures from multiple fact tables. BO will automatically break the query into 2 parts and merge the results together.

It is surprising that they work individually.

Make SURE that each SQL statement is correct on it's own. If they are not correct, then you have incorrectly defined some contexts in your universe.

Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[misdeveloper]

Just to provide some further information...

The measures do not select data from multiple fact tables, they are both based on the one central fact in the universe (a view actually, but I can't see how that would matter).

And yes, both measures work perfectly well on an individual basis. I've checked the SQL myself and there's nothing wrong there. It's just when Webi tries to execute them both together that there's a problem.

I'm wondering if there's a performance issue. Each SQL statement takes a number of minutes to execute individually, as they return millions of rows. To test this theory I wanted to run the generated SQL out on a smaller (test) database by copying the generated SQL out of Webi, but it doesn't seem to be valid?!

 

[skrandel]

The only way BO generates Join query is if you have contexts (or implied ones).

Have you looked at the limits on this universe. Do your queries take longer than the limits.

Alos, if your queries return millions of rows, what's the point? This is not the right tool for millions of rows.

Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[misdeveloper]

Sorry, perhaps I didn't explain my last post very well. The result of each measure is a SUM of millions of rows, rather than actually returning millions of rows, per se.

I'm expecting to see totals of around 3-4 million rows in each measure. If they were simple queries this wouldn't be so bad.

However, the fact table is joined to at least 2 reference data tables, and DECODE statements are used to limit the rows returned in the reference data. This has enabled us to 'soft code' some of our parameters as records in the warehouse, at the expense of performance.

I would have used CASE statements to implement this functionality, but was told that this could produce unpredictable results in BO.

I'm don't think this is a time-out problem, I think it's more to do with the way Webi is implementing the query, so I'd just like to know if 'join x,y' is documented somewhere so that I can troubleshoot it?!

 

[skrandel]

How does the same query perform in the client?

Join x, y is not a documented the way you might think. Look it up in the full-client documentation. It's probably in there under the context subject.


I don't believe the Join x,y thing is the problem. These are not database joins. What this means is that it is taking the results from Query X and the results from Query Y and "join"ing them in the BO microcube. However, this is not really a join, it's a full outer-join. So, there is no way that BO can drop all the data.

Where are you located? Perhaps we can discuss this offline. Send me a message with your contact info and I'll give you a call.

Steve
skrandel@baseconsulting.com

Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[vaass]

This is because you have not defined the context properly in your universe. Create a context using all the tables used in the query with proper joins and then check in the report sql, whether it is creating a single query or multiple queries. Then try generating it.