6 Replies Latest reply: Nov 16, 2010 8:27 AM by s j RSS

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

      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.

      error loading image

      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.

      error loading image

      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.

      error loading image

      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