Missing Dimension Keys in DMR

[kswayze]

Does DMR have a way of handling fact rows with a null dimension key.  I have set the joins to "outer" in the Framework, but when creating a crosstab report , I lose rows (or columns) where the fact row has a NULL foreign key for that dimension.  I have tried using a coalesce, but in doing so, I lose drill capability.  Short of adding an "Unknown" row to each dimension and defaulting the fact table's foreign key to "0", is there any other option?

[blom0344]

I think so.. When you define a database view on the fact that resolves the nullable keys (substituting for instance zero) and use the view in the FM model, then you can make use of outer join without losing data or drill-down capability.

When you have just one level (and nothing to drill down to) then cognos leaves the member blank in the crosstab. When a lower level exists the value 'null' is used. It still allows me to then drill down from the null value with no problem..

[kswayze]

I can get it to work in a list, but not a crosstab.  In the example below, I want to see the blank row with a count of 5814 in the crosstab.

Crosstab

Org Unit Level 2                             Count
North American Development            398
North American Marketing and Sales  254
Total(Org Unit Level 2)                    652

List

Org Unit Level 2                             Count
North American Development            398
North American Marketing and Sales   254
                                                   5,814
Summary                                       6,466

[blom0344]

My test case involved Analysis Studio AS (we have no use for DMR based Report Studio reports) and AS shows the empty values as long as the key value exists. If the key is indeed missing altogether I lose the member value in AS as well