Hello Folks -
I have a view with the following logic:
WITH SALES_CHECK AS (
(
SELECT
ORG.LOCATION_ID,
SAL.MRP_ORGANIZATION_KEY,
SAL.MRP_CALENDAR_KEY,
SAL.MRP_DEPARTMENT_KEY,
SAL.SALES_RETAIL,
SUM(SAL.SALES_RETAIL) OVER(PARTITION BY ORG.LOCATION_ID) AS TOTAL_LOCATION_SALES
FROM F_DEPARTMENT SAL
INNER JOIN F_BUDGET BUD ON SAL.MRP_ORGANIZATION_KEY = BUD.MRP_ORGANIZATION_KEY
AND SAL.MRP_CALENDAR_KEY = BUD.MRP_CALENDAR_KEY
AND SAL.MRP_DEPARTMENT_KEY = BUD.MRP_DEPARTMENT_KEY
INNER JOIN D_ORGANIZATION ORG ON SAL.MRP_ORGANIZATION_KEY = ORG.MRP_ORGANIZATION_KEY
)
)
SELECT
SALES_CHECK.MRP_ORGANIZATION_KEY,
SALES_CHECK.MRP_CALENDAR_KEY,
SALES_CHECK.MRP_DEPARTMENT_KEY
FROM SALES_CHECK
WHERE TOTAL_LOCATION_SALES <> 0
The challenge is that this code is within a view and therefore, anytime someone views an OAC Dashboard/Report, the view is rerun each time. This is in turn yielding VERY long OAC rendering times.
My question is: Are there any tweaks to be made to the view to speed it up? Also, would you reccomend instead of a view, populate a "TEMP" table so it doesn't need to render each time?
Thanks!