Dimensions joined in FM

[sanchoniathon]

Hi to all,

We have this fact table joined to these 2 dimensions but also these 2 dimensions are joined togeter

dimension Ship To Client <-> FACT <-> dimension Sales
               |                                                   |
               |                                                   | 
               |<------------------------------------>|
   

QUestion:
- Wehenever i include facts from the fact table and most of the fields from "dimension Ship To Client" in our report it works fine

- But as soon as i include an additional field from that same dimension "dimension Ship To Client" it all of a sudden crashes, meaning the report returns the following error message :

The report could not run because a server error occured.
Details :
XQE-GEN-0005 Found an internal error:
com.cognos.xqe.runtree.relational.vectorization.expressions.generated.XVectorFilterIsNotDistinctFromTimestampColumnTimestampColumn
RSV-SRV-0042 Trace back:
RSReportService.cpp(769): XQEException: CCL_CAUGHT: RSReportService::processImpl()

I mean that one field comes from the same dimension but it fails ? Any ideas ? Suggestions ?

Thanks !

[bus_pass_man]

You might recall when you read the Cognos proven practices document that it talks about removing ambiguity in relationships.  This situation might be one of the cases where that is relevant.  If there are ambiguous relationship paths the query engine will take the alphabetically first one, which may or may not be what you want. 

You want to have one and only one possible path between a table and another table. If there is role-playing then you create aliases.

Why does this dimension to dimension relationship exist?  You don't say, which precludes any analysis.


What are the properties of the query item which causes the problem?  The error seems to be complaining about a timestamp and, possibly, a filter. 

Wehenever (sic) i (sic) include facts from the fact table and most of the fields from "dimension Ship To Client" in our report it works fine

Is there anything from the sales dimension?

I mean that one field comes from the same dimension but it fails ?

Elaborate on this.

Can you describe what the dimensions are?  The names almost (especially in the case of Sales) suggest that they are facts as well.

What release?

[sanchoniathon]

To begin with, thank you for having replied it's greatly appreciated.

Second, here are my answers:

You might recall when you read the Cognos proven practices document that it talks about removing ambiguity in relationships.  This situation might be one of the cases where that is relevant.  If there are ambiguous relationship paths the query engine will take the alphabetically first one, which may or may not be what you want.
==> Our company is dealing with a firm that might of have developed this months or even years ago i've been told.
==> Are we delaing with what we call a LOOP here in your opinion ?

You want to have one and only one possible path between a table and another table. If there is role-playing then you create aliases.

Why does this dimension to dimension relationship exist?  You don't say, which precludes any analysis.
==> I don't know,  i started using that cognos package a couple of days only.


What are the properties of the query item which causes the problem? 
==> [zip Code]
Datatype   Character Length 16
Precision   10
Scale   0
Size   22


The error seems to be complaining about a ti
mestamp and, possibly, a filter.
==> Exactly, there is a FILTER using a field from [DIM TIME] and the filter is as follows: 
[QuerySubject].[Dim Time].[QuarterYear] = 'Q3-20'
but the error still displays even when getting rid of this filter IF we add the specified problematic CHAR field in the report indicated before.


Quote
Wehenever (sic) i (sic) include facts from the fact table and most of the fields from "dimension Ship To Client" in our report it works fine
 

Is there anything from the sales dimension?
==> Also true!
==> [OrderStatus] comes from the "Dim Sales"
Datatype   Character Length 16
Precision   10
Scale   0
Size   22


Quote
I mean that one field comes from the same dimension but it fails ?

Elaborate on this.
==> Meaning there are 3 other fields on the report query coming from the SAME query subject as the 4th field we are trying to add (that generates the error message). These are the types of those 3 other fields already in the query:


Datatype   Character Length 16
Precision   10
Scale   0
Size   22


Datatype   Character Length 16
Precision   50
Scale   0
Size   102


Datatype   Character Length 16
Precision   30
Scale   0
Size   62


