I saw a post this week regarding use of FOCCACHE. This would be a good solution for me with OLAP reports that I am developing.

Currently, I am using APP PATH and APP HOLD to create hold files in a particular application called HOLDTEMP. In order to keep a user from using the wrong hold file, I am creating user-specific hold file names by appending their userid to the hold file name. Then, when I run the OLAP report, I use the APP FI command so OLAP knows where to find the hold file.

Everything is running well with this setup, but one concern is that the hold files are not cleaned up. Because of how OLAP works, I cannot delete them after the report is run because they need to stay there as long as the user is using the OLAP functionality. So, I delete any hold files just before the step that creates them.

After I saw the post on FOCCACHE, I did some research on it and tried to implement it with my OLAP report. However, I received this error:

(FOC3245) ERROR ON PARSING TABLE IN OLAP

I checked the config settings on the server, and FOCCACHE is set to Y, and I can see the hold files being created in the FOCCACHE directory. However, I keep getting the above error when running the report.

Here is the code in question:

-*APP PATH HOLDTEMP
-*APP HOLD HOLDTEMP

-SET &CURRDAYSC = 'CURRDAYSC_' | &USERID;
-*CMD ERASE &APPROOT.EVAL\HOLDTEMP\&CURRDAYSC.EVAL.foc
-*CMD ERASE &APPROOT.EVAL\HOLDTEMP\&CURRDAYSC.EVAL.mas

DEFINE FILE CURRDAY
&METRIC_TWK_SC/I7 = IF &METRIC_TWK EQ 0 THEN 0 ELSE 1;
END
TABLE FILE CURRDAY
SUM &METRIC_TWK
BY STORE_ID
ON TABLE HOLD AS FOCCACHE/CURRDAYSC FORMAT XFOCUS
-*ON TABLE HOLD AS &CURRDAYSC FORMAT XFOCUS
END

-IF &FOCERRNUM NE 0 THEN GOTO END_TREND;

-OLAP ON

OLAP DIMENSIONS
Stores: COMPANY_NAME, REGION_NAME, DIVISION_NAME, AREA_NAME, DISTRICT_NAME, STORE_NAME;
END

-*APP FI &CURRDAYSC HOLDTEMP/&CURRDAYSC.EVAL.FOC

TABLE FILE FOCCACHE/CURRDAYSC
-*TABLE FILE &CURRDAYSC
SUM
     COMPUTE &METRIC_TWK_PERWK/P13.2 = &METRIC_TWK / &METRIC_TWK_SC ;
END
-RUN

I tried playing around with APP PATH/HOLD/FI using FOCCACHE as the directory, but that didn't help.

Does anyone know if FOCCACHE can be used with OLAP reports? If so, can you tell me what I am doing wrong?

Thanks!

This message has been edited. Last edited by: JRLewis,

It appears that simply adding -OLAP ON to the code causes the problem. I'd open a case with Tech Support.

-GOTO STEP2

-STEP1
TABLE FILE CAR
SUM
SALES 
BY COUNTRY
BY MODEL
ON TABLE HOLD AS FOCCACHE/HCAR001 FORMAT FOCUS INDEX COUNTRY MODEL
END
-RUN

-STEP2

-OLAP ON

TABLE FILE FOCCACHE/HCAR001
PRINT *
END
-RUN

First run with -GOTO STEP1, comment -OLAP ON.
Then run with -GOTO STEP2, uncomment -OLAP ON.

A possible workaround while you're looking for the solution: Create a second HOLD file from the FOCCACHE HOLD file, then issue the OLAP ON command and use this file for the OLAP request.

Thanks, Francis. I have opened a case, and I'll update this post when I have received a response/answer.

This works for me:

  
TABLE FILE GGSALES
PRINT * 
ON TABLE HOLD AS FOCCACHE/GOTSOME
END
-RUN
-OLAP ON
TABLE FILE GOTSOME
SUM DOLLARS BY CATEGORY BY PRODUCT
END

All the subsequent olap interactions go against the gotsome file stored in my foccache.

Brian, how come you don't have to say

TABLE FILE FOCCACHE/GOTSOME

According to the documentation for FOCCACHE, when it is turned on (which appears to be the default starting with release 7.6.7), FOCCACHE is appended to the front of the APP path for every new WebFOCUS session. So FOCCACHE becomes the first location searched for all file references. Therefore, despite what the documentation says, after a file has been stored in FOCCACHE (via ON TABLE HOLD/SAVE AS FOCCACHE/filename), the syntax TABLE FILE FOCCACHE/filename should not be necessary - and in fact is not: TABLE FILE filename works just fine.

It occurs to me that APP HOLD FOCCACHE might be used to direct all temporary session files to FOCCACHE so references to FOCCACHE in ON TABLE HOLD/SAVE would also not be necessary.

This message has been edited. Last edited by: Dan Satchell,

Brian, I tried to test with your code sample, and I was able to run an initial OLAP report. However, when I tried to sort the Dollars column, I received the error that the description could not be found for the GOTSOME file.

I have a case open with IBI and am working with someone there now. One possible issue is that it just doesn't work with the version of WebFOCUS we are using here.

Probably due to permission and version issues on my side, I was not able to get the FOCCACHE process to work with code provided by IBI. Because I have a working process in place, I have closed my case with IBI.

Thanks to everyone for the input on this!