How cognos decides which SCD version to use?

[Jiping]

For a slowly changing dimesion, say Customer, the ID is the business key, and Name SCD attribute.
ID   Key Name  Current
1001   1   William   0
1002   1   Bill   1
Question: when do analysis, how Cognos decides which Name to show? I don't see settings for this in Framework Manager Project

[MFGF]

For a slowly changing dimesion, say Customer, the ID is the business key, and Name SCD attribute.
ID   Key Name  Current
1001   1   William   0
1002   1   Bill   1
Question: when do analysis, how Cognos decides which Name to show? I don't see settings for this in Framework Manager Project

Hi,

I assume that ID is used to link the dimensional rows above to their relevant fact rows in the data warehouse? If you bring in Name and a measure in a report, you will see two rows for this person - one shown as William and one shown as Bill, each with the appropriate measure value based on the fact rows they link to. If you bring in the key (1) and a measure, you will see a single row for key value 1 with a summary of the measure across all dimensional rows that have this key.

In short, it is really down to what you want to see and therefore what you decide to include in your report.

MF.

[redstang]

Cognos doesn't decide, the relationship between the data in the fact table and the dimension table, as well as the user, decides what is shown (assuming you have the appropriate relationship defined in the FM model).

[bus_pass_man]

Look at the employee by manager dimension in the cognos sample model.

It has a very simple type II SCD case.   The lowest level employees change managers (none of the higher level employees change, which would be a bit more difficult, mostly on the ETL side).  There's two employees who get employees of their own in addition to changing managers.

Do a query on the emp_employee_dim table.  You will see multiple records for each of these cases. 

Open the dimension and select the employee level.  You will see a checkbox called 'unique level'.  It's checked on.   Select the other levels in the hierarchy.  You will see that the unique level checkbox is unchecked.  This tells the query engine to include the keys from the higher levels in order to uniquely identify members in that level. 

This is from the FM documentation. 

"Unique Level
A unique level indicates that the keys of the levels above are not necessary to identify the members in this level."

"If you want to indicate that the keys of the levels above the current level are not necessary to identify the members in this level, select the item and select the Unique Level check box. This indicates that key values belonging to the level should be considered unique regardless of context. In some circumstances, the Unique Level setting is used by IBM Cognos software to optimize SQL queries.

Note: The Unique Level check box does not affect the generation and handling of MUN identifiers for the members in this level. All MUNs are fully-qualified."

The help topics on these areas, such as creating reqular dimensions, levels for regular dimensions, and level key are not that bad.