Is there any limitation in rows returned by our MDX driver for LOV? XI R3

[gabriel_delapparent]

Hello All:

A customer has created a prompt with a LOV with more than 60000 rows - i.e. 60000 members in SAP BW cube. However, when searching for values in the prompt, the prompt doesnt show up values which are located beyond 50500 rows. When looking at the SOFA trace, we can find that webi splits the research result set into 2 batches, one of 500 rows, then another one of 50 000 rows:

Fri Mar 06 17:09:18.639: SAPMODULE: SAPConnection: SAPConnection::getMaxRowsPerLoad

Fri Mar 06 17:09:18.639: SAPMODULE: SAPServerConfiguration: SAPServerConfiguration::getSAPServerLoadLimits

...

Fri Mar 06 17:09:18.639: SAPMODULE: SAPServerLoadLimits: Looking for tlsrv052.swna.wdpr.disney.com from the list of ServerLoadLimits...

Fri Mar 06 17:09:18.639: SAPMODULE: SAPServerLoadLimits: Unable to locate the server name tlsrv052.swna.wdpr.disney.com from the list of ServerLoadLimits.

Fri Mar 06 17:09:18.639: SAPMODULE: SAPConnection: Enable to locate the MaxRowsPerLoad for the server(tlsrv052.swna.wdpr.disney.com) from the SAPServerLoadLimits. Using the default value 50000

...

Fri Mar 06 17:09:20.968: SAPMODULE: FIS: SAPHierarchy::get_members

...

Fri Mar 06 17:09:20.968: SAPMODULE: FIS: MDDataProviderBW.GetMembers(Cat=PSCPROJ3,Cube=PSCPROJ3/YFTPR_PSC_PSCPROJ3_Q006_ODBO,Dim=[0PROJECT],Hry[0PROJECT],Lvl=[0PROJECT].[LEVEL01],Mem=,OpCode=,PropFlag= )

Fri Mar 06 17:09:20.968: SAPMODULE: FIS: MDDataProviderBW.GetMembers(StartRow=0,EndRow=499)

...

Fri Mar 06 17:09:32.828: SAPMODULE: FIS: RFC function call MDDataProviderBW.GetMembers returned S

Fri Mar 06 17:09:32.828: SAPMODULE: FIS: Got 500 members

...

Fri Mar 06 17:09:33.250: SAPMODULE: FIS: SAPHierarchy::get_members

...

Fri Mar 06 17:09:33.250: SAPMODULE: FIS: MDDataProviderBW.GetMembers(Cat=PSCPROJ3,Cube=PSCPROJ3/YFTPR_PSC_PSCPROJ3_Q006_ODBO,Dim=[0PROJECT],Hry[0PROJECT],Lvl=[0PROJECT].[LEVEL01],Mem=,OpCode=,PropFlag= )

Fri Mar 06 17:09:33.250: SAPMODULE: FIS: MDDataProviderBW.GetMembers(StartRow=500,EndRow=50499)

..

Fri Mar 06 17:10:18.239: SAPMODULE: FIS: RFC function call MDDataProviderBW.GetMembers returned S

Fri Mar 06 17:10:18.239: SAPMODULE: FIS: Got 50000 members

In Designer, we have tried the object option "Delegate search", but it did not work.

So, here are the questions:

• How come MDA_SAP asks for 500 and then ask for an additional 50,000 members? Is this limit configurable? Why does it not read the entire member set?

• Should WebI not delegate the search to MDA_SAP which could then forward that search criterion to SAP via get_members?

• Because users have access to different member sets, how does WebI cache these values and make sure that users can only see the members they have access to and all the members they have access to in BI?

Thanks in advance,

- Gabriel

Edited by: Gabriel De Lapparent on Mar 19, 2009 7:28 PM

[IngoH · Accepted solution]

Hi,

which version / patch level is this ?

thanks

Ingo

[gabriel_delapparent · Accepted solution]

It's XI 3.0 FP2 and BI 7 patch level 18.

[gabriel_delapparent · Accepted solution]

On the XI 3.X server, I have found the parameter for the current limitation of 50 000 rows:

·

· HKEY_LOCAL_MACHINESOFTWAREBusiness ObjectsSuite 12.0MDASAPServerLoadLimits[your SAP BW server name or its IP]

"MaxRowsPerLoad"=dword:000F4240

[IngoH · Accepted solution]

just to be clear - its not a limit. it is the number of reading per chunk.

Ingo

[Former Member · Accepted solution]

Dear Ingo, <BR>

One customer using BOE XI3.1 SP3 FP3.1 also met some problem of the LOV. They refresh their report and will get duplicate rows sometimes, when we check the SOFA log, we found something interesting:<BR>

Why the same BAPI call with same data records on bw side sometimes will find one chunk but sometimes will find more than one chunk (they just have 2 records match the requirement). <BR>

And the wierd thing is why the paramter sent to BAPI changed into 1for the startrow if the first chunk returned 2 rows, it should be start from 2 not 1.<BR>

<BR>

The right LOV will end as below:<BR>

And I also checked the log (Alex also found the similar issue) and found if the return value is right then it will be as below: <BR>

Mon Oct 17 11:16:10.065 ThreadID<3388> SAPMODULE : MDDataProviderBW.GetMembers(Cat=ZR_PGM05,Cube=ZR_PGM05/ZR_PGM05_Q001,Dim=[0FISCYEAR],Hry[0FISCYEAR],Lvl=[0FISCYEAR].[LEVEL01],Mem=,OpCode=,PropFlag=S) <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RFC function call MDDataProviderBW.GetMembers returned S<BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : Call to MDDataProviderBW.GetMembers took 0.641 seconds <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : Got 2 members <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : Call to SAPHierarchy::get_members took 0.641 seconds <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::beforeFirst() <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::first() = 1<BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::getString(0) = Z1/2010 <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::getString(1) = Z12010 <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::getString(2) = [0FISCYEAR].[Z12010]<BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::next() = 1 <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::getString(0) = Z1/2011 <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::getString(1) = Z12011 <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::getString(2) = [0FISCYEAR].[Z12011]<BR>

Mon Oct 17 11:16:10.707 ThreadID<3388> SAPMODULE : SAPMemberVariableLovRowsetDataModel::reachEndOfRowset() <BR>

Mon Oct 17 11:16:10.707 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::next() = 0 <BR>

Mon Oct 17 11:16:18.604 ThreadID<4804> SAPMODULE : SAPConnection::connect <BR>

Mon Oct 17 11:16:18.604 ThreadID<4804> SAPMODULE : SAPClient::load_structure <BR>

<BR>

If itu2019s wrong then it will find next chuck and keep on fetching the data. But even the parameter they found and passed to BAPI call is mismatched. Parameter StartROW changed from 2 to 1.<BR>

(if the next chunk start from 2, then we won't get any duplicate values in this case).<BR>

<BR>

The wrong trace is as below: <BR>

Mon Oct 17 11:17:04.033 ThreadID<4976> SAPMODULE : MDDataProviderBW.GetMembers(Cat=ZR_PGM05,Cube=ZR_PGM05/ZR_PGM05_Q001,Dim=[0FISCYEAR],Hry[0FISCYEAR],Lvl=[0FISCYEAR].[LEVEL01],Mem=,OpCode=,PropFlag=S) <BR>

Mon Oct 17 11:17:05.226 ThreadID<4976> SAPMODULE : RFC function call MDDataProviderBW.GetMembers returned S<BR>

Mon Oct 17 11:17:05.226 ThreadID<4976> SAPMODULE : Call to MDDataProviderBW.GetMembers took 1.192 seconds <BR>

Mon Oct 17 11:17:05.226 ThreadID<4976> SAPMODULE : Got 2 members <BR>

Mon Oct 17 11:17:05.226 ThreadID<4976> SAPMODULE : Call to SAPHierarchy::get_members took 1.192 seconds <BR>

Mon Oct 17 11:17:05.226 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::beforeFirst()<BR>

Mon Oct 17 11:17:05.226 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::first() = 1 <BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(0) = Z1/2010<BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(1) = Z12010 <BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(2) = [0FISCYEAR].[Z12010] <BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::next() = 1<BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(0) = Z1/2011<BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(1) = Z12011 <BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(2) = [0FISCYEAR].[Z12011] <BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : SAPMemberVariableLovRowsetDataModel::moveToNextChunk() : m_StartRow = 2 <BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : SAPMemberVariableLovRowsetDataModel::moveToNextChunk() : m_EndRow = 49807 <BR>

Mon Oct 17 11:17:05.231 ThreadID<4976> SAPMODULE : SAPHierarchy::get_members <BR>

Mon Oct 17 11:17:05.231 ThreadID<4976> SAPMODULE : Calling SAPHierarchy::get_members ... <BR>

<BR>

Mon Oct 17 11:17:05.231 ThreadID<4976> SAPMODULE : Calling MDDataProviderBW.GetMembers ... <BR>

Mon Oct 17 11:17:05.231 ThreadID<4976> SAPMODULE : MDDataProviderBW.GetMembers(Cat=ZR_PGM05,Cube=ZR_PGM05/ZR_PGM05_Q001,Dim=[0FISCYEAR],Hry[0FISCYEAR],Lvl=[0FISCYEAR].[LEVEL01],Mem=,OpCode=,PropFlag=S) <BR>

Mon Oct 17 11:17:05.231 ThreadID<4976> SAPMODULE : MDDataProviderBW.GetMembers(StartRow=1,EndRow=49806) <BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : RFC function call MDDataProviderBW.GetMembers returned S<BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : Call to MDDataProviderBW.GetMembers took 0.481 seconds <BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : Got 1 members <BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : Call to SAPHierarchy::get_members took 0.481 seconds <BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : SAPMemberVariableLovRowsetDataModel::moveToNextChunk() : chunk(1) has 1 rows.<BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::next() = 1<BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(0) = Z1/2011<BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(1) = Z12011 <BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(2) = [0FISCYEAR].[Z12011] <BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : SAPMemberVariableLovRowsetDataModel::reachEndOfRowset() <BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::next() = 0<BR>

<BR>

Please feel free to address your concerns. <BR>

Thanks, <BR>

Sophie

Edited by: Sophie Xu on Oct 19, 2011 11:00 AM

[Former Member · Accepted solution]

Dear Ingo, <BR>

One customer using BOE XI3.1 SP3 FP3.1 also met some problem of the LOV. They refresh their report and will get duplicate rows sometimes, when we check the SOFA log, we found something interesting:<BR>

Why the same BAPI call with same data records on bw side sometimes will find one chunk but sometimes will find more than one chunk (they just have 2 records match the requirement). <BR>

And the wierd thing is why the paramter sent to BAPI changed into 1for the startrow if the first chunk returned 2 rows, it should be start from 2 not 1.<BR>

<BR>

The right LOV will end as below:<BR>

And I also checked the log (Alex also found the similar issue) and found if the return value is right then it will be as below: <BR>

Mon Oct 17 11:16:10.065 ThreadID<3388> SAPMODULE : MDDataProviderBW.GetMembers(Cat=ZR_PGM05,Cube=ZR_PGM05/ZR_PGM05_Q001,Dim=[0FISCYEAR],Hry[0FISCYEAR],Lvl=[0FISCYEAR].[LEVEL01],Mem=,OpCode=,PropFlag=S) <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RFC function call MDDataProviderBW.GetMembers returned S<BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : Call to MDDataProviderBW.GetMembers took 0.641 seconds <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : Got 2 members <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : Call to SAPHierarchy::get_members took 0.641 seconds <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::beforeFirst() <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::first() = 1<BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::getString(0) = Z1/2010 <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::getString(1) = Z12010 <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::getString(2) = [0FISCYEAR].[Z12010]<BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::next() = 1 <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::getString(0) = Z1/2011 <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::getString(1) = Z12011 <BR>

Mon Oct 17 11:16:10.706 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::getString(2) = [0FISCYEAR].[Z12011]<BR>

Mon Oct 17 11:16:10.707 ThreadID<3388> SAPMODULE : SAPMemberVariableLovRowsetDataModel::reachEndOfRowset() <BR>

Mon Oct 17 11:16:10.707 ThreadID<3388> SAPMODULE : RowsetDataModelBase[1]::next() = 0 <BR>

Mon Oct 17 11:16:18.604 ThreadID<4804> SAPMODULE : SAPConnection::connect <BR>

Mon Oct 17 11:16:18.604 ThreadID<4804> SAPMODULE : SAPClient::load_structure <BR>

<BR>

If itu2019s wrong then it will find next chuck and keep on fetching the data. But even the parameter they found and passed to BAPI call is mismatched. Parameter StartROW changed from 2 to 1.<BR>

(if the next chunk start from 2, then we won't get any duplicate values in this case).<BR>

<BR>

The wrong trace is as below: <BR>

Mon Oct 17 11:17:04.033 ThreadID<4976> SAPMODULE : MDDataProviderBW.GetMembers(Cat=ZR_PGM05,Cube=ZR_PGM05/ZR_PGM05_Q001,Dim=[0FISCYEAR],Hry[0FISCYEAR],Lvl=[0FISCYEAR].[LEVEL01],Mem=,OpCode=,PropFlag=S) <BR>

Mon Oct 17 11:17:05.226 ThreadID<4976> SAPMODULE : RFC function call MDDataProviderBW.GetMembers returned S<BR>

Mon Oct 17 11:17:05.226 ThreadID<4976> SAPMODULE : Call to MDDataProviderBW.GetMembers took 1.192 seconds <BR>

Mon Oct 17 11:17:05.226 ThreadID<4976> SAPMODULE : Got 2 members <BR>

Mon Oct 17 11:17:05.226 ThreadID<4976> SAPMODULE : Call to SAPHierarchy::get_members took 1.192 seconds <BR>

Mon Oct 17 11:17:05.226 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::beforeFirst()<BR>

Mon Oct 17 11:17:05.226 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::first() = 1 <BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(0) = Z1/2010<BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(1) = Z12010 <BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(2) = [0FISCYEAR].[Z12010] <BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::next() = 1<BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(0) = Z1/2011<BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(1) = Z12011 <BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(2) = [0FISCYEAR].[Z12011] <BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : SAPMemberVariableLovRowsetDataModel::moveToNextChunk() : m_StartRow = 2 <BR>

Mon Oct 17 11:17:05.227 ThreadID<4976> SAPMODULE : SAPMemberVariableLovRowsetDataModel::moveToNextChunk() : m_EndRow = 49807 <BR>

Mon Oct 17 11:17:05.231 ThreadID<4976> SAPMODULE : SAPHierarchy::get_members <BR>

Mon Oct 17 11:17:05.231 ThreadID<4976> SAPMODULE : Calling SAPHierarchy::get_members ... <BR>

<BR>

Mon Oct 17 11:17:05.231 ThreadID<4976> SAPMODULE : Calling MDDataProviderBW.GetMembers ... <BR>

Mon Oct 17 11:17:05.231 ThreadID<4976> SAPMODULE : MDDataProviderBW.GetMembers(Cat=ZR_PGM05,Cube=ZR_PGM05/ZR_PGM05_Q001,Dim=[0FISCYEAR],Hry[0FISCYEAR],Lvl=[0FISCYEAR].[LEVEL01],Mem=,OpCode=,PropFlag=S) <BR>

Mon Oct 17 11:17:05.231 ThreadID<4976> SAPMODULE : MDDataProviderBW.GetMembers( StartRow=1,EndRow=49806 ) <BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : RFC function call MDDataProviderBW.GetMembers returned S<BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : Call to MDDataProviderBW.GetMembers took 0.481 seconds <BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : Got 1 members <BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : Call to SAPHierarchy::get_members took 0.481 seconds <BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : SAPMemberVariableLovRowsetDataModel::moveToNextChunk() : chunk(1) has 1 rows.<BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::next() = 1<BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(0) = Z1/2011<BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(1) = Z12011 <BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::getString(2) = [0FISCYEAR].[Z12011] <BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : SAPMemberVariableLovRowsetDataModel::reachEndOfRowset() <BR>

Mon Oct 17 11:17:05.712 ThreadID<4976> SAPMODULE : RowsetDataModelBase[36]::next() = 0<BR>

<BR>

Please feel free to address your concerns. <BR>

Thanks, <BR>

Sophie

[Former Member]

Hi Gabriel,<BR>

i found something can explain why it get 500 rows first. <BR>

According to the SP3 code : <BR>

if ( m_FilterOnSameLevel && !m_FlatHierarchyLevelOne ) <BR>

{ <BR>

chunkSize = SAP_MAXIMUM_ROW_NUMBER;<BR>

}<BR>

else<BR>

{ <BR>

chunkSize = MINIMUM_MEMBER_ROWSET_CHUNK_SIZE;<BR>

} <BR>

After check the minimum_member_rowset_chunk_size, it is defined MINIMUM_MEMBER_ROWSET_CHUNK_SIZE into 500 //The small initial chunk would allow for fast loading of LOVs if partial loading were implemented in the higher levels. <BR>

I guess your first fetch mismatches the condition, so it goes to set the chunk size into 500, but later it matches the condition, then it goes to the default maximim size.<BR>

Hope this helps. <BR>

Sophie <BR>