Relative Time Periods - Critical Issue

[bwalikar]

Hi All,
We have a fact with day grain. I am trying to build Special Categories "Last 13 weeks" and "Last 13 months"

Date Dimension : Week starts on Sunday.

Requirement :
Last 13 months : This should exclude the current month. Effectively this would mean Previous month + 12 months back.
Last 13 weeks : This should exclude current week. i.e., Previous week + 12 weeks back.

Approach followed:
Last 13 months -> To exclude the current month I have used Target Offset = -1. The problem I see here is in the month of Jan-2016, Dec-2015 isn't part of the 'Last 13 month' until data loads for 3rd Jan 2016. This means only after the 1st week of new month data is loaded into cube, Previous month falls under Last 13 months.

Last 13 weeks -> Similar to above scenario, 27th Dec - 2nd Jan data to fall under 'Last 13 weeks' , cube waits for 3rd Jan (Start of new week) data to load.

This really is confusing the way transformer builds special categories. Any help here would be greatly appreciated !!

Thanks in advance

[Lynn]

bheemrao.walikar wrote in a personal message:

Hi Lynn,
could you please help me with the following post.

http://www.cognoise.com/index.php/topic,30030.0.html

Hi bheemrao.walikar,
I try to help when time permits and when I feel I have a good answer to offer. There is no need to send a personal message requesting help.

I don't use Transformer anymore and it has been a little while since I've used it, so I don't know the best answer.

If your date dimension only goes to the week level rather than the daily level then I suspect this is expected behavior. Otherwise I'd check all your sources to see which is the one setting the current period. I would always make sure there is only one data source that has the sets current period checkbox selected.

Again, these are just guesses so hopefully someone more current with Transformer might chime in to assist you.

[marre]

Hi,

What is the lowest Period grain in your dimension, week or day?

[bwalikar]

Thanks for your replies Lynn and Marre !

Marre - Granularity is Day level.

After further analyis what I understood is, the way categories or special categories for a date dimension are generated also is decided by the property - "How do you want the week to be split when it spans across 2 months". When the dimension was created - It was set as "Move the days to first month". I have changed it to "Split week between months" and that seems to have resolved Months issues, but Rolling 13 weeks generates more than 13 weeks when week is split in 2 months. I am not sure on how to resolve weeks stuff..

[Lynn]

Thanks for your replies Lynn and Marre !

Marre - Granularity is Day level.

After further analyis what I understood is, the way categories or special categories for a date dimension are generated also is decided by the property - "How do you want the week to be split when it spans across 2 months". When the dimension was created - It was set as "Move the days to first month". I have changed it to "Split week between months" and that seems to have resolved Months issues, but Rolling 13 weeks generates more than 13 weeks when week is split in 2 months. I am not sure on how to resolve weeks stuff..

You can create an alternate drill path for the dimension and omit the month level. From this you can easily get rolling weeks without any splitting by month.

[bwalikar]

Thanks Lynn !! I will try alternate drill path.