Can you describe what the dimensions are?  The names almost (especially in the case of Sales) suggest that they are facts as well.
==> Sales Order is the exact name
==> Contains fields such as [SalesOrder#], [Invoice#], [OrderTotalAmnt], [OrderLineCount] but none of thes factual fields from this dimension are beeing used in the query not even in the filter. Only the

What release?
==> IBM Cognos Analytics 11.2.0

[bus_pass_man]

Did this thing ever work?  Did anyone verify that the queries were reasonable and that the results were correct? If it did work, what changes (environment etc.) preceded the failure?

You've got several issues.  The first being this error. 

The second is that just from what you have reported the model is a dog's breakfast. It's possible that the bits which you do not mention are involved somehow too.

==> Are we delaing (sic) with what we call a LOOP here in your opinion ?

Yes probably a loop. Not a good thing but that should not need to be restated.

Why does this dimension to dimension relationship exist?  You don't say, which precludes any analysis.
==> I don't know,  i (sic) started using that cognos package a couple of days only.

It would be helpful for you to know. 
Since you mention that Sales also has facts (OrderTotalAmnt), it is probable that it is a fact table as well.  You need to be able to identify the dimensions and facts. You don't mention the cardinalities of the relationships, which is probably something which you should.

What are the properties of the query item which causes the problem?
==> [zip Code]

Usage is an important property.

==> Exactly, there is a FILTER using a field from [DIM TIME] and the filter is as follows:
[QuerySubject].[Dim Time].[QuarterYear] = 'Q3-20'
but the error still displays even when getting rid of this filter IF we add the specified problematic CHAR field in the report indicated before.

The same error or another one?

What exactly do you mean by 'factual' when you talk about things like SalesOrder# and Invoice# ?

Those are numeric columns but are not facts; they are identifiers.  If their usage have been set to fact (aka measure) then the query engine will treat them differently than if they had the correct usage.  It is possible that whoever did the modelling of this did not check the usage after metadata import.  This is one of the proven practices.

[sanchoniathon]

Did this thing ever work?  Did anyone verify that the queries were reasonable and that the results were correct? If it did work, what changes (environment etc.) preceded the failure?
==> Good question. I've haven't receveid much information on what exactly beeing done by a certain it firm that works with us and is working on that particular cognos package other than they haven't published any new version of that strange behaviour cognos package. The one person that is the most aware of anything going on is too busy on other issues as i heard !! Si i guess i'm kind of stuck for the moment tryign to make all this work!


It would be helpful for you to know.
==> I agree 100

Since you mention that Sales also has facts (OrderTotalAmnt), it is probable that it is a fact table as well.  You need to be able to identify the dimensions and facts.
You don't mention the cardinalities of the relationships, which is probably something which you should.
==> These are the  relationships

dimension Ship To Client (1..1) <-> (1..n) FACT (1..n) <->  (1..1) dimension SalesOrder
                |                                                                                  |
                |                                                                                  |
                | (1..1)<---------------------------------------------->(1..n)|


Usage is an important property.
==> [ZipCode] has the usage = Attribute


The same error or another one?
==> Same one.
XQE-GEN-0005 Found an internal error:
com.cognos.xqe.runtree.relational.vectorization.expressions.generated.XVectorFilterIsNotDistinctFromTimestampColumnTimestampColumn
RSV-SRV-0042 Trace back:
RSReportService.cpp(769): XQEException: CCL_CAUGHT: RSReportService::processImpl()



What exactly do you mean by 'factual' when you talk about things like SalesOrder# and Invoice# ?

Those are numeric columns but are not facts; they are identifiers.  If their usage have been set to fact (aka measure) then the query engine will treat them differently than if they had the correct usage.  It is possible that whoever did the modelling of this did not check the usage after metadata import.  This is one of the proven practices.
==> SalesOrder#  = ATTRIBUTES
==> Invoice#       = Identifier

[bus_pass_man]

==> These are the  relationships

dimension Ship To Client (1..1) <-> (1..n) FACT (1..n) <->  (1..1) dimension SalesOrder
                |                                                                                  |
                |                                                                                  |
                | (1..1)<---------------------------------------------->(1..n)|

If you include something from ship to client then it is possible that the SQL will join to the fact table either directly from ship to client or indirectly via salesorder. 


Why was this modeled that way?  What it is saying is that whatever is in ship to client is part of a dimension with SalesOrder.  It is also saying that ship to client is also a dimension.  This is wrong as there is ambiguity.  Without actually seeing and going through the model, I can't comment with much certainty about it.  The information which you have disclosed doesn't seem to be all of the pieces of the puzzle.

Are there other tables in the model?

Is it an attempt to deal with multiple fact grains?

I've haven't receveid much information on what exactly beeing done by a certain it firm that works with us and is working on that particular cognos package other than they haven't published any new version of that strange behaviour cognos package.

I'm not quite sure what is being said here.  When you say 'they haven't published any new version of that ...package' do you mean that it used to work and now it doesn't?  If it used to work, what changes were made to the model, the environment etc.?


You will probably need to get the model straightened away, and that requires that you get a handle on what is being attempted with it.

Once you have a proper model and the problem still happens you will probably want to log a bug.

[dougp]

If "they" say that it worked and now it doesn't (without anything changing), "they" are lying.  Although, maybe they just assumed the outputs were correct and didn't know that they have been making decisions based on wrong information for the last few years.

A Star Schema (what you should be using) provides a single version of the truth.  If there is a relationship between dimensions, it's wrong.  The model will not consistently allow you to create reports that provide good answers.

Delete the relationship between the two dimensions and republish.  Then fix any reports that break.

You may want to hire a data modeler who is knowledgeable about star schemas.