"group by Key" for aggregate columns for aggregate transformations - Urgent

[mohandalai@gmail.com]

Hi ,
I am pretty new to cognos data manager, I am looking at some of  the jobs already developed in cognos data manager.  I am looking at one of the dimension build job which has aggregation SUM applied on one of the columns. I am pretty much an ETL guy working in datastage . One thing which I haven't  understood in the cognos data manager dimension build job is the 'group by' column for aggregation SUM.
how do I know, which is my 'group by' for aggregation function used in the  'transformation map'??  Does the cognos data manager assumes the primary key on source extract table as group by key???
Please let me know the answer

Kind Regards,
Mohan

[MFGF]

Hi ,
I am pretty new to cognos data manager, I am looking at some of  the jobs already developed in cognos data manager.  I am looking at one of the dimension build job which has aggregation SUM applied on one of the columns. I am pretty much an ETL guy working in datastage . One thing which I haven't  understood in the cognos data manager dimension build job is the 'group by' column for aggregation SUM.
how do I know, which is my 'group by' for aggregation function used in the  'transformation map'??  Does the cognos data manager assumes the primary key on source extract table as group by key???
Please let me know the answer

Kind Regards,
Mohan

Hi Mohan,

It's not really clear what you are asking here, sorry. A dimension build doesn't read data directly from a table - it uses a defined hierarchy as its souce. Are you referring to data being read into a hierarchy? Or are you referring to data being read into a Fact Build? Or something else?

I'm guessing that since you are referring to "Aggregation Type" you are looking at a Fact Build, and specifically at a measure in the Transformation Model of the Fact Build? There are potentially three different types of source transformation model element in a fact build:
1. Dimensional elements have a three-pointed arrow icon
2. Measures have a ruler icon
3. Attributes have an icon with a blue stripe at the top.
There are also Transformation Model Derivations and Derived Dimensions too, but these are calculated within the build so let's ignore them here.

Dimensional elements represent the foreign keys you will find in a fact table - they can optionally be validated in the build against either a hierarchy (multiple levels of diemnsional data) or a lookup (a single level of dimensional data). You can see if they are being validated by going into the properties of each dimensional element and looking at the Reference tab.

If an element is being validated against a hierarchy, the build can optionally also roll the measures up to higher levels of the hierarchy and deliver aggregates for these levels. For this to happen, two things need to be defined on the Reference tab of the dimensional element:
1. The Aggregate checkbox needs to be checked
2. The Output boxes for the levels to be delivered need to be checked. If only the lowest level output box is checked, no aggregation happens. If the output boxes for higher levels are checked, output happens at these levels.

Assuming this has been defined, the last decision is how each measure gets aggregated when it is rolled up the levels. This is defined in the properties of each measure, on the Aggregation tab. The default is SUM.

If the Aggregate option is not defined on any dimensional element or if output levels are not enabled other than the lowest hierarchical level, aggregation does not occur. In this instance it makes no difference what aggregation behaviour is defined on each measure because it doesn't happen.

I'm assuming this is what you are asking about? Apologies if I've misunderstood. :)

Cheers!

MF.