Simple question: Struggling to model correctly data for aggregating

[littlevoices]

Hi all,

I've been trying to teach myself the basics of Cognos (I work for big blue and I like to know a little about some of our more interesting product purchases) and I am struggling with how to correctly model the following data in Framework Manager 'correctly', below is a simplified version of what I am trying to accomplish;

My single source table - each organisation provides a figure at a 15 minute interval.

DateTime	Source	Figure
12th Nov 2007, 10:15	org1	17
12th Nov 2007, 10:15	org2	17
12th Nov 2007, 10:30	org1	11
12th Nov 2007, 10:30	org2	24
12th Nov 2007, 10:45	org1	16
12th Nov 2007, 10:45	org2	20

My struggle is, I want the data to appear in the following format - in Query Analyser;

At a 15 minute level - essentially the two organisations figures 'summed'

DateTime	Figure
12th Nov 2007, 10:15	34
12th Nov 2007, 10:30	35
12th Nov 2007, 10:45	36

At an hourly (and then up to day/month/year) level - essentially the average of the two figures summed together

DateTime	Figure
12th Nov 2007, 10-11	35
12th Nov 2007, 11-12	....

I'd also like to be able to drill into the 15 minute level to get the individual organisation information.

My struggle is how to organise this with regards to Dimensions, Determinants and Aggregate rules.

Currently I have;

• Regular Dimension 1 - A Time Hierarchy, drilldowns work correctly and without issue here
• Regular Dimension 2 - Organisation Hierarchy - A top level 'all organisations', then a lower level 'organisation' that corresponds to org1/org2 etc
• Measure Dimension - Linking the two together, contains DateTime & The figure I want to sum/average. All values are in scope for the dimensions.
• Source Table - The original table (hidden), with a Time-Org determinant, with DateTime & Org as the keys, and the figure as the attribute. This is set to 'group by' and 'unique'. No other determinants.

- I've played around with a number of combinations of determinants at the source table level, without any joy
- Each organisation reports their figure once every 15 minutes and only reports the figures once.

I think I could probably change the database query quite simply to group by datetime... but I'd lose the drill down ability. The best I have been able to create myself is an average at all levels (so it effectively halves my data), or a sum of all levels (no use) and so on...

Since this is a learning experience, how should I be modelling this data, and if this is explained somewhere in one of the many manuals feel free to direct me there as well? I know I could 'fudge' it in several ways (doing a custom calculation etc), but I'm sure there must be a simple and correct way to do this.

Thanks for any help or advice,

lv

[blom0344]

I do not have a real solution for you, just a tip that averages are non-additive measures. You cannot 'design'  a roll-up mechanism that is able to compute averages the way totals are rolled up.
You will always have to perform the rollup of the individual components and perform the division at the end.

 

[littlevoices]

blom0344,

Thanks for your reply - does that mean that I would be creating a valid model by effectively doing the aggregation at a database level through a new view for example, rather than relying upon Cognos to do this addition? If so, I'm more than happy to since I'm quite comfortable within the DB land.

Thanks for your reply though - it's appreciated.

[blom0344]

To the best of my knowledge (not really much experience with DMR/cubes) drilling will work with measures/facts that are additive.
Percentages are non-additive (at least the outcome will not make any sense), so need to be precalculated for every level or perhaps defined as calculated objects from AS