Modeling the TIME dimension

[Feanor]

I've encounterd this issue twice now and I hope some of you can help me out since I don't exactly understand what's happening.

Situation:
There are two reports based on two different (dimensional) models, one DMR and one PowerCube. With both reports I'm experiencing the same issue. The user want's to be able to select one or multiple years, quarters and months to filter the report on the appropriate data.

I've included a prompt page with several prompt filters that are based on the TIME dimension. I use listboxes (with the property Multi-Select=Yes) to meet the requirements. When I prompt on the highest level (YEAR), all goes well. But when I prompt on a lower level (ie MONTH or DAY), then the listbox is filled with multiple redundant values (see the example below).

What is wrong is that I get as much as redundant values as there is data. So if there is data for five years then there are five instances of JANUARY visible in the listbox. Also for MONTH and DAY each instance is connected to each year, so if the last year is 2007 than I must select the last JANUARY in the listbox to get the appropriate data.

Obviously I just want the user to see one instance of each unique item.

Help?
Can someone explain me what is happening here and why. And of course what can be done to resolve this??
Thanks in advance.  8)

Listbox example:

JANUARY
FEBRUARY
....
NOVEMBER
DECEMBER
JANUARY
FEBRUARY
...
NOVEMBER
DECEMBER
JANUARY
FEBRUARY
...
NOVEMBER
DECEMBER

[rockytopmark]

Your Dimension is modeled just fine.  Change how you are presenting the prompt to the user.

Use a Dimensional Prompt object... the Tree Prompt, instead.

This will render your Time Dimension in its true hierarchical format, and not require any patchwork filtering to handle the multiple levels, that surely you have with the current setup.

[Feanor]

Thanks for replying.

However I already tried this and it seems this does not meet the requirements of selecting multiple years, months and days at the same time. As far as I'm aware of you can select only one item of each level with the tree prompt.

[rockytopmark]

If your Filter is set ot use the IN clause (Rather then "=" ) you can multi-select.  You weren't able to multi-select from different levels in older Cognos 8 versions, but with the more recent versions, you can.... I just tested it successfullly on C8 MR2.

[Feanor]

The problem is not that I can not multi select. I can multi select because (indeed) I use the IN statement.

The issue at hand is that the list boxes for all of the levels lower than the top level, in this case YEAR are loaded with double values. Let me try to explain this again focussing only on the levels YEAR and MONTH. If I add a listbox for YEAR on the prompt page the list box is filled with only one label for each year in the model like shown below, this is correct:

2005
2006
2007

If I add a listbox based on the month level, this listbox is filled with a month label for each available year. So if there is data for three consecutive years in the model, than the MONTH listbox looks like this:

JANUARY
FEBRUARY
MARCH
APRIL
MAY
JUNE
JULY
AUGUST
SEPTEMBER
OCTOBER
NOVEMBER
DECEMBER
JANUARY
FEBRUARY
MARCH
APRIL
MAY
JUNE
JULY
AUGUST
SEPTEMBER
OCTOBER
NOVEMBER
DECEMBER
JANUARY
FEBRUARY
MARCH
APRIL
MAY
JUNE
JULY
AUGUST
SEPTEMBER
OCTOBER
NOVEMBER
DECEMBER

I just want to create a listbox which contains only one label per month.

[rockytopmark]

OK... I assume you are dead set against using a Tree Prompt?

In your Listbox, if you see January, do you know what Year that January belongs to?  If you were to have only one January in your list, that represented 5 year's worth of Januarys, how would you handle that?

I think you shold just create Propmt Controls with Static Values for the Year, the Month and so forth.  Then create some sort of way to bring all those parameters together to use in conjunction with your report query.

Not as usable or elequent as a Dimensional Prompt like the Tree Prompt, but I suppose it will work.  Its my wish that you consider going this way instead... your users will appreciate it.

Checking out.

[shri]

go to properties of the query related to month list box, and change property of Auto group & summarize to yes.

[Feanor]

First of all thank you guys for your help in thinking with me in this issue.  :)

@rockytopmark
I understand your thinking but the problem with the tree prompt is that it limits the flexibility of searching on a specific time period. The business wants this functionality.

I want to be able to select one or multiple years in combination with one or multiple months without specifying which JANUARY belongs to which YEAR. There should be only one instance of JANUARY in the month list.

Using static values is an option I considered but I do not prefer these because of possible future changes in the data. The values in the TIME dimension are now stored in English format and it is possible that it will change to the native language in the future.

@shri
Good suggestion but this property is already set to YES correctly.

Moreover, if I navigate to the members of MONTH in the INSERTABLE OBJECTS PANE I also see multiple months for each year. I think it's not normal behaviour of the TIME dimension. That is why I thought it was a modeling issue but I can't seem to find out how to resolve this. Maybe some of you can confirm to me if this is normal behaviour or not?

[shri]

It seems to me is a modeling issue, try modifying model by creating filter for month (group by)

[rockytopmark]

Not sure why I came back to this thread, but a member is not the same as a value.  Your confusion as to why you see multiple Members for the same month (different years) is the issue.  A member of a hierarchy can have similar attributes to other members, but ultimately are not the same.

A member January of a time dimension has a parent.  It might be 2006, 2007 or any other year.  A member can have only 1 parent.  Since every year has a January, there will inevitable be a January for every Year that has been or will be.  What makes a January unique is its parent... not itself.

The VALUE January is indeed unique to itself.  When connected to other Values, it can become part of a unique key or a concatenated key.  But in the scope of Month, January is unique.

Your issue is not an issue in scope about Month, but about Time.  And in the scope of Time, January doesn't tell you more than it was in the Month a January, which is only 1/12th of the history of Time.

You mentioned earlier that you didn't like the idea of Static Values... I thought that your response was odd, as you indicated that your time dimension may change...  new months???

How about a Datasource Query Subject in your model that just returns the individual month values... Select Distinct Month from Time_Table    ...?

[Feanor]

Good explanation (applaud+  ;)).

Your confirming more or less what I thought that was happening. I just thought this maybe would be a familiar issue and there was a way to alter this behaviour in the FrameWork model in stead of the other solutions we discussed. Anyway, I'm glad to be more certain now :D.

The static values option is a good alternative. However, in this particular database the names of the months are stored in English whereas the rest of the data (including other levels in the time dimension, like quarter) are stored in our native language. That is why I preferably would not take the chance of using static values, in case someone would alter the month names back to the native language. Maybe it's not a big chance, but I always try to think about the long term when I'm developing something  ;).

Creating a new query subject based on the month and using this for the list is a good idea. I already used this method to solve the issue for the DMR based report. It works like a charm. Maybe I can use it as well for the PowerCube report.

I think it's clear for me now what's happening and what the options are. Thanks to you, both!