June 08, 2005, 08:56 PM
<frankie>conditional row label
I have the below code executing fine, but the performance is unacceptable. I've been told the main reason for the poor performance is due to the fact I am grouping by a defined field (CBLYNN_A7).
How do I(can I?) group by the indicator (IS_CB_LYNN_IND) and still get row labels to be CB Lynn or Catalog
correctly?
Thanks,
Frankie
-SET &LST_YTD=DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')),'Y',-1)),'YYMD', 'I8YYMD');
-* Change month/day to Jan 1
-SET &CUR_BEG = EDIT(&DATEYY,'9999') | '0101';
-SET &LST_BEG = EDIT(&LST_YTD,'9999') | '0101';
-SET &CUR_EOM=DATECVT(DATEMOV((DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'M',-1)),EOM),'YYMD', 'I8YYMD');
-SET &LST_EOM=DATECVT(DATEMOV((DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'M',-13)),EOM),'YYMD', 'I8YYMD');
JOIN
RPTN_DIM_PERIOD.RPTN_DIM_PERIOD.PERIOD_KEY IN RPTN_DIM_PERIOD TO ALL
RPTN_FCT_DLY_ITM_SLS.RPTN_FCT_DLY_ITM_SLS.PERIOD_KEY IN RPTN_FCT_DLY_ITM_SLS AS J0
END
JOIN
RPTN_FCT_DLY_ITM_SLS.RPTN_FCT_DLY_ITM_SLS.FOB_KEY IN RPTN_DIM_PERIOD
TO RPTN_DIM_FOB.RPTN_DIM_FOB.FOB_KEY IN RPTN_DIM_FOB AS J1
END
DEFINE FILE RPTN_DIM_PERIOD ADD
CBLYNN_A7/A7=IF IS_CB_LYNN_IND EQ 'Y' THEN 'CB Lynn' ELSE 'Catalog';
YTD_2005/D12.2=IF YR_NBR EQ 2005 THEN ITM_EXT_PRC_US_AMT ELSE 0;
END
TABLE FILE RPTN_DIM_PERIOD
-*AS 'YTD Variance'
SUM
YTD_2005 NOPRINT
SUM
ITM_EXT_PRC_US_AMT/D13.2M AS ' '
BY
FOB_BUS_UNIT_NM AS 'Family, of Business'
BY
CBLYNN_A7 AS 'Product, Group '
ACROSS
YR_NBR AS 'Net Sls'
COMPUTE
Var_%/D6.2% = ( ( C5 - C2 ) / C2 ) * 100;
2005_%_Ttl/D6.2% = ( C4 / YTD_2005 ) * 100;
HEADING
"Product Sales Report"
WHERE ((( INV_DT GE &LST_BEG ) AND ( INV_DT LE &LST_EOM )) OR (( INV_DT GE &CUR_BEG ) AND ( INV_DT LE &CUR_EOM )))
AND ( FOB_BUS_UNIT_NM EQ '&Business_Unit.(Cronatron,Drummond,Kent,Lawson De Mexico,Lawson Products).Business Unit.' );
June 08, 2005, 09:23 PM
reFOCUSingI have some questions.
Is IS_CB_LYNN_IND is 'Y' or 'N'?
Why are you doing this?
...<br />SUM<br />YTD_2005 NOPRINT<br />SUM<br />...
How much data is being return by this report?
Have you tried moving DEFINE to a COMPUTE?
June 08, 2005, 09:58 PM
codermonkeyI'm assuming these tables are relational databases. In order to optimize requests to an RDBMS you need to ensure that the join, sort, aggregation and selection criteria get passed. Mutli-verb requests and sorting on defined fields typically disable optimization. Since the sort on CBLYNN_A7 is probably failing, all the aggregation is probably failing too. Which means your request is pulling back all the details rows and asking FOCUS to sort and aggrgate. I typically try to write an optimized RDBMS pull, hold it, then apply the defines and heavy formatting to the HOLD file. Something like this:
SUM ITM_EXT_PRC_US_AMT
BY FOB_BUS_UNIT_NM
BY IS_CB_LYNN_IND
BY YR_NBR
WHERE ((( INV_DT GE &LST_BEG ) AND ( INV_DT LE &LST_EOM )) OR (( INV_DT GE &CUR_BEG ) AND ( INV_DT LE &CUR_EOM )))
AND ( FOB_BUS_UNIT_NM EQ '&Business_Unit.(Cronatron,Drummond,Kent,Lawson De Mexico,Lawson Products).Business Unit.' );
ON TABLE HOLD AS HOUT1 FORMAT ALPHA
END
DEFINE FILE HOUT1
CBLYNN_A7/A7=IF IS_CB_LYNN_IND EQ 'Y' THEN 'CB Lynn' ELSE 'Catalog';
END
TABLE FILE RPTN_DIM_PERIOD
SUM
ITM_EXT_PRC_US_AMT/D13.2M AS ' '
BY FOB_BUS_UNIT_NM AS 'Family, of Business'
BY CBLYNN_A7 AS 'Product, Group '
ACROSS
YR_NBR AS 'Net Sls'
COMPUTE
Var_%/D6.2% = ( ( C5 - C2 ) / C2 ) * 100;
2005_%_Ttl/D6.2% = ( C4 / YTD_2005 ) * 100;
HEADING
"Product Sales Report"
END
I didn't include the YTD_2005 DEFINE because you might be able to accomplish that with row-total or column-total -- not sure. If not, add those elements back in as well.
June 08, 2005, 09:59 PM
<frankie>Is IS_CB_LYNN_IND is 'Y' or 'N'?
YES
===============
How much data is being return by this report?
2 rows / 6 columns (very little returned, however the fact table is ~ 50mil)
===============
Have you tried moving DEFINE to a COMPUTE?
Not exactly sure how to code/what you mean, something like?:
COMPUTE catalog/D12.2 = IF IS_CB_LYNN_IND = 'N' THEN AMT ELSE 0; AS 'Catalog'
COMPUTE cblynn/D12.2 = IF IS_CB_LYNN_IND = 'Y' THEN AMT ELSE 0; AS 'CB Lynn'
===============
Why are you doing this?
code:
-------------------------------
...
SUM YTD_2005 NOPRINT
SUM
...
-------------------------------
I was having a problem with my compute statement after the across verb, it wasn't recognizing YTD_2005 until I did this.
================================
Bare with me, new to language, trying to develop with dev studio etc.
June 08, 2005, 10:08 PM
<frankie>codermonkey,
thanks for the post. I will give your example a try and keep you posted. I didn't use row-total/column-total because I only wanted that calc on the current year (2005), when I tried using the other method, I would get it (pct of ttl) for all years.
June 09, 2005, 02:28 PM
reFOCUSingfrankie,
Since your final dataset is very small I would try to aggregate your data as much as you can before doing a DEFINE. This what codermonkey did in code that was posted.