BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

Dynamic Timeframe method - best? approach


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
CB13
Forum Member
Forum Member



Joined: 25 Jun 2007

Posts: 19



PostPosted: Mon Jun 25, 2007 9:44 am 
Post subject: Dynamic Timeframe method - best? approach

This post is a bit long - but the topic is an interesting and important one so I hope data architects and universe designers will chime in with their knowledge and experience.

I'm wondering how people approach having dynamic timeframes. I have implemented them one way in the past - but I'm considering other options this time around.

The business requirement would be to make reporting over multiple timeframes as easy as possible (ie. they could select Sales for Current Year Last Week, Last Period, and YTD, and the same for Prior Year by simply selecting 6 different measures named as such from the Universe - and the data provider would return the result set by running 6 queries - one for each timeframe - and then joining the data back together... all seamless to the user).

The method I used in the past was to maintain through ETL a "TimeFrame" table. It had a row for each timeframe our users required (CY LFW, CY LFP, CY YTD, LY LFW, LY LFP, LY YTD, etc.). Each night the ETL would run and set the start and end dates for each timeframe in the TimeFrame table. In the Universe 'between' joins were created from TimeFrame to the FACT tables on the date keys (the TimeFrame table was aliased once for each timeframe(row) - this created a LOT of joins to TimeFrame alias' - but worked) Each timeframe bound measure was set up to reference a basic sales measure - but a table join to the appropriate TimeFrame table alias was forced resulting in the date restriction to the proper timeframe.

Another method I have been brainstorming is to add a column for each TimeFrame to the TimeDays table (a table with one row per day...). Through ETL I would populate the column with either a 1 or 0 depending on whether that Date fell within the Timeframe associated with the column (ie. the CY LFW column would be populated with 1's for dates that fall within the current year, last full week.. all other rows would get a 0). In Designer, the FACT tables would join to the TimeDays table on the Date Key field. A measure would be created for each TimeFrame (sales CY LFW, Sales CY LFP, etc.). They would be defined as the Sales multiplied by the appropriate TimeDays column for that timeframe (which contains either a 1 or a 0) - resulting in a proper total sales for that timeframe.

So... that is the method I am considering now... but I'm not sure if I'm missing something that may cause issues if I attempt to implement using that method.

I haven't had any luck finding information about this type of approach while searching the forums here - although I'm sure this must have been discussed before?

Most posts I came across seemed to suggest using functions in Designer to create timeframe bound measures?? Is this the preferred and suggested method? (Does it give better performance? Or? What would the benefits be compared to timeframes driven by a table - a table maintained through ETL).

Thanks in advance! I know this is a long post - but I hope the material being covered is worth the investment of our time to help the community.
Back to top
Anita Craig
Forum Groupie
Forum Groupie



Joined: 17 Jun 2002

Posts: 8541
Location: Palo Alto, California, U.S.A.


flag
PostPosted: Mon Jun 25, 2007 7:08 pm 
Post subject: Re: Dynamic Timeframe method - best? approach

The reason you see lots of posts where people use functions to do this kind of timeframe reference is that so many people don't want the kind of solution you propose, or don't have the authority to implement that kind of solution.

But I think what you're proposing is a wonderful way to go. icon_wink.gif

_________________
Anita Craig Image link
Institutional Research & Decision Support
Stanford University Image link
____________________
Search is Your Friendô
Back to top
richardcottave
Principal Member
Principal Member



Joined: 30 Mar 2006

Posts: 305



PostPosted: Tue Jun 26, 2007 7:56 am 
Post subject: Re: Dynamic Timeframe method - best? approach

We are starting a project where I want to do exactly the same thing. However, I'm finding it not anywhere near as easy as the way we use to do it. We used cognos which his cubed based and had a date wizard that you could create just about any date dimension you could want in minutes. You just specify simple parms such as your first day of week, whether you want it based on lunar week or calendar of 365 days. It automatically creates all the dims for QTD,YTD, PYTD, PQTD. all those and more. Th BO way using date tables and manually creating calculations is a giant 25 year leap backwards IMHO.
Back to top
CB13
Forum Member
Forum Member



Joined: 25 Jun 2007

Posts: 19



PostPosted: Wed Aug 08, 2007 10:42 am 
Post subject: Re: Dynamic Timeframe method - best? approach

I agree that the BO way is not as "easy" as the Cognos way. Having not seen the nitty-gritty details in Cognos I'm not sure how well it would handle quirks involved with a fiscal calendar (53rd week and how the business wishes to deal with it in reporting, etc.).

Having full control over how you define timeframes is a benefit - and a curse....

Regarding the approach I proposed above - it appears to be working well so far. Our ETL is almost completed for our initial set of timeframes.
Back to top
hbanerjee
Forum Member
Forum Member



Joined: 16 Aug 2010

Posts: 18


flag
PostPosted: Wed Nov 03, 2010 8:00 pm 
Post subject: Re: Dynamic Timeframe method - best? approach

You can do that quite easily using aliases in your universe. I have illustrated that in my blog post http://howtobi.wordpress.com/2010/11/03/how-to-do-year-ago-or-prior-period-type-comparison-in-webi/
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0288 seconds using 17 queries. (SQL 0.0029 Parse 0.0009 Other 0.0250)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo