[SOLVED] RDBMS-managed join disabled - in Report Painter but not InfoAssist
Hello Everyone,
I am running into an issue where a Report Painter is trying a "focus-managed join" which causes a performance issue. The exact same kind of report, however, runs totally fine in InfoAssist. Note that we are using a small fact table without a primary key. Below is the SQL trace first for Report Painter then InfoAssist.
Any ideas would be helpful. --- Using Report Painter:
FOC2510 - FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S): FOC2505 - A SEGMENT IN THE STRUCTURE IS NON-KEYED: STAR_FACT_INPATIENT_CENSUS _DAILY FOC2517 - FST. OR LST. WHERE SORT FIELDS DO NOT COVER KEY FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON: FOC2592 - RDBMS-MANAGED JOIN HAS BEEN DISABLED SELECT T1."DATE_KEY", T1."PATIENT_COUNT", T1."ALC_COUNT" FROM GRH_DMRT.STAR.FACT_INPATIENT_CENSUS_DAILY T1; FOC2641 - IF/WHERE TESTS ON UNIQUE SEGMENT COMMON_DIM_DATE WHERE NOT PASSED T O SQL SELECT T4."DATE_KEY", T4."FULL_DATE", T4."FISCAL_YEAR_NAME" FROM GRH_DMRT.COMMON.DIM_DATE T4 WHERE (T4."DATE_KEY" = ?) ORDER BY T4."DATE_KEY"; 0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
Using InfoAssist:
AGGREGATION DONE ... SELECT T4."FISCAL_YEAR_MONTH", SUM(T1."ALC_COUNT"), SUM(T1."PATIENT_COUNT") FROM GRH_DMRT.STAR.FACT_INPATIENT_CENSUS_DAILY T1, GRH_DMRT.COMMON.DIM_DATE T4 WHERE (T4."DATE_KEY" = T1."DATE_KEY") AND (T4."FULL_DATE" BETWEEN '20140101' AND '20141231') GROUP BY T4."FISCAL_YEAR_MONTH" ORDER BY T4."FISCAL_YEAR_MONTH"; 0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0This message has been edited. Last edited by: Josh Morel,
WebFOCUS 8004 Windows Server 2008 R2 Standard 64-bit All Outputs
February 09, 2015, 09:55 AM
Francis Mariani
Is it possible that the two reports point to different versions of the metadata (in different app folders)? There are declarations in an ACX file that *might* cause this to occur.
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
February 09, 2015, 11:12 AM
eric.woerle
Josh,
Can you post the focus code created in both scenarios, the another possibility is that they are not being created in the same way.
Eric Woerle 8.1.05M Gen 913- Reporting Server Unix 8.1.05 Client Unix Oracle 11.2.0.2
February 10, 2015, 11:17 AM
Josh Morel
Hi, by inspecting the focus code I found out that I had a compute without a by field in the Report Painter report. This caused the issue.
Thanks all!
WebFOCUS 8004 Windows Server 2008 R2 Standard 64-bit All Outputs