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

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

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

Function 'add_months()' can not be pushed down to DB
1 members found this topic helpful
Goto page Previous  1, 2
 
Search this topic... | Search DI: Performance and Tuning... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Author Message
jd.pro
Senior Member
Senior Member



Joined: 15 Nov 2010

Posts: 93
Location: Grenoble


flag
PostPosted: Mon Apr 16, 2012 8:55 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

Hi,

Instead of pushing the add_months function, I pushed the calculation outside the DF in a script and used the result as a parameter.

But I still don't have my calculation pushed down to the DB (SQL Server 2008 R2) icon_sad.gif

My mapping code is :
min(
ifthenelse((W_POLICE_DWH.DT_FIN_ACTIVITE >= $p_dt_24mois),
0,
1))

DT_FIN_ACTIVITE has datetime type.
$p_dt_24mois has date type.

This code is not pushed down even if I cast the parameter to datetime type or is I set its type to datetime.

If I try using the sysdate() function instead of my parameter, that works fine...

Any idea?
Am I using the parameter in a wrong way?
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 4280
Location: Denver Area


flag
PostPosted: Mon Apr 16, 2012 9:45 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

If all else fails then use pushdown_sql() to push the expression down to the database.
_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
jd.pro
Senior Member
Senior Member



Joined: 15 Nov 2010

Posts: 93
Location: Grenoble


flag
PostPosted: Thu Apr 19, 2012 6:34 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

Yes, but the function pushdown_sql() is limited to the WHERE clause, and I need to use my parameters in an aggregate calculation...
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 4280
Location: Denver Area


flag
PostPosted: Thu Apr 19, 2012 8:59 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

Ah, I see. I glossed over that you were using that expression in a mapping.

One thing to try would be to create a function in the database, import it to the Datastore and then use the function in the mapping.

_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10591
Location: Germany


flag
PostPosted: Fri May 04, 2012 2:25 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

Why is sysdate() pushed down but a global variable is not???

Are you 100% sure that when the column and the global variable is of type datetime, even then it is not pushed down??

_________________
Werner Daehn
ex Product Manager Data Services at SAP SE

www.rtdi.io
Back to top
jd.pro
Senior Member
Senior Member



Joined: 15 Nov 2010

Posts: 93
Location: Grenoble


flag
PostPosted: Fri May 11, 2012 3:55 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

Actually, it's pushed down (sysdate or variable) when I use a simple DF (sources -> Query with my variables -> target).
But when I add other sources and joins, the initial query with variables is not pushed down.

So I decided to split my DF in multiple simple DF with temporary tables.
That uses more DB space but improves the performance because the pushed down code include the variables and the insert statement.
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10591
Location: Germany


flag
PostPosted: Fri May 11, 2012 4:20 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

Could be a conversion or similar. Do you get any conversion warnings?

http://wiki.sdn.sap.com/wiki/display/EIM/Pushdown+not+working

_________________
Werner Daehn
ex Product Manager Data Services at SAP SE

www.rtdi.io
Back to top
jd.pro
Senior Member
Senior Member



Joined: 15 Nov 2010

Posts: 93
Location: Grenoble


flag
PostPosted: Fri May 11, 2012 8:42 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

I first had conversion warning between date and datetime data types.
I used a variable in DI date type and compared it to a Oracle date type (equivalent to DI datetime).

Changing the variable to datetime type did not resolve my point.

As I said earlier, I did testing with complex DF with several sources and joins, that were not fully pushed down to the DB (expecially some GROUP BY clause). And did testing with parts of these DF (copy-paste of one sources->query in another DF) and got a fully pushed down SQL, without doing anything on variables and in mapping code.
Back to top
Johannes Vink
Forum Enthusiast
Forum Enthusiast



Joined: 20 Mar 2012

Posts: 1138


flag
PostPosted: Fri May 11, 2012 10:14 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

And was the date at the end of the DF or at the beginning? Because when BODS cannot push down any more because of a blocking operation everything is processed on the BODS server after that.

And when you put it in a simple DF and is pushed down, then it sounds like this is the case.
Back to top
jd.pro
Senior Member
Senior Member



Joined: 15 Nov 2010

Posts: 93
Location: Grenoble


flag
PostPosted: Fri May 11, 2012 10:31 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

I ensured in my sources -> query, everything in the query could be pushed down.

That is : sources -> query -> target is fully pushed down (including the target).

But when designing : sources -> query -> join query (with others queries) -> target
The part [sources -> query] is not pushed down !!
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10591
Location: Germany


flag
PostPosted: Fri May 11, 2012 10:41 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

Please post the ATL of the one that is not being pushed down.
_________________
Werner Daehn
ex Product Manager Data Services at SAP SE

www.rtdi.io
Back to top
jd.pro
Senior Member
Senior Member



Joined: 15 Nov 2010

Posts: 93
Location: Grenoble


flag
PostPosted: Tue Jun 12, 2012 3:22 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

Hi,

Here is the ATL (passphrase is "pushed down").

2 DF to compare:

DF_TEST_PUSHED_DOWN_1
=> query QY_W_CONTRAT_AGG is pushed down OK
=> query QY_W_POLICE_AGG is pushed down OK
=> query QY_W_INTERVENTION_AGG is not pushed down !! This is problem 1.

DF_TEST_PUSHED_DOWN_2
=> query QY_W_CONTRAT_AGG is pushed down OK
=> query QY_W_POLICE_AGG is not pushed down !! For this one I added a calculation based on a date parameter (TOP_NON_RETRAIT_OPTION_24M). This is problem 2.
=> query QY_W_INTERVENTION_AGG is pushed down OK

Any explanation for both problems?
Back to top
jd.pro
Senior Member
Senior Member



Joined: 15 Nov 2010

Posts: 93
Location: Grenoble


flag
PostPosted: Tue Jan 19, 2016 8:36 am 
Post subject: Re: Function 'add_months()' can not be pushed down to DB

Hello,
Any new comment on that topic?
Regards,
Jerome
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Page 2 of 2 All times are GMT - 5 Hours
Goto page Previous  1, 2
 
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.0303 seconds using 17 queries. (SQL 0.0035 Parse 0.0009 Other 0.0259)
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