Multi-column Indexes and Usage

[Deku]

Hi all,

I had a question on query item usage in FM models that I thought the group might be able to help me understand better.  Based on the materials I've read and the training I've attended, I believe that in order for Cognos to correctly understand how to write SQL for queries involving numeric fields that are not truly to be used as measures, the fields need to have their usage set to either Identifier or Attribute.

My understanding is that if the query item is for a field that is a key, indexed field, or date, it needs to be an identifier.  For all other instances, it should be an attribute.  If at a later point, the field in the source is indexed it could then be made an identifier.

The issue I am having trouble wrapping my brain around is what is appropriate when a query item is referencing a field that is not individually indexed in the source but it is part of a multi-column index. 

So for example, if you had a query subject with the query items ID and RECEIPT NO.  If the source view/table behind the query subject had an multi-column index of ID and RECEIPT NO, but did not have individual indexes for the column ID and RECIEPT NO, would you still want to set usage for the query items ID and RECIEPT NO to identifier? Should these remain attributes unless the individual column has been indexed?  In this example, I'm making the assumption that ID and RECEIPT NO are also not keys.

Thanks for any advice the community can offer and apologies if this has already been answered before.  I did a cursory search on the forum, but I couldn't find this exact scenario. 

[MFGF]

Hi all,

I had a question on query item usage in FM models that I thought the group might be able to help me understand better.  Based on the materials I've read and the training I've attended, I believe that in order for Cognos to correctly understand how to write SQL for queries involving numeric fields that are not truly to be used as measures, the fields need to have their usage set to either Identifier or Attribute.

My understanding is that if the query item is for a field that is a key, indexed field, or date, it needs to be an identifier.  For all other instances, it should be an attribute.  If at a later point, the field in the source is indexed it could then be made an identifier.

The issue I am having trouble wrapping my brain around is what is appropriate when a query item is referencing a field that is not individually indexed in the source but it is part of a multi-column index. 

So for example, if you had a query subject with the query items ID and RECEIPT NO.  If the source view/table behind the query subject had an multi-column index of ID and RECEIPT NO, but did not have individual indexes for the column ID and RECIEPT NO, would you still want to set usage for the query items ID and RECIEPT NO to identifier? Should these remain attributes unless the individual column has been indexed?  In this example, I'm making the assumption that ID and RECEIPT NO are also not keys.

Thanks for any advice the community can offer and apologies if this has already been answered before.  I did a cursory search on the forum, but I couldn't find this exact scenario.

Hi,

Firstly I'll caveat things by saying I'm not a database expert :)

My feeling here is that for a multi-column index, the database can use it if you are using the first item, or the first and second item combined, but not the second item alone?

If so, in your example, I would set ID as an Identifier and RECEIPT NO as an attribute.

Having said all that, any decent database will have a query optimizer, and its job is to get queries to perform optimally. It will decide when an index should or shouldn't be used regardless of how you define the items in your model as Identifier or Attribute...

Cheers!

MF.

[Deku]

Thanks much MF! I appreciate the help.  :)

[Invisi]

My suggestion is: ignore things like database indexing when determining the role of your individual data items. Look at them from a business perspective. As MFGF says, the database will optimise the query it gets. Making that go well is a DBA issue.

[Deku]

Thanks Invisi. 

As a follow up related question, if you have a query subject that will provide one distinct record for a set combination of fields, would you concur with setting those fields to identifiers regardless of database indexes and field types?

For example, let's say you have a query subject containing all student GPAs (I'm from the higher ed world so this example works best for me).  If that query subject will always produce a distinct record for a student when looking at the combination of fields ID number (numeric), major (string), and student level (string), would you concur with setting all three fields to identifier? A student will have a unique ID number, but some students may have the same majors and student levels.

In the above example, would setting all three fields to identifier make more sense than setting the numeric field to identifier and leaving the string fields as attribute?

Hopefully, I'm grasping this well enough.  It's always different in application than compared to training.

Regards,
Deku

[Invisi]

For me I would only assign the Student ID the role of identifier and the other two as attributes. Consider that from a business perspective, uniqueness is not an issue as you describe it. That's a database unique key issue, being technical in nature. I would even wonder why in any given point in time you need the major and student level of a student to identify him or her uniquely. With my limited knowledge of the field, I think a student has only one major and student level at once.

[Deku]

Hi Invisi,

Just wanted to follow up and say thank you very much.  I'm not sure what is appropriate protocol and hate to bump a topic to people with no further update, but didn't want to look like I didn't bother to read your response either.

Regards,
Deku

[MFGF]

Hi Invisi,

Just wanted to follow up and say thank you very much.  I'm not sure what is appropriate protocol and hate to bump a topic to people with no further update, but didn't want to look like I didn't bother to read your response either.

Regards,
Deku

A post to say thanks is always happily received :)

You can also use the Applaud link below a poster's name to send them an applause - their Forum Citizenship score shows the number of positive/negative feedbacks they have received :)

Cheers!

MF.

[Invisi]

A post to say thanks is always happily received :)

You can also use the Applaud link below a poster's name to send them an applause - their Forum Citizenship score shows the number of positive/negative feedbacks they have received :)

Cheers!

MF.

I agree with MFGF in this case.  ;D