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.

optimization - Reverse Pivot


 
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
shazi58
Forum Member
Forum Member



Joined: 01 Feb 2016

Posts: 17



PostPosted: Mon Jun 06, 2016 7:53 am 
Post subject: optimization - Reverse Pivot

Hi,

Can Reverse pivot be optimized .my job is taking too long to process just 1000 records .
Back to top
CLS69
Principal Member
Principal Member



Joined: 11 Jun 2009

Posts: 215
Location: Italy


flag
PostPosted: Mon Jun 06, 2016 10:55 am 
Post subject: Re: optimization - Reverse Pivot

In case you don't find any other solution, replace reverse pivot with as many query transform as the number of columns you need. As an example, consider the following table:
SHOP - Sales - Qtr
Shop1 - 100 - Q1
Shop1 - 400 - Q2
Shop1 - 300 - Q3
Shop1 - 500 - Q4
Shop2 - 120 - Q1
Shop2 - 440 - Q2
Shop2 - 350 - Q3
Shop2 - 510 - Q4

divert the source flow 4 times (one for each quarter) and populate the following record structure:
SHOP - Qtr1 - Qtr2 - Qtr3 - Qtr4
with 3 NULLS and 1 value (the one for which your value is defined).
For instance, output for Qtr3 would be:
SHOP - Qtr1 - Qtr2 - Qtr3 - Qtr4
Shop1 - NULL - NULL - 300 - NULL
Shop2 - NULL - NULL - 350 - NULL

Then join the 4 flows always choosing the non NULL values, so to obtain:

SHOP - Qtr1 - Qtr2 - Qtr3 - Qtr4
Shop1 - 100 - 400 - 300 - 500
Shop2 - 120 - 440 - 350 - 510

This should speed dramatically up.

My 2 €cents

_________________
When you are right, there is no need to yell. When you are not right, there is no reason to yell.
Back to top
eganjp
Forum Addict
Forum Addict



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


flag
PostPosted: Fri Sep 23, 2016 2:00 pm 
Post subject: Re: optimization - Reverse Pivot

Turn on the "Input data is grouped" in the reverse pivot and then make sure the data is actually sorted in the correct order upstream from the reverse pivot.

As was said above, if you can, avoid the reverse pivot altogether. I often get greatly improved performance by pushing down the reverse pivot functionality 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
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  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.0272 seconds using 17 queries. (SQL 0.0029 Parse 0.0008 Other 0.0235)
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