Hello, i think i have strange problem. I need to collect values from a SAP BW which has 700000 records, i need details for 240 entries. i thought i can solve this by using IN FILE, but i got a message that the memory of the SAP BW is not large enough. Now is my idea to call every single record in a loop and add this record to a focus file. So i get my 240 records step by step. I think i can run this tasks scheduled by the report caster at night.
Regards
ChristianThis message has been edited. Last edited by: ChristianP,
WF Production Version: 7.7.02M WF Test Version: 7.7.02M Developer Studio: 7.7.02 HTML, EXL2K, FLEX, PDF,PPT
September 29, 2011, 09:38 AM
Francis Mariani
Christian,
Normally, with relational databases, SQL selection criteria (WHERE statements) can have up to 1000 test values, so I'd be surprised that SAP BW does not allow even 240. Perhaps it's how the program is coded. Did you turn SQL traces on?
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
September 29, 2011, 09:44 AM
Doug
YUCK to: "call every single record in a loop and add this record to a focus file. So i get my 240 records step by step." . Can you HOLD those 240 records and work with that? That way it's on the WebeFOCUS side of things, not the SAP BW side (just athought)?
September 29, 2011, 05:42 PM
Waz
If you are doing the loop, I would join to the records in the IN FILE, instead. It will effectively do the same thing.
In fact it would be better to join from the records in the IN FILE.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
September 29, 2011, 08:54 PM
njsden
How many values can you use with IN FILE before you get a memory issue?
If it's a manageable amount, say 50-ish, you could split your list of values across multiple files; to follow the example, you'd end up with 5 files (let's say HVAL1, HVAL2, ..., HVAL5).
You could then loop through them and query your data referencing them with IN FILE. Something like:
APP FILE HRESULTS DISK hresults.ftm (APPEND
-RUN
-REPEAT :GETDATA FOR &I FROM 1 TO 5
TABLE FILE MYSAPTABLE
SUM ....
WHERE MYFIELD IN FILE HVAL&I ;
ON TABLE HOLD AS HRESULTS
END
-RUN
-:GETDATA
-* Process HRESULTS ...
TABLE FILE HRESULTS
blah
END
You'll be hitting the DB 5 times ... not optimal but better than hitting it 240 times with the row-by-row approach, I think.This message has been edited. Last edited by: njsden,
I would still follow Francis' suggestion of enabling traces to determine what's going on at the SAP level.
I can't imagine an enterprise platform such as SAP blowing up when trying to handle a query statement that has an IN clause with 240 values in it ... there must be something else ...
Hello, thanks for the help so far. I think it is even an internal political problem, because the SAP BW Team provides in our campany BO. And i think they limit the memory for our WEBFocus requests.
Regards
Christian
WF Production Version: 7.7.02M WF Test Version: 7.7.02M Developer Studio: 7.7.02 HTML, EXL2K, FLEX, PDF,PPT
September 30, 2011, 09:39 AM
njsden
Oh brother! they should revisit their policies then. SAP BW is a business intelligence platform and as such it should allow BI tools to have as much resource availability as possible because they are supposed to be dealing with significant volumes of data upon reporting/analysis.
Unless as you say, "political reasons" may be motivating them to push WebFOCUS away in favour of SAP's own BI technology ... hmmm, sounds like a conspiracy ... This message has been edited. Last edited by: njsden,
From experience, databases and other BI/DW platforms run so much more efficiently when there's no users on the system locking tables and hogging resources.
Of course, one will not be able to tell that for sure if one cannot log on....
Hello, in the meantime, I build a loop but i can't put the records together!! I get the message that the table with x lines have been build, but they are not build together. When i look at the result, i can only see the last result. I also tried it with the Append, same result.
-RUN
-SET &LIMIT=&LINES;
-SET &I=1;
-REPEAT ENDLOOP &LIMIT TIMES
-READ HOLD NOCLOSE &KEY.10.
-SET &KEY.&I=&KEY;
-SET &I=&I+1;
TABLE FILE xxx
SUM
vvv
vvv
BY ttt
WHERE ggg EQ '&KEY.&I1' ;
ON TABLE HOLD AS RESULT FORMAT ALPHA
END
-RUN
-ENDLOOP
-RUN
WF Production Version: 7.7.02M WF Test Version: 7.7.02M Developer Studio: 7.7.02 HTML, EXL2K, FLEX, PDF,PPT
October 05, 2011, 09:27 AM
njsden
Look at the first line of the sample code I gave you originally ... just adjust it to your specific HOLD file name.
The must be a -RUN after that APP FILE or it will not work.
quote:
I also tried it with the Append, same result.
Please post the code exactly as you had it using (APPEND. I've used that feature multiple times in the past and it's never failed me.
APP FILE RESULT DISK result.ftm (APPEND
-RUN
-SET &LIMIT=&LINES;
-SET &I=1;
-REPEAT ENDLOOP &LIMIT TIMES
-READ HOLD NOCLOSE &KEY.10.
-SET &KEY.&I=&KEY;
-SET &I=&I+1;
TABLE FILE xxx
SUM
vvv
vvv
BY ttt
WHERE ggg EQ '&KEY.&I1' ;
ON TABLE HOLD AS RESULT FORMAT ALPHA
END
-RUN
-ENDLOOP
I got a FOC 224 Syntax Error message. Thanks for you help !
Regards
Christian
WF Production Version: 7.7.02M WF Test Version: 7.7.02M Developer Studio: 7.7.02 HTML, EXL2K, FLEX, PDF,PPT
October 05, 2011, 10:52 AM
njsden
quote:
I got a FOC 224 Syntax Error message. Thanks for you help !
Where exactly?
Could you please work an example using the CAR table (or GGSALES) so we can replicate and perhaps be better equipped to help?
TABLE FILE CAR
PRINT COUNTRY AS KEY
ON TABLE HOLD AS HKEYS FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET ASNAMES ON
END
-RUN
-SET &LIMIT=&LINES;
FILEDEF HRESULTS DISK result.ftm (APPEND
-RUN
-REPEAT :ENDLOOP &LIMIT TIMES
-READFILE HKEYS
TABLE FILE CAR
PRINT COUNTRY AND CAR AND MODEL
WHERE COUNTRY EQ '&KEY'
ON TABLE HOLD AS HRESULTS
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN
-:ENDLOOP
TABLE FILE HRESULTS
PRINT COUNTRY AND CAR AND MODEL
END
The reason why you see your last result only is very simple. When you issue the -RUN, the stack is emptied and the input file is closed. Try it with the NOCLOSE option and, if needed, close it with the command -CLOSE filename