NULLS IN FACT TABLE 1

[DWArchitect]

Hi,
1. I have a fact table where some FK's to the dimension tables are null. I've added a NULL value to the dimension table, but still my counts are not adding these fact records.

How can I include the nulls in my cube totals?

2. Can I have two columns in the fact table point to the same dimension. ie, I have from city and to city in the fact table can i join both fields to the same dimension table without getting cartesian product results, ie double counting?

3. When processing my cube, suddenly it starts processing partition segments 10 or 12 of them, when it never did this before. It happened after adding new dimensions. These are not partitioned cubes.

Thanks,
DWArchitect

 

[MDXer]

1 ) You can't join on a NULL. The best way to handle these is to force a dimension member with a key value of something like -1 with the member name 'Unknown' key all unmatched records to this dimension member.

2) Sort of, you can have multiple dimensions based off the same fact table but to actually use the same cube dimension twice will end up causing problems. WHat you need is to create 2 Dimensions such as From City and To City. alias the dimension table when you create these dimensions so the joins are Clear to AS.

3) A segment within a cube can only contain a certain amount of data in MB size. So as your cube grows so does the space required to store it. Segments are nothing to worry about as I have created cubes with in excess of 1k segments.

One thinking to watch for though is the aggregation types within you cube. If you use a distinct count measure it really needs to be implemented differently and is poorly documented.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck