[Solved] YTD/ MTD and more...Aggregations

[charon]

Hi everyone :)

I am wondering...what might be the easiest way to implement YTM, YTM-LY and LY for several fact tables?
Using C10, DMR modeled with 4 fact tables.

The performance is an issue already, therefore il put some effort in etl, but i need for 1 (of 16) reports a fast solution.
I know that there are several approaches, i found some IBM PB even (like "dimensional modelin for time period analysis"),
but i was not able to build it so it works.

anybody any experience in an "easy", not 2 complex way how i can deliver at least the correct data (again, performance is no issue in this regard)?!

thx a lot guys :)
cheerz :P

[tjohnson3050]

There is an IBM Best Practice on Modeling for Relative Time:

http://www.ibm.com/developerworks/data/library/cognos/page90.html

The way we handle relative time is with a view on our calendar dimension.  For YTD we use a case statement in the view like:

case when calendaryear = year(getdate()) and calendardate <= getdate() then 'YTD' else 'Other' end

[cognostechie]

This is way too much maintenance and less functionality. Maybe this was made in earlier days. It requires creating diff. query subjects and so many joins for every relative time. It also requires modelling for every measure seperately so what happens if a meausre gets added.

I do it in this fashion which requires no additioanl joins and these can be dragged and dropped in the report and works for every scenario. It also lets us make a report to apply these filters in individual columns so if somebody wants to see a report that will show YTS sales in the 1st column and Prior YTD sales in the 2nd columns, they can do that.

[charon]

Hi cognostechie...

a solution based on filters alone? interesting... ive build the "dimensional modeling for time period analysis" yesterday, but im not convinced thats the best way to do it...it also need some maintenance in fm to do.
@ tjonhson, thx for your fast response sir, i will give it a try in a few minutes :)

i would like to try your attempt, would you mind to post/ pm me the filter definition?
would be awesome  :o
ty in advanced
cheerz charon :P

[wyconian]

I would normally push period on period analysis into a cube.  It's then very simple to calculate YTD, prior YTD etc etc etc using the date wizard.

If I want something a bit more 'quick and dirty' I've added relative date columns to my calendar, I then use sub queries in the report studios to bring back YTD, prior YTD etc attributes/measures into a report.

I'd suggest best way of doing this though is to a build a cube :-)

[charon]

unfortuneatly, a cube is no option, is has to be a pure relational db -> DWH and a dmr model on this relational layer.

still, thx for the response :)

[Greg]

Since you're using DMR and especially if you're using DQM I would recommend the following approach:

Create a set expression for each category.
E.g. YTD Months
filter (
  [Namespace].[Calendar].[YQM].[Month],
  [Namespace].[Calendar].[YQM].[Year].[Calendar Year Number] = extract( year, current_date )
  and
  [Namespace].[Calendar].[YQM].[Month].[Calendar Month Number] <= extract( month, current_date )
)

This may cause performance problems if you're not using DQM since the entire fact query would need to be processed locally by the OLAP query provider, however using DQM the query engine will evaluate the set expression first and then issue the fact query against the relational DB substituting the values returned by the filter expression into the where clause.

This method also allows you to compare YTD and LYTD for any measure in the same query.

Cheers,
Greg

[cognostechie]

Hi cognostechie...

a solution based on filters alone? interesting... ive build the "dimensional modeling for time period analysis" yesterday, but im not convinced thats the best way to do it...it also need some maintenance in fm to do.
@ tjonhson, thx for your fast response sir, i will give it a try in a few minutes :)

i would like to try your attempt, would you mind to post/ pm me the filter definition?
would be awesome  :o
ty in advanced
cheerz charon :P

I had posted the filter definitions earlier on Cognos 8 forum.

This is the way it works  and it does not need any DMR -

1> Join all your Fact query subjects to your Date Dimension. Make sure that the join is inner ! Since we are talking about Date Dimension, I don't see any reason to have an outer join with a Fact query.
2> In the Business Layer, create a folder, call it 'Filters'. Right click on the folder, create a Standalone filter and insert this expression:

extract(year,[Database Layer].[Date_Dim].[Calendar_Date]    ) = extract(year, current_date) and
[Database Layer].[Date_Dim].[Calendar_Date]   <= _add_days(current_date,0)

This will be the filter for YTD. This will work for every Fact query and every measure in those queries.

Similarly you can create other filters.

Good Luck!  :P

[charon]

HI guys
Thx for all the suggestions, everyone earned his applause :)

Techie, about the last idea, creating filter (in FM). I like the idea because its not a lot of effort i have to invest, BUT: is there a way to use different columns with different filter?
As far as i know each filter filters a whole query, not only a column. So i wont be able to compare different time periods in one crosstable, am i?!
I have like
YTM   -    Actuall Month            YTM - LY                      Actual Month - LY   

and so on...
is this possible to use this filter strategy on this request without creating 4 crosstabs, disable the rows of crosstab 2-4 (box = none) or some tricks, just filter the each column with a different filter?

Else im in trouble  :-X

[cognostechie]

As I mentioned in my first reply to this post, these filters can be applied to columns instead of applying to the entire query. That actually has nothing to do with these filters. There is a way in Repor tStudio to apply filters to columns instead of applying to the entire query. I posted this on another thread. 

Look at this and use this technique. Replace the condition in the IF statement with these filters -

http://www.cognoise.com/community/index.php/topic,19035.msg56450.html#msg56450

[charon]

I see :)

Ty you techie, i finally understood your approach and like it...tricky.
i also realized the other approach, creating a view (first action i did on a db myself, im kinda proud :D) with ytd, ytd-ly and ly, and jkoined them in fm to the fact tables. worked as well..
ive learned a lot today. now i can go to sleep happy like a sunflower ...thx to all :) ;D