Does Cognos Framework Manager has the "Last" function like Dynamic Cubes in Cognos?

Question

I was wondering if Cognos Framework Manager has the built-in function "Last" like in Dynamic Cubes?

Or does someone know how to model following case:

We have two dimensions - a time dimension with year, half-year, quarter and month and another dimension that categorises people depending how long they are attending a project (1-30 days, 31-60 d, 60-180, 180 -365, 1-2 years, +2 years). However the choice of the time dimension level (year, half-year etc.) influences the categorization of the other dimension).

An example:

A person attends a project starting from 15.11.2018 and ends 30.06.2020. The cognos user uses for the time dimension the year level thus 2018, 2019 & 2018 will be displayed.

For 2018 the person will be in the category 31-60 days, since 46 days have passed until 31.12.2018. For 2019 the person will be listed in category 1-2 years as 46 + 365 days will have been passed since 31.12.2019. For 2020 the person will also be in that category as 46 + 365 + 180 day have gone by.

The categories will change if the user selects another time dimension level e.g. half-years:

• 2nd HY 2018: 31-60 (46 days passed)
• 1st HY 2019: 180-365 days (46 + 180 --> End of HY2019)
• 2nd HY 2019: 1-2 years (46 + 180 + 180)
• 1st HY 2020: 1-2 years (46 + 180 + 180 + 180)

Does someone know how to model dynamic dimension categories based on selection of another dimension (here time dimension)?

The fact table contains monthly data and for the mentioned peroson above there will be 20 seperate records (for each month between november 2018 and june 2020).

Answer 1

For any period, a person may or may not be working on a project.

Without knowing exactly what your data and metadata is it would be somewhat difficult to prescribe an exact solution but the approach would probably be somewhat similar to a degenerate dimension scenario.

You would want to model the project dimension as a fact as well as a dimension. You would have relationships between it and time and whatever other dimensions you need.

Depending on the data and the metadata you might need to do some gymnastics to get there.

If the data was in a form similar to this it would be not too difficult. This is an example to get you an idea about some ways of approaching the problem.

Date_Key   Person_Key   Project_Key   commitment_status, which would be the measure.
20200101    1                 1                      1
20200101    1                 2                      0
20200101    1                 3                      0
20200102    1                 1                      1
20200102    1                 2                      0
20200102    1                 3                      0
20200103    1                 1                      0
20200103    1                 2                      1
20200103    1                 3                      0

In the above, person 1 was working on project 1 for 2 days and then put onto project 2 for a day. By aggregating the commitment status, which is done by setting the aggregate rule property, you would be able to determine the number of days a person has been working on a project no matter what time period you have set in your query.