Multi-fact query

[chmcmm]

I have a four fact query. The stitching is correct except for one attribute. They put a an attribute directly on the facts. We'll say "Invoice Number". Cognos won't use this in the where clauses. Has anyone encountered this? Any workaround? I'm assuming it's because Cognos doesn't have any relationship knowledge between the facts. It's looking for a 1:N dimension instead. My first thought is to create a dummy dimension with just a key and invoice number. Thanks.

[bus_pass_man]

Hi you didn't set up the private message functionality


Where is this attribute?  Who's 'they'?  What does " put a an attribute directly on the facts. " mean? That you have an attribute in a fact table (i.e. it's a degenerate dimension )? https://en.wikipedia.org/wiki/Degenerate_dimension)

I'm assuming it's because Cognos doesn't have any relationship knowledge between the facts.

I'm not sure what you mean by this.  I think this is possibly related to your other statements.   

Is invoice conformed to more than one fact?  Does it exist in more than one of the facts? 

Can you describe the layout of the model please.  It will help me mentally trace through your situation.

A diagram would be very helpful.

[chmcmm]

Yes. Degenerate dimension. The data modeler put "invoice number" directly on the facts. It exists on all facts.

The model follows Kimball. Facts with multiple dimensions. The dimensions are conformed.

The report has invoice number. It's in the first query with the first fact but not in any of the other queries so it doesn't try to coalesce the invoice numbers or join in the outer joins.

I tried to create a fake degenerate dimension using the values and key from the fact. I created a 1:n relationship with the facts but behavior is the same.