System overiding joins

[Naith]

Hey guys,

Take a look at this for me, and tell me if you have any ideas.

I'm on XI 2, on SQL 2000, developing a report querying 7 tables on one datasource. The query approach is a spider one, with one key table left outer joining to the other 5, and one equi-join on primary/foreign keys.

There are some groups based on the key table (which for simplicity's sake, we'll call key_table). The first of these groups is based on customer_number and product_id, which form part of the clustered primary key of this table. This table stores customer and - wait for it - product info for the current business quarter.

These keys are left outer joined to history_table, which stores the same data, but for the last 4 business quarters. It's possible to have some customers or products in history who stopped trading and don't appear in key_table, and new customers who create a vice versa scenario.

Here's where it starts coming off the rails a bit; take the following simple extract;

KEY_TABLE
Cust	Prod
1234	AAA1
1235	AAA1
1236	AAA2

HISTORY_TABLE
Cust	Prod
1233	AAA2
1234	AAA1
1235	AAA1

Because the driving table is key_table, I would expect the groups to be 1234/5/6, (based on a formula of {key.cust}+'-'+{key.prod}). However, what I also get is 1233 as a group also. This occurs if the left outer link is enforced either or both ways.

There's definitely no data for this customer in key_table, and the SQL being generated at run time respects the joins as I would expect. Taking the SQL and running it on the backend for this customer returns 0 rows.

I'm not creating any views from here on in, as I've been tasked with turnaround times of sub-10 seconds per report, and the datamart I've created supports that up to here.

So, anybody have any bright ideas, or come across a workaround for this before?

Cheers,

Naith

 

[lbass]

Hi Naith,

Do you get the correct results if you choose "not enforced" for the links?

-LB

 

[Naith]

Hi Lisa.

I generally don't enforce links. I only opted to enforce once I realised I was having this issue.

Enforcing in any/both direction(s) or leaving the default non enforced joins has the same result; i.e. the wrong one.

Naith

 

[IdoMillet]

Hi Naith,

Are you 100% sure you are grouping on columns from the Key Table (not from the outer tables)?
If you are, are you 100% sure you are displaying a value from that same key table column (no Customized Group Name, no other column used to display the value)?

If you are getting a value (1233) that doesn't exist in the column displaying that value then the only possible conclusion is that the XI R2 has a nasty bug.

Good to see you back on this forum,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

Also, are you linking only on the Cust ID?

-LB

 

[Naith]

Hey Ido,

Good to hear from you, buddy.

I don't think the grouping field is the problem here.

The grouping formula is pretty much as it says in the initial post; just a concatenation of two fields from the primary key of the main table. There's no decode, no case, just literally field.a + field.b.

It wouldn't be the first bug I've found in XI2 if it were the case, but I'm trying to exhaust all possibilities before I flag it up as one.

I've created a simplified version of the report, based on just these two tables, and chucked a couple fields from each table onto the canvas, and funnily enough - no issue.

Naith

 

[Naith]

Hey LB - sorry, didn't see your post before - the linking between the two tables is on the same two fields which make up the group; customer_number and product_id.

Combined with the quarter field, they three fields ensure uniqueness, but as one is current quarter, and the other is historical quarters, this part of the primary key is obviously omitted from the link.

Naith

 

[IdoMillet]

Just to be sure, you are confirming that, when the problem occurs, a column is showing data that is not in any of the records for that table & that column?

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[IdoMillet]

Placing the bug issue aside (I still think there's a chance you are displaying a column from the history table instead of the key table -- otherwise, it's a very strange bug).

In most cases, your reporting needs would be easier to address by creating a View (or a Command) that UNION (UNION ALL actually) the key table to the history table(s).

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Naith]

Ido, that is what I'm confirming. I know what you're thinking - if I was talking someone else through this problem without seeing the data, I too would be convinced that they were grouping at on at least one historical field.

This is categorically not what I'm doing.

I can trace which fields are being used from the history table from the field explorer. There are only checkmarks on the measures, and on quarter. (The latter, confirmed by the Find in Formulas function is used only in one formula which works out the $ difference between this quarter and last. This field is not used for grouping.)

A view isn't a viable solution, due to the compilation time - (the -10secs thing is a major deal). I could write a SP solely as a workaround eventually, but the fact is that I shouldn't have to do, if you know what I mean. At the moment, it might be the only option.

Naith

 

[IdoMillet]

Naith,

I don't believe a Command (or a VIEW) would pause a performance hit. The key is to make sure you are using "UNION ALL" instead of
"UNION".

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Naith]

Fair point, Ido.

I was experiencing a hit of about 3 to 5 seconds for view compilation before I switched to using a table, but I might have been falling for the union as opposed to union all.

There is a conclusion to these shenanigans, if you're interested to hear it - though it's not so much a victory for science as much as it's just an example into the unspeakable numbnuttery of human nature.

Someone had been running tests on this report but not being sure about the integrity of the source table, created a snapshot of an older instance of the data and inserted this into a mirror of the table.

Here's where the retardation sets in; they called this table TSRDBI as opposed to the original TSRDB1, and aliased it to TSRDB1.

Crystal was acting the way it was supposed to all along - it just took me this long to ascertain that the back end had been messed about with.

I swear, if I still had my license to kill...

Thanks LB and Ido for taking the time out to look into this.

Take care,

Naith

 

[IdoMillet]

)

Thanks for "closing the loop".

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

Hiya Naith, good to see you about again.

I agree with Ido here, a Union All query should produce the results you're after, and you shouldn't see a performance hit versus your current method.

A 3 to 5 second compilation time is extortionate, something sounds amiss on the database, and keep in mind that your queries are parsed and executed on the database as well, so it should be similar.

I have many Views that run at basically the same speed of a Stored Procedure as they are simply UNIONS or simple Joins, the compilation time should be insignificant.

-k