COGNOiSe.com - The IBM Cognos Community

Hi Experts ,

Can anyone tell me what happens in the cube when the join on the customer and fact has unmatched records .


For example customer dim has 1,2,3,4

And fact has 1,4 and 6 . Whats happens to customer 6 sales , is it loaded in dynamic cubes or just loaded to overall summary without showing customer 6 or not loaded at all .


I dont want to load unmatched customers sales in dynmaic cubes , how can i do that .

In transformer it creates an element 6 outside the customer hierarchy and then loads the sales .Wanted to know how it works here in dynamic cubes .

QuoteWanted to know how it works here in dynamic cubes .

It operates in the same way as in DMR or, indeed, with plain old relational queries.   If there are keys on one end of a relationship which do not have matches to the result of whatever the join expression is defined as then the records for those keys will not be

For example, if the expression which defines the join between two tables is some like T1.key = t2.key then if there is a key value in either t1 or t2 which does not exist in the other table then any record which uses that key will not be returned.



dim table

1
2
4

Fact table.

1
4
6


The members 1, 2, and 4 will be created.   

If the dimension is used in a query alone (bear with me; I'm trying to be clear, not necessarily realistic) or with some other fact table where all of those keys exist then you will have all 3 members returned.

1
2
4



If the dimension is used in a query with the fact table then only the values for 1 and 4 will be returned.


dim   Fact
1      1
4      4

I have not tried it but I don't think you will be able to get the results you want using an operator other than the default = operator as your dimension table simply does not have key values which exist in the fact table.

It is a good practice to have your data warehouse set up so that you don't end up with this sort of situation.

It is possible that there could be some expression which could create members which are appropriate and which have analogue keys in your fact table but you'd need to be quite careful and I don't want to hold out much hope.