Skip to Content

Selection discarded with Multiprovider containing Virtual Infoprovider

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 07, 2009 at 01:53 PM

    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)

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 07, 2009 at 02:30 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • Posted on Jan 08, 2009 at 10:03 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 19, 2009 at 07:29 AM

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

    Best Regards,

    Hans

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.