If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

[FM] usage of identifiers / attributes in FM

Started by blom0344, 21 Jun 2007 05:54:24 AM

Previous topic - Next topic

blom0344

We've noticed that Cognos 8.2 seems to randomnly assign the usage of identifiers / attributes to objects.
This introduces different icons within the reporting tools and prompts questions from users.
When should an object be assigned as an identifier? Does it make any difference to change the usage to either all identifiers or all attributes?

sir_jeroen

It does not happen randomly:
If the item has an index, is a primary key or is a date then Cognos assumes it's an identifier.
If it's a number without index or pk then it's a Fact.
All others are assumed to be attributes.

This is a very short intro to Facts, identifiers and attributes. Search the Cognos KB / FM documentation for more information

blom0344

Thanks,this is valuable information (and perhaps should have been searched for in the documentation..)

However, digging a little deeper and then a couple of other issues come up:

1. The casual cognos user / report builder is going to wonder why some objects have different icons, for a fact this is rather obvious (most of the time by the screen tip/ description)

2. Does the assignment of identifier/attribute have any consequence for the Cognos methodology?
[does changing everything to identifiers or everything to attributes make a difference?]

My background is with rival Business Objects and there is the distinction between dimension and detail.
Assigning the correct type does come into play with linked dataproviders (synchronization over the common dimensions)

Our FM is build on database views that reference the DWH tables. That may contribute to the fact that Cognos seems to have trouble assigning the correct type to objects

sir_jeroen

#3
My comments:
#1: My believe is that a report Builder (using RS) must have AT LEAST any understanding of what a Fact / Attribute / Identifier is. This is the minimum requirement otherwise don't give him RS. Report Studio is a very powerfull studio in which the user can do almost anything. If the user doesn't have this understanding give the user Query Studio.

#2: The assignment of identifier/fact/attribute set a default setting for handling the processing of the Query Item. E.g. an integer as a Fact will by default be summarized,
An attribute will get a Min(Name) or Max(name) and an Identifier will be used to group on

In case of e.g. Employee_Number (identifier), Employee_Name (Attribute), Quantity_Sold (Fact) you wil see a statement something like:
SELECT
Employee_Number,
Max(Employee_Name) as Employee_Name,
SUM(Quantity_Sold) as Quantity_Sold
FROM table_X
GROUP BY
  Employee_Number

The Identifier is mostly used for grouping, the fact is summarized and the attributes will be Maximum/Minimum for the Identifier.

But you're not yet there.... You must also carefully set the correct determinants in the query subjects in order to create the correct grouping levels that are to be used for the identifiers/facts/attributes....

I hope this clarified somethings to you, but anyway I would advise you take some courses in Cognos 8 Metadata Modelling (1 & 2) because they will make a lot of things clearer.

But anyway you can always overrule the settings, but then take a good look at the results and check them if they are correct.


QuoteAssigning the correct type does come into play with linked dataproviders (synchronization over the common dimensions)
this is also solved by setting the correct determinants.

Quote
Our FM is build on database views that reference the DWH tables. That may contribute to the fact that Cognos seems to have trouble assigning the correct type to objects
It's most likely to be caused by the fact that there are no indexes or any other constraints...

blom0344

Thanks for your input.

Frankly, this is raises as many questions as are answered.

To begin with the behavior towards attributes. Cognos does not wrap a min or max around an attribute as far as we have seen in RS.
[perhaps if you intentionally set the aggregation behavior to this default it does]

We have checked with a test FM on a set of tables and a set of views (directly related to the tables). Cognos can indeed only detect dates as identifiers when views are used.
If setting the correct fields as identifiers is essential, we have a truckload of work ahead of us.

Can you perhaps point to any official Cognos publication on this subject?


sir_jeroen

#5
Have you also set the determinants? And have you tried a grouping on an identifier and added attribute?

E.g.
Employee#, Name and group on Employee#

I'm writing this all down based on my experience and I'm not aware of any publication about this subject... But take a look in Cognos KB...

Btw:
QuoteIf setting the correct fields as identifiers is essential, we have a truckload of work ahead of us.
Normally there are a lot of attributes and only a few identifiers in a table.... Or do your tables contain more than one dimension? And are all your identifiers identified by e.g. "xxx_PK" in the name? If so try the search option in FM and select all items that must be changed to Identifier and change their usage by dragging down the new value.

Btw 2:
As soon as my vacation is over (I'm all doing this by head) I will try and see if I can find more info about this.

blom0344

I see what you mean with adding a grouping (and having the actual query changed) I have yet to get accustomed to this type of behavior (always worked with BO full client where grouping is client-side)

We have indeed a great many tables of the variety factless facttables. These contain often many dimensions. Certainly not all dimensions are part of the primary key. Also most tables have 'natural' keys often composed of 2,3,4 fields in the primary key.

I certainly welcome any input when you are back from vacation    :D

sir_jeroen

So if i'm correct you have now seen the behaviour with max/min?

Are you currently working on a HRM database?