Alternate Drill Path in Time Dimension

[Lynn]

I started with a time dimension of Year > Quarter > Month > Week > Day. The weeks are set to split into the appropriate month. I also have a number of relative time categories defined (latest date, rolling n period, etc.)

Everything was working fine and life was good.

Then the users got their hands on the cube for UAT.

The want to report on weeks that are complete weeks and do not split across the months BUT I still need the figures to roll into the correct calendar month. So I added an alternate drill path for Year > Week > Day and set the weeks not to split.

I have only one data source that sets the current period. I created the alternate path by dragging in the same date I used originally (the only date in my whole model).

Now when I build my cube I can see data through March 15th (the latest date in the database) when I use the primary drill path. This is correct. But my alternate drill only shows data through February 4th! All my relative time categories only go through February 4th as well.

I've searched the forum and hunted through my model but I can't find what would cause this. Any ideas?

[Grim]

What version of c10?
Have you tried 10.1.1?

Sounds like a bug.

[Lynn]

We are on 10.1.1

Bummer. I'll see if I can troubleshoot further. Thanks.

[Lynn]

Grim (honorary master of IBM links),
The response you posted in this THIS thread seems to explain my dilemma.

The articles state:
"For both Lunar and Calendar time dimensions, the rules governing weeks must coincide in all drill downs."

My requirement is specifically to handle weeks differently, so defining the rules for weeks the same isn't going to do much for me.

Maybe I should try to build two different time dimensions? I'm worried what would happen if users attempt to mix and match from both, but I can't think of another way to accommodate the requirement.

[Grim]

Cool. Glad to help.

IMHO, 2 time dimensions may also confuse the consumers. (Who am I joking, it will most definately confuse them. ::) )

[Arsenal]

You could also try setting the date ranges for each month manually via category codes (i don't exactly remember all the steps) but this would add maintainence at the beginning of every new year.

[RobsWalker68]

Hey Lynn,

I've had a quick go of replicating the issue using the Cognos samples using an 8.4 installation and everything appears to work as expected.

I believe we followed the same steps.

1. Using a date(Day) column only, created a time dimension with the levels - Calendar Year, Calendar Quarter, Calendar Month, Week & Date

2. Using Date as the convergence level created a alternative drill down with two levels Calendar Year, Week.  Both based on my Date column.

3. Used a seperate query to set the current period.

4. The primary drill category is set to always split, which it does.  The alternate drill category I left alone and it displays a full week not splitting across months.

5. The relative time categories are displaying the correct setting.

It might be worth a go replicating the issue at your end with the sample databases and then perhaps raise a support call should the issue remain on 10.1.1 as it works on 8.4

Cheers

Rob 

[Lynn]

Perhaps step 3 is the key to my problem...I did not have a separate query to set the current period. The only other odd thing in my situation is that the date from my DB2 source is actually a numeric data type, not a date. Transformer seems to take that in stride no problem so I don't think that is a factor.

I appreciate you taking the time to try it out!! It certainly seems like a reasonable requirement that ought to be possible. I'll let you know how I make out. Thanks Rob!

[Lynn]

Bingo!

I got pulled away from this and just today got a chance to try out your suggestions.

It seems adding a separate query to set the current period does the trick!!

Lynnie is a happy camper and totally carried away with exclamation points!!!! :D

Many thanks Rob!!

[MFGF]

Wow! I never had you pegged as the camping type! Who'd 'a thunk? :)

Great news that you got it working!

MF.

Sent from my iPad using Tapatalk HD

[RobsWalker68]

Result, glad it worked and I'll let you Carry on Camping !!

Cheers

Rob

[MFGF]

I'll let you Carry on Camping !!

Oh dear! Now I have visions of Barbara Windsor! Rob, what have you done? :)

PS. Lynn, if Rob starts calling you Babs, it is a compliment. Honest! :)

Sent from my iPad using Tapatalk HD

[RobsWalker68]

Now why did I know that you Mark of all people would pick up on that  ;D

[Lynn]

As usual, the American in the group stands off to one side with a silly smile but has absolutely no idea what is going on.

I thought about googling for a moment but decided ignorance is probably bliss in this case.

:)


Sent from my iPhone using Tapatalk