cancel
Showing results for 
Search instead for 
Did you mean: 

Selection discarded with Multiprovider containing Virtual Infoprovider

h_fokker
Explorer
0 Kudos

Hey Everybody

We want to compare data in our BI system and OLTP. So we created a multiprovider containing a basic infoprovider and a virtual infoprovider. The virtual infoprovider is based on dtp.

Now we have a problem with queries running on the multiprovider. A TSV_TNEW_PAGE_ALLOC_FAILED shortdump occurs on the OLTP. After some SQL tracing i discovered that the selection criteria used in the query are discarded. As a result of that the extractor tries to read to complete sourcetable into memory. The same problem occurs when using listcube.

Same query directly on the virtual infoprovider however runs OK and uses the correct filter values in the SQL statement on OLTP.

Any ideas anyone ?

Thanks,

Hans

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

So the selection criteria passed to the virtual cube gets lost? And you have set up the virtual provider against a datasource which has selections set up as ticked in the source system

I have been down this route and my pain is now your knowledge..

The virtual cube looks at the query and gets the sids for the selection

If it can do a "from and to" on the selections it tries to do a "between" clause otherwise it will just do a "in" clause and list the selections.

This is okay unless you have alpha type records (ie most of the master data) or are using hierarchy nodes where it is damn near impossible to get a between clause

Now - if the number of IN clauses is greater than 200 it will drop the clause and retirve the entire dataset and do it in memory on BW.

Why?

The length of an SQL statement is the problem - if in a large hierachy you select a node you could end up with a SQL statement of hundreds on IN records and this will break the Oracle limit on the size of a statement

There is an RSADMIN parameter you can set to stop this - but last time I tried I got a Oracle abend on the source system because the size of the statement was too large

I got around this by creating extra indexes on standard SAP tables to help the SQL as well as restricting the queries running of the virtual cube to single value parameters only

The RSADMIN parameter is

VCUBE_<virtual cube name>_SEL

with a flag of X

How did I find it out? - debug the virtual cube function module then OSS! (note 606445)

Answers (3)

Answers (3)

h_fokker
Explorer
0 Kudos

Problem solved by setting multiproviderproperty Read Mode to 'Query to read data during navigation'. Thanks for the suggestions.

Best Regards,

Hans

h_fokker
Explorer
0 Kudos

Hi

Restricting the keyfigures on infoprovider did not help unfortunately and it's a very simple query to be honest ( 2 characteristics and 1 keyfigure ). I did a RSRT with breakpoint on Virtual Infoprovider based on DTP. Table I_T_RANGE remains empty. More suggestions or should i try the guru's in Walldorf.

Thanks,

Hans

h_fokker
Explorer
0 Kudos

Hey Simon

Thanks for the fast reply. I already tried the parameter VCUBE_<INFOPROVIDERNAME>_SEL according to note 606445. This had no effect. I also tried the 'Do not Transform Select. Cond' checkbox as described in note 963603. Same issue here.

Did you also have the same problem with queries directly on the virtual inforpovider itself ? They run just fine on our case. So is suspect the problem has got something to do with the combination Multiprovider <> Virtual Infoprovider.

Regards,

Hans

Former Member
0 Kudos

In that case - have you restricted your RKFs by the infoprovider

In addition in some circumstances SAP consider some queries too "complex". This came around in version 7 and is a bit of a pain.

Wtat I have to do with some complex structures is introduce filter or free char variables with cmods which read the contents of variables in structures and re-filter them (if you get my drift)

The SQL then gets the restriction

I actually have had the problem this morning - ie a structure with this year, last year and current month in it generating no period in the where clause

So we put cmod variable into the cmod which does a between of last year to this year (hence the partition in this case gets hit)

As you have found OSS note 963603 is useless and never works in this scenario

In my circumstance - I played and played with the queries to make sure the SQL was generating correctly - never had a problem with the multiprovider versions of them