Hi
I have followed Kimball methodology by including an unknown member in each dimension in the relational database. In the case of a time dimension I have an unknown member with key value -1. All time related fields, e.g. FullTime, Hour, Minute, etc are NULL in the database. I have a field called DayPart which has the value 'Unknown' where the key = -1.
With the UnknownMember property for the dimension set to None, all attributes that have a NULL value for the -1 member display a blank member in the browser - except for DayPart which has 'Unknown' as one of its values. If I set the UnknownMember property to Visible and NullProcessing for all attribures that have a NULL value to Unknown Member, the blank member is replaced by Unknown - which appears much better, but then the DayPart attribute displays two Unknown members.
If I change the UnknownMember dimension property to Hidden, all the attributes with NULL attributes for the -1 member don't show either blank or unknown members and the DayPart attribute is left with its original Unknown member. This appears nice but is it correct? BIDS gives a recommendation to only use the Visible or None options for the UnknownMember property and MSDN advises the Hidden should only ever be used with prototyping. I'm also guessing that when browsing the cube, any data assigned to the -1 member would not be accounted for unless the DayPart field is included as a row or column.
What is the correct approach to this?
I'm using SSAS 2008 R2.
Thanks
Ian
I have followed Kimball methodology by including an unknown member in each dimension in the relational database. In the case of a time dimension I have an unknown member with key value -1. All time related fields, e.g. FullTime, Hour, Minute, etc are NULL in the database. I have a field called DayPart which has the value 'Unknown' where the key = -1.
With the UnknownMember property for the dimension set to None, all attributes that have a NULL value for the -1 member display a blank member in the browser - except for DayPart which has 'Unknown' as one of its values. If I set the UnknownMember property to Visible and NullProcessing for all attribures that have a NULL value to Unknown Member, the blank member is replaced by Unknown - which appears much better, but then the DayPart attribute displays two Unknown members.
If I change the UnknownMember dimension property to Hidden, all the attributes with NULL attributes for the -1 member don't show either blank or unknown members and the DayPart attribute is left with its original Unknown member. This appears nice but is it correct? BIDS gives a recommendation to only use the Visible or None options for the UnknownMember property and MSDN advises the Hidden should only ever be used with prototyping. I'm also guessing that when browsing the cube, any data assigned to the -1 member would not be accounted for unless the DayPart field is included as a row or column.
What is the correct approach to this?
I'm using SSAS 2008 R2.
Thanks
Ian