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: No events within the next 45 days.

Magic date for last business date


 
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
esiegel
Principal Member
Principal Member



Joined: 09 Nov 2009

Posts: 105



PostPosted: Wed Jun 16, 2010 9:46 am 
Post subject: Magic date for last business date

we have a table in our database that stores one value...the maximum allocation date in our transactions table.

we use this date to determine the last business date. This helps us take weekends and holidays into account from prompting.

We have a filter object in our univese that uses the "magic date" technique that has been described here so many times. The code for that object is:
DM.V_DIM_DATE.CAL_DATE =
case
when @Prompt('Enter Date','D','Allocation Date\Date',Mono,Free)='01/01/3000 00:0:0'
then (Select convert(varchar(10),MAX_ALLOCATION_DATE_KEY) from DM.V_MAX_ALLOCATION_DATE)
else @Prompt('Enter Date','D','Allocation Date\Date',Mono,Free) end


When entering a regular date it uses that date and our query runs quickly...3 - 5 seconds.

when entering 01/01/3000 to force it to user the MAX_ALLOCATION_DATE the query runs very slowly...20 - 30 minutes.

Any idea why the performance would be so bad...and how to improve it?
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22138
Location: Dallas, Texas


flag
PostPosted: Wed Jun 16, 2010 11:03 am 
Post subject: Re: Magic date for last business date

The first thing that jumps out at me is that you're running a select as part of the case. It's going to run that select for every single row that comes back. If you hit a million fact rows, it's going to run a million selects. Can you join to the table instead of running the select as a sub-query?
_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-09-19 Stephen Few Blog Post on Multivariate Visualization
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution
Back to top
esiegel
Principal Member
Principal Member



Joined: 09 Nov 2009

Posts: 105



PostPosted: Wed Jun 16, 2010 2:18 pm 
Post subject: Re: Magic date for last business date

what would we join to or on?
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22138
Location: Dallas, Texas


flag
PostPosted: Wed Jun 16, 2010 9:49 pm 
Post subject: Re: Magic date for last business date

The date in the table.
Code:
DM.V_DIM_DATE.CAL_DATE =
case
when @Prompt('Enter Date','D','Allocation Date\Date',Mono,Free)='01/01/3000 00:0:0'
then convert(varchar(10),MAX_ALLOCATION_DATE_KEY)
else @Prompt('Enter Date','D','Allocation Date\Date',Mono,Free) end

There's only one row in the "max date" table, correct? if you join it in this way, it will match on the date when the "magic date" is entered, else it will match on the prompt date and the results won't be skewed because they're only joining on a single-row table.

_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-09-19 Stephen Few Blog Post on Multivariate Visualization
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution
Back to top
esiegel
Principal Member
Principal Member



Joined: 09 Nov 2009

Posts: 105



PostPosted: Thu Jun 17, 2010 7:42 am 
Post subject: Re: Magic date for last business date

thanks,
We'll give it a shot
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.0287 seconds using 18 queries. (SQL 0.0033 Parse 0.0009 Other 0.0246)
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