Cube Processing--Grouping NULL or invalid values

[kri]

Say I have a fact table with two columns "MarketId" and "Sales" and a separate dimension table for "MarketId".

Valid values for "MarketId" dimension are 1,2 and -255(unknown market).

Fact table contains five rows
1 1000
2 2000
0 1000
NULL 1000
1 1000.

Is there a way during cube processing, the values 0 and NULL in fact table can be grouped under -255 (unknown market)?

Thanks
kri

 

[johnherman]

You can create a calculated column such that when fact value is 0 or null, move a 'Y' to UnknownMarket otherwise move a 'N' to Unknown Market. Then make UnknownMarket a higher level in the MarketID hierarchy and dimension.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[kri]

johnherman ,
Thanks for your time in suggesting a solution.
I hope you meant adding a column in the fact table to reflect the unknownmarket. I was looking for something from the cube processing side to take care of this without adding any additional column on the fact table.(Our DB is in terabytes and it takes long amount of time to run any update query)...
We, however, implemented your logic by creating a view and using that as a fact table. The view will return a -255 if the marketid in table is 0 or null..

Thanks

 

[johnherman]

You can create a calculated column in the fact table which is populated during cube creation. You do not need to add a column to the base table (Microsoft Analysis Services). For other OLAP cubes, I don't know.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright