QV and SAP BW - DSO or OLAP Connector - Extract >1m records

[#1]

Hello everyone,

I am using QV9 v9.00.7320.0409 , QvSAPDSOConnector v 5.3 or QvSAPOLAPConnector v 5.3.

I am trying to extract data from SAP ODS's using the QvSAPDSOConnector with various results depending on settings of the query code and/or volumes of data in the ODS's.
What I am looking for is some advice/knowledge from the community on the logic behind the results I am getting and a possible solution. QvSAPDSOConnector
I have an ODS with 600k records in and using this code I can extract all the 600k records by 65 characteristics ( approx 39 million cells of information ?)

================================================================================

CUSTOMCONNECT TO "Provider=QvSAPDSOConnector;ASHOST=SERVER_NAME;SYSNR=10;CLIENT=500;
ODSMaxRows=10000000;XUserId=USER;XPassword=PASSWORD;";

[ODS_NAME]:

Load *;

SQL Select (NoKey) * from ODS_NAME;

Store * from [ODS_NAME] into D:\FILE_NAME.QVD;

Drop table [ODS_NAME];

================================================================================

I am successful with this and a similar success when connecting to another ODS with 100k records by 90 characteristics ( approx 9 million cells of information ?)But when trying the same code on an ODS with 18m rows (eg. years 2001-2010) in it by 90 characteristics, I don't get the same results and get the error below.

I try a reduced ODSMaxRows at 500k and the result is saved into a .QVD . I will get 500k rows in there, but it won't be the first 500k records of the ODS in the way I would expect, e.g. 500k records from year 1, it is more or less evenly spread across 6 of the years, 2001-2007. From 0CALDATE 01/01/2001 until 10/12/2007, some transactions every day, no days missing, 2536 days in total. Is there some significance with this number ?

I also get this error when the script has finished. No big deal as long as the .QVD is created I am not too worried, but I would prefer not to get it or at least understand why.

With the ODSMaxRows at 1,000,000, the result is saved into a .QVD . I will get 1m rows in the .QVD, but again it won't be the first 1m records, it will be spread across the years up to 2007. Exactly the same dates as above. Same error message.

Increasing the ODSMaxRows above 1,000,000, results in a different error and no .QVD.

I have tried both 1,500,000 and 1,00,100 and get the error.

This, I believe, is due to table space allocation within SAP server/environment. I have increased the table space allocation to the max size with in our system and that recomended by SAP.

I have also tried a preceeding where 0CALYEAR = 2002 statment, that does work at getting the records and showing the ones from 2002, but not 1m of them, it will still extract the same amount as from the above method.....strange ?

Seeing that the default setting for ODSMaxRows is 10,000,000 would suggest that it is possible to extract that much from SAP ODS's, does anyone have experience of extracting these volumes or higher with a QvSAPDSOConnector I would also be grateful.

We have also tried with the OLAP connector but run into similar problems with more than 50 characteristics with associated attributes etc etc.

Any help on this subject would be very helpful indeed.

Many thanks
Greenee

[Lars_Wahlstedt]

Hi,

with the volumes you are describing i would defintely recommend using the SQL Connector instead. The DSO connector will run into memory problems on the SAP side when hitting large volumes.

I hope you understood the WHERE syntax is quite different from SQL. As you can see in the documentation it should look like:

where 0DIVISION I EQ 01

I = Include

You cam also use the column-based slicing to avoid some memory-problems.

Regards,

Lars Wahlstedt

[mike_garcia]

Hello Lars,

I would like to know where can I find the Documentation you mention that covers topics like the WHERE clause. The only docuentation I have about SAP Connector is the Installation Guide. I looked in the Program Files > QlikView Folder but there was only documentation for QVS and QV Desktop.

Thanks

Mike.

[suniljain]

Dear Grenee,

In SAP, DSO have their different identity than DSO Name. See

For Example. if DSO Name is OSD_C06 then Physical Table name of that DSO may be ASQ_COO5.

I have same problem in our one SAP BW client . Got the same error.

I solved that by above scenario. and it working fine.