Framework Manager - treating some fields as sums

[ry1633]

Hi all,

It looks like Framework Mgr is treating some of the fields in my model as sums.    For instance I have a view (from Oracle db) that has a field like this:

ID with a data type of NUMBER(19).

But when I put that field into Query or Report Studio, instead of giving me a a column listing of all the ID numbers, it gives me 1 entry with a number of something like '1,612,127' which is the sum total of all the IDs, and not the listing of each one.   I didn't do anything special to that field in FM so I'm not sure what's up.  I do also notice that field and other numeric fields like it from my model, have the yellow "L" -shaped icon in Framework Mgr.

[bdbits]

Check the properties for those columns. You likely have them set as facts. Set them as identifiers or attributes. You also have control over if/how numbers are aggregated in the properties.

[ry1633]

Refresh me again, what's the difference between Fact, Identifier and Attribute.  And which one would be best for this instance?

I'm going to try that field as usage "Attribute" and Regular Aggregate "Count"

[MFGF]

Refresh me again, what's the difference between Fact, Identifier and Attribute.  And which one would be best for this instance?

I'm going to try that field as usage "Attribute" and Regular Aggregate "Count"

Usages are as follows:

- Fact. This means the item will be treated as a measure value, and will be aggregated automatically when used in a report. The aggregation type will default to whatever the RegularAggregate property is defined as (default is Sum).

- Identifier. This means the item is a (part of a) key, and will be grouped automatically when used in a report. The aggregation type is mostly irrelevant as you wouldn't aggregate key values.

- Attribute. This means the item is descriptive, and will be selected distinct when used in a report. Again, the aggregation type is mostly irrelevant as you wouldn't aggregate descriptive items.

These really are basic concepts fundamental to any modelling you do in FM. If you're not already in a position where you know what they mean and what they do, I'd earnestly recommend you take a training class in Framework Manager, where all the basic concepts plus the best practice guidelines are covered in detail. Although it's an initial expense, the time and effort you will save by gaining the knowledge it imparts will pay for the course many, many times over.

Cheers!

MF.

[Michael75]

What MF says !

To get you by in the short term, your ID field should most likely be set as an Identifier. When you make this change, the following properties are automatically set:

Regular Aggregate : Count
Semi-Aggregate : Unsupported

and you should leave these values.

[ry1633]

I've taken one class (the ExitCertified) on on Framework Manager but it was 900 pages of content that I had to finish in 5 days.   And in my middle age-ness I have a hard time remembering that much content in that short of time.  I'm not a 20 yr old college student any more ha ha!  :)    But I did save all the docs and manuals for the class so I will go back through.

[bdbits]

I can relate to middle age-ness.  ;D

Maybe next relationships, column properties are one thing you do want to pay a lot of attention to when modeling. Particularly if your users are self-service writing their own queries and reports, it will make a tremendous difference in how well they think Cognos works. Nobody likes misleading aggregates on their reports. And proper assignment helps Cognos get queries right.

Keep at it. Once you get models refined so things are defined correctly, Cognos can be pretty good at doing a lot of work for you.

[ry1633]

a related question.... why is it that some column names get automatically set to Fact/Sum when a view or table or data source gets brought into Framework Manager?

I wouldn't have known to check this when I first brought in the views to my model.

[bdbits]

I think it mostly just looks at the datatype and says "oh, number of some kind, must be a fact", then assumes sum would be the most common default aggregation.

For what it's worth, no matter what the tool and not specific to Cognos, if it is making some assumptions I nearly always double-check that it made the right ones. I've been bitten too many times by assumptions.

[MFGF]

a related question.... why is it that some column names get automatically set to Fact/Sum when a view or table or data source gets brought into Framework Manager?

I wouldn't have known to check this when I first brought in the views to my model.

The crude rules it uses to determine item usage automatically are:

1. Is this item part of a key or an index, or is it a date? If so, let's make it an Identifier.
2. If not 1 above, is the data type of the item numeric? If so, let's make it a Fact.
3. If not 1 and 2 above, let's make it an Attribute.

One of the fundamental rules for success when modelling in Framework Manager is to check the usage of each item after a metadata import. Because the automatic determination is so basic, it is virtually guaranteed you will need to change some of them. You also need to check the RegularAggregate for each Fact item too - as Bob says, the default is always Sum, which is not always appropriate for all measures.

Cheers!

MF.

[ry1633]

For sure, I didn't know that and now I know better :)   I will watch it when I'm bring in views from now on.