Uniqueness of a level

[bloggerman]

I  have heard that we should have the lowest level in the dimension set to unique

1)    My question is what would happen if i dont set it to unique
2)    And, why set only the lowest level as unique
3) How does setting lowest level as unique help the cube

[wyconian]

HI

The lowest level in the dimension forms the relationship to the measures.  Each measure should only be associated with one value in the lowest level of the dimension which is why the lowest level has to be designated unique.

If you don't specify that the lowest level is unique you won't be able to directly associate a measure to the dimension (check the scope) and the cube will throw an error.

Sorry I know that's not a very technical answer I'm sure someone will add a link to the relevant pages in the user manual :-)

[cognostechie]

HI

The lowest level in the dimension forms the relationship to the measures.  Each measure should only be associated with one value in the lowest level of the dimension which is why the lowest level has to be designated unique.

If you don't specify that the lowest level is unique you won't be able to directly associate a measure to the dimension (check the scope) and the cube will throw an error.Sorry I know that's not a very technical answer I'm sure someone will add a link to the relevant pages in the user manual :-)

Only when you have multiple data sources in Transformer, not with a single data source. Lot of people do it that way even if it is not a suggested best practise.

[dssd]

i read that if we have only one datasource then we dont need to set the lowest level as unique. Do we always need to set the lowest level as unique if we have multiple data sources in a cube? I was able to build a cube without an error without setting the proprty even though i had two data sources one for date and other for country. The thing just baffles me

[norkos]

I can just partially agree with cognostechie, or at least I have to add a remark to his post.
If the lowest levels in the cube are not unique than you can face serious problems even the cube will work properly.

If there are reports/analysis contain MUNs in their specifications than those can be corrupted after cube generation, in the case you didn't set category code for the levels or the data items used as category codes are not unique.
I have already faced with this problem so it can be really strange especially when there are a lot of reports based on this cube...

So even this problem nor related directly to the uniqueness property you always have to keep this in your mind when you design a new cube.

You can find a detailed description of the problem on the BI Professional blog: http://www.biprofessional.com/2011/02/cognos-transformer-and-category-uniqueness-part-1/

[cognostechie]

Did you try to understand what the poster said and what I replied to? Uniqueness of a level is different than uniqueness of category codes. The poster was referring to marking the lowest level unique.

True that the reports will stop working if the category codes change and Transformer assigns tilda in the category codes but that has nothing to do with marking the lowest level as unique. That happens because the property Category Code was left blank so Transformer assings it randomly by itself!  Those are two different issues !

Even if you mark the lowest level as unique, the problem of reports stopping to work will still happen if you leave the Category Code property as blank.

Lastly, I had indicated that it is not the best practise. The best practise is to have different data sources for different dimensions and mark the lowest level as unique without which the fact/measures will not get associated with all/required dimensions. 

That being said, if you have just one data source (like the poster said) then not marking the lowest level as unique will still work and that has nothing to do with the category codes getting changed later on. That would happen because the cetagory code property was left blank and not because the level was not maked as unique.

I saw the link you posted and whoever wrote that does not have sufficient knowledge of the subject. Try it out yourself and see what happens. Mark the lowest level as unique and leave the category code property blank. Your reports will still stop working at some point because Transformer will still change the category codes.

Just because something is working right now does not mean it will work in the future.