Level Properties

[bloggerman]

Have a question

If i have specified Source code for a level and if the category code would also be the same field, do i need to specify both or can i do with just specifiying one the cube picks up the other on its own. For e.g, if I have a country level, and i specify Country code as my source code then do i need to specify country code as the category code again.. What are the pros and cons of doing nad not doing it

[bloggerman]

Anyone?

[CognosPaul]

You need to specify the source value, category and label will default to that if they are left blank. I haven't done any tests to see if the cube builds any faster if they are left blank, but I prefer to populate category code even if they are the same value. The idea being that later developers would instantly know what's going on. Also, it helps ensures that the members are unique.

[tjohnson3050]

Your source will also be the category code.  You can define a separate category code if you want it to be different.

For example, you might have an integer key for a dimension table used as the source, but to support drill through to relational reporting, you may want the category code (business key) to be a natural key that will work when you drill to a relational report that has category code, but not the dimension key.

[bloggerman]

Whats the difference between Category Code and Source value? What role do each play in the cube build process?

I tried to research but could get much out

[bloggerman]

My question comes from the fact if I have specified both source value and category code then what does each contribute in Transformer

[CognosPaul]

The source will be what creates the member. The category code will allow you to control how the mun is created. You could, for instance, prevent Cognos from adding ~123 to the MUNs by creating a unique category code. Consider a hierarchy containing product and color. The bottom level, color, would not be unique. Cognos would add the tilde when the cube is processed:

Bikes
-Red
-Blue
-Green
Cars
-Red~123
-Blue~34
-Green~222
Airplanes
-Red~333
-Blue~321
-Green~98

But you could use a calculation in the category code to make them look like:

Bikes
-Bikes.Red
-Bikes.Blue
-Bikes.Green
Cars
-Cars.Red
-Cars.Blue
-Cars.Green
Airplanes
-Airplanes.Red
-Airplanes.Blue
-Airplanes.Green

[bloggerman]

If the lowest level is not unique in the example above as you said would the cube even be built. I had a feeling that an error would be thrown saying uniqueness violation.

Also, if i specify source value, label & category code then what does source value contribute. This because label would be the member and category code would be the unique identification for the category

[cognostechie]

I would suggest to read the tutorial or the user guide for a better understanding.

The source is used to accumulate the data (measures). The category code is used to create the categories within that level. Once the cube is created, go to the diagram mode and you will see the categories. The label is used to display in the reports.

If you have multiple data sources in Transformer (as the best practices suggest) then you will need to mark the lowest level as unique otherwise the data sources will not be able to map properly.

As Paul suggests, it's better to create keys which will make the category codes unique. In addition to that, I would suggest to use those keys not only to category codes but also to source property which will take care of the uniqueness violation in case the same lower level value belongs to multiple higher level categories.

[wyconian]

If the lowest level is not unique in the example above as you said would the cube even be built. I had a feeling that an error would be thrown saying uniqueness violation.

Also, if i specify source value, label & category code then what does source value contribute. This because label would be the member and category code would be the unique identification for the category

Transformer will add ~123  to the category code to make it unique.  The issue with that is the category code generates the MUN which is used in the report, if something in the cube changes or new records are added the ~123 may change and you will have to update the report to reflect the new MUN, I found that the hard way  :(

It would be better practice to not have any ~ in the category codes, you can do that by adding a calculation which makes it unique and using that as the category code